If you use the AutoSum button in Excel, then figures in hidden cells are still used in the totals.
If, however, you first format the data as a table and then do a total within the table, the values in hidden cells are not included.
To format data as a table, click in any cell in the dataset, then press Ctrl + T or on the Home tab, in the Styles group, click Format as Table and select your required table style.
The data will be formatted as a table and the Table Tools Design contextual tab will be displayed. Check the Total box and in the Totals row, select the column or columns that you want to total (you can also choose Average, Count etc.)
Now, say I hide rows, 6 to 9, the Totals row will change to reflect only data that is displayed.
Find out more about our Microsoft Excel training in the London, Middlesex, Surrey areas.
Find out more about our Microsoft Excel training in the Dorset, Hampshire, Wiltshire areas.