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:
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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
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
If you learned something valuable today:
1. Follow me @theantonioreza for more
2. RT the first tweet so more people can learn
https://twitter.com/theantonioreza/status/1603026821959802881
• • •
Missing some Tweet in this thread? You can try to
force a refresh
