Practice reading Excel formulas so you can figure out how they work and what they mean, especially if someone else created them.
If you ever use a spreadsheet that was created by someone else, you'll also need to read the formulas in that spreadsheet. Reading a formula written by someone else can be tricky—it may not be immediately obvious why someone chose a certain formula, or how it's working. If you're struggling to read a formula, don't panic! Many formulas are actually less complicated than they appear, but it may take some time and effort to figure out how they work.
With that in mind, we've put together a list of tips to help you read formulas. At the end of this lesson, we'll give you a chance to practice reading formulas with an interactive quiz!
You may also find the tips in our Double-Check Your Formulas lesson to be helpful.
It's important to remember that any cell can contain a formula, and it won't always be obvious which cells contain them. You may want to get in the habit of checking the formula bar to see if the currently selected cell contains a formula.
You can also double-click a cell that contains a formula to see what cells it's referencing.
You can also switch to formula view, which will display all of the formulas in the spreadsheet. This can help you understand how the spreadsheet is put together and where the formulas are stored. Just hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the upper-left corner of the keyboard. Press Ctrl+` again to switch back to normal view.
Whenever you read a formula, you'll need to think about why that formula was used, and what it's doing in the spreadsheet. For example, let's take a look at this spreadsheet—a running log our friend Sophie created:
It looks like Sophie didn't label the calculated value in cell B8. However, if we take a look at the formula we can see that she used the AVERAGE function to find the average of the values in cell range B3:B7. Based on the data in column B, we can reasonably guess that she used this formula to find the average distance for these different runs.
Remember the order of operations from math class? If not (or if you want a refresher), you can check out our Complex Formulas lesson. Your spreadsheet will always use this ordering, which means it doesn't just calculate a formula from left to right. If you're not sure how a formula is working, try walking through the order of operations to figure out how it's calculating a value.
It's also important to remember that whenever a formula contains a function, the function is generally calculated before any other operators, like multiplication and division. For example, in the formula below, the SUM function will be calculated before division:
If one function is nested inside another function, the innermost function will be calculated first. For example, in the formula =WORKDAY(TODAY(),3), the TODAY function will be calculated first.
You can review our lesson on Functions to learn more about how functions work within formulas.
Review the formulas in the interactive below and see if you can figure out how they're working: