Like many other visualization tools in the market, MS Excel provides many options to create nice and easy charts and Dashboards. However, one needs to be familiar in using the tool. The following components in MS Excel are used in order to create Dashboards :
– tables
– charts
– pivot tables and pivot charts
What is a dashboard – A dashboard is a view or a report that displays the information /data from multiple sources/tables in a single screen through charts and graphs and provides an overview. It helps in understanding the status of business activity or function, monitoring and analyzing performance and taking quick decisions.
A dashboard can be created in all versions of Excel 2007 onwards.
What are the steps for preparing a dashboard?
1. Preparing the data from various data sources – the raw data can reside in the same workbook in Excel or in different workbooks or tables or databases. However, all the relevant raw data needs to be copied and pasted and made available in worksheets in Excel files.
2. Selecting the visualization tool – the right and relevant chart needs to be selected for the right representation of the data. For example, a line chart if the data is in time series, a pie chart if the share or percentage contribution of sales of a product from different regions needs to be shown, a histogram for exhibiting the distribution of a continuous variable like time to commute for office employees, etc. Excel also has the option of suggesting a chart for the selected data. In the toolbar, select the ‘insert’ option and select ‘recommended charts’ and select ‘data’.
3. Create the dashboard – there are multiple charts in a dashboard hence the data needs to be selected accordingly. A pivot table is very useful in selecting the fields for the chart from raw data. A ‘Macro’ can also be used to automate the task of selecting and updating the data for the chart.
4. Label the chart – once created, the chart needs to be formatted by applying the color, font, etc. for visual appeal. It is important to also add the labels and axes information so that the scale is properly understood and visible. This is more important in a dashboard as it has multiple charts and graphs – one may be a bar chart of sales of a product (in million rupees), the other could be a pie chart of sale of the product from different regions (in percentage), another could be a line chart showing the Net Promoter Score of the product monthly (score on a 1 to 10 scale) and the other could be a scatter plot of sales of products and advertising spends on those (in million rupees). The units and scale of the chart are different in this example of a dashboard.
A compact visual report is a powerful tool for any business, be it large or small. And, the person who knows how to wield her data analysis skill and create meaningful visual reports is highly valued in any organization.
Enroll in a comprehensive Business Analytics course or Data Science course or a Machine Learning course with Python Certification or even choose from one of the analytics courses that fits you the most.