Scribd
Upload
8 views5 pages

Excel Pivot Table Analysis Guide

The document outlines a series of Excel and Alteryx assignments focused on analyzing sales data from a superstore dataset. It includes tasks such as calculating segment-wise total sales, average sales, and customer order counts while providing hints for using pivot tables and formulas. The assignments aim to enhance skills in data manipulation and analysis using specific Excel functions and techniques.

Uploaded by

Shreyanshi mahapatra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views5 pages

Excel Pivot Table Analysis Guide

The document outlines a series of Excel and Alteryx assignments focused on analyzing sales data from a superstore dataset. It includes tasks such as calculating segment-wise total sales, average sales, and customer order counts while providing hints for using pivot tables and formulas. The assignments aim to enhance skills in data manipulation and analysis using specific Excel functions and techniques.

Uploaded by

Shreyanshi mahapatra
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

[Link].

com
+91 7303066379

Below assignment covers Excel pivot tables, remove duplicates, VLOOKUP , sumif formula and if
the else for cumulative sales. Please do this first in excel and then in Alteryx. The numbers should
match. Use super store original data file.

Quest 1: What is segment wise total sales.

Quest 2: Get segment- Category wise total sales, avg sales, and total quantity
[Link]
+91 7303066379

Hint/: Right click on pivot and in display check ‘classic pivot’ option.
then when you have selected pivot, go to design tab on top menu, in report layout- check ‘repeat
row labels’ and in subtotals- check ‘do not show subtotals’

Quest 3: For year 2016- segment wise show me total sale, Count of records
[Link]
+91 7303066379

Quest 4: Get the sale of each customer- also the no of orders of orders places by him

Hint: Don’t get the normal count of orders as it will have duplicate values. Get customer orders
count by removing duplicates and then join back with sales using VLOOKUP.
[Link]
+91 7303066379

Quest5: Get segment wise sale contribution in percentage of total sales:

Hint: divide total by Grand total and freeze grand total cell in the calculation using F4
[Link]
+91 7303066379

Quest6: Get year wise segment sales and get which year had the maximum sale.
Hint: NO manual writing. Get year from a formula which will look like below :

= IF(G23=C23,2013,
IF(G23=D23,2014,
IF(G23=E23,2015,
2016)))

Quest7: get for each segment, customer name with maximum sale, no of orders of that customers
and sales given by that customer.

Quest8: get for each segment, year wise sales, % contribution of that year’s sale in that segment,
and segment wise cumulative sales. Hint: use formula like

For year wise total sale= SUMIF($A$23:$A$34,A23,$C$23:$C$34)

= IF(A24=A23,F23+C24,C24) to get cumulative sales

You might also like