Excel Guides

Recalculating when Filtering in Excel

When you filter data in Excel, only the visible cells are included in the calculations. This can be frustrating if you're trying to sum a column of data and only want to include the cells that meet certain criteria. However, there is a way to force Excel to recalculate even hidden cells.

To do this, you need to use an array formula. An array formula is a type of formula that allows you to perform multiple calculations on one or more sets of values. To create an array formula, you first need to select the range of cells that you want to include in the calculation. Then, enter the formula as usual, but enclose it in curly braces ({}).

For example, let's say you have a column of data that contains sales figures for each month. You want to sum only the cells that contain data for January. You could use the following array formula:

=SUM({1;4;7;10;13;16;19;22;25;28;31})

This would add up all the cells in the range that contain data for January (1, 4, 7, 10, 13, 16, 19, 22, 25, 28, 31).

You can also use logical operators (<>, =, >, <) in your array formulas. For example, let's say you want to sum only the cells that contain data for months other than January. You could use the following array formula:

=SUM({2;3;5;6;8;9;11;12;14;15;17;18;20;21;23;24;26;27;29;30})

This would add up all the cells in the range that contain data for months other than January (<>, 2, 3, 5, 6, 8, 9, 11, 12, 14-30).

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.