# 12 Microsoft Excel functions that will increase your productivity and make you feel like an expert!

**I’ve worked 15+ years in Finance and use Microsoft Excel daily, here are 12 Excel tips & functions that will increase your productivity and make you feel like an expert:**

(1) XLOOKUP

(2) Filter

(3) Pivot Tables

(4) Auto-fill

(5) IF

(6) SUMIF

(7) SUMIFS

(8) COUNTIF

(9) COUNTIFS

(10) UPPER, LOWER, PROPER

(11) CONVERT

(12) Transpose

**Let’s discuss each in detail (with examples):**

**(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) Filter**

The FILTER function allows you to filter a range of 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

**(3) 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”

**(4) Auto-fill**

With large data sets, instead of typing a formula multiple times, use auto-fill. There are 3 ways to do this:

(1) Double click mouse on the lower right corner of a 1st cell, or

(2) Highlight a Section and type Ctrl + D, or

(3) Drag the cell down the rows.

**(5) 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”)

**(6) SUMIF**

Use this to sum the values in a range, which meet a criteria.

For example, use this if you want to figure out the number of sales for a given region.

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

**(8) COUNTIF**

Use COUNTIF to count the number of cells that satisfy a query.

For example, you can count the number of times a particular word has been listed in a row or column.

**(9) COUNTIFS**

CountIf counts the number of times a criteria is met.

For example, it counts the number of times that both, a (1) apples and (2) A price > $10, are mentioned.

**(10) UPPER, LOWER, PROPER**

=UPPER, Converts text to all uppercase,

=LOWER, Converts text string to lowercase,

=PROPER, Converts text to proper case

**(11) CONVERT**

This converts a number from one measurement to another.

There are multiple conversions that you can do.

An example is meters to feet, or Celsius to Fahrenheit.

**(12) Transpose**

This will transform items in rows, to instead be in columns, or vice versa. To transpose a column to a row:

1. Select the data in the column,

2. Select the cell you want the row to start,

3. Right click, choose paste special, select transpose

**Which functions, formulas or shortcuts would you add?**

**If you enjoyed this read, feel free to follow me here on medium or clap/ like this article. Also, feel free to also follow me on other social media:**

**Twitter**: @FluentInFinance

**Youtube**: @FluentInFinance

**My Newsletter on SubStack**: The Newsletter

**My Blog on Medium:** Andrew Lokenauth on Medium

**Facebook Page**: @FluentInFinance

**Instagram**: @FluentInFinance

**TikTok**: @FluentInFinance

**LinekdIn**: Linkedin.com/in/lokenauth

**My Reddit Community:** r/FluentInFinance

**Facebook Group**: Fluent in Finance: Investing, Stock Market, Crypto & Personal Finance Group | Facebook

#MicrosoftExcel #Excel #ExcelTips #ExcelAdvice