Keep It Simple: Episode 02
Welcome back to another episode of "Keep it Simple"! Before we dive into the formulas, I want to share a little insight. I've seen some Excel enthusiasts make their workbooks super intricate, almost like a maze.
You might be thinking, "Is that what it takes to be an Excel pro?" Well, not really.
A good Excel user? They get things done, plain and simple. But the best Excel users? They tackle complex tasks quickly, but in a way that's clear and straightforward.
The real test? When they can pass their work to a newbie, and that person can pick it up, run with it, and truly get it. That's the goal.
Whenever I onboard a new financial analyst to the team, there's a foundational set of formulas I always introduce them to. These aren't the most advanced functions Excel has to offer, but they're crucial stepping stones to mastering the software. Surprisingly, many regard functions like VLOOKUP as high-tier, when in reality, they're just the tip of the iceberg. These formulas lay the groundwork for their future in financial analysis. By starting with this essential list, newcomers can begin to appreciate the depth and power of Excel. It's my hope that this introduction inspires them to explore further and truly harness the vast capabilities of this versatile tool.
Now, with that perspective, let's jump into 10 essentials Excel formulas...
 1. SUM() - Adding Numbers Together
- Example: If you have numbers in cells A1 through A5 and you want to get their total, simply use =SUM(A1:A5). Excel will do the math for you!
 2. AVERAGE() - Finding the Mean
- Example: To find the average of numbers in cells B1 through B5, use =AVERAGE(B1:B5). It's that easy!
- Example: To find the highest number in cells C1 through C5, use =MAX(C1:C5). For the smallest number, use =MIN(C1:C5).
- Example: If you want to know how many cells in the range D1:D5 have numbers, simply type =COUNT(D1:D5).
- Example: Let's say you want to categorize sales in cell E1 as "High" if they're over 100, and "Low" otherwise. Use =IF(E1>100, "High", "Low").
- Example: If you have a table of products and their prices, and you want to find the price of a specific product, VLOOKUP can help. If your product names are in column F and prices in column G, and you're searching for the price of the product in cell H1, use =VLOOKUP(H1, F:G, 2, FALSE).
- Example: To join the words in cells I1 and J1, simply use =CONCATENATE(I1, " ", J1). This will give you a single string with the words from both cells.
- Example: If you have the text "ExcelRocks" in cell K1 and you want to extract the first 5 characters, use =LEFT(K1, 5). For the last 5 characters, use =RIGHT(K1, 5). And to extract 5 characters starting from the 3rd character, use =MID(K1, 3, 5).
- Example: If you want a cell to always display the current date, simply type =TODAY().
- Example: To find out how many characters are in cell L1, use =LEN(L1).
Â
Bonus Formula: IFERROR() - Handling Errors Gracefully
Ever had a formula that sometimes gives you errors like #DIV/0! or #VALUE!? It can be a bit unsightly, especially if you're sharing your workbook with others. Enter the IFERROR() formula. It lets you decide what should be displayed when a formula results in an error.
Example: Imagine you have a division formula in cell A1: =B1/C1. If C1 is zero, this will result in a #DIV/0! error. Instead of showing this error, you can use =IFERROR(B1/C1, "Not Applicable"). This means if there's an error in the division, "Not Applicable" will be displayed instead.
Why is this a great bonus? Because it's all about presentation. With IFERROR(), you can ensure your worksheets always look polished and professional, no matter the data input. It's a small touch that can make a big difference, especially when sharing your work with others.
Â
These are just the tip of the iceberg when it comes to Excel formulas, but mastering them will give you a solid foundation to tackle more complex tasks. Remember, practice makes perfect. So, play around with these formulas, experiment with different scenarios, and soon, you'll be an Excel pro!
What about you? Did I miss a formula that was a game-changer for you when you were starting out with Excel? Let me know in the comments below – I'd love to hear your insights and recommendations for beginners!
Did you find this episode helpful? Share it with friends who might benefit from it! And if you're hungry for more Excel and Power BI tips, don't forget to sign up for our weekly newsletter. Until next time, keep it simple!
