When creating a pivot table, you may have noticed that it generally tends to include subtotals. When you analyze the information contained in the table, the subtotals can deconcentrate you. In simple steps we show you how to hide subtotals in an Excel pivot table . Before we see, the usefulness of this resource.
The subtotals command in Excel allows you to create a kind of summary of data based on rows. That is, it gives us the summation by category or data ranges automatically, with the help of functions used in Excel such as: Sum, average, count, product, max., Min., And more.
We can use subtotals when working with well-organized data tables. So you can perform total and partial computations of the tables. It should be noted that, they are not used in any tables; but in tables that include ranges of data such as the dynamic tables that we explained a while ago in our blog.
How to hide subtotals in pivot table
As noted above, pivot tables often include subtotals when grouping multiple levels. In this case, we will help you with a simple explanation how to hide subtotals in a pivot table in Excel. There are 2 easy ways to do it:
- Option 1. Select the subtotals from the table with the mouse. If there are many, to select it all at once, place the mouse at the beginning of a row where the subtotal is found. A small black arrow will appear pointing to the right. At that time select it. And it will highlight all subtotals.
- Go to the top menu. In the active section “ Pivot Table Tools ”. Activate the “Design” tab.
- Press the “Subtotals” icon.
- Then press the item “Do not show subtotals”. And ready. You will notice that they are no longer reflected in your pivot table.
- Option 2. Select the subtotals with the mouse.
- Click the right mouse button. A pop-up menu appears.
- Select ” Field Settings .” A window opens.
- Activate the “Subtotals and filters” tab. And in the Subtotals section, check the “None” box. And you will get the same result.
If you later want to retrieve the subtotals command, use the same indications mentioned above. But this time, you enable the option “Show all subtotals at the bottom of the group” . And you will see them again. For more extensive information, you can go to Microsoft Office Excel support .
Interesting facts about this command
- When executing the subtotals function, you should take into account that, regardless of the number value of the function you use, it does not take into account the rows that are not included in the result of a filter .
- This function is not intended to be used on rows of data, nor is it intended for horizontal ranges. It was designed to run in column of data or in vertical ranges .
- If you use 3D references between the references, the subtotal command will return an error such as: “#VALUE! Error # VALUE! ”.
- Something interesting to keep in mind is that, when using the subtotals function, you must use the range (1 to 11 and 101 to 111) as the first numerical argument. This argument can be applied to subtotals of the values that are: cell ranges and named ranges.
- When using the subtotals function, we recommend that you work with already completed data . If you apply it to databases that you are continually adding data to, later you will have to remove the subtotals to add new data.
As always, we guide you step by step with simple words to solve small problems with Excel. In this case, hide subtotals in a pivot table . Putting this knowledge into practice will help you develop skills in managing the program. We hope they’re useful to you. And if you know of a different method than the one we mentioned, share it with us.