How to Subtotal with Multiple Levels in Excel Easily (Example)

If you work with large volumes of data in a table, this function will suit you very well. It will help you to have control of the table by visualizing the information in different ways. We will teach you how to make subtotals with multiple levels in excel, But first, let’s familiarize ourselves with certain concepts that we will use in the program.

Subtotal in Excel

Subtotals. We refer to the sum or the partial result of a certain group, which will be added to other results to get the grand total. Regarding the multi-level expression in Excel, It is because by using the subtotal command, it allows you to create data sets that meet a certain category. As groups are created, organized levels are established.

Subtotals in a table

So that you can follow us in the explanation, we will do it based on An example. Suppose we work with a data table of a company, whose data reflects the salaries of the employees, it is made up of the fields: Names, Department, Position, Delegation (Place) and Salary. Our mission, to know the average salaries by Departments.

Here we will use the Subtotal command for each Department, to manage this function you can access official support.

table reflecting ordered column

  1. Before using the command it is important to sort the column “Department”. To do this, we touch or select any cell in that column. Activate the “Data” tab and press sort from “A to Z”.
  2. Already ordered the data, we can use the function. We select any cell in the table and in the “Data” tab we click on the “Subtotal”.
  3. A dialog box called “Subtotals” will appear. In the field “For each change in” you will choose from the list the column that we order which is, “Department”.
  4. In “Function” choose the command that will help us to fulfill the mission, namely, “Average”.
  5. In “Add subtotal in” we activate the “Salary” box. Because we need the average salaries by department. And click on “OK”.

You will observe how schematize neatly the table according to our needs. On the left side is reflected 3 numbers. The 1 gives you the general average. The 2 shows you the average by department (We accomplished the mission). Number 3 details the information better, the subtotals by department. At this point we can do subtotals with multiple levels in Excel.

Adding multilevels

Suppose we are asked for the average of each position per department. To meet these conditions, we must first remove the previous subtotals to sort the charge column. So adding the command will create other levels sort order to reflect subtotals.

table that reflects the average of subtotals by levels

  • First we go to the “Subtotals” window and click the “Remove all” button.
  • Then clicking anywhere in the table. We go to the “Data” tab.
  • We click on “Order”. In the window we will see that the Department column is already ordered.
  • Then we click on “Add level”. And we select the column of “Charge”. If you want to take advantage of this moment, you can include more levels. For example, organize the column “Delegation (Place)”, because you want know the average salary by department in each position depending on the place. And click on “OK”.
  • We go to the “Subtotals” icon. And we repeat the previous process, steps 3, 4, 5 to include the subtotal for Department.
  • Then we use the “Subtotals” function for “Charge” again. This time we uncheck the box “Replace current subtotals”. And we touch the “Accept” button.

You will notice that more levels of scheme have been included on the left side, in number 3 you will notice the average per department and in turn the average per position. We hope you take advantage of this powerful tool that Excel offers you to work in an orderly manner with the data.

Source by

Deja un comentario