# Category: Excel Formulas

## Excel VLOOKUP Formula Error Mystery

Someone sent me a workbook in which a simple VLOOKUP formula was returning #N/A errors, instead of the correct results. The product numbers looked the same, but Excel didn’t match them in the lookup....

## Problems Counting Excel Data COUNTIF COUNTA

Last week, I ran into problems counting Excel data with COUNTIF, and it’s Twitter’s fault! Why did they do that? The COUNTA function can cause problems too, when it counts cells that look empty....

## Count Items in a Cell with SUBSTITUTE

Do you use Excel’s SUBSTITUTE function very often? It’s a handy way to count items in a cell, when they’re separated by commas or spaces. The examples below show different ways to use this...

## What Are the Rules for Excel Names?

As Juliet famously said to Romeo, “What’s in a name?” And she was talking about rows (misspelled as “rose”), so maybe Juliet was using a spreadsheet at the time. There are special rules for...

## Excel Holiday Planner Workbook

Long ago, I created a Christmas planner in Excel, and it has evolved over the years. Well, tomorrow is the first of December, so you’ve probably finished your planning for this year. But, if...

## Do You Still Need Excel Subtotals?

Excel has a SUBTOTAL function, which ignores hidden or filtered rows. There is a Subtotal feature too, that quickly groups your data, and adds one or more rows of subtotals. Do we still need...

## Excel Lookup Formula Challenge 20171026

Here’s an Excel Lookup Formula challenge to get your brain fired up. Can you solve it without doing a Google search? The problem details are shown below, and you can download the sample workbook....

## Excel CONVERT Function With Drop Down Lists

With the Excel CONVERT function, you can change quantities from one measurement type to another. For example, a hot day in Celsius looks even worse in Fahrenheit! Get my CONVERT workbook, with drop down...

## Split Address With Excel Formulas

If a cell contains a full address, what formulas would you use to show the street address, city, state and zip code in separate cells? That was the challenge that I gave to my...

## Excel Price Lookup for Date and Product Name

If there’s just one price per product in an Excel lookup table, you could use the INDEX and MATCH functions to to get that price. But what if the price changes occasionally, and your...

## 7 Ways to Round in Excel

It’s not usually as obvious as the error in the screen shot below, but have you ever seen Excel adding things up incorrectly? No wonder some people take out a calculator, to check the...

## SUMIFS Formula With Empty Criteria Cells

I recently learned about The 100-Day Project (#The100DayProject), in which you work on making something every day, for 100 days. Would Excel formulas (#100DaysOfXL) be considered an artistic project? Maybe not, but I had...