Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
A. Proper fomatting of data.
- Made the heading bold
- Increased font size to 12
- Added border to all the data
- Changed Quater ID 1, 2, 3, and 4 to QTR 1, QTR 2, QTR 3, and QTR 4 using ="QTR "& cell reference
- Center aligned the heading
- Added space between words in the heading
- Converted data into table
- Alligned data appropriately
- Changed formate of Price to US dollar
B. Summary of sales provide in "summary of sales" worksheet.
C. Analysis of sales pivot table in "Analysis of sales" worksheet.
D. Created a custom tab named ABC and added Borders, Shading, Font color, Font size, Increase font size, and Sum functions to it.
E. Analysis of the "Quantities ordered" by each Country in Year 2019,2020 and 2021 for different Quarters.
F. Applied different colors to cells from Status and Product Line based on the text in the cell using conditional formatting
G. changed the values of 2003 to 2019, 2004 to 2020 and 2005 to 2021 using find and replace.
H. Total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products in "Total sales" worksheet
I. Product code column split into Product code A and Product code B using formula "=LEFT([@[PRODUCT CODE]],SEARCH("_",[@[PRODUCT CODE]])-1)" and "=RIGHT([@[PRODUCT CODE]],LEN([@[PRODUCT CODE]])-SEARCH("_",[@[PRODUCT CODE]]))"
J. Top 10% in "Quantity ordered" highlighted using conditonal formatting.
=IF(ISNUMBER([@[ORDER DATE]]),DATE(YEAR([@[ORDER DATE]])+16,MONTH([@[ORDER DATE]]),DAY([@[ORDER DATE]])),"")