Excel is one of the most powerful accounting software tools out there, and most of that power comes from the ability to use Excel formulas to automate your work. When you’re first learning how to use them, it’s easy to get a little overwhelmed – luckily, this Excel formulas cheat sheet is here to help you make sense of it all!
Let’s take a look at the basics of formulas, some common functions you’ll use with them, and how to keep them organized no matter how complex they get.
A lot of people use the terms “function” and “formula” interchangeably, but they’re actually two different things. A formula begins with an equal sign followed by an expression, which may include one or more functions. A function starts with a name and is typically followed by arguments enclosed in parentheses.
Here’s an example of a formula that uses the COUNT function:
=COUNT(A1:A15)
You can see how the formula uses the COUNT function to compute the result, which is the count of numbers in the A1 through A15 cell range.
The list below shows a small sample of the Excel functions that you’ll probably find yourself using often.
| SUM | Calculates the sum of the determined values | SUMIFS | Calculates the sum of a set of values where one or more conditions are met |
| AVERAGE | Finds the average value in a group of values | MEDIAN | Finds the median value in a group of values |
| MIN | Calculates the smallest number in a range | MAX | Calculates the highest number in a range |
| COUNT | Counts the number of numeric cells in a range | COUNTIFS | Counts the number of cells in a range where one or more conditions are met |
When working with names, addresses, customer lists, or any other text-based data, Excel’s text functions come in handy.
Some of the text functions you’ll use often include:
| LEFT | Returns one or more characters from a text string’s left side | RIGHT | Returns one or more characters from a text string’s right side |
| MID | Returns one or more characters from the middle of a text string | CONCAT | Merges two (or more) strings of text |
| UPPER | Converts a string of text to all uppercase | LOWER | Converts a string of text to all lowercase |
| TEXT | Converts a number to text | VALUE | Converts a text to a number |
The lookup functions are useful for searching through large amounts of data. While most Excel users have their own favorite Lookup function (or pair of functions), each one has perks that works best in different situations.
| VLOOKUP | Vertically searches for a value in a table | HLOOKUP | Horizontally searches for a value in a table |
| INDEX | A lookup function that searches a table both vertically and horizontally | MATCH | Returns the location of a value in a sequence |
| XLOOKUP | An updated version of VLOOKUP available in newer Excel versions | OFFSET | Moves the cell reference by the number of given rows and/or columns |
While those are a handful of functions you’ll often use in your formulas, it’s far from the whole list. If you need the full list of Excel functions, Microsoft has them conveniently organized by category!
Formulas are, for the most part, totally customizable based on the needs of the user. Which ones you use most frequently will vary based on the type of work you need to do.
However, you can still get some practice by working with some simple formulas. Once you master the basics, creating customized, complex formulas will be a lot easier – and even fun!
Formulas can contain:
For some good practice with basic Excel formulas, check out these posts:
Once you’ve gotten comfortable with creating simple formulas, you’ll want to start using two tools that come in handy when they become more advanced: range references and the LET function.
You can refer to data in other parts of a worksheet by providing range references in the formula or function arguments. You can reference cells (or a range of cells) when creating either a basic formula or one that includes a function. For example, let’s say you choose to reference cell B2 – the formula will calculate the result using the value of that cell. The formula will automatically recalculate its result when the value in that cell changes.
In Excel, you can use two types of cell references:
When writing a formula, you can mix relative and absolute references so that the row changes but the column does not, or vice versa.
To assign absolute references, you’ll use a dollar sign ($). Where you insert it in the cell reference determines whether the column, row, or both are absolute. It can sound a little confusing, but it’s not too difficult to learn once you see a couple of examples!
Let’s say you needed to reference cell B2 again. Here’s how you’d assign the reference type:
=B2 – The reference is completely relative
=$B2 – The column (B) is absolute, but the row (2) is relative
=B$2 – The column (B) is relative, the row (2) is absolute
=$B$2 – The reference is completely absolute
When you need to repeat the same calculation across numerous rows or columns, relative references come in handy. Absolute references are useful when you need to keep a reference constant, for example, if you needed to calculate a single sales tax percentage across a range of cells.
No Excel formulas cheat sheet would be complete without mentioning the useful LET function! Excel formulas are usually pretty short and sweet. As a result, they’re simple to comprehend and maintain over time. An example of a short formula looks something like this:
=SUM(B2:B8)
This formula uses the SUM function to add the values from cell B2 to cell B8. Pretty simple, right?
As spreadsheets grow more complex, the formulas tend to get more complicated as well. You may wind up with cumbersome formulas that are difficult to read and maintain, often due to duplication. For example, a formula with several duplications may look like this:
=IF(VLOOKUP(B8,Table1,2,0)>100, VLOOKUP(B8,Table1,2,0), VLOOKUP(B8,Table1,2,0)*C8)
You can see that VLOOKUP(B8, Table1, 2, 0) is used three times, making the formula lengthy, repetitive, and a bit confusing. The LET function allows you to assign names to the values in your formulas, and then use the names as needed throughout the rest of the formula.
It can take that confusing formula above, and turn it into something much easier to read:
=LET(Rate, VLOOKUP(B8,Table1,2,0), IF(Rate>100,Rate,Rate*C8))
Using the LET function is considered more of an advanced skill, but beginners can start using it in simpler functions to get familiar with how it works.
In Excel, formulas can be used to calculate just about anything you can think of – you don’t have to be afraid of getting a little creative. While this Excel formulas cheat sheet covers the essentials of Excel formulas, the best way to get really familiar with them is to practice by having Excel compute as many values as possible. In other words, when you find yourself typing a value into your worksheet manually, see if you can convert it to a formula. This habit will not only enable you to see what is possible, it will also eliminate a manual entry step (improving efficiency, and reducing manual errors).
Do you have any other neat tips about Excel formulas? Let us know in the comments!
