Excel Workout 9 - Pivot Table 1

[IlgarZarbaliyev]

Welcome to Excel Workout #9!
Difficulty Level:

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

Pivot Tables
Pivot tables in Excel are powerful tools for data analysis that allow you to summarize and analyze large amounts of data quickly and easily. Pivot tables allow you to create summary reports from large data sets by grouping, filtering, and sorting data based on different criteria.

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

• Determine the percentage of sales made by each Manufacturer in each year, relative to their overall sales.

Submission
Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.

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

Challenge #9.xlsx (8.4 MB)

Good luck,

Ilgar Zarbaliyev

[Keith]

Hi @IlgarZarbaliyev ,

Thanks for the workout. Nice refresher on pivot tables.
Here is my solution:

[Excel Workout Challenge #9 Keith Smith.xlsx|attachment]spoiler (8.4 MB)[/spoiler]

file attached
Thanks
Keith

[MohammedHuzefaBagwan]

@IlgarZarbaliyev

Determine the percentage of sales made by each Manufacturer’s in each year, relative to their overall sales.

Here’s my solution to show both the Amount and %
Please see the attached solution file as well. Thanks
Solution - Challenge #9.xlsx (8.5 MB)

[QuadriAtharuOlayinka]

Challenge #9- Quadri.xlsx (8.4 MB)

[HermioneGranger1176]

The word their is a little confusing. Does it mean that we have to compare individual salespersons or total sales?

English is a funny language. Different people interpret it differently. It would be nice if we have the required output as an image. It would help us to get the logic.

Considering the word their in legal language, I would mean Sales of Individual Salesperson w.r.t. their Sales in all years.

Amount	Years
Manufacturers	2022	2013	2014	2015	2016	2017	2018	2019	2020	2021	Grand Total
Bubbletube	16%	9%	20%	21%	6%	9%	9%	5%	0%	5%	100%
Einti	11%	15%	9%	10%	4%	8%	11%	3%	14%	15%	100%
Eire	17%	6%	5%	11%	15%	6%	15%	14%	1%	11%	100%
Gabcube	4%	11%	18%	13%	7%	11%	8%	15%	1%	11%	100%
InnoZ	7%	9%	14%	15%	9%	7%	22%	4%	7%	7%	100%
Kaymbo	3%	10%	3%	11%	9%	22%	12%	6%	15%	10%	100%
Lazz	10%	4%	16%	5%	4%	10%	14%	17%	1%	20%	100%
Linktype	7%	2%	2%	17%	12%	28%	6%	10%	7%	9%	100%
Livetube	9%	9%	14%	5%	18%	9%	7%	15%	3%	12%	100%
Meemm	7%	9%	11%	3%	6%	19%	4%	18%	15%	10%	100%
Npath	4%	8%	10%	19%	8%	12%	13%	9%	13%	4%	100%
Shufflebeat	4%	15%	13%	4%	14%	11%	3%	12%	15%	10%	100%
Skilith	20%	9%	7%	9%	4%	10%	5%	9%	13%	12%	100%
Trudeo	15%	2%	9%	9%	14%	12%	9%	8%	11%	12%	100%
Voonte	11%	11%	16%	20%	12%	7%	2%	10%	11%	0%	100%
Grand Total	9.26%	8.46%	11.07%	10.81%	9.42%	12.35%	9.32%	10.38%	8.82%	10.12%	100.00%

Challenge #9.xlsx (8.4 MB)

[IlgarZarbaliyev]

@IlgarZarbaliyev

Determine the percentage of sales made by each Manufacturer in each year, relative to their overall sales.

Thank you for your message.

I hope and believe that the task - " * Determine the percentage of sales made by each Manufacturer in each year, relative to their overall sales." is very straightforward.

With their is meant “Manfucaturers”

[HermioneGranger1176]

Is my workings correct then? Or should I change my method to match what others have done.

[ClaudiaRoxo]

Here is my file.

Challenge #9.xlsx (1.8 MB)

[KimC]

Thanks Ilgar for the workout.

% of Grand Total

% by Manufacturer for each year

[jany.gibson]

Here is my solution. Although Conditional formatting was not asked, I found it necessary to easily read the data and provide insight, a little effort goes a long way. I also highlighted the most recent year and formatted based on 80/20 rule.

Challenge #9.xlsx (8.4 MB)

[IlgarZarbaliyev]

I will check on next Sunday:)))

[IlgarZarbaliyev]

@ClaudiaRoxo Great! Thank you for your active participation…Good to see you in this forum frequently

[MichaelSullivan]

Thank you for the workout!

Challenge #9.xlsx (8.4 MB)

[IlgarZarbaliyev]

Thank you @MichaelSullivan for your solution.

Good luck in next challenges.

[abbi]

workout #9 PIv.xlsx (8.4 MB)

[Anonymous1]

This text will be blurred

[IlgarZarbaliyev]

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

Here is my solution to Workout #9:

In this example, we need to find the percentage of each firm’s total sales over the years.

Challenge #9 with Solution.xlsx (8.9 MB)

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

[abbi]

workout #9 corrections.xlsx (8.4 MB)

[Anonymous14]

It was a good workout thank you @IlgarZarbaliyev looking forward to more challenges.

Challenge #9 Golam Mohammad Sardar.xlsx (8.4 MB)

[homesh_agarwal]

Presenting the required Pivot with the share of the Manufacturer’s sales over time.

I have cloned Pivot and created a Line Chart along with a Slicer to view the trend graphically.

Here’s the Workbook:
C9_Pivots1_Homesh.xlsx (182.4 KB)