Conventional Subtotalling in Excel and VBA

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to perform subtotal and the alternative way to perform the same operation using VBA. Let’s get started


In the caption below, we have our dataset in Subtotal sheet tab

We want to calculate the Net Salary for each of the Department

To do that, we must ensure that our dataset is not formatted in Excel Table and Department in this case, is sorted in Ascending order.

  • Click anywhere in the dataset
  • In the Outline group of the Data tab, click on Subtotal
  • In the Subtotal dialogue box, select Department from the at each change in drop-down

  • All other automatic selection are fine. Click OK

In the caption above, we can see that the Total Net Salary for all the Accounting department employees is ₦209,576 in row 6 and in the status bar respectively


In the caption below, we have our dataset in VBA Subtotal sheet tab

In the above caption, I inserted rows to demarcate the records for each department. After writing and running the VBA code, we expect the result to be displayed in cells I6, I12, I16, I20, I26 and I42 respectively

  • On the keyboard, execute ALT + F11 to open the VBA Editor
  • In the Insert tab, click on Module
  • In the code window, execute the few lines of code as seen in the caption below

  • Execute F5 on the keyboard or the green play button to run the code

In the above caption, the Total Net Salary for Accounting department employees is ₦209,576.00 which is the same with the conventional subtotal result


We have seen how to use conventional Subtotal tool located in the Data tab on the ribbon to calculate the Total Net Salary for each department. We also used the VBA to achieve the same outcome. I hope you learn something new

Posted in Blog and tagged , .

Leave a Reply

Your email address will not be published. Required fields are marked *