FIND SUBTOTAL IN EXCEL

Table of Contents

INTRODUCTION

Excel provides us the option of analyzing the data in many simple ways by providing many ready-to-use helpful tools.

Totaling is one of the very basic operation which we use very frequently.

One more subcategory to the simple and final totaling is Subtotaling.

Subtotal is the total or sum of any values under any category or class which is a subsection of the main data.

For Example,

If we are analyzing the sales data for a month, we must have day wise data.

What if we want to get the sales happened only on Monday or Saturday or any other day.

In such cases, we can make use of the Subtotal option provided in Excel.

or

Suppose we have a data which shows the number of vehicles sold in april and may and four days of week.

We’ll be totaling the number of vehicles sold in one month and subtotal it.

Subtotal is required many times in our reports and we have to do this manually by inserting a row and then apply the addition on the data.

But Excel have this readymade option which we can use.

SUBTOTAL in excel provides us an easy way to do this automatically.

In this article, we’ll learn the use of subtotal function in Excel.

WHERE IS SUBTOTAL BUTTON IN EXCEL?

The option to do subtotal is present under the DATA TAB and OUTLINE SUBGROUP as shown in the pictures below.

BUTTON LOCATION OF SUBTOTAL IN EXCEL 2016 AND LATER:

SUBTOTAL BUTTON LOCATION IN EXCEL 2019, EXCEL 365:

SUBTOTAL BUTTON LOCATION IN EXCEL 2007:

The button is located under the DATA TAB> OUTLINE SECTION as shown in the picture below.

BUTTON LOCATION

EXAMPLES: STEPS TO DO SUBTOTAL IN EXCEL

The steps to do subtotal in Excel will be discussed with the help of an example.

EXAMPLE DETAILS :

Suppose we have a data which shows the number of vehicles sold in on specific four days of a week in the two months [ APRIL and MAY ].

We’ll be totaling the number of vehicles sold in one month and subtotal it.

​

SALES NUMBER ON WEEK DAYS

STEPS:

  • Select the data and . [ Select it including the Headings ].
  • Go to DATA TAB and click SUBTOTALS button under OUTLINE DROP DOWN [ Right most portion of the ribbon ]
  • The following screen will be shown.
SUBTOTAL DIALOG BOX
  • Choose the value as per your requirement.
  • The usage of every option is given below.
AT EACH CHANGE IN:  Choose the value which will be base of your grouping. For our example, we choose the MONTH to be the basis of our subtotaling. 

USE FUNCTION: Choose the function. This is the function which we want to apply on our data. The standard is SUM. We can also choose out of a long list containing the functions like average, count, standard deviation and more. This function will be applied on the sub group.

ADD SUBTOTAL TO: Choose the value which is to be added. Choose the value which will be put into the operation chosen. For our examples,, we'll chosen SUM of vehicles.

In our case we’ll be adding the vehicles sold. Fill the table and click OK.

SUBTOTAL BY VEHICLE NUMBER

RESULT :

The following picture shows the result of the example taken.

You can see that Excel didn’t just subtotaled but also grouped the months.

You can simply ungroup it to see the complete details.

It also groups the subtotals, which gives us the option of summarizing the tables easily. We can click the + sign and it’ll look like this.

RESULT

In this way, we can subtotal any data within seconds to analyze it.



FAQs

IS SUBTOTAL FUNCTION SAME AS SUBTOTAL UTILITY IN EXCEL ?

Any option in Excel needs to be manually applied whereas Function can be a part of the further bigger process.

Utility will work only on the specific area but function once applied can be generalized to work over many areas.

You can learn the SUBTOTAL FUNCTION here.



HOW TO ADD CONDITION IN SUBTOTAL IN EXCEL?

SUBTOTAL won’t allow you to add any condition but you can choose the operation from a given list in Excel.

The available operations are [ as per EXCEL 365. Previous versions may not have any specific functions ]

1SUM
2COUNT
3AVERAGE
4MAX
5MIN
6PRODUCT
7COUNT NUMBERS
8STDDEV
9STDDEVP
10VAR
11VARP
DIFFERENT CONDITIONS AVAILABLE IN SUBTOTAL [ EXCEL ]

You can choose any of the functions above to act upon the data.

For Example,

If we want to find out the maximum number out of the different values for any particular class, we’ll choose the function as MAX.



HOW TO REMOVE OR EDIT SUBTOTAL IN EXCEL ?

For changing any function or any selections, simply go to the DATA TAB and again chose the SUBTOTAL OPTION.

You can make changes in the selected formula if you need.

For Removal, click REMOVE ALL in the left bottom corner of the SUBTOTAL DIALOG BOX.



WHY IS MY SUBTOTAL SHOWING 0 IN EXCEL?

This happens when you have mistakenly taken the RESULTING FIELD i.e. the field or values on which you are applying the function doesn’t contain numbers.

Check the ADD SUBTOTAL TO field if you have chosen the correct data field or not.