If you are searching for a 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.
How to Average Only Visible Cells in Excel: 3 Methods
In this section, I will show you 3 quick and easy methods to average only visible cells in Excel on the Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If anything in 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.
📌 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 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.
To exclude filtered-out cells only, we have used 1 in the function_num argument. 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 the average sales of any region, then click on the Filter icon on the Region column header.
- here, mark any region for which you want to calculate the average and unmark the rest.
- After that press OK.
- Now, you will see that the Average cell is showing the average sales for only the Alberta region.
Read More: How to Average Filtered Data in Excel
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.
📌 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 rows.
- And, you will see the Average cell is giving the average value of the visible cells.
Read More: How to Average Every Nth Row in Excel
2. Using AGGREGATE Function in Excel
Alternatively, you can use the AGGREGATE function to calculate the average of only the visible cells.
📌 Steps:
- First, hide some rows from the dataset.
- Then, paste this formula into 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 rows, and the average value is changed which is the average of only the visible cells.
3. Average Visible Cells Using Total Row Feature of Excel Table
You can use the Total Row feature of the table to calculate the average of the visible cells only.
📌 Steps:
- First, you have to convert the dataset into an Excel table.
- To make a table, select any cell of the dataset.
- Go to the Home tab.
- Then, select the Format as Table option and thus there will be a list of table designs.
- 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.
- The dataset will be converted into an Excel table.
- 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.
- You will see a new row created at the bottom of the table which shows 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 option.
- 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.
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.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, you have found how to average only visible cells in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Calculate Average Only for Cells with Values in Excel
- How to Find Average of Specific Cells in Excel
- How to Find Average with Blank Cells in Excel
- How to Exclude a Cell in Excel AVERAGE Formula
- How to Calculate Average of Multiple Ranges in Excel
- How to Average a Column in Excel
- How to Calculate Average of Multiple Columns in Excel
- How to Fix Divide by Zero Error for Average Calculation in Excel
- How to Ignore #N/A Error When Getting Average in Excel
- [Fixed!] AVERAGE Formula Not Working in Excel
<< Go Back to Conditional Average | Calculate Average | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
