Effective Data visualization using Dashboards
In this article I’ll show you how to use the core Excel components to build an Excel Dashboard from the ground up.
- Create a dashboard wireframe
Before create a dashboard it’s important to keep in mind the final structure. You can use built-in Excel tools, like shapes. Create three Worksheets: Data, Calculation, and Dashboard.
2. Import and clean your Data
Okay, let’s say your data is in Excel. The problem is the raw data! If you want to use effective data cleansing methods to the following:
- remove unnecessary characters, extra spaces
- remove duplicates
3. Build a drop-down list
The drop-down list is the most effective way to build an interactive menu for your dashboard. If you are working with large periods, for example you have results for 12 months, use the drop-down list.
4. Build Excel dashboard Widgets
When your data is clean, create a new sheet, Dashboard. Split the dashboard area into 5–10 equal part. Merge the cells if it necessary! Link your data from the calculation Sheet. Create simple line chart the show your data on a simple line chart. Use Conditional formatting to highlight the differences between the selected and the previous month’s data. That’s all!
5. Create a progress chart
Progress chart is so popular because this is the best infographic style chart type in Excel. You need to add only two values to build the chart. The first is the ‘actual value’, the second is the ‘reminder value’. Insert a simple doughnut chart! We’ll use a small trick: select the chart and press ‘Ctrl +C’ then ‘Ctrl + V’. Format the chart!
6. Improve your dashboard
If you to improve the Dashboard UX and Dashboard UI, we strongly recommend you using a ribbon control and contextual help. Both solutions take the dashboard on a higher level.
Visit website and download free templates!
PS: a new Google Sheets Hub: Read more…
