10 Excel Functions You Must Know
10 Microsoft Excel functions you must know:
(5) Pivot Tables
XLookup is an upgrade compared to VLOOKUP or Index & Match.
Use the XLOOKUP function to find things in a table or range by row.
Formula: =XLOOKUP (lookup value, lookup array, return array)
A wildcard is a special character that allows you to perform partial matches in your Excel formulas. Excel has three wildcards: • asterisk “*” • question mark “?” • tilde “~”
Sparklines allow you to insert mini graphs inside a cell to provide a visual representation of data. Use sparklines to show trends or patterns in data. On the ‘Insert tab’, click ‘Sparklines’
The FILTER function allows you to filter data based on a query. For example, you can filter a column to show a specific product or date.
You can also sort in ascending or descending order.
The shortcut for this function is CTRL + SHFT + L
5) Pivot Tables:
A powerful tool to calculate, summarize & analyze data, which allows you to compare or find patterns & trends in data. To access this function, go to “Insert” in the Menu bar, and then select “Pivot Table”
The IF function makes logical comparisons & tells you when certain conditions are met.
For example, a logical comparison would be to return the word “Pass” if a score is >70, and if not, it will say “Fail” An example of this formula would be =IF(C5>70,”Pass”,”Fail”)
SUMIFS sum the values in a range that meet multiple criteria.
For example, use it if you want the sum of two criteria, for example, Apples from Pete. The formula is SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)