Thread by @theantonioreza

I was a Senior Finance Manager at Microsoft from 2019 to 2021.

I built hundreds of complex financial models in Excel that helped close billion-dollar deals.

If you want to use Excel like a PRO, start by learning these 10 things:

1/ SUMIFS

Use it to add up values that meet one or two criteria.

In English:

SUMIFS(values you want to add, condition 1 range, condition 1)

Ex: Give me the total downloads for each podcast.

2/ XLOOKUP

Use it to search for a value in a column and return the corresponding value from another column.

In English:

XLOOKUP(value to find, column to look in, what do you want to get, what to do if not found)

Ex: Give me the total downloads on June 19, 2022.

3/ SUBTOTAL

Use it to add up values in a column but excludes anything that is filtered away.

In English:

SUBTOTAL(SUM[you have to type '9'], column you want to add up)

Ex: Give me the total downloads when I exclude the On Purpose podcast.

4/ IFERROR

Use it to stop showing error messages such as #N/A or #DIV/0! in your file.

In English:

IFERROR(your formula, error message to show)

Ex: Give me the ratio of 5 star ratings to 1 star ratings.

5/ Conditional formatting

Use it to highlight important values that need to stand out in your dataset.

In English:

• Select column
• Go to Home > Conditional formatting > New rule

Ex: Highlight in green if total downloads in a given day were higher than 7,000.

6/ Data validation

Use it to avoid mistakes in data input. Create a drop-down list with the options you want.

In English:

• Create a list
• Select the column
• Data > Data validation
• Allow > List > Select list > OK

Ex: Give your 1 to 5 rating to each podcast

7/ Pivot tables

Use it to summarize large amounts of data into tables.

In English:

• Select table
• Insert > Pivot table > OK
• Drag the fields into rows, columns, and values

Ex: Create a table that summarizes each podcast with total downloads per month.

8/ Transpose

Use it to reorganize your data from columns to rows, or vice versa.

In English:

• Select table > Copy it
• Paste > Transpose

Ex. Reorganize the data so that the months are the columns.

9/ Slicers

Use it to filter your data by clicking a button.

In English:

• Select table
• Inser > Slicer
• Select the field you want

Ex: Create a slicer that lets you filter out each podcast.

10/ Goal seek

Use it to find the value needed to yield a desired outcome.

In English:

• Data > What if analysis > Goal Seek
• Select set cell > input desired value
• Select cell that needs to change to yield desired outcome

Ex: Give me the grade needed to get a 92 average.

TL;DR

10 tips to become an Excel PRO

• Conditional formatting
• Data validation
• Pivot tables
• SUBTOTAL
• Transpose
• XLOOKUP
• Goal seek
• IFERROR
• SUMIFS
• Slicers

I hope you enjoyed reading this thread.

If you learned something valuable today:

1. Follow me @theantonioreza for more
2. RT the first tweet so more people can learn