How to Average Only Visible Cells in Excel (3 Quick Methods)

If you are searching for the solution or some special tricks to average only visible cells in Excel then you have landed in the right place. There are some ways to average only visible cells in Excel. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.


Download Practice Workbook

You can download the practice workbook from here:


3 Methods to Average Only Visible Cells in Excel

In this section, I will show you 3  quick and easy methods to average only visible cells in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.

Dataset of How to Average Only Visible Cells in Excel


1. Using SUBTOTAL Function to Average Visible Cells

You can use the SUBTOTAL function to get the average of the visible cells for both the filtered dataset and the dataset with manually hidden rows.

1.1 For Filtered Data

When you use a filtered dataset and want to get the average of the filtered cells excluding the hidden rows, you can use the SUBTOTAL function. For this, follow the steps below:

📌 Steps:

  • First, you have to filter the dataset to hide rows. To enable the Filter feature for the dataset, select any cell of the dataset.
  • Then, go to the Home tab >> Editing Menu >> Sort & Filter Option >> Filter

Using Filter Feature to Average Only Visible Cells in Excel

  • Now, paste the formula in cell D15 to get the average of all cells in the range D5:D13.
=SUBTOTAL(1,D5:D13)

Using Subtotal Function to Average Only Visible Cells in Excel

🔎 Formula Explanation  

It returns a list or database. It offers 11 Excel functions to perform by inserting the corresponding argument.

Syntax:

SUBTOTAL(function_num,ref1,[ref2],…)

  • function_num = 1 to 11: Includes manually hidden rows but excludes filtered-out cells
  • function_num = 101 to 111: Excludes both manually hidden rows and filtered-out cells

So, to exclude filtered-out cells only, we have used 1 as the function_num here. And selected the full range of the dataset D5:D13 to calculate the average. Before filtering, we got the average of the full column.

  • Then, you have to filter the data to check the output. If you want to get average sales of any region, then click on the Filter icon on the Region column header cell.
  • here, mark any region for which you want to calculate the average and unmark
  • After that press OK.

Filtering Data

  • Now, you will see that the average cell is showing the average sales for only the Alberta

Average Only Visible Cells in Excel using Subtotal Function


1.2 For Data with Hidden Rows

Now, I will show you how you can exclude the manually hidden row’s value from the average calculation. Follow the steps below for this-

📌 Steps:

  • Insert the following formula into cell D15:
=SUBTOTAL(101,D5:D13)
  • So, you get the average of the full column before hiding any rows.

Average Only Visible Cells for Data with Hidden Rows

  • Now, hide any row from the dataset. Suppose, you have hidden the 6th, 10th, and 12th
  • And, you will see the average cell is giving the average value of the visible cells

Average Only Visible Cells for Data with Hidden Rows using Subtotal Function

Read More: Excel VBA to Select First Visible Cell in Filtered Range


Similar Readings


2. Using AGGREGATE Function in Excel

Alternatively, you can use the AGGREGATE function to calculate the average of only the visible cells. Follow the steps below for this:

📌 Steps:

  • First, hide some rows from the dataset.

prepare dataset to Use AGGREGATE Function

  • Then, paste this formula into the cell D15:
=AGGREGATE(1,1,D5:D13)

🔎 Formula Breakdown:

Syntax:

AGGREGATE(function_num, options, array, [k])

  • Insert function_num = 1 to calculate the average value of the selected data range.
  • Insert options = 1 to exclude the hidden rows from the calculations.
  • Select array = D5:D13 to calculate the average of this data range excluding hidden rows.
  • So here, you have got the average of only the visible cells in the selected data range

Using AGGREGATE Function to average only the visible cells

  • Now, hide one or more rows of the dataset to check whether the formula works or not. Here, I have hidden the 8th and 12th row, and the average value is changed which is the average of only the visible cells now.

Average Using AGGREGATE Function

Read More: How to Paste into Visible Cells Only in Excel (5 Easy Methods)


3. Average Visible Cells Using Total Row Feature of Excel Table

You can use the Total Row feature of table to calculate the average of the visible cells only. For this follow the steps below:

📌 Steps:

  • First, you have to convert the dataset into an Excel table.

Dataset for Using Table Total Row Feature

  • To make a table, select any cell of the dataset.
  • Then, go to the Home
  • Then, select the Format as Table option and thus there will open a list of table designs.
  • Then, select any design from the options.

Steps to create Excel table

  • After selecting a design for the table, a pop-up window named Create Table will appear.
  • You will see here the range of table cells inserted already.
  • Press the OK button here.

Create table window

  • Then, the dataset will be converted into an Excel table.
  • Now, select any cell of the table and you will see a new tab created in the top ribbon named Table Design.
  • Here, go to the Table Style Options and mark the Total Row

Using Total Row Feature

  • Then, you will see a new row created at the bottom of the table which is showing the Total Sales of the table. You can change it to show the average of the sales value.
  • For this, click on cell D14 then a drop-down arrow will appear. Click on the arrow.
  • From the options, select the Average

Find Average using Total Row feature

  • Then, click on the cell B14 and rename it as “Average”
  • Now, cell D14 is showing the average value of the visible cells.

Rename as Average

  • Now, you can hide some rows to check whether the average cell value changes or not.

Output Using Table Total Row Feature

Read More: How to Select Visible Cells in Excel with VBA (5 Easy Methods)


Things to Remember

  • Use function_num 1 when you use the filtered dataset to exclude the filtered-out rows from the average calculation.
  • Use function_num 101 when you want to exclude the manually hidden rows from the average calculation.

Conclusion

In this article, you have found how to average only visible cells in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo