Dashboards using MS Excel

There are numerous ways in which you can create data visualizations on MS Excel but before that let us first understand what is the purpose of building dashboards. A dashboard is a visual display of the most important information needed to achieve one or more objectives. The objective can be as simple as achieving a certain goal, such as making sales targets or reducing costs, or it could be something more complex, like improving operational efficiency.

The various components of data visualization include charts and graphs and sometimes interactive graphics that provide access to numeric data. Data visualization tools make data easily visible so that viewers can understand what the data means without having to calculate it themselves. These tools help users make better decisions. Data Visualization is not limited to the numbers and formulas present on the spreadsheet; you can also use visual and interactive objects to visually represent information.

There are a number of data visualization practices that help in simplifying complex data or information so that a large amount of text and numbers can be understood easily by common people. Dashboards provide a picture of how an organization is performing in certain aspects with respect to its targets (or goals).

5 tips for creating dashboards

1. Use Real-Time Data:

The main purpose of creating a dashboard is to have information available in front of you, where you can analyze it and take action. Ensure that the dashboard shows real time data. For example, if the purpose of your dashboard is to help your company meet its revenue goals, make sure you use live data that shows how much revenue the company has earned since the beginning of the year.

2. Make a list of key performance indicators:

A Key Performance Indicator (KPI) is a unique representation of the performance of an organization. For example, the revenue goal for a company is represented by the KPI called quarterly revenue. Other KPIs like monthly sales or total annual profits can also be part of your key performance indicators (KPIs) dashboard. List all these relevant KPIs and use them as parameters in your dashboard so that you keep track of how well your company is doing in achieving its goals.

3. Know your audience:

Targeted audiences can be internal (employees) or external (clients, investors, consumers). Design your dashboard based on the target audience. For example, if your dashboard is about revenue for a consumer product manufactured by a company, make sure you use graphs and charts that clearly display revenue in relation to time. Additionally, the layout of the dashboard should be easy to read for the target audience.

4. Use the right chart:

The chart you choose to represent your data on the dashboard must be compatible with the data and what you are trying to display. For instance, if your dashboard is designed around total revenue and you decide to use a bar graph to represent this data, it will not make sense because a bar graph represents a range of numbers. It would be better to use an area or line chart where each point will show total revenue for a particular time period.

5. Resist the temptation to include everything:

While creating a dashboard, it is very tempting to include everything that a user can possibly need. However, resist this temptation as it will create clutter on the dashboard and may not help users in analyzing data quickly and efficiently.

With these steps in mind, you are ready to start building your visual dashboard. Let’s look at how you can create a simple but useful dashboard with MS Excel as its foundation.

Building dashboards on MS Excel

As you already know, Excel is a spreadsheet application that allows you to use tables, bar charts, line graphs, and other visual tools to represent data. By using these components together in an interesting way, you can create a dashboard that will help your company achieve its goals. For example, if you have revenue targets for the company, once the monthly targets are achieved or missed, dashboards can be used to quickly show whether the quarter targets are also being met and whether more action is required.

Follow these simple steps to create your first dashboard on MS Excel:

• Open MS Excel and create a new workbook. On the first sheet, create the following components in this order—a Pivot Table, a chart, and a table. This will become the basis of your dashboard.

• Use the Pivot Table to summarize your spreadsheet data in a way that will be most helpful for users in analyzing data from various perspectives. Note that you can also use tables as part of your dashboards if appropriate.
• Use a chart to represent your data, whether it is a bar or line graph. Choose the type of chart based on how you want to present your data, such as time-based (revenue over time for example) or data-based (a bar graph showing revenue by-product). Using the Pivot Table, you can change the periods shown in the chart easily and also change its layout if necessary.

• Add a table as the next component of your dashboard. The table should contain data related to your chart data. For example, if you have a bar graph showing revenue by product, you can use the corresponding table to display the actual figures or percentages for each product. This will give users a complete view of how much each product contributes to the total revenue of your company.

Additionally, use formulas and conditional formatting on the Excel dashboard tool to make it more visually appealing. For example, if you want the average amount to be displayed in green, you can use the formula below:

=color(“green”,average(A2:A20))

This formula will display the word “Green” on cell A2 assuming that cell A2 contains an average of values in cells A2 to A20. The average function is =average(A2:A10). You can change this formula and apply it to other cells as necessary.

• Save your dashboard as a PDF file. Use the regular file format to display your Excel dashboard as it is. You can also save the dashboard and use it as a PPT or PowerPoint template in MS PowerPoint to edit, format and present your dashboard in the future.

These steps will help you create a simple but useful Excel dashboard for your company. Remember to use charts, tables, formulas, and conditional formatting to make the most of any tool that you choose to use on its own or alongside other components.

Quicker ways to create dashboards

Building excel dashboards is a multi-step process and it does require having a working knowledge of Microsoft Excel. However, if you would like to eliminate the delay caused by building formula cells, adjusting formats, building charts, and want to gather data-driven insights within seconds- you can use Conbi.

Conbi is short for Conversational Business Intelligence- It lets you simply ask questions to your MS Excel database using Natural Language.

Let’s say you wanted to know the Sales numbers for current quarter. All you need to do is ask- Conbi will automatically extract data from the excel sheet and give you a report. You can do much more like this- more about it here.

3 easy steps to create a dashboard with Conbi

You may ask questions to your database and Conbi will create visualizations for all the questions you asked for. And the best part of it is that you do not need to write any code!

Conbi works with any MS Excel database and even with Google Spreadsheets as well.
Conbi can transform a simple spreadsheet into an interactive dashboard in three easy steps:

• Connect your database or upload your spreadsheet
• Use the search bar to ask questions about your data.
  For example- ” Show me the monthly sales in the year 2021″
• Once you have asked all your questions, pin the important visualizations to your dashboard
• Convert all your visualizations into a PowerPoint with a single click

It is easy to learn, simple to use, fun to play with, quick to deploy, and will definitely be a great addition to your business productivity toolkit. Signup for early access here.