This is a simple Microsoft Excel "Sales" dashboard
beginners in Data Analytics can learn from.
Follow the step-by-step instruction to create it and
get the dataset at the end.
beginners in Data Analytics can learn from.
Follow the step-by-step instruction to create it and
get the dataset at the end.
1. Get the Data.
This data was imported from the Northwind Traders database template for Microsoft Access.
Get the file and the content at the end of this thread.
This data was imported from the Northwind Traders database template for Microsoft Access.
Get the file and the content at the end of this thread.
2. Create a PivotTable.
Select anywhere in your data range, then go to Insert > PivotTable > New Worksheet > OK.
Select anywhere in your data range, then go to Insert > PivotTable > New Worksheet > OK.
3. Create a PivotTable for Category.
β’ Click anywhere in the PivotTable area on the left to launch the Field list, which will appear to the right.
β’ In the Field list, click Category and Sales, then drag Sales into the Value field so it appears twice.
β’ Change the Value Field Settings for the second Sales field to Show Values As > % of Grand Total, then Format as Percentage.
β’ Format your PivotTable as desired by renaming header fields, number format and PivotTable style.
β’ Click anywhere in the PivotTable area on the left to launch the Field list, which will appear to the right.
β’ In the Field list, click Category and Sales, then drag Sales into the Value field so it appears twice.
β’ Change the Value Field Settings for the second Sales field to Show Values As > % of Grand Total, then Format as Percentage.
β’ Format your PivotTable as desired by renaming header fields, number format and PivotTable style.
4. Create copies of your PivotTable.
β’ Select the entire PivotTable, copy it with Ctrl+C, then paste it 3 times.
β’ Change each PivotTable to reflect the details you want to display.
β’ You can Sort by values by clicking the Sort button in the Category field, then select More Sort Options.
β’ At this point you might want to give your PivotTables meaningful names, so you know what they do.
Otherwise, Excel will name them PivotTable1, PivotTable2 and so on. Go to PivotTable Tools > Analyze > Rename the PivotTable in the PivotTable Name box.
β’ Select the entire PivotTable, copy it with Ctrl+C, then paste it 3 times.
β’ Change each PivotTable to reflect the details you want to display.
β’ You can Sort by values by clicking the Sort button in the Category field, then select More Sort Options.
β’ At this point you might want to give your PivotTables meaningful names, so you know what they do.
Otherwise, Excel will name them PivotTable1, PivotTable2 and so on. Go to PivotTable Tools > Analyze > Rename the PivotTable in the PivotTable Name box.
5. Create PivotCharts.
β’ Click anywhere in the first PivotTable and go to PivotTable Tools > Analyze > PivotChart > select a chart type.
β’ We chose a Combo chart with Sales as a Column chart, and % Total as a Line chart plotted on the Secondary axis (see below).
β’ Select the chart, then size and format as desired from the PivotChart Tools tab.
β’ Repeat for each of the other PivotTables
β’ Now is a good time to rename your PivotCharts too.
β’ Click anywhere in the first PivotTable and go to PivotTable Tools > Analyze > PivotChart > select a chart type.
β’ We chose a Combo chart with Sales as a Column chart, and % Total as a Line chart plotted on the Secondary axis (see below).
β’ Select the chart, then size and format as desired from the PivotChart Tools tab.
β’ Repeat for each of the other PivotTables
β’ Now is a good time to rename your PivotCharts too.
6. Add Slicers& Timeline.
β’ Temporarily increase row 1's height
β’ Select any PivotTable and go to PivotTable Tools > Analyze > Filter > Insert Slicer, then check Category, Product, Company and Employee.
β’ 4 Slicers will be placed on the worksheet, so you'll need to arrange them as you see fit. If you click on each Slicer you can go to Slicer Tools > Options and change the Style, how many columns are displayed, etc. You can also align the Slicers to each other.
β’ Slicer Connections - The Slicers will only be connected to the PivotTable you used to create them, so you need to select each Slicer then go to Slicer Tools > Options > Report Connections and check which PivotTables you want connected to each. Slicers and Timelines can control PivotTables on any worksheet.
β’ Select any PivotTable and go to PivotTable Tools > Analyze > Filter > Insert Timeline > select Order Date.
β’ Select the Timeline and place it wherever you want it, then go to Timeline Tools > Options > Report Connections and check each PivotTable you want to connect.
β’ Final adjustments - Now you can make any final adjustments, like turning off Gridlines and Headings (see the View tab), adding a report title and moving things around so they fit your needs.
β’ Temporarily increase row 1's height
β’ Select any PivotTable and go to PivotTable Tools > Analyze > Filter > Insert Slicer, then check Category, Product, Company and Employee.
β’ 4 Slicers will be placed on the worksheet, so you'll need to arrange them as you see fit. If you click on each Slicer you can go to Slicer Tools > Options and change the Style, how many columns are displayed, etc. You can also align the Slicers to each other.
β’ Slicer Connections - The Slicers will only be connected to the PivotTable you used to create them, so you need to select each Slicer then go to Slicer Tools > Options > Report Connections and check which PivotTables you want connected to each. Slicers and Timelines can control PivotTables on any worksheet.
β’ Select any PivotTable and go to PivotTable Tools > Analyze > Filter > Insert Timeline > select Order Date.
β’ Select the Timeline and place it wherever you want it, then go to Timeline Tools > Options > Report Connections and check each PivotTable you want to connect.
β’ Final adjustments - Now you can make any final adjustments, like turning off Gridlines and Headings (see the View tab), adding a report title and moving things around so they fit your needs.
Get the data through the link below:
eyowhite.com
eyowhite.com
If you found these posts helpful then consider getting
this FREE eBook on Excel's aggregate functions:
Follow the thread on how to get yours.
this FREE eBook on Excel's aggregate functions:
Follow the thread on how to get yours.
Get your copy within 24-48 hours:
πRetweet & share.
πLike & reply "yes".
πFollow.
Sign-up below:
eyowhite.com
πRetweet & share.
πLike & reply "yes".
πFollow.
Sign-up below:
eyowhite.com
Loading suggestions...