Mastering Excel: Your Formulas For Success

Overview

Formulas are the backbone of Excel. Without them, it wouldn’t be much more useful than grid paper. Whether they are simple or extremely complex, they power calculations of all kinds, from finance to engineering to generic data analysis. If you’re new to Excel, understanding how formulas work should be a top priority. If you’re a seasoned pro, there’s probably something new and valuable to learn.  In this article, we’ll walk through eight key concepts related to Excel formulas, starting with the basics.

Functions

Excel has over 450 built-in functions across several categories: math, dates, financial, text, logical, lookup, and more. They’re like pre-built mini programs, performing calculations of all kinds and supporting deep analysis. You’ll likely only need to use about one fifth of these formula functions. Check out our 52 Crucial Excel Functions e-book to test your knowledge and learn the most valuable Excel functions you need to know.

References

Most Excel formulas contain cell references that point to cell(s), column(s), or row(s). While they are useful for calculations, their magic is in their ability to make files dynamic. Imagine updating a handful of cells with dates, names, IDs, etc. that recalculate an entire workbook! Cell references make it happen.

It’s important to understand the different types of cell references. They are distinguished by the $ symbols before the column letter, row number, or both.

• Relative (example: A4) – both the column letter and row number update when copied and pasted
• Absolute (example: $A$4) – neither the column letter or row number update when copied and pasted
• Row-absolute (example: A$4) – the column letter updates but the row number does not update when copied and pasted
• Column-absolute (example: $A4) – the column letter does not update but the row number does update when copied and pasted

To learn more about reference types, here’s another article and video.

Named Ranges

Named ranges are a powerful but way underused feature in Excel. They allow you to define a name to a range of cells – one cell, column, or row, or a contiguous block of cells, columns, or rows. They can be static and defined to that range, or be dynamic and be defined with a formula.

They offer several benefits:

• They make formulas easier to read (for yourself and others!) with names that you get to define. “Total_Sales_CY” is easier to understand than “A1:A100” isn’t it?
• They make it faster to navigate your workbook with the named box showing a list of them you can easily jump to. The XLEV8 Add-in Search Named Ranges macro makes this even easier by letting you search your workbook’s named range with auto-complete suggestions as you type!
• They allow you to reference cells without worrying about structural changes. This is crucial when referencing cells in VBA/macros as they don’t shift when you insert/delete cells/columns/rows. The same goes for references to external files.
• They help reduce errors. When typing a named range in a formula, Excel offers suggestions to help you spell them. Not so with normal column/row references.
• They can be dynamic and update with your data. This is especially helpful for linking them to drop-down lists via data validation. The XLEV8 Add-in Add Dynamic Named Range macro can automatically set a dynamic named range formula so you don’t have to type it out!

Dynamic Array Functions

In the last couple of years, Excel recently rolled out Dynamic Array Functions (DAF) that might be the biggest innovation ever in Excel. These functions can return an array of values, not just one value. This allows you to combine formulas that would normally need to be split out into multiple different cells. The formula results can also “spill” over into multiple cells across or down.

They offer several benefits:

• They make your data dynamic – expanding rows or columns automatically based on the formula results.
• They allow you to combine many different functions that would otherwise need to be split out, giving you a lot more flexibility in how you structure your data.
• Powerful data analysis with the ability to sort, filter, and restructure your data directly within formulas

Lambda

Lambda allows you to define custom functions in Excel (complete with arguments/parameters), without the need for a macro-enabled file. This allows you to define complex formulas in one place and use them throughout your workbooks, making updates much easier. Building block Lambda functions can be used within other functions, building efficiency. Lambda functions are maintained using the name manager (much like named ranges). Lambda functions often leverage the LET function to define reusable variables within the rest of the formula. Lambda functions are tied to the workbook they are defined in. For even more power and flexibility in defining custom functions, check out user-defined functions.

User-Defined Functions

Similar to custom-defined Lambda functions, user-defined functions (UDF) allow you to define your own custom functions using Visual Basic for Applications (VBA) code, much like writing macros. VBA offers a lot of power and flexibility with UDFs, such as working with external files/systems and the operating system. Because they are written in VBA, they must be saved in macro-enabled Excel files (i.e. .xlsm or .xlam).

Error-Handling

Formulas can result in a variety of errors – deleted references, lookups with no matches, misspelling a function, dividing by zero, etc. These are the formula error types in Excel:

• #N/A – Excel can’t find the value you’re looking for
• #VALUE! – The wrong parameters were provided (i.e. text when Excel is expecting a number)
• #REF! – A reference is invalid or was deleted
• #NAME? – An invalid function name or named range was entered (likely misspelled)
• #DIV/0! – The denominator is zero
• #NULL! – Excel can’t determine the range of a formula (due to an empty value)
• #NUM! – A numeric calculation result is not a valid number (i.e. the square root of a negative number)

It’s important to handle errors in a way that keeps your data looking neat and helps you identify when there are issues upstream of your formula. Using IFERROR is a popular option because it allows you to return an alternative value if your formula would otherwise result in an error. Several other functions can help identify and handle errors as well, such as ISERROR, ISERR, and ISNA.

Conditional Formatting

Conditional formatting is a powerful way to automate different formatting types that help you to visually find data you’re looking for – exceptions, top/bottom values, blanks, etc. Conditional formatting allows you to format cells based on conditions, and it is dynamic, so the formats can change as your data changes. There are several built-in conditional formats, but the most powerful and flexible option is using formulas to define the conditions – anywhere from simple to complex and specific. When using formulas to define your formatting conditions, they should evaluate to TRUE or FALSE (i.e. “=A4>7”).

Macros That Help

The XLEV8 Excel Add-in contains several macros that help you work more efficiently with formulas:

1. Formula Picker – Offers several options that make working with formulas easier, including saving and searching for your favorite formulas.
2. Show Cell References – Shows all cell references in a formula in a searchable list where you can jump to any of them and back, helping you review them.
3. Toggle Formulas / Values – Allows you to toggle formulas to values and back for columns you specify across one or more sheets, which can significantly speed up recalculation in your workbooks.
4. Conditional Formatting Picker – Offers several different conditional formatting options that help you identify duplicates, blanks, hard-coded values/formulas, and more.
5. Named Range Picker – Offers several different options for efficiently working with named ranges – searching them with auto-complete, setting dynamic ranges via shortcut, and adding/updating them in bulk.

Summary

If you work in Excel every day or you plan to be, mastering formulas is crucial for unlocking the true potential of Excel. From basic calculations to complex data analysis, formulas streamline tasks, improve accuracy, and provide deeper insights into your information. Whether you’re a seasoned user or just starting out, remember that the journey of formula exploration is continuous.

What are your favorite Excel formula tips?  Share your thoughts in the comments below!