Introduction to Excel Formulas

  • An Introduction to Excel Formulas (Part 1) - In this series of blog posts, I'll introduce you to one of Excel's most powerful and useful features, Excel formulas. Formulas can be used for a multitude of things, such as calculating tax on an invoice or the volume of water an object can hold, removing or replacing specific characters in a string of text, ...
  • An Introduction to Excel Formulas (Part 2) - Welcome to part two of three in this series of blog posts introducing Excel formulas. During this blog post, I will introduce Reference Operators, Operator Precedence, and Error messages. First, let's have a quick recap of what was introduced in part one of this series. Excel formulas are one of Excel's most powerful and useful ...
  • An Introduction to Excel Formulas (Part 3) - Welcome to the final part of this three-part series of blog posts introducing Excel formulas. If you haven't already read part one and part two in this series, then I suggest you read them first. For this final blog post, I will introduce the single Text Operator and some commonly used functions that can be ...

Names and Named Ranges in Formulas

  • How to Use the Excel Name Feature - The power of the Excel Name function is a personal favourite method of mine for saving time and improving quality when working in Excel. In this blog post, I'm going to explain what the Excel Name functions is, why it is useful, and give you a few examples of how they can be used.
  • How to Create and Use a Named Constant - In this blog post, we're going to take a look what an Excel named constant is, how to create them, and how to use a named constant in a couple of examples. A named constant is similar to a named range except the value of the name doesn't appear in any cell.
  • How to Create a Dynamic Named Range - How many times have you wanted to add things to a list in Excel without needing to change formulas or update cell references? Named Ranges are excellent for this because they allow you to define a specific name for your range of cells and then reference the name throughout your spreadsheet. If you want to ...

Wildcard Characters in Formulas

  • How to Use Excel Wildcard Characters in Formulas - Excel has a number of characters referred to as wildcard characters that enable you to carry out partial matching on a text string. For example, imagine you want to search or count all the people in a list whose last name is Smith. You could use wildcard characters to search for names ending in Smith ...

 

Cell References in Formulas

  • How to Write Relative and Absolute Cell References - In this blog post, I'm going to explain what relative and absolute cell references are, some of the advantages and disadvantages of each type of reference, the situations when each can be used, and, of course, some examples.

Error Handling in Formulas

  • How to Handle Excel Error Messages in Formulas - Work with Excel formulas long enough, and you are going to bump into Excel error messages. They are annoying and can be time-consuming to deal with. However, there are things that you can do to handle the different types of Excel error messages you might receive. In fact, some of these error messages can be ...

Lookup Formulas

  • How to Use the INDEX MATCH Functions to Find Data - In this blog post, we are going to take a look at how we can use a combination of the INDEX and MATCH functions, commonly referred to as INDEX MATCH, to find data in a range of cells. Chances are that you are familiar with the VLOOKUP function. VLOOKUP is my favourite function. It can ...
  • How to Perform a Two-Way LOOKUP Using INDEX MATCH MATCH - In this blog post, you will learn how to perform a two-way lookup using the functions INDEX MATCH MATCH. No, that's not a typo; you will use the MATCH function twice. You’ll learn what a two-way lookup is, why it is useful, how to manage some of the error messages you may receive, and work ...
  • How to Use VLOOKUP and MATCH Functions to Perform a Two-Way Lookup - In this blog post, we will look at how to use the VLOOKUP and MATCH functions to perform a two-way lookup, sometimes referred to as a matrix lookup. This blog includes a quick refresher on the VLOOKUP and MATCH functions, but I'm going to assume that you are aware of both these functions.