Selecting the Data to Import Into Excel

(1)  You'll want to start out by gathering the data required to build this KPI dashboard. Below is a list of fields that I would try to include from your order table.  If you have issues getting the month name, this can be created in Excel by including the ship date (that also goes for the year).  If you have any questions about please feel free to post in the comments below.

(2) Once the data is loaded into excel for the fields mentioned above, you can rename the tab to "ORDER TABLE".  At this point you should also have all of your data setup and organized to now use in a pivot table.

Building the Excel KPI Dashboard

(1) The first think you'll want to do is make sure the range selected is a table.   You can do this by selecting the entire data range using Ctrl+ACtrl + A allows the user to select the entire range around the current selected cell and then typing Ctrl+TCtrl + T allows the user to create a table based on the select cell range.  This will ensure that if additional data is added to the data table, it will automatically be selected in the range for the pivot table.  You can rename the table from the table design section of the ribbon as shown below.

Now that we've got all of the data we need to build the dashboard, we'll select all of the data by selecting the first cell of our table (in my case cell A1) then by typing Ctrl+ACtrl + A allows the user to select the entire range around the current selected cell.  This will highlight your entire table.Now from the header ribbon select the insert tab and create a pivot table.

(2) We are going to create the pivot table on a separate worksheet.  To do this simply leave the option to create the pivot table set to the current default.

(3) After clicking OK, you'll be brought to a new worksheet.  Here you can now rename your pivot table in the ribbon from the pivot table options section (you need to make sure you've selected a cell within the pivot table section of the worksheet).

(4) Now let's rename the worksheet tab at the bottom of the worksheet.

(5) Add the fields that are required to build the pivot table.  There are four different sections on a standard pivot table.  The report filter, column labels, row labels and then values.  The report filter allows you to add a field which you would like to filter by (for example if you wanted to filter by region you would add this field to this section).  The second section is the column label.  Here we are going to add the ship year as your dimension.  This is the data that will be displayed across the top of the pivot table.  The third section is the row label and is usually the field you are trying to measure.  In our case we are trying to measure our salesmen performance.  Finally, the values section is the metric/value you are using to try and measure the row label.  In our case a good indicator of salesmen performance is the sum of the total price of all order lines for each salesman.  To summarize, this pivot table is going to show us the total sales (value) for each salesman (row label) by year (column label).

(6) Now let's format the pivot table to make it a little easier on the eyes.  Let's change the total price field under the values section of the table to currency data type.  To do this from the PivotTable field list right click on the total price field under values.  This will bring up field setting options.  Select the value field settings, number format and finally currency category number format.

(7) I like working with the classic pivot table settings because it shows us the name of the row labels clearly and separates them by column.

(8) Also set the value of blank cells to zero for each record.  This again is done through the pivot table options:

(9) In order for a report/pivot table to be a dashboard, we need it needs to provide us with a result/answer.  In our case, we want to be able to show the top salesmen by the most recent year.  In order to do that we need to sort on the total price (amount sold) from highest to lowest for the most recent year.

(10) Now we need to setup the conditional formatting  where we want to measure how well each salesman performed against the company's quotas (the key performance indicator).  Here is the quick table/legend that we built within the dashboard that shows what each stop light indicates.

Below are some screen shots showing how to set the conditional formatting to identify where each salesman ranks by year.

Once you hit OK, on the edit rules form select apply.  Once you've applied it, the dashboard will now show each salesman performance from best to worst for the year 2013.

(11) Because we are measuring the performance of each salesman based on the year, we need to ensure that the grand total is not being included on their performance as this is the sum of their sales for all years.  To remove this go back to conditional formatting, manage rules and select all cells showing "SUM of TOTAL_PRICE" values for "SALESMAN" and "SHIP YEAR".

(12) Now that the conditional formatting and the sorting of the top salesmen is functional, we can add a slicer (filter) on the sales region field  to compare salesmen and their success within each region.   In order to do that, select a cell within the pivot table and under pivot table options, select insert slicer.

Now that you've added the slicer, you have the ability to show top salesman for 2013, include their KPIs as well as compare their performance to create measures to improve the business.  There are so many more things that you could do with this data, what would you do?

Get This KPI Sales Dashboard Template