_edited_edited.png)
The dataset being used below is the Superstore Dataset available on Tableau Community.
Steps to create a Dashboard in Microsoft Excel
Open a new excel workbook
Rename sheet1 — Dashboard,
Rename sheet2 — Data
Copy your data into DATA sheet of the working book,
Select the data and click insert Pivot Table
1.Click the option — From table/Range
2. Leave table range as such and click new worksheet, a new sheet will be created and rename it as Analyze
3.On your right side, pivot table fields will appear. There will be four sections filters, columns, rows, values. Drag sales into values — display as sum of sales(aggregate) and Sub-category into rows. New pivot table data will be created.
4.These are 3 KPIs needed to display in dashboard sheet. Now, copy and paste the pivot table data into the new cell.
5.Remove the fields from all the sections and add sub-category to the rows section and sales into value section.
Select the pivot table(sub-category/Sales) and click insert, choose recommended charts — click column chart.
Sales By Sub-Category Chart will be created.
Format the chart
o Edit Title and Right click on the bar and sort it.
o Hide remaining all buttons
o Remove the gridlines
Go to Analyze sheet again, copy the pivot table and paste into new cell.
Now another pivot table fields will appear to the right side. Remove the fields from all the sections and add region to the rows section and sales into value section.
Select this pivot table, and click insert, choose recommended charts — click pie chart.
Format the chart
o Edit Title
o Hide remaining all buttons
o Remove the gridlines
Follow the same steps to create another chart — a line chart displaying monthly sales.
Add month(ORDER DATE) to the row section.
Format the chart
o Edit Title
o Hide remaining all buttons
o Remove the gridlines
Formatting the dashboard sheet in workbook
First let’s draw the structure of our dashboard,
Insert and choose text box from shapes, write the title, fill the box with color and make it centered.
Similar way, create place holders for KPI measures and charts.
Add KPI Values respectively. Click on the Total Sales Box, at the end of the text “Total Sales”, Click enter, Now the cursor is in the down cell. Add new text box, then enter the sales amount using the below formula. Format the text box as per KPI box
=Analyze!A4
Copy the charts from Analyze sheet into the respective placeholders(dashboard sheet) and format it.
Reference :