How to Remove Subtotals in Excel

Excel will create a subtotal when applying a certain function to cells. This could be for the average, sum, or median of your values, giving you a comprehensive view of the values. However, subtotals aren’t always preferable. You might need to reanalyze the data, import it into other platforms or simplify it altogether. Fortunately, there are some simple solutions for eliminating subtotals in Excel.

This article will explain everything you need to know.

The Quickest Method to Remove Subtotals on Excel

Let’s say you’re tracking inventory on your Excel project sheet. At the bottom of your data, you have a row marked as the grand total. This is an example of a sub-row you may want to eliminate.

Here’s how it’s done:

1. Navigate to the upper ribbon of Excel.
   
2. Select the “outline” group and then move to “subtotal.”
   
3. A pop-up window will appear. Select the “remove” option at the upper right corner.
   

It’s that simple. When you select the remove option, all your data becomes ungrouped, and the subtotals will disappear. If you want to ungroup data rather than deleting all the subtotals, select the “ungroup” option instead.

Adding Back Subtotals in Excel

Removing subtotals is easy. With just a few clicks, you can ungroup your data. However, you might want to apply them back again after reanalyzing the values. Doing so will require certain functions. But don’t let that scare you. Adding subtotals is a straightforward process allowing you to better analyze your data for future use.

Here are some subtotal functions that you can make for your data:

• The SUM function – You can set a certain group of values and add them all together. The subtotal will display this number for your chosen value group.
• The AVERAGE function – This function is a favorite of teachers worldwide. It calculates the average of assigned table data. This makes it a good choice for calculating final grades comprised of test scores.
• The COUNT function – When tracking inventory, the COUNT function is a valuable asset. It counts the value entries you’ve defined.
• The MAX and MIN functions – Using either of these functions gives you the maximum or minimum value within a selected data set.

Now that you know some essential functions, you can move on to creating subtotals. However, ensure your list is sorted properly. Here’s what you need to do:

1. Go to the columns where you want to select data. Select the “data tab” and then the “filter option.”
   
2. Select one of the filter options available such as “Sort A to Z.”
   

That’s it. With your content sorted and with no blank values, you can add functions and create subtotals for your data.

1. Select any cell, group, or value where you want to add subtotals.
   
2. Go to the upper ribbon and find the data tab. Then select “subtotal.” After the pop-up window opens, you’ll be able to define the conditions.
   
3. You’ll need to select the function you want to use, the column to group by, and which columns to subtotal, and then select “OK.”
   

Once complete, you should see your set subtotals on your spreadsheet.

Things to Consider When Dealing With Excel Subtotals

While subtotals are a handy way to organize, analyze, and prioritize your data, they might seem a bit unpredictable. There are a few reasons behind the issue. Subtotals have certain conditions you’ll need to address before adding or removing them from your Excel spreadsheet.

If you’re frustrated with SUM or AVERAGE not working, here’s how to solve the problem:

• Ensure that none of your desired information is filtered out – there’s a filtering function on Excel. If you select some values for a subtotal, it won’t include any cells hidden by filtering. However, if manually hidden, the subtotal will still include it. If you want to leave out some data, make sure it’s filtered out rather than manually hiding it.
• Subtotals only reflect original data – Let’s say you have multiple subtotals added and want to add a new one. The latest subtotal will only include the original cell data, ignoring the value of subtotals you’ve already added.
• Subtotals won’t work with Excel tables – You might notice that your subtotals option is greyed out. Unfortunately, the option isn’t available for Excel tables. Ensure that you’re working with a normal range.

How to Get Rid of Empty Cells in Excel

Empty cells can be an annoying problem for anyone wanting to use subtotals or accurately track their data. Excel simply won’t group cells if they’re empty. If you’re using a function, you’ll need to remove these spaces.

Fortunately, there are a few methods to do this quickly and easily:

1. Select all your cells in the worksheet by using the command CTRL + A.
   
2. Press the F5 key on your keyboard. This will open Excel’s “Go to” option.
   
3. Click “Special” from the options and select the “blanks” option and then click on “OK.”
   
4. Then use the command CTRL + -. A new deletion pop-up window will open, then select “OK.”
   

The above method is quick because it uses commands. However, there are still other effective ways to delete empty cells. If you need to delete a whole empty row, follow the steps below:

1. Select the empty rows you want to delete.
   
2. Go to the upper ribbon and select the delete option. It has a table icon with an “X” on it.
   
3. Select “delete sheet rows.”
   

Deleting empty values and rows not only primes your spreadsheet for optimal subtotaling in Excel but also makes your data easily readable and neat for analysis. Once you’ve gotten rid of the empty cells, you can use the methods above to apply subtotals and remove them if needed. However, beware of any filtered or hidden data, as this won’t appear on the final subtotal amount.

FAQS

Are there more Excel subtotal functions than the ones mentioned above?

Yes, the ones mentioned above are just the main and widely used functions for subtotals. Other examples include SUMIF, COUNT, and COUNTIF.

Can I use percentage functions with Excel subtotals?

Unfortunately not. Excel doesn’t allow percentage functions to work with subtotaling.

What real-world scenarios will generally need subtotaling?

You can use the subtotal function in any field to organize and structure data. This includes teaching, HR, inventory management, and other fields where Excel spreadsheets are necessary.

Losing Subtotals and Bringing Them Back

When working with Excel data, making a mistake with subtotals is easily done. You might apply a function only to realize that you need to reanalyze specific fields. You can easily do so by going to the upper ribbon, navigating to the outline group, and selecting the subtotal option. Once you’ve removed the subtotal and reestablished the data, you can reapply them by adding functions. When working with subtotals, ensure that your cells aren’t empty and that all of the information is there and not filtered out.

Did you find it easy to remove subtotals? How about adding them back again? Let us know in the comments section below.