How to Use Excel Formulas on Visible Cells Only?

Sharing is caring!

Do you need to calculate a sum, average, count, or other statistic in Microsoft Excel but only want to include visible cells in the calculation? There’s an easy way to do this using a special Excel formula syntax. By modifying your formula slightly, you can perform calculations on visible cells only while ignoring hidden or filtered out cells.

In this article, we’ll explain exactly how to use Excel formulas on visible cells only. We’ll cover the proper formula syntax, provide several detailed examples, and share some tips and best practices. Whether you’re a beginner or advanced Excel user, you’ll learn how to quickly perform calculations on just the data you can see.

The Importance of Calculating Visible Cells Only

There are many situations where you may want to perform a calculation in Excel but only include cells that are currently visible:

• Your spreadsheet has hidden rows or columns that you don’t want factored into totals, averages, etc. Hidden rows and columns are a useful way to declutter a busy spreadsheet or temporarily remove data from view, but they can distort your formulas if not handled properly.
• You’ve applied filters to show only a subset of your data. Filters are one of Excel’s most powerful features, allowing you to dynamically display only rows that meet certain criteria. But filtered out rows are still included in normal calculations.
• You’ve used Excel’s outlining or grouping features to collapse/expand sections. Outlining lets you create collapsible/expandable sections to better organize your data hierachically. But as with filtering, collapsed rows are still factored into regular formulas.
• You want to share summary statistics for only the data shown on screen. Sometimes you may want to present or share just a portion of a larger spreadsheet. Using formulas for visible cells only ensures your calculations always match what the viewer can see.

In all of these cases, Excel’s standard SUM, AVERAGE, COUNT and other formulas will still factor in values from the hidden cells. This can lead to incorrect or misleading results. To calculate just the visible data, you need to use a special formula syntax.

Excel’s Formulas for Visible Cells Only

Fortunately, it’s very easy to modify any standard Excel formula to calculate only visible cells. You simply need to add a SUBTOTAL function and visibility argument.

The basic syntax is:

=SUBTOTAL(function_num,ref1,ref2,…)

Where:

• function_num is a code for the calculation you want to perform (e.g. 9 for SUM, 1 for AVERAGE)
• ref1, ref2, etc. are the cell ranges you want to include
• The function_num includes the option to ignore hidden cells

Here are the function numbers to use for common calculations:

As you can see, to ignore hidden cells, simply add 100 to the standard function number. This instructs Excel to perform the calculation on visible data only.

So if you wanted to SUM a range named SalesData, you would use:

=SUBTOTAL(9,SalesData) to include hidden cells
=SUBTOTAL(109,SalesData) to ignore hidden cells

The same goes for other operations like AVERAGE, COUNT, MIN, MAX, etc. Just reference the table to determine the appropriate function number. Once you understand the basic syntax, it becomes very intuitive to perform calculations on visible cells only.

Examples of Formulas on Visible Cells

Let’s look at a few more detailed examples to illustrate how this works in practice. Consider the following spreadsheet with sales data by month:

To calculate the total sales across all months, you would normally use a formula like:

=SUM(B2:B9)

Which in this case returns $85,500.

However, if the rows for March and June were hidden (either manually or by a filter), this standard SUM formula would still include those amounts, even though they’re not visible. To sum just the visible cells, use:

=SUBTOTAL(109,B2:B9)

The 109 function number tells Excel to SUM while ignoring values in hidden rows. With March and June hidden, the SUBTOTAL formula correctly returns $68,500 – the sum of only the visible cells.

Similarly, to calculate the average sales per month across visible cells only:

=SUBTOTAL(101,B2:B9)

The 101 function averages only visible data. With all rows visible this returns $10,687.50. With March and June hidden it returns $11,416.67, ignoring those two months.

You can apply this SUBTOTAL technique to any calculation or statistical function. For example, to find the maximum sales amount in visible cells only:

=SUBTOTAL(104,B2:B9)

To count the number of visible rows that contain a data value:

=SUBTOTAL(103,B2:B9)

And so on. Simply use the appropriate function_num from the table above to perform your desired calculation on visible cells only. This provides an easy way to dynamically analyze just the data you can see, while ignoring any hidden values.

Tips for Using Formulas on Visible Cells

Here are a few more tips and best practices to keep in mind when using SUBTOTAL formulas:

• Remember that SUBTOTAL only ignores cells hidden manually or by a filter. Cells with zero or blank values that are still visible will be included. Use =SUBTOTAL(103,…) to count only visible cells with actual data.
• If you change which rows are hidden or visible (e.g. by applying/removing a filter or expanding/collapsing a group), your SUBTOTAL formulas will instantly recalculate based on the newly visible/hidden data. There’s no need to manually update the formulas.
• Be careful when copying and pasting SUBTOTAL formulas, as the relative cell references may inadvertently shift and reference the wrong range. Consider using absolute cell references like $B$2:$B$9 instead of B2:B9 to keep references locked.
• You can nest SUBTOTAL functions inside of other formulas for even more complex calculations. For example:
  =SUBTOTAL(109,B2:B9)/SUBTOTAL(103,B2:B9)
  will calculate the average of just the visible data by SUMming the values and dividing by the COUNT of rows with data.
• The SUBTOTAL function even works across multiple worksheets, as long as you use a 3D reference like =SUBTOTAL(109,Sheet1:Sheet3!B2:B9). This will sum visible data across all three sheets.
• If you want to preserve some visible calculations of your full data range while also adding calculations for visible cells only, you can place the SUBTOTAL formulas on a separate worksheet to avoid overwriting anything.

Final Thoughts

The ability to easily perform Excel calculations on only visible cells is an extremely useful feature. Whether you have hidden rows or columns, filtered out data, collapsed outline groups, or just want to analyze a subset of information, the SUBTOTAL function makes it simple.

By adding the appropriate function number argument, you can quickly modify any traditional SUM, AVERAGE, COUNT, MIN, MAX or other formula to ignore hidden values and only calculate visible cells. This saves you the hassle of manually adjusting ranges, creating extra columns, or splitting up your data.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.