Scribd
Upload
6 views10 pages

Sales Data Analysis with Pivot Tables

The document provides a detailed overview of sales data, including product prices, quantities, and total values, as well as monthly sales figures. It also explains the use of Pivot Tables and functions like VLOOKUP and HLOOKUP for data analysis. Additionally, it highlights the SUBTOTAL function for calculating sums of filtered values in Excel.

Uploaded by

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

Sales Data Analysis with Pivot Tables

The document provides a detailed overview of sales data, including product prices, quantities, and total values, as well as monthly sales figures. It also explains the use of Pivot Tables and functions like VLOOKUP and HLOOKUP for data analysis. Additionally, it highlights the SUBTOTAL function for calculating sums of filtered values in Excel.

Uploaded by

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

product price quantity total value

a 10 2 20 Pivot Table/chart is used to filter data. In pivot table we ca


fragment fields into columns & Rows and select Filter fiel
b 11 3 33
c 34 5 170
d 22 8 176
e 56 12 672
f 45 4 180
g 67 6 402
o filter data. In pivot table we can select or
mns & Rows and select Filter field as well.
Data
product Sum of price Sum of quantity Sum of total value
a 10 2 20
b 11 3 33
c 34 5 170
d 22 8 176
e 56 12 672
f 45 4 180
g 67 6 402
Total Result 245 40 1653
Data 1800
product Sum of price Sum of quantity Sum of total value 1600
a 10 2 20
1400
b 11 3 33
c 34 5 170 1200
d 22 8 176 1000
e 56 12 672
800
f 45 4 180
g 67 6 402 600
Total Result 245 40 1653 400
200
0
a b c d e f g To
Re
Data Sum of price
Sum of quantity
Sum of total value

c d e f g Total
Result
month week day date hour sales date - all -
Jan Sun 1 9 11
Jan Mon 2 9 23 Sum of sales week day
Jan Tue 3 9 25 month Sun
Jan Wed 4 9 23 Jan 30
Jan Thr 5 9 21 Total Result 30
Jan Fri 6 9 12
Jan Sat 7 9 11
Jan Sun 8 9 19
Jan Mon 9 9 18
Jan Tue 10 9 15
Jan Wed 11 9 16
Jan Thr 12 9 17
Jan Fri 13 9 18
Jan Sat 14 9 15

month Jan Jan Jan Jan


week day Sun Mon Tue Wed
date 1 2 3 4
hour 9 9 9 9
sales 11 23 25 23
Mon Tue Wed Fri Sat Thr Total Result
41 40 39 30 26 38 244
41 40 39 30 26 38 244
Vlookup
pos RUNNER PRIZE pos RUNNER PRIZE
1 WE 1000 3 ERT 700
2 ER 800 4 ERTY 500
3 ERT 700 5 DFG 200
4 ERTY 500 1 WE 1000
5 DFG 200 2 ER 800

Hlookup
pos 1 2 3 4 5
runner a b c d e
prize 10 8 7 5 2

pos 3 4 5 1 2
runner c d e a b
prize 7 5 2 10 8
month week day date hour sales
Jan Sun 1 9 11 1. SUBTOTAL is used to
other SUBTOTAL value
Jan Mon 2 9 23
Jan Tue 3 9 25
Jan Wed 4 9 23
Jan Thr 5 9 21
Jan Fri 6 9 12
Jan Sat 7 9 11
126
Jan Sun 8 9 19
Jan Mon 9 9 18
Jan Tue 10 9 15
Jan Wed 11 9 16
Jan Thr 12 9 17
Jan Fri 13 9 18
Jan Sat 14 9 15
118

SUBTOTAL 244

SUM 488
1. SUBTOTAL is used to find sum of filtered values or only visible values. 2. SUBTOTAL ignores
other SUBTOTAL values between range. 3. SUBTOTAL is directly present in Data TAB as well.

You might also like