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
SUBTOTAL IN CONVENTIONAL EXCEL METHOD
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
SUBTOTAL IN EXCEL VBA
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
CONCLUSION
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