Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
Hi ,while i am using the sales excel sheet which u have provided .Its not responding , iam using 2019 versoion
a.first fetch the given data into excel sheet and select the entier sheet ,create it as a table , change the font size to 14 ,change the alignment to center
b. i: =sum(select the column which contains sales) press enter
ii: =avg(select the column which contains sales)
iii: = stdev(select the colum which contains sales)
iv: =var(select the column which contains sales)
v: =max(select the column which contains sales)
vi: =min(select the column which contains sales)
c. select the entire sheet goto insert tab create a pivot table select the field status and drag into the row ,then select the productline drag into the column ,then select the sales drag into the sum .Then the pivot table is created.
d.goto home tab select the options then its shows the customized ribbon select it , then we can select what the tabs we want in it and save it as ABC.
e. select the sheet goto insert tab select the pivot table and select the country and quarters drag into row,then select the years and drag them into the column.
f. select the field status and productline into the pivot table we can change the color for the contents which is present in it by using conditional formating,we can highlight the cell rules ,highligth the top and bottom rules we can create icons for particular values .
g. select the dates you want to format ,on the home tab in that the number group is preseni ,click the number format box and select the desired form .
h. select the column called shipped ,cancelled, in process ,on hold, resolved and to find total create a pivot table and drag them to sum .
i. select the cells you want to splitselet data in that text to columns , then one dialog box will appear select delimited enter next and then select _ to split the data, select where you want the new column and enter finish .
j.select the quantity ordered field go to home tab click on conditional formatting under the home tab choose the top/bottom rules command then click on the top 10% option then, the pop up window will be opened select the % option we can choose any colors then press ok.then the 10th row will be displayed in perticular color.