# Excel Workout 3 - Subtotals

Welcome to Excel Workout #3!
Difficulty Level:

This week’s challenge is designed to test your knowledge on Subtotals.

The Subtotal feature in Excel is a tool that allows you to summarize data in a list or table by calculating subtotals for each group of related data. It is a quick and easy way to create a summary of your data without having to manually calculate each subtotal.

Goals

Calculate the total quantity in pieces and the cost of delivery in euros
for each Product. Within each item, calculate the same
values for each Supplier.

Submission

Period
This workout will be released on Monday April 03, 2023, and the author’s solution will be posted on Sunday April 09, 2023.

Ilgar Zarbaliyev

Challenge #3.xlsx (8.3 MB)

Interesting and tricky challenge!

Challenge #3.xlsx (8.3 MB)

1 Like

Hi there,
This was a tricky one for me as i haven’t done this for a long time. Thanks for the youtube

Thanks for the challenge
Keith

2 Likes

This challenge pushed me to use SubTotal in a unique way. I have never used it before.

1. Sorted data Product Wise and then Supplier Wise
2. Used Subtotal first on Product Column
3. Used Subtotal then on Supplier Column and Unticked Remove Existing Subtotals

Challenge #3.xlsx (8.3 MB)

2 Likes

Great reminder that you can add multiple subtotal layers.

Challenge #3.xlsx (8.3 MB)

2 Likes

@IlgarZarbaliyev thanks for the workout, here is my solution:

Summary

I got momentarily stuck on the last step and wasn’t unselecting the Replace current subtotals.

Excel workout Ch #3 Multiple Subtotals.xlsx (8.3 MB)

1 Like

I was aware of this functionality thanks to an Excel course, but I’m so used to using Pivot tables that I never used it and as a result it was left in the Excel attic. Thanks Ilgar!

Challenge #3.xlsx (8.4 MB)

1 Like
1 Like

Thank you for participating in the Excel Challenge related to Subtotal!
I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to perform Subtotal in Excel.

Here is my solution to Workout #3:

Sort

• Place your cursor anywhere in the range L4:S356

• Go to Data Tab, from Sort&Filter Group select Sort

• Sort by Products, add one level and select Supplier

• Sort on (Cell Values) and Order (A to Z)

Subtotal by Products

• Select Range L4:S356

• Go to Data Tab, from Forecast Group click on Outline Icon

• Select Subtotal from opening window

• Select the followings from the Text Boxes in order:

1. At each change in: Products

2. Use Function: Sum

3. Add Subtotal to: Check Q-ty, pcs and Delivery Cost, €

• Do not change the other options and keep them as instructed:
1. Replace current subtotals: Checked

2. Page Break between Groups: Unchecked

3. Summary below data: Checked

• Hit OK.

Subtotal by Suppliers

• Place your cursor again anywhere in the Range L4:S356

• Go to Subtotal through Data Tab and Forecast Group

• Select the followings from the Text Boxes in order:

1. At each change in: Suppliers

2. Use Function: Sum (Leave like this)

3. Add Subtotal to: Check Q-ty, pcs and Delivery Cost, € (Leave like this)

• Do not change the other options and keep them as instructed:
1. Replace current subtotals: Unhecked

2. Page Break between Groups: Unchecked

3. Summary below data: Checked

Hit OK.

Challenge #3 with Solution.xlsx (8.4 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

nice challenge but I think the subtotal function ( outline ) is very in use because of the flexible pivotables
kind regards

roger

1 Like

Sharing my solved workbook on Subtotals. I’ve formatted the table for better readability and enhanced presentation.

C3_Subtotals_Homesh.xlsx (51.3 KB)

1 Like

This was interesting workout. Here’s my submission: Challenge #3.xlsx (8.3 MB)

1 Like