Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
193241306843174788305508285969291501482
| 1 | Do proper formatting of the data with using borders, font size and appropriate alignment of the content. | |
| Ans. | Cells.Select With Selection .HorizontalAlignment = xlGeneral .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:E").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$E"), , xlYes).Name = _ "Table5" Columns("A:E").Select ActiveSheet.ListObjects("Table5").TableStyle = "TableStyleLight2" Columns("F:G").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$F:$G"), , xlYes).Name = _ "Table6" Columns("F:G").Select ActiveSheet.ListObjects("Table6").TableStyle = "TableStyleLight12" Columns("H:J").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$H:$J"), , xlYes).Name = _ "Table7" Columns("H:J").Select ActiveSheet.ListObjects("Table7").TableStyle = "TableStyleLight9" Range("L11").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 Columns("K:K").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$K:$K"), , xlYes).Name = _ "Table8" Columns("K:K").Select ActiveSheet.ListObjects("Table8").TableStyle = "TableStyleMedium13" Columns("L:M").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$L:$M"), , xlYes).Name = _ "Table9" Columns("L:M").Select ActiveSheet.ListObjects("Table9").TableStyle = "TableStyleLight9" Columns("N:R").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$N:$R"), , xlYes).Name = _ "Table10" Columns("N:R").Select ActiveSheet.ListObjects("Table10").TableStyle = "TableStyleMedium3" Range("S12").Select ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.SmallScroll ToRight:=1 Columns("S:Y").Select ActiveSheet.ListObjects.Add(xlSrcRange, Range("$S:$Y"), , xlYes).Name = _ "Table11" Columns("S:Y").Select ActiveSheet.ListObjects("Table11").TableStyle = "TableStyleLight18" Columns("S:Y").Select ActiveSheet.ListObjects("Table11").TableStyle = "TableStyleMedium3" Range("X13").Select ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("G14").Select Application.Run "'Excel challenge-7.xlsx'!Macro1" End Sub |
|
| 2 | Make summary of Products Sales by providing Total Sum of Sales Average of Sales Standard Deviation of Sales Variance of Sales Maximum Sales Minimum Sales |
|
| Ans. | Sum of SALES | |
| Average of SALES | ||
| StdDev of SALES | ||
| Var of SALES | ||
| Max of SALES | ||
| Min of SALES | ||
| 3 | Analyse the sales happened by product line & status using pivot table | |
| Ans. | pivot-1 | |
| 4 | ||
| 5 | Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table. | |
| Ans. | LINK | |
| 6 | Present the data in different colours for values in field Status and ProductLine using conditional formatting. | |
| Ans. | sales_data | |
| 7 | Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products. | |
| Ans. | sales status | |
| 8 | Split the productcode field into two using text functions Example :S10_1678 -> S10 in one column and 1678 in another column. | |
| Ans. | split |