# How to Use Excel Formulas on Visible Cells Only?

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:

Function | Include Hidden | Ignore Hidden |
---|---|---|

AVERAGE | 1 | 101 |

COUNT | 2 | 102 |

COUNTA | 3 | 103 |

MAX | 4 | 104 |

MIN | 5 | 105 |

PRODUCT | 6 | 106 |

STDEV.S | 7 | 107 |

STDEV.P | 8 | 108 |

SUM | 9 | 109 |

VAR.S | 10 | 110 |

VAR.P | 11 | 111 |

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:

Month | Sales |
---|---|

January | $10,000 |

February | $12,000 |

March | $8,000 |

April | $14,000 |

May | $11,000 |

June | $9,000 |

July | $13,000 |

August | $8,500 |

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

### What is the purpose of using formulas on visible cells only in Excel?

Using formulas on visible cells only in Excel allows you to perform calculations, such as SUM, AVERAGE, COUNT, etc., while ignoring hidden or filtered out cells. This is useful when you want to analyze a specific subset of data without including hidden values.

### How do I create a formula that only calculates visible cells in Excel?

To create a formula that only calculates visible cells in Excel, use the SUBTOTAL function with the appropriate function number. The syntax is: =SUBTOTAL(function_num, ref1, ref2, …), where function_num is a code for the calculation you want to perform (e.g., 109 for SUM, 101 for AVERAGE), and ref1, ref2, etc., are the cell ranges you want to include.

### What are some common function numbers used with SUBTOTAL?

Some common function numbers used with SUBTOTAL are: 101 (AVERAGE), 102 (COUNT), 103 (COUNTA), 104 (MAX), 105 (MIN), 106 (PRODUCT), 109 (SUM). To ignore hidden cells, add 100 to the standard function number (e.g., 109 for SUM ignoring hidden cells).

### Will SUBTOTAL formulas automatically update when I hide or unhide rows?

Yes, SUBTOTAL formulas will automatically recalculate when you hide or unhide rows, or when you apply or remove filters. There’s no need to manually update the formulas when you change the visibility of cells.

### Can I use SUBTOTAL formulas across multiple worksheets?

Yes, you can use SUBTOTAL formulas across multiple worksheets by using a 3D reference. For example, =SUBTOTAL(109, Sheet1:Sheet3!B2:B9) will sum visible data across the specified range in all three sheets.

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.