Even though Python, R, and other data tools have taken center stage in recent years, I still find myself using Excel regularly as it still is one of the most versatile, accessible, and widely-used data analysis tools in the professional world.
When I first started working with spreadsheets, I actually underestimated the power of Excel formulas. Back then, Excel was just a glorified calculator for me. However, when I had to organize and analyze a sales report without access to any other tools I started to learn and appreciate Excel’s many capabilities. The right formula combinations not only saved me hours of work but also impressed some of my peers with quick insights.
In this cheat sheet, I’ve compiled the Excel formulas I’ve found most valuable so far in my experience, from basic calculations to advanced data manipulations. It doesn’t matter if you are an analyst, business professional, or just someone who works with numbers, these formulas will help you work more efficiently and extract more insights from your data.
Basic Formulas Every Professional Should Know
I’m convinced that even the simplest Excel formulas can be powerful when used correctly. These basics are the foundation of more complex analyses:
SUM and AVERAGE
I use these functions constantly to get quick statistics on numerical data. They seem way too basic but combining them with other functions can produce powerful results.
For example, I once needed to calculate sales performance by region, and a simple use of AVERAGE() along with IF() gave me instant regional averages without needing to filter the data.
COUNT Functions
For me, counting functions are invaluable for quick data checks. When I receive a new spreadsheet, one of my first steps is using COUNTIF to check for patterns.
For instance, this quickly tells me how many completed orders I’m working with.
IF Statement
The IF function is my go-to for creating conditional values. I have used nested IF statements to create categorization systems and they work great. Having too many nested statements can make things confusing, though.
For example, if we wanted to create a super simple categorization system, this would do the trick:
With this simple formula we can instantly convert numeric grades to letter grades, saving hours of manual categorization.
Intermediate Formulas That Will Make You More Efficient
These formulas separate Excel novices from power users. Mastering these will significantly boost your productivity:
VLOOKUP vs. INDEX + MATCH
VLOOKUP is probably the most famous Excel formula after SUM as it’s super versatile.
However, I learned the hard way that it has limitations. it can only look up values to the right of your lookup column. That’s why I sometimes prefer to use INDEX + MATCH:
The INDEX + MATCH combination is more flexible because it can look in any direction. I once had to analyze a customer spreadsheet where the data I needed was to the left of my lookup values. VLOOKUP couldn’t handle it, but INDEX + MATCH worked perfectly.
TEXT Functions
These text functions are super helpful when I need to clean and standardize text data:
Perhaps some of these text functions can save you countless hours if you ever need to clean contact information, something that once happened to me. I received a spreadsheet with inconsistently formatted names (some ALL CAPS, others with extra spaces) and this simple use of PROPER() instantly standardized the entire column.
Date and Time Functions
Date calculations are often tricky but these functions simplify them:
For example, if I need to deal with project timelines, I use NETWORKDAYS to calculate realistic delivery dates that account for weekends.
Advanced Formulas That Will Set You Apart
These powerful formulas are the ones I believe separate Excel experts from the rest as they combine multiple functions and use Excel’s more advanced capabilities:
Array Formulas
Array formulas are great for performing multiple calculations at once. In older Excel versions, you may have to enter them by pressing Ctrl+Shift+Enter:
I love array formulas because with them I can sum values based on criteria without needing helper columns. I normally use them to summarize information where I need conditional statistics.
INDIRECT
INDIRECT converts text strings into valid cell references. This may sound simple, but this allows for crazy dynamic formulas.
This was super useful for me when I created a monthly report. With INDIRECT I could pull data from different sheets based on the selected month.
Dynamic Named Ranges
This technique combines OFFSET and COUNTA to create ranges that automatically expand with your data:
Named ranges like this are game-changers for creating flexible reports. I have used them for charts that automatically update when new data is added, without needing to manually adjust the data range. They feel like magic.
Real-Life Scenarios
Throughout my career, I’ve encountered spreadsheet challenges that required thinking out of the box. Here’s one that showcases the power of combining formulas:
Finding Duplicates Across Multiple Criteria
Identifying duplicate transactions in a financial spreadsheet normally isn’t that big of a deal but if the duplicates aren’t 100% exact (same date, amount, and vendor but different transaction IDs), things can turn complicated.
This can be solved with this:
With this formula we can flag potential duplicates by looking for transactions that share the same date, amount, and vendor. Perfect to avoid reconciliation errors.
Creating Dynamic Date Ranges
For a monthly sales report, I needed to compare “current period” to “previous period” where the periods could be defined by the user.
This was a non-issue with this:
Combined with dynamic named ranges based on user inputs, this formula allows flexible period-over-period comparison without complex reconfiguration.
Cheat Sheet Summary Table
Table 1: Essential Excel Formulas for Professionals
| Formula | Description | Example |
| SUM | Adds numbers | =SUM(A1:A10) |
| AVERAGE | Calculates the arithmetic mean | =AVERAGE(A1:A10) |
| COUNT/COUNTA | Counts cells with numbers/non-empty cells | =COUNT(A1:A10) |
| COUNTIF | Counts cells meeting criteria | =COUNTIF(A1:A10,”>100″) |
| IF | Performs conditional logic | =IF(A1>10,”High”,”Low”) |
| VLOOKUP | Finds values in a table by row | =VLOOKUP(“Smith”,A1:C20,3,FALSE) |
| INDEX + MATCH | More flexible lookup method | =INDEX(C1:C20,MATCH(“Smith”,A1:A20,0)) |
| CONCATENATE | Joins text strings | =CONCATENATE(A1,” “,B1) |
| LEFT/RIGHT/MID | Extracts portions of text | =LEFT(A1,5) |
| SUMIFS | Sums with multiple conditions | =SUMIFS(sum_range,criteria_range1,criteria1…) |
| IFERROR | Handles errors gracefully | =IFERROR(formula,”Error message”) |
| OFFSET | Returns a reference offset from a starting point | =OFFSET(A1,1,1,5,5) |
| INDIRECT | Converts text to cell reference | =INDIRECT(“A”&ROW()) |
Keyboard Shortcuts & Productivity Hacks
Knowing the right formulas is definitely super important but using keyboard shortcuts probably has saved me countless hours in Excel:
- F2: Edit cell
- Ctrl+Arrow Keys: Move to edges of data regions
- F4: Toggle absolute/relative references (essential for copying formulas)
- Alt+Enter: Start a new line within a cell (great for documentation)
- Ctrl+Shift+L: Toggle filters on/off
- Ctrl+1: Format cells dialog
Named Ranges
My most-used productivity hack is defining named ranges for data that I reference often. This way, instead of typing A1:A100 repeatedly, I can name that range something like “Sales” and simply use that name in formulas. It makes formulas more readable and easier to maintain.
Excel Tables
Another important time-saver is using Excel Tables (Ctrl+T). They automatically expand when new data is added and provide structured references in formulas. For example with an Excel Table, =AVERAGE(Table1[Sales]) will always calculate the average of the entire Sales column, even as rows are added.
Wrapping Up
Excel certainly isn’t the newest or trendiest tool, but its flexibility, accessibility, and formula power make it indispensable for professionals who work with data. I use it in my normal workflow even if I have more specialized tools available to perform certain quick analyses and reporting.
The formulas I’ve shared in this cheat sheet have saved me countless hours and made my experience working with Excel more enjoyable.
There are formulas for nearly every data manipulation need. I encourage you to bookmark this page and experiment with the ones I’ve shared on your own spreadsheets. Start with the basics if you’re new to Excel, or challenge yourself with the advanced formulas if you’re already comfortable with the fundamentals.
What are your most used Excel formulas? Have you developed creative solutions to unique spreadsheet challenges?
If you feel you’re ready to take your data skills beyond Excel, take a look at Udacity’s Data Analyst Nanodegree which builds on these concepts and teaches you how to work with more complex datasets using Python and SQL.
If you are interested in business applications, the Business Analytics Nanodegree might be what you’re looking for. And if you’re just getting started with data analysis, the Introduction to Data Analysis course provides a solid foundation in fundamental data skills.
