Exclude Cells that are Filtered, Hidden or Grouped from Formulas

Add to Favorites
Author: | Edits: don

2 different Powerful data analysis formulas for Excel that allow you to exclude hidden, grouped, or filtered data from calculations and that will also allow you to avoid issues caused by errors in the data set.

Normally, functions in Excel will include data that is hidden or filtered - this means that if you want to count only the visible rows of data after you have filtered them, the normal COUNT() function will not work - this causes an issue with data analysis in Excel where the values that you see, output from the formulas, do not correspond with what you actually see in the spreadsheet.

This tutorial will cover 4 distinct scenarios and show you how to perform analysis on only the visible results:

  • filtering data in Excel
  • grouping data in Excel
  • manually hiding data in Excel
  • data sets that include errors in them, regardless of whether the errors are hidden or not

I hope you enjoy this tutorial!

Question? Ask it in our Excel Forum

Downloadable Files: Excel File

Similar Content on TeachExcel
Count the Visible Rows in a Filtered List in Excel
Tutorial: How to use the COUNT function on a filtered list of data so that hidden rows are not incl...
Sum the Visible Rows from a Filtered List in Excel
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
Filter and Sort Data on Charts in Excel
Tutorial: Create a dynamic chart in Excel that displays only the data you want. You can filter it an...
Prevent Cells from Summing to a Negative Value or Vice Versa
Tutorial: How to prevent a range of cells from adding up to a negative value, or preventing the same...
Quickly See All Cells linked to a Formula or Function in Excel
Tutorial: Ill show you how to see the cells used in a formula/function in Excel and also how to tel...
Dynamic Formulas that Update When you Add Data in Excel
Tutorial: I'll show you how to make formulas and functions that automatically update when more data...