Knowing your way around Excel formulas can seem quite daunting, especially if you’re a beginner in using the spreadsheet. But if you wish to be proficient in financial analysis, you have to know how to insert data efficiently. Microsoft Excel is considered the king of all software in terms of data analysis and is the most preferred software by financial analysts and investment bankers. If you’re looking to master the art of financial modeling, presentation, and data processing, then you have to understand the basics first.
In this guide, we’ll be going over a couple of tips that will help you grasp the fundamentals of Excel formulas and apply it when using the spreadsheet. We also provide excel courses in Perth WA for anyone looking to explore further and better their Excel knowledge.
Performing calculations in Excel
The first thing you need to know is how to perform basic calculations in Excel. This can be done using formulas and functions.
- Formulas in Excel are expressions that work on values in a cell or a range of cells (e.g. =A1+A2+A3) which defines the sum of the range of values from the A1 cell to the A3 cell.
- Functions on the other hand, are predetermined formulas. With functions, you don’t need to manually enter formulas and gives them human-friendly names for you to remember. An example would be =SUM(A1:A3) that sums up the values from the A1 cell to the A3 cell.
Now let’s focus on inserting basic excel formulas in a way that’s efficient and time-saving. Each method comes with its own advantages and it’s up to you to decide on how you want to apply them.
1. Inserting a formula inside the cell
One of the most straightforward ways to insert basic Excel formulas is to type it in a cell or the formula bar. The process begins by typing an equal sign that’s followed by the Excel function’s name. Excel is quite smart when you type in the function’s name by displaying a pop-up list where you can choose your reference.
Once the list appears, you want to press the Tab key so you can insert other options. Don’t hit Enter as this can lead to an invalid name error, often as ‘#NAME?’. If this problem appears, you can fix it by reselecting the cell and go to the formula bar to complete the function.
2. Take full control of inserting functions
One handy tip to take full control of inserting functions is by utilising the Excel Insert Function dialogue box. Simply click the Formulas tab and choose the first menu with the “Insert Function” label. The dialogue box will reveal all the functions you need in completing your financial analysis.
3. Go to your favorite functions quickly
This method works great if you want to go to your favorite functions as quickly as possible. You can find this menu by going to the Formulas tab and choose your preferred group. Click it to reveal a sub-menu that contains a list of functions and from there, you can choose your preference. If you notice that your preferred group doesn’t appear on the tab, go to the More Functions option where it’s most likely hidden.
4. Take advantage of the AutoSum option
The AutoSum function is your bread and butter when it comes to simple everyday tasks. As the name implies, AutoSum sums up a column or row of numbers and makes it easier for you to add together a range of cells. You can find the AutoSum function in the Home tab at the very right corner and click it to reveal other hidden formulas.
You can also use this option when you’re in the Formulas tab which appears right after the Insert Function option.
5. Avoid retyping your most recent formula with Recently Used Tabs
There’s no denying that retyping your most recent formula can feel quite tedious. Fortunately, you can work around this by using the Recently Used menu. You can find it on the Formulas tab, which is the one with the star logo on the book.
Examples of Excel formulas for beginners
1. SUM
Sum is Excel’s most basic formula. It adds up the values in a given column or row, which is great if you’re looking to calculate totals across data tables. Here’s an example:
2. AVERAGE
Once you’ve learned about SUM, the next thing you should familiarise yourself with is AVERAGE. This function takes the average of the values in a given column or row and works great for basic calculations such as rolling averages. Take a look:
3. COUNT
The COUNT function helps you identify which cells in a given range contain numeric values only. It skips empty cells as well as cells with non-numerical values. Here’s an example.
Things to remember about basic Excel formulas
- All Excel formulas start with an equal sign or else, an error will appear.
- When entering any text value, it should be given with inverted commas (“”) instead of a cell address.
- Make sure that the cell format is in general before entering the function. The formula will not work if Text format is selected.
- Keep in mind that spaces are counted as a single character. Therefore, a cell with even a single space will not be considered a blank cell so make sure to keep an eye on this and double-check each blank cell.
