Osman Goni Ridwan
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.
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
- Now, paste the formula in cell D15 to get the average of all cells in the range D5:D13.
=SUBTOTAL(1,D5:D13)
🔎 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.
- Now, you will see that the average cell is showing the average sales for only the Alberta
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.
- 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
Read More: Excel VBA to Select First Visible Cell in Filtered Range
Similar Readings
- How to Copy Visible Cells Only without Header Using VBA
- [Fixed!] Paste Visible Cells Only Not Working (4 Possible Solutions)
- How to Sum Visible Cells with Criteria in Excel (5 Easy Methods)
- How to Select Visible Cells in Excel (5 Quick Tricks)
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.
- 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
- 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.
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.
- 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.
- 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.
- 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
- 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
- Then, click on the cell B14 and rename it as “Average”
- Now, cell D14 is showing the average value of the visible cells.
- Now, you can hide some rows to check whether the average cell value changes or not.
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.
