The Excel Document Inspection window shown below opens up.Ĭlick Inspect to identify hidden content, and then click Remove All to remove the item of your choice.Īfter you have completed these steps, save the document, and the spreadsheet will not contain the hidden data that you have removed. To open the Document Inspector, click File > Info > Check for Issues > Inspect Document. Use the Document Inspector to Remove Hidden Data Repeat the process for each year’s column.Avoid unintentionally distributing hidden information, such as the document's author, names that are associated with comments or tracked changes, or hidden cells or worksheets, by removing hidden data. The data table will convert that cell range (normally, D3:D28) to it’s Data Table range name. Underneath the column for each year, type the SUBTOTAL function, indicate 9 for the function_num, and select the range of cells in the table for the corresponding year. Let’s replace the calculations in the Total row. Since we are only dealing with filtered rows and not hidden rows, both 9 and 109 will work equally well. In this example, we want the SUM calculation from SUBTOTAL. In other words, with three-digit values, what you see is what you get.Ī summary of the potential values of function_num is below: Three-digit values indicate that SUBTOTAL should ignore both filtered rows and any rows that are manually hidden. One- and two-digit values calculate only values that show in the filtered table, but they also include any manually hidden rows in the spreadsheet. The function_num indicates which type of calculation you would like to perform. The syntax for SUBTOTAL is as follows: = SUBTOTAL( function_num, ref1. It can perform the same function as AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, and VARP, all while taking into consideration hidden and filtered rows. The SUBTOTAL function in Excel works as a kind of master function for many others. Click on the filter icon by the Region header, choose the Select All option in the drop-down menu, and click OK. First, we should remove the filter on Region so we can work with the entire table at once.

Let’s try replacing SUM with SUBTOTAL in the Total row of the table. 5Using SUBTOTAL to SUM a Filtered Table.

Excel 2016 select visible cells icon