Microsoft 365 Excel All-in-One For Dummies Cheat Sheet

So, you’re working your way through Microsoft 365 Excel All-in-One For Dummies? That’s a great choice for getting more out of your powerful spreadsheet app. Use this handy Cheat Sheet to keep key Excel information at your fingertips as you build your worksheets and formulas and as you write Visual Basic for Applications (VBA) code in the Visual Basic Editor.

Absolute, relative, and mixed references in Excel formulas

In Excel formulas, you can refer to cells and ranges either relatively or absolutely. You can also create mixed references. Here’s a guide:

• Relative references: Relative references change when you copy or fill the formula to another cell. For example, say you enter the formula =A1 in cell B1. If you copy the formula to cell C2, Excel changes it to =B2, so the formula still refers to the cell immediately to the left of the cell that contains it.
• Absolute references: Absolute references stay the same when you copy or fill the formula to another cell. So, if you enter the formula =$A$1 in cell B1, and then copy the formula to cell C2, the formula remains =$A$1. Here, the $ signs indicate absoluteness: $A indicates that column A is absolute, and $1 indicates that row 1 is absolute.
• Mixed references: Mixed references are either absolute in column and relative in row (such as $A1) or relative in column and absolute in row (such as A$1).

Order of operations in Excel formulas

To make your formulas come out right, you need to know the order in which Excel calculates mathematical operators and syntax in them:

1. Parentheses
2. Exponents
3. Multiplication and division
4. Addition and subtraction

The initial letters make the acronym PEMDAS. To remember the acronym, use whichever of the following mnemonics you prefer:

• Please Excuse My Dear Aunt Sally
• Penguins Enjoy Math During Arctic Summers
• Purple Elephants March Down A Street

10 essential Excel functions

Which functions you’ll use most in Excel will depend on the type of data you’re crunching and your goals in crunching it. But many worksheets will feature some — or perhaps even all — of these ten essential functions.

Building comparison expressions in Excel

A comparison expression— also known as a logical expression or a Boolean expression — is an expression where you compare the items in a range or table column with a value you specify. In Excel, you use comparison expressions to create advanced filters for a table, as well as in functions that require criteria, such as COUNTIF, SUMIF, and AVERAGEIF.

To construct a comparison expression, you enter a comparison operator from the following table below, followed by the value you want to use for the comparison.

Visual Basic Editor shortcut keys

To work quickly in the Visual Basic Editor, make sure you know the most useful keyboard shortcuts.

20 Excel for Mac keyboard shortcuts

In Excel, as in many other apps, pressing a keyboard shortcut is often the quickest way to take an action. If you’re using Excel for Mac, make sure you’re familiar with the following 20 essential keyboard shortcuts.