Troubleshoot Excel Formulas with ERROR.TYPE Function

Did you know that you can use the Excel ERROR.TYPE function to identify specific types of errors on a worksheet? And after the error type is identified, you can use that information to provide help with error troubleshooting.

There’s a short video below, that shows an example, and there’s a list of the Excel error values that this function can identify.

Continue reading “Troubleshoot Excel Formulas with ERROR.TYPE Function”

Excel Text in Multiple Rows – No Merged Cells

I add lots of screen shots to my Excel tutorials, so it’s easier to follow the steps. And if I’m explaining a formula, it’s helpful to show that on the worksheet too.

However, some Excel formulas can get pretty long, and I found a way to fix that problem! You can see all the solutions that I tried, or skip to the end, to see the final solution.

Continue reading “Excel Text in Multiple Rows – No Merged Cells”

How to Sum Filtered Excel Data – AGGREGATE or SUBTOTAL

When an Excel list is filtered, how can you show a total sum or count for numbers in the visible rows only? There are two Excel functions for that task — SUBTOTAL (all Excel versions) and AGGREGATE (Excel 2010 and later). See how these functions work, and decide which one to use.

Excel SUBTOTAL Function

Should you use SUBTOTAL or AGGREGATE to get a total for filtered data?

There’s a video further down the page, where I show both functions, along with the Excel SUM function. That might help you decide which function will work best for you.

Here are a couple of reasons to choose the SUBTOTAL function:

Old Versions of Excel: If there’s any chance that you’ll have to share the file with someone who’s using Excel 2007 or earlier, go with the SUBTOTAL function. Yes, those are really old versions of Excel, but some people haven’t upgraded.

Easy to Use: It’s easy to insert the SUBTOTAL function below a filtered list:

  • Select the blank cell immediately below the column of numbers
  • On the Excel Ribbon’s Home tab, click the AutoSum button.
  • Excel automatically inserts a SUBTOTAL formula for you, with the Sum function (9) selected.
    • NOTE: In newer versions of Excel, function 109 is automatically selected, so change that to 9, if you need compatibility with older Excel versions
Excel automatically inserts SUBTOTAL formula
Excel automatically inserts SUBTOTAL formula

Excel AGGREGATE Function

If you don’t have to share the Excel file with anyone who’s using Excel 2007 or earlier, I’d recommend using the AGGREGATE function.

Here are a couple of reasons to choose AGGREGATE:

  • It has 19 functions, compared to the 11 functions in old versions of SUBTOTAL
  • There are 8 options for what to ignore, compared to the 2 options in SUBTOTAL
AGGREGATE function has 19 functions
AGGREGATE function has 19 functions

Video: Sum Filtered Excel Data

This short video shows how to sum filtered numbers in Excel, with the AGGREGATE function, or the Excel SUBTOTAL function.

You’ll also see the differences between those two functions, and how they compare to the SUM function.

More Info

Filtered Rows Count/Sum Formula Examples

Sum a Filtered List with AGGREGATE Function

_______________________

How to Sum Filtered Excel Data – AGGREGATE or SUBTOTAL

How to Sum Filtered Excel Data - AGGREGATE or SUBTOTAL
Sum Filtered Excel Data – AGGREGATE or SUBTOTAL

_______________________

How to Compare Two Lists in Excel-New Items

Do you ever need to compare two lists in Excel, and see if there are some items in one list, that aren’t in the other list? The video below shows how to check for new items, and only add those to your existing list. You don’t want to have a list that’s full of duplicates entries!

Continue reading “How to Compare Two Lists in Excel-New Items”

Excel Formula Troubleshooting Tips and Tools

What’s your favourite way to troubleshoot Excel formulas? Here are a few tips for current versions of Excel, and a preview of new features that are coming soon, for Excel 365.

Use the Formula Bar

If there’s a problem with a simple Excel formula, I usually click in the formula bar, so Excel colour codes the precedent cells on the worksheet.

Sometimes the wrong cell is in the formula, so I can drag the coloured borders, and choose the correct cell instead.

Video: Audit Excel Formulas

This short video has a few more formula auditing tips, from my Audit Excel Formulas page. There’s another video further down, with new features that are coming soon, for Excel 365.

Go to Precedent Cells

For a formula that’s more complicated, you can go to the precedent cells, with this keyboard shortcut:

  •  Ctrl + [

That shortcut will even take you to a formula cell on a different worksheet, but only if it’s the first reference in the formula.

Formula Troubleshooting Tool

For bigger formula auditing projects, you can use an Excel macro, to see where formulas have been copied down, across, or both.

In the screen shot below, I used a macro, written by Dermot Balson, which applies fill patterns to the formula cells.

  • Fill lines go down, across, or crosshatch, to show the direction in which the formulas were copied on the worksheet.
  • Solid coloured cells contain the original formulas

This colour coding makes it easy to spot cells where a formula is missing or different. That can give you a quick start in your troubleshooting!

And don’t worry – the colour coding is done on a new sheet, so it doesn’t mess up your worksheet!

New Excel Formula Tools

If you’re using Excel 365, there are new features coming soon, including improvements to the formula bar.

  • In current versions of Excel, you can select part of a formula in the formula bar, then press the F9 key, to evaluate that part of the formula.
  • In the upcoming updates, you’ll be able to click on an argument name in the formula tooltip, and see the values from that range of cells.

In this video, Mike Tholfsen, from Microsoft, shows 8 new Excel features, with the formula bar feature shown at the 6:28 mark.

Get the Excel Files

–1) Audit Tips: To get the formula auditing tips workbook, go to the How to Audit Excel Formulas page on my Contextures site.The zipped file is in xlsx format, and does not contain any macros.

–2) Formula Cell Colour: To get Dermot’s colour coding macro, and read more about it, go to the Formula Cell Colour Code Macro page on my Contextures site. The zipped file is in xlsm format, and contains the macros to colour coded the formulas. Be sure to enable macros when you open the workbook, if you want to test the macro.

_________________

Excel Formula Troubleshooting Tips and Tools

Excel Formula Troubleshooting Tips and Tools

________________

Create Excel Monthly Calendar From 6 Formula Cells

Create a monthly Excel calendar, based on a few short formulas. Next, add formatting and a heading, and if you’re feeling fancy, show a list of the month’s holidays too! This example uses functions that are available in Excel 365, and in Excel for the Web.

Continue reading “Create Excel Monthly Calendar From 6 Formula Cells”

Calculate Ratio with Excel Formula – 3 Steps

Do you ever use Excel to calculate a ratio? In business, you could compare income to expenses, and at home, you could use ratios to bake a cake, without a recipe. In my latest video, I show a quick Excel formula for the ratio between two numbers, in x:y format. And no, Excel doesn’t have a RATIO function, even though one overly-confident AI helper tool said it does!

Continue reading “Calculate Ratio with Excel Formula – 3 Steps”

Excel REPT Function Examples Groundhog Day

Happy Groundhog Day! Have you made plans that you’ll be happy to repeat, day after day? Maybe these Excel REPT function examples will give you some ideas! There are written steps, screen shots, and 3 videos below.

Continue reading “Excel REPT Function Examples Groundhog Day”