Home » Our Blog » Excel Time Saving Features » Functions

Category: Functions

Sum only visible rows when Filtering data in Excel

sharyn bainesA common question I get asked when I’m teaching people how to filter data is “how do I sum only the visible rows when filtering data in Excel”?

It’s very common for people to try creating a SUM total at the bottom of a list and then filter the data hoping that the total will change to reflect the SUM of the data displayed. However, the total doesn’t update. It still shows the SUM of the entire list.

You can definitely use the AutoSum button to instantly create a total that will update as you filter your data. Here’s how you do it.

Read more

Excel – How do I sum a range of cells that include #N/A or #DIV/0! errors?

I think most of us will agree that error values can be helpful, and sometimes extremely unhelpful.  For example, the #N/A error (Value Not Available error) is very helpful when cross-referencing two lists using the VLOOKUP function.  If Excel can’t find a lookup value the #N/A error makes it very obvious.

However if you are wanting to total a range that includes #N/A errors all of a sudden they aren’t so special any more.  Read more

Excel – Hide errors in a worksheet

PROBLEM: how do I hide errors in a worksheet?  For example the #DIV/0 error or the #N/A error. Not only do they look not so great on the worksheet, they can cause other functions and formulas that include the cell holding the error to bring back an error as well (sigh).

SOLUTION: Start using the IFERROR function to hide the errors. Not only will it hide the errors in your workbook, your other calculations will no longer show errors. Read more