Excel Workout 3 - Subtotals

[IlgarZarbaliyev]

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
Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.

Task
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
Reply to this post with your screenshot and solution file. Please be sure to blur or hide your screenshot.

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)

[peps984]

Interesting and tricky challenge!

Challenge #3.xlsx (8.3 MB)

[Keith]

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

Excel Workout Challenge #3 Keith Smith.xlsx (8.4 MB)

Thanks for the challenge
Keith

[HermioneGranger1176]

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)

[mbraun]

Great reminder that you can add multiple subtotal layers.

Challenge #3.xlsx (8.3 MB)

[KimC]

@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)

[jany.gibson]

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)

[QuadriAtharuOlayinka]

Quadri Atharu Challenge #3 - Solution.xlsx (8.3 MB)

Steps

Solution Snapshot

[IlgarZarbaliyev]

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!

[Roger]

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

roger

[homesh_agarwal]

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

C3_Subtotals_Homesh.xlsx (51.3 KB)

[Sujan_Chhetri]

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