Making Sales Dashboard using Excel Slicers – How To

 

So many reasons why not to hate Excel dashboards. They are beautiful, they are dynamic, they make everything super easy to understand. And not to mention it one cool way to grab attention at workplace (if that is the only thing you can do 😉 )

So here is what we are about to learn today.

It is a simple sales dashboard reporting on sales/cost for profitability analysis, sales slabs and the number of sales in each slab and top 10 sales personnel. The idea to do the tutorial was sparked looking at the promotion of taleneted John Michaloudis‘s website myexcelonline.com. When he started the blog he reached me but I was too busy in my teaching schedule so wasn’t able to catch up with him. So I hope this shout out, thumbs up will help me cover up for it.

Making the dashboard: Step by step

The actual effort to pull out this task is to have an appropriate chunk of data structured the right way and then pivoted the way that can get us the right charts. Having slicers is just an icing sugar. So by the looks what is actually making you so excited is just a handler and not the actual reason itself. And we are going to learn all of it!!!

Excel Tutorial File

Click here to download the excel file needed to practice this tutorial by following the steps mentioned below

Step 1: If you have already downloaded the excel dashboard tutorial file then open it and you will see there is a sheet named data. The data is already formatted as table. Learn why tables are good and how they can help make data dynamic

Top 10 Sales personnel chart

Step 2: Select the data by having an active cell within data and hitting Ctrl+A. Go to Insert tab > Tables group > Pivot tables, create pivot table dialogue box will pop up. Give any name or leave it as is and click OK. A new worksheet will be inserted with pivot table options interface enabled.

Step 3: Click and drag Sales value from the list to values quadrant, click and drag sales person to rows quadrant. This will give you a pivot report on the left.

Step 4: Click on the drop-down arrow button with the Row labels and select value filters > Top 10. Click OK. This will get you top 10 sales personnel by total sales generated.

Step 5: Click on the arrow again and this time click more sort options. Choose descending and from the drop down menu select sum of sales. This will sort the data in descending order.

Bonus: Top 10 sales person by sales count or by sales sum

Once you have the report, it is super easy to know who made the highest number of conversions and who generated highest revenue. To do it simply go to values quadrant and left click on downward arrow to invoke a menu. Click value field settings and from the list you can pick if you want the report on the basis of sum of the value or count of the value.

Step 6: Select the data in pivot table. Just the data. Click Insert tab > charts group > column charts > clustered column charts and click OK. This will insert the chart showing top 10 sales personnel. And this is not just an ordinary chart, it is actually a pivot chart.

Step 7: Click on chart to select it. Go to Analyze sub-tab > show/hide group > click field buttons to turn of pivot options inside chart to make it look clean. Rest are cosmetic changes which you can do as you wish easily.

Number of sales by slab

Step 1: Go back to data worksheet and select the data having an active cell within data and hitting Ctrl+A

Step 2: Click insert tab > table group > click pivot table button > OK. This will insert another pivot table on a new worksheet.

Step 3: Click and drag sales to value quadrant. Click and drag sales again but in Row quadrant. Yes same element in two different quadrants.

Step 4: Right click on pivot table and from the menu select group. A dialogue box will appear. Uncheck both options and in start at field type in 500 and in ending at field type in 50000. By field should have 10000. Click OK. This will give you slabs of 500-10499, 10,500-20499 and so on with the count of sales in each slab.

Step 5: Select the data within pivot table go to Insert tab > charts group > column charts > clustered column > OK. Now you have the pivot chart of sales count for each slab. You can turn of the buttons and make the cosmetic changes as you desire

Monthly Sales-Cost chart

Step 1: Go to data worksheet again and select the data then go to Insert tab > tables group > click pivot table button and then click OK on the dialogue box that appear. A new worksheet will be inserted with pivot table interface enabled

Step 2: Click and drag Sales and Cost items to values quadrant one by one. Then drag month item in rows quadrant to generate a report with monthly cost-sales information.

Step 3: Select the data within pivot table, all three columns, go to insert tab > chart group > column chart > cluster column

Step 4: Having chart selected go to analyze tab and click once on field button to hide filter controls and other buttons on chart.

Step 5: Go to design tab > type group > click change chart type button. A dialogue box will open. From the left in the list click combo. For cost select stacked line with markers and for sales select area from chart type drop down. You can choose to plot either of the aspects on secondary axis but its not needed in our case. But you can if you want easily by checking the box of secondary axis.

Step 6: Make cosmetic changes as you desire using chart layout options. I just moved the legends to the bottom using quick layout preset.

So now we have all the three elements we needed:

1. Top 10 sales personnel
2. Count of sales for each sales slab
3. Monthly Sales-cost chart

Now we need to have all of them at one place (as currently these charts are in three worksheets) and insert slicers that will be linked to all three charts. Once done, all three of them will dance on our finger tips 😉

Assembling the Dashboard

Step 1: Insert a new blank worksheet and name it Dashboard. Select the whole worksheet and change the fill color to any color you like other than white. I chose gray. Go to page layout tab > sheet options group > gridlines > uncheck view option. Grid lines will vanish.

Step 2: Cut each chart and paste it in the dashboard worksheet. Its simple just cut paste. The charts will keep their integrity with underlying data even if they are moved to another worksheet so don’t worry.

Step 3: Once all the charts in the worksheet and arranged. Click on any chart go to analyze tab > filter group > click insert slicers option. A new dialogue box will open. Check year, month and sales and click OK to close it. It will insert three sets of slicers.

Step 4: Select the year slicers box and go to slicer tools options tab and in buttons group change the number of columns to 3. Change width and height of the box as you wish.

Step 5: Having it still selected go to slicer group under slicer tools options tab and click report connection button. From the dialogue box check the pivot tables appearing. We have three pivot tables in place and we want all three of them to filter if slicer buttons are clicked. Check them and click OK. Now if you click the buttons chart will update automatically.

Step 6: Repeat the same step as told in Step 5 with other slicers to arrange and connect them.

And done! Now you can watch the charts updating as you click and select slicer buttons! Your dashboard is ready. Here is what it looks like once finished with other two slicers connected and arranged:

So you have it! This is definitely not the best of dashboards possible but surely to help you understand how this is done. Creating dashboard require deep understanding of presenting information. Brad Edgar has a good article in this regard. Do check it out.