10 Excel Functions You Must Know

10 Microsoft Excel functions you must know:

(1) XLOOKUP

(2) Wildcards

(3) Sparklines

(4) Filter

(5) Pivot Tables

(6) IF

(7) SUMIFS

(8) COUNTIFS

(9) Transpose

(10) TRIM

(1) XLOOKUP:

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)

(2) Wildcards:

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 “~”

(3) Sparklines:

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’

(4) Filter:

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”

(6) IF:

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”)

(7) SUMIFS:

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], …)

(9) Transpose:

This will transform items in rows, to instead be shown in columns or vice versa.

To transpose a column to a row: • Select the data in the column • Select the cell you want the row to start • Right click, choose to paste special, select transpose

(10) TRIM:

TRIM removes the extra spaces in data.

TRIM can be useful in removing irregular spacing from imported data =TRIM()

7 tools every Excel user must know

10 Excel shortcuts you must know

Jobs where Excel skills will pay you handsomely