October 14, 2020
If you are a financial analyst, then you know how important it is to sharpen your dashboard creation skills. Moreover, you may have read our previous articles covering Microsoft Power BI as a great tool for the creation of dashboards or seen our COVID19 Power BI Dashboard, so why dashboard creation in Excel?
Microsoft Excel is one of the most widely known and used apps globally. Creating a user interface on Excel that summarizes particular objectives or business processes makes work so much easier. It is easy to gain insight from data when an overview is given.
It is important to note that it is not only financial analysts that can enjoy the power of Microsoft Excel but also marketers, sales persons, project managers, operations managers and human resources officers among others. In addition, Microsoft Excel provides a good number of options for creating dashboards.
There are a number of questions you should ask yourself before creating a dashboard on Excel. They include:
1. Why you are creating the dashboard
2. What do you need to track? -Define the main Key Performance Indicators (KPIs)
3. Who’s your target audience and who in your team needs access to the dashboard
4. Where you will generate the data from
5. How often you will update the data
Compile all the data that you want to feed in your dashboard. Well-organized data will make your work simpler moreover, ensuring you adhere to data modelling best practices, which helps to “integrate data from multiple tables, effectively building a relational data source inside an Excel workbook through Pivot Tables and Pivot Charts”.
Arranging the data you intend to graph or tabulate makes data modelling and dashboard designing much easier.
After compiling the data, create a page. You can create a one-page or a multiple page dashboard on Excel. Other considerations you need to make is if the dashboard will be majorly for printing or emailing, if you will use visuals more than numbers or words, and who in the organization or outside the organization you expect to share it with.
To come up with a great dashboard design, you need to brainstorm and experiment with different ideas before narrowing down on a design. Your draft, which you can put down on paper, should have ideas on the structure of the dashboard and the particular data you want included in each part of the dashboard.
In this stage, it is important to look for examples of other dashboard structures that help you gain more ideas or use the available templates on Microsoft Excel to help you create what you need. You can improve existing templates to help you save on time and money.
Excel has a good number of charts or visuals that can help you represent your data clearly and in an aesthetically pleasing way. You can choose from column charts, line charts, pie charts, doughnut charts, scatter charts, bar charts and area charts among others.
Once you have chosen a chart, add attributes to it that will make it pop. From comments to lines and arrows use attributes to add more information to the graphs about your data.
Since elements occupy space on a dashboard and contribute to readability, it is important to filter elements to ensure only what is necessary remains on the dashboard. In addition, ensure you choose a background color that enhances readability on the dashboard. A great tip on color is when choosing color codes, similar data sets can be given one color to create contrast. You can also freeze certain task planes to limit scrolling.
Finally, the data story that is on your dashboard should align with your goals. If the story that is on your dashboard does not align with the goals you intended, consider changing the data or coming up with a different design.
To help you create a compelling data story, view our article on “Data Visualization: How to Get the Right Narrative from Your Data”
Following the above guidelines will help you create a user-friendly dashboard on Microsoft Excel. To effectively create a dashboard, you need extra Microsoft Excel skills. We are leading trainers in Microsoft Excel in Kenya and we have trained hundreds of people both at their workplaces and in our offices.
We have some exciting news!!
You will now be able to study Microsoft Excel Online through our Data Analysis self-paced course. Get to enjoy the convenience of studying from anywhere with and earn a free certificate upon completion. We wish you all the best!