How to Build Dashboard in Excel
In Excel dashboard is a tool you can use to visualize vast amounts of spreadsheet data. When you create a dashboard you are going to convert your spreadsheet data into charts and diagrams. This will allow you to visualize and better make sense of the data.
As a result of that you can analyze the data to make important business decisions or showcase it to your team. In an organization you can use the dashboard to help you with visualizing sales information, analyzing key performance indicators, visualizing your company’s financials etc. If you are new to Excel you have very long way to go. If you want to reach a certain level of understanding it, so you can create awesome applications.
Don’t get discouraged though because that’s just the process. Once you get over the bumps you are going to see how extraordinary Excel is. Otherwise from that, creating a dashboard can get from easy to complex. Below you can see a tutorial on how to create a simple interactive dashboard. Even if this is going to be your first application in Excel, it can give you a feel of what excel is like and you are going to learn a lot from it.
Tutorial on How to Build Dashboard in Excel plus Excel Dashboard Templates:
Import data into Excel or insert information into spreadsheet.
You have to now create a pivot table. To do this you first got to highlight the data, go to insert PivotTable and place in a new worksheet.
Next you are going to go on pivot table. Click on insert Slicer and then select the values that you want to filter. It depends on your project but examples of these can be expenses, sales figure etc.
To make it standout you can format the size and color of the slicers under the slicer tools tab.
You can easily change the slicer header if you want to along with sorting the slicer categories and setting data display options in slicer settings. Click on the sign visually indicate with no data and then leave show items with no data last. Lastly click leave show items deleted from data source unchecked.
Next you are going start a new worksheet which you are going to use as a dashboard canvas. You are going to design it to your preferences by adding desired text, pictures and paste slicers.
Return back to pivot table and put the continuous variables that you are going to display on your dashboard into the different value areas on the pivot table.
Next create references from the different cells on the dashboard to the continuous variables in the pivot table and format as desired.
You are going to create a separate pivot table for each categorical variable or value. To do this click on each pivot table’s field and display items with no data. This will prevent cell references not pointing to incorrect locations when selections are going to be made.
Next go to dashboard and link the slicers to the newly created pivot tables by clicking each slicer, then go on slicer tools. Check the desired pivot tables from the list under pivot table connections.
To now create charts, you are going to do it by indirectly referencing the data in the pivot tables and pasting it into the dashboard. Don’t reference pivot charts directly because this can create pivot charts. That will have features and appearance characteristics that will be totally different than what you were expecting. Create cell references to pivot tables instead and build charts off of the referenced data.
You can create frequency distributions of categorical variables by making cell references from dashboard to values in the pivot tables. Add data bars or other icons and calculating percentages right from the conditional formatting menu on the home tab.
The method above is a very simple method you can use when creating a dashboard in Excel. If you follow this correctly you will see how easy it is to create a dashboard in Excel. If you are serious about learning Excel in general, you can find a lot of tutorials online. Overall this tutorial is very simple. You can even use this as base to build upon for further progress in learning Excel.
More on Analytics