Objectives of Dashboards
The Purpose of the Management Reports is to provide analysis, insights into business operation in a short time. Majority excel dashboards are one pager, means all the key indicators are stored in one place which makes the management job much easier for key decision based on the available reports. Since dashboards include charts for data analysis, the non-finance management can easily understand it. Sales transactions are a major part of any organization as it brings the revenue which finances projects related and other expenses.
Link between Excel and Management reports
Excel is a great analytical tool used for reports and dashboards. We will be a preparing dashboard for sales department which will provide us analysis from products, customers, sales person, year wise trend. For this particular report, the dashboard will be prepared with the help of Pivot Table, Slicers, Pivot charts.
We have daily sales report for the period of six years (2010-2015).
Select the table and insert a series of pivot tables. In Excel 2016, the keyboard short cut of inserting the Pivot table is ALT+N+V.
The “Create Pivot table screen” provided you with an option where to place your pivot table. You can place it in any of the existing sheets or in a new worksheet. We will be saving it in a new worksheet. Named the sheet as Year wise sales.
Prepare Pivot Tables for each Field on Separate Sheets
To the right of the screen, you will notice Pivot chart fields and Pivot chart areas. Drag Date field to the Row Area and Sales field to values area. Rows and values are highlighted for easy guidance. A data table will be created to the left of the sheet instantly when you perform the above step. Select the data and right click on it. Choose format cells. Choose currency, remove the decimals and click ok.
Repeat the step 1 to create four more pivot tables for others data fields. The minor change is that date field will be replaced by product/Sales Person/Sales Region/Customers field in the row area respectively. Remember that Sales figures field will always be in values row.
Create Bar Charts for each Pivot Table
Go to the Year wise Sales sheet and click inside the pivot table. Hit the keyboard shortcut (ALT+N+C) and hit Enter. Change the Title of the chart by clicking on chart area and add the chart title from the add chart element from the design tab. Alternatively, when you click on the chart area, a Plus (+) will appear in the top right corner of the chart. Click on the plus sign will open up chart elements. For more details on charts, we would recommend reading the article on Basic of Charts.
Repeat the same steps for other pivot tables to create charts for them. At this stage, charts and pivot tables are on the different sheets.
Insert a new sheet, rename it as Sales Dashboard. Now select the chart on each different and copy it. Go to the Dashboard sheet and paste it. Your Dashboard sheet will look like the one below. If not, recheck if you have missed any of the above steps.
Format of Chart Area
Select all the charts with the help CTRL+Mouse Click. It will activate Format tab. You can resize chart area (height and width). Click OK once you select the size of the chart area.
The distinctive part of the excel later versions is the availability of slicers. with the help of it, our charts will be interconnected and dynamic. Select one of the chart areas, go to the Analyze tab and choose Slicers. From option menu, as highlighted below, choose the ones that are essential.
Select the slicer and select show settings from the options tab under slicers tool. 2nd way to get the same objective is to right click on the slicers and then open setting. Uncheck the Display header, check the hide items with no data from the slicers setting screen below. use the Buttons tab to increase/decrease the Column, height/width of the buttons. From the size tab, you can increase/decrease the height and width of the Slicer. Repeat the same formatting procedure for all the slicer.
The last step is to connect all the slicers to the pivot reports and charts. Select the slicer and go to Report connection option from options tab under Slicer tools. The second way of doing this is to right click on the slicer and then choose report connections from the list. It opens up the below screen. Check all the pivot table reports. Do this step for all the slicers.
Another valuable trick is to fix the charts Area, so moving of column or rows do not affect it. For that to happen, simply select the chart, hit CTRL+1 and check the option “Do not move or size with cells”. Do the same for the slicers as well.
It’s time to fix the dashboard area. Go to the last row of the dashboard, select it and then choose the freeze panes from view tab. Pick the year 2010 from the slicer and your dashboard will reflect the changes. your Sales report/dashboard is ready. CTRL+mouse click will on buttons to select more than one year, customer, products etc.
Download the Sales Dashboard from here.