Want to create this

From this:

In about 40 minutes?

Read on!!!

To create a dashboard in Excel, first you need to import the data into Excel, for this guide we will use the sample superstore dataset.

The link for the data set is given below:

Import the file into Excel using the “Data” tab on Excel.

The steps to import the data into Excel are as follows:

1. Click on the data tab and go to the first option on the left most side called “Get Data”.
2. Next click on “Get Data” and then from the drop down select the “From File” option.

3. Next select the last option “From Excel Workbook” and select the file that you have downloaded from the downloads folder and them select import in the window.

This will open the navigator window to select the particular tables and sheets in the workbook to you blank workbook.

Select the “Orders”, “People” and “Returns” sheets from the Navigator window by selecting “Select Multiple Items” from the top left of the Navigator window.

Next on selecting all the sheets click on the option “Load”.

This will load the data of the superstore example data set into the Data Model of the present workbook.

You can see the Queries and connections to the Data Model in the “Queries and connections” tab in the Data Tab.

Now this has set up the data model into the present Excel Workbook, now we will build the front end UI of the dashboard using simple pivot tables and slicers.

First go to the Queries and Connections setup and the Right click on the Sheets named Orders on the right side.

Select “Load To…” option on the menu and then follow the settings below.

Click on the PivotChart option and Select Existing workbook option and select the cell A1 on the worksheet of Excel.

This will create a Pivot Chart on the worksheet which we will customize for the Reports in the dashboard we are building.

Now we need to choose the fields from the PivotChart field list in the menu on the right side.

Now select the Row ID, Country, Segment, Ship Mode in that order to get the relevant PivotChart on the worksheet.

You will notice that as soon as you select the options given above they will get dropped into the bottom four Layout area of Filters, Legend, Axis and Values.

Your Fields List should now look as above and it will create a PivotChart as shown below you can adjust the width by dragging the edges and corners of the chart as per your requirements and display size.

You will see that this Chart made is interactive to the user. On the top you have the Numerics and on the bottom you have the categories of the chart. Country, Segment and Ship mode options are the categories and Sum of Row ID is the measure of the chart we are using to create the bar graph above.

This was generated automatically by the Excel and we can customize this too.

A normal glance shows that the “Sum of the Row ID” is a wrong measure as we just need the count of the Row ID. Hence we do the change as follows.

Go to the Fields List and select the Sum of Row ID in the values section and click on it.

It will open a menu where you need to select “Value Field Settings…”.

On selecting it will open a dialog box where you should select the “Count” instead of “Sum” and press on Ok.

Now when you see the PivotChart you will see that the axis on Y axis is changed from very big numbers to just in a scale of thousands.

This was about PivotChart.
Now let us select the slicers and make new slicers to slice and dice this information in the PivotChart.

To insert a slicer select the “PivotChart” first, note that the PivotChart must be selected in order to do this.

Then go to the top menu bar and find the PivotChart Analyze options and Select the option “Insert Slicer”. This will add a slicer on the screen.

You need to select what all options or fields you want the slicer on. Let us select the date first.

Select “Order Date” from the options.

After checking the option “Order Date” and clicking on OK. You will see all the dates in the slicer one by one as shown below.

This will show all the individual dates on the slicer, to add years filter we need to add “Timeline” from the same menu where we added the “Insert Slicer”, select the option “Insert Timeline”.

Now select the Order Date option and click Ok.

You will see the timeline in Months and Year. Just Select the option Year from the drop down on the top right.

Now when you Selec the particular year you will see only the data relevant to that year in the PivotChart itself. This tool gives interactivity to the PivotChart and helps to slice down data accordingly.

Right click on the TimeLine to Select the Size and Properties option where you can make the TimeLine look better.

Next when you select the particular years or drag the Timeline you will see the data according to the year you have selected only.

For example the graph looks for the years 2013-14.

You can rename the PivotChart “Orders” from “Total” by just doubleclicking on the Title.

Next you can also select the zoom in and out functionality on the PivotChart for more control on the variables you want to show on the PivotChart.

For example if you hit “-” to the max you will see only the data of US.

Now you can add granularity by hitting the “+” to get more insights into the Dimensions of Country, Segment and Ship Mode.

In this tutorial upto now we saw how to connect data, add PivotChart, Slicers, Timeline.

These were the basic steps for building a dash, you can now mix and match as per your analysis and create more charts automatically as per the analysis you require.

To find lets make the format better by removing gridlines and arranging the dash in a better format using click and drag options. And also changing the colours using the Paint Brush Icon on the top right of the Pivot Chart.

Remove the axis on the left and also the grid lines on the chart by selecting them and make the chart look better by increasing font size of the Labels.

The final piece looks like the finished piece below.

The above is some of the most basic dashboards in Excel based off the sample data.

To learn more such stuff visit my website DataGridInsights.com for more such valuable content, do comment and share this to your friends.

All the best !