Excel Delete Entry With Out Deleting The Formula How To Display Or

You will also learn how to display or hide the subtotal details, copy only subtotal rows, and how to remove subtotals.

Luckily, Microsoft Excel provides a powerful Subtotal feature that lets you quickly summarize different groups of data and create an outline for your worksheets.

It allows you to group and summarize your data using SUM, COUNT, AVERAGE, MIN, MAX and other functions.

The easiest way to do this, is click the Filter button on the Data tab, then click the filter arrow, and select to sort either A to Z or Z to A.

To show a summary row below the details row, select this check box (usually selected by default).

The subtotals will appear below each data group, and the grand total will be added to the end of the table.

Below, you will find the detailed explanations of Subtotals specificities.

However, it includes values in rows hidden manually, i.e.

Hide Rows command on the Home tab Cells group Format Hide Unhide, or by right clicking the rows, and then clicking Hide.

The function is defined by the number in the first argument (functionnum) that belongs to one of the following sets.

Where 9 represents the SUM function, and C2:C5 is the first group of cells to subtotal.

However, if you hide those rows manually, they will be included in the subtotals.

Just compare the following screenshots to see the difference.

Since the Subtotal feature cannot be used with Excel tables, you would need to convert your table to an ordinary range first.

Please check out this tutorial for the detailed steps: How to convert Excel table to range.

And now, lets take it further and add subtotals for inner groups within the corresponding outer groups.

More specifically, we will group our sample data by Region first, and then break it down by Item.

To do this, go to the Data tab Sort Filter group, click the Sort button, and add two or more sorting levels.

As the result, you will have Sales and Profit subtotals per Region.

This can be the same column(s) as in the outer subtotals or different ones.

It is the key point that prevents overwriting the outer level of subtotals.

In fact, you can summarize data in the same column with as many different functions as you want.

Just remember to clear the Replace current subtotals box every time you are adding the second and all subsequent levels of subtotals.

Depending on how many subtotals you have inserted in your worksheet, there may be one, two, three or more in-between numbers in the outline.

Excel For Mac How To Delete Entry With Out Deleting The Formula