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

By Andrew Lokenauth

By Andrew Lokenauth

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Andrew Lokenauth (Fluent in Finance)

My thoughts on money, investing, personal finance, stocks, crypto and building wealth. Subscribe at BeFluentInFinance.com