Now that we know how to move around quickly throughout a spreadsheet, as well as how to format, we can finally jump into making formulas and creating analysis! I contemplated doing these first, but I felt that navigation and formatting were more "foundational," to help make Excel less intimidating by being able to move around quickly within it.
While financial models may look massive and daunting (especially to those who have never built one from scratch), they actually are comprised of simple arithmetic. Spreadsheets are capable of much more, but for most people, all you need to know is how to add, subtract, multiply, and divide coupled with some logic behind it all.
The first thing to know about spreadsheet formulas is that you can start a formula by typing = or +. *I like using + because it's close to the rest of the numbers in the number keypad and it's a large button that you don't have to look at versus the = button next to the backspace button.* Once you start the formula with one of these two keys, you can either start typing a number, a formula or just start moving the arrow keys to reference another cell.
For practice, in Excel, Subset, or your favorite spreadsheet let's press = and navigate to another cell using the arrow keys and press enter (if it's not working, that means you aren't referencing another cell and you're just moving your cursor inside the cell, so press F2 and try again). When you do this, you are now referencing another cell. This means if there's an output or answer in another cell, it will show up in this cell because it is now linked. If you press F2 to audit the selected cell, it will show the location of the referenced cell in A1 notion (it will show you the cell column and then the row). If there are $ signs before the letter or the number, it just means that the column or row with the sign before it is locked (more on this later).
In order to sum something up, you could use the SUM function.
This is another foundational skill to learn. In most spreadsheets, as soon as you type in a formula, there should be a dropdown on how the function works. Sometimes the text is intuitive and other times you may have to Google it, but get in the habit of looking at a complex formula and breaking it down into manageable pieces. So when you type in =SUM (you can see that its prompting you to finish the formula with [number1],[number2]...). This is prompting you to select the numbers you want to add together.
The rest of the arithmetic operations are exactly how you would write out a math formula.
Remember when you do these you want to use your keyboard only. So instead of typing = or + and clicking cells and typing in +,-,x,/ you should be using your arrow keys to navigate to the cells. Use a combination of CTRL and ARROW KEYS to help you navigate around quicker. Another tip is to try and use your number pad on your keyboard (if you have one). While this may be uncomfortable at first, this will really increase your speed of inputting in numbers with less mistakes and also make it easier for you to add, subtract, multiple, and divide, since all of those buttons are also right on the keypad. While this may feel tedious at first, you’ll thank me later when you can navigate through a spreadsheet much faster.
Once you write one of these simple formulas, usually you can reuse the same formula in the same row or column. This is the power of a spreadsheet on the computer versus doing math on pen and paper: you can reuse formulas. In order to do this, you need to highlight the range that you want to copy the formula (with the first cell having the formula you want to copy to the right or down). If you'd like to copy to the right, press CTRL+R (R for "Right") and if you'd like to copy down, press CTRL+D (D for "Down"). Reminder, in order to select entire ranges, you need to hold SHIFT. Example below:
When you use CTRL+R or CTRL+D, you have to be careful that your references are locked appropriately. The way you lock a row or column is by typing in a “$” before the letter or number of the reference. Pro tip: Instead of physically typing $ before the letter or number, you can press F4 when you are auditing the cell to cycle through the different options.
A1 = Nothing is locked
$A1 = The column is locked, but the row is unlocked.
A$1 = The column is unlocked, but the row is locked.
$A$1 = Both the column and the row are locked
Play around with trying to drag different variations of the same referenced cell. This is one of the nuances in spreadsheets that makes it extremely powerful and efficient when used correctly, but also more error prone.
