Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
a. uploaded file.
b.
| sum of sales | 10032628.85 |
| avg of sales | 3553.889072 |
| standard deviation of sales | 1841.865106 |
| variance of sales | 3392467.068 |
| maximum of sales | 14082.8 |
| minimum of sales | 0482.13 |
file is also uploaded.
c) File is uploaded.
d) File is uploaded .
e.
| Row Labels | Sum of QUANTITYORDERED |
| 2019 | 34612 |
| Australia | 2514 |
| Austria | 872 |
| Belgium | 47 |
| Canada | 500 |
| Denmark | 903 |
| Finland | 1086 |
| France | 2971 |
| Germany | 710 |
| Italy | 1480 |
| Norway | 1755 |
| Philippines | 803 |
| Singapore | 1452 |
| Spain | 4205 |
| Sweden | 602 |
| UK | 1864 |
| USA | 12848 |
| 2020 | 17631 |
| Australia | 1500 |
| Austria | 611 |
| Belgium | 228 |
| Canada | 285 |
| Denmark | 174 |
| Finland | 1328 |
| France | 2487 |
| Italy | 460 |
| Japan | 336 |
| Singapore | 111 |
| Spain | 3262 |
| Sweden | 316 |
| UK | 371 |
| USA | 6162 |
| 2021 | 46824 |
| Australia | 2232 |
| Austria | 491 |
| Belgium | 799 |
| Canada | 1508 |
| Denmark | 1120 |
| Finland | 778 |
| France | 5632 |
| Germany | 1438 |
| Ireland | 490 |
| Italy | 1833 |
| Japan | 1506 |
| Norway | 1087 |
| Philippines | 158 |
| Singapore | 1197 |
| Spain | 4962 |
| Sweden | 1088 |
| Switzerland | 1078 |
| UK | 2778 |
| USA | 16649 |
| Grand Total | 99067 |
f.The colour of status and productline is formatted using conditional formatting and uploded.
g) open power pivot table and divide the year coloumns as seperate tables and select diagram view option and make attachments to the years to swap the values from one year to another year.
h)
| Row Labels | total sales |
| Cancelled | 194487.48 |
| Disputed | 72212.86 |
| In Process | 144729.96 |
| On Hold | 178979.19 |
| Resolved | 150718.28 |
| Shipped | 9291501.08 |
| Grand Total | 10032628.85 |
the total sales of shipped,cancelled etc are analyzed with pivot table and uploaded file name as powerpivot.
i) To split the one coloumn into two columns as text and numbers seperatley
- select the entire column and go to data tab ribbon
- select on text to columns option and
- set the delimiter or fixed options to split the column
- the productcode is now splitted to product and code seperatley.File is attached name split.
j) Quantityordered is highlighted with top 10% values using conditional formatting and uploaded file with name quantityorder.