Category: Excel Formulas

Excel VLOOKUP Formula Error Mystery http://blog.contextures.com/ 3

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....

Count Items in a Cell with SUBSTITUTE http://blog.contextures.com/ 0

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...

Beyond the Rules for Excel Names http://blog.contextures.com/ 2

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 http://blog.contextures.com/ 2

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? http://blog.contextures.com/ 3

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...

Split Address With Formulas in Excel http://blog.contextures.com/ 0

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...

7 Ways to Round in Excel http://blog.contextures.com/ 6

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 with empty criteria 5

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...