In this article, we will review Excel’s top 10 formulas.
Whether you’re an analyst, accountant, student and or job seeker – you must know these tools. The list contains the top 10 formulas and tools, which we identified in a survey conducted in one of the Facebook groups we manage with over 40K users! Over 200 participants voted for the most useful formulas. The results and instructions for how to use them are detailed in this article!
1. VLOOKUP
VLOOKUP is a super helpful function that lets you search for a value in a table and return a corresponding value in another column! Whether you want to complete a table automatically, compare two datasets, or find a value from another sheet – VLOOKUP is what you’re looking for.
V is for vertical, as the lookup feature allows us to search for a value in a vertical table.
Most of the users will use VLOOKUP exact match, which is explained in our VLOOKUP tutorial
2. Pivot tables
Ask any excel expert and they will tell you that this tool is a must! With only three clicks, pivot tables will allow you to create super powerful reports and use many tools such as filter, sorting and charts!
Learn more about Pivots or click here to download the Pivot table example
3. SUM
If you ever used a calculator – you probably know how tedious it is to add many items into the sum line.
Sum function will allow you to add up cells together. In 5 seconds, you’ll be able, to add up, thousands of rows.
Learn and practice SUM here
4. SUMIF
Now let’s assume that you would like to add cells together by criteria. You can easily do it by using SUMIF formula.
This formula (and its sisters SUMIFS, COUNIFS and average if) will quickly help you with aggregating many cells tougher
5. SUMIFS
If you liked SUMIF – you would love SUMIFS. SUMIFS is very similar to SUMIF, however – it allows summing cells by multiple criteria
For example, we would like to sum only cells that match more than one condition – SUMIFS is precisely for that!
if you would like to apply other operations than SUM – you can use COUNTIFS for counting and AVERAGEIFS for averaging!
Learn and practice SUMIFS here
6. Charts and Graphs
Charts and Graphs are great for presentations and quantitative storytelling. We are all familiar with charts from newspapers, TV, School and sports. With Excel, you can easily create charts from many different kinds! More on charts here
7. IF
IF function checks whether a statement is TRUE or FALSE and lets you choose the value for each of the case. Use the simple IF formula to quickly check if one cell is greater/equal/less than another one.
8. Conditional Formatting
Yes, Excel is boring! So why don’t we add some color to it? Reports and analysis papers could be much easier to read if we highlight the points that we want to focus on with conditional formatting.
Simply make your spreadsheet return red for negative or green for positive, in fact, you can control any formatting attribute you’d like! Such as bolding, underline, or even sizing a font! More on Conditional formatting here
9. Filter
Quickly want to hide all irrelevant values? Filter is a great tool that would visually help you analyze and structure your report better!
10. Index & Match
This combination of formulas is Like VLOOKUP – only better! Even though Vlookup is super popular, it is not so flexible. With index&match you can use any column as the lookup column.
Learn and practice index & match here!
