Functional dashboards are a critical component of any business analytics strategy. They provide a centralised location for monitoring key performance indicators (KPIs) and other essential data visualisations that analysts, executives, and other users can access on demand. A well-designed dashboard can quickly and clearly communicate performance at a glance, which makes it easier for users to understand the current state of their KPIs. However, designing functional dashboards can be complicated. That’s where we come in! In this article, we offer the top tips for creating functional dashboards, which will help you get off to a good start.

Be intentional about who you include and what you display.

The first step in creating functional dashboards is to decide who you want to include and what metrics you want to display. This will help you determine an appropriate layout, number of visualisations, and types of data to include. For example, you may want to include key stakeholders, such as executive leadership, marketing managers, and sales directors. Or you could target a specific department, such as finance or customer service. In most cases, functional dashboards should include data important to managers, executives, and other stakeholders responsible for making strategic decisions. To create operational dashboards accessible to a broader audience, you should consider displaying data related to KPIs that affect their day-to-day work. In either case, be sure to keep in mind accessibility requirements when deciding who will see your dashboard.

Ensure your visuals are easy to read and understand.

Regardless of the metrics you display, ensuring that your visuals are easy to read and understand is important. To start, use graphs and charts that are familiar to your industry. Additionally, make sure to avoid visual clutter. Remember – your dashboard will likely contain multiple visualisations and be viewed on various devices, so avoid anything challenging to read or interpret. One way to prevent visual clutter is using the “less is more” strategy. For example, display only the most essential information, removing unnecessary visuals and details. Finally, avoid using too many contrasting colours on a single visualisation, as this can be challenging to interpret and look at.

Show context by including key metrics for reference.

In addition to visualising your most important KPIs, you should display critical metrics for reference. For instance, if you’re visualising sales data, include a relation to the target sales and the number of sales leads you’ve received. Doing so will help your users contextualise the current state of their KPIs by providing related data that they can use to draw conclusions and make informed decisions.

One way to show context is by using a reference line. This is a thin line connecting two visualisations to demonstrate their relationship. For example, if you want to visualise sales data alongside the number of sales leads you’ve received, you can use a reference line to connect the two visualisations. Doing so will allow your users to quickly see how the two metrics relate, providing context for the sales data. This will contextualise the data, and users will better understand what they are looking at.

Show only the most critical information at any given moment.

Some KPIs will be more important than others. This is why it’s crucial to determine which visualisations are most relevant and display them above others when necessary. You can use filters or conditional formatting to highlight specific visualisations. You can also use other data visualisations to highlight important data. For example, you may have a sales dashboard that visualises the number of sales leads versus the number of sales closed. Suppose the number of closed sales appears low. In that case, you may want to overlay a red visualisation on the sales leads visualisation to indicate that this metric is falling behind expectations. Doing so will allow your users to quickly identify which metrics are falling behind, giving them an at-a-glance view of critical data.

Make your KPIs stand out with the right colour palette.

Colour is an essential part of any functional dashboard design. It can draw attention to KPIs behind schedule or those performing well. Certain colours may strongly associate with specific industries, making them less appropriate for a dashboard designed for general audiences. In most cases, you’ll want to remember the cultural associations of the colours you select for your dashboard, especially if you’re creating a dashboard that will be used globally. To choose the right colours for your dashboard, start by selecting colours that are distinct from one another. Typically, red indicates the KPIs performing below the expected standard, and green is used for those performing well. Choose colours with positive associations, such as trust and reliability, for your critical KPIs and don’t forget to keep your target demographic/audience in mind. For example, formal industries are not interested in bright, vibrant colours!

Don’t forget to include an explanation of KPIs.

Since most people are visual learners, visualisations will help them understand the current state of your KPIs. However, others prefer to read data presented in a textual format. For this reason, you should explain the KPIs you’re visualising. Doing so will allow your users to quickly understand how the data is being presented, allowing them to make more informed decisions based on the data presented in your dashboard. In most cases, you’ll want to include an explanation of all metrics, especially unfamiliar KPIs. Still, incorporate a description of the graphs and charts you’re using. For instance, if you’re using a visualisation that your users may not be familiar with, such as a Sankey diagram, you should include an explanation of the visualisation and the data it represents. If you use both visualisations and explanations, you are enabling all types of learners to understand the data.

Refrain from overloading your dashboard with too much data.

In theory, the more data you visualise, the better. However, this isn’t always the case. Analysts have found that visualising too much data can cause analysts to lose focus and make less-informed decisions. In most cases, you’ll want to keep the number of data visualisations you include on your dashboard to a minimum unless your dashboard is being used to monitor a large number of KPIs. You’ll most likely want to incorporate no more than five visualisations on a single dashboard. If you need to visualise more data, there is no problem with creating more than one dashboard.

Overall, creating functional dashboards can be complicated. However, if you keep these tips in mind, you can create a dashboard to inform and engage your users. Dashboards can be created using various tools on several visualisation platforms. For example, Excel or Google Sheets dashboards are often one-pagers that assist businesses in tracking key KPIs or metrics and deciding based on them. Here is additional information on creating a dashboard for these two popular platforms.

Collect and organise data

Before you begin your dashboard creation in Excel or google sheets, collecting and organising data well is crucial. Google Forms are one of the quick and simple ways to collect data. The most important part is to have all the data in one area. It can be challenging to link the graphs if data is spread out throughout the spreadsheet.

Outline key elements

Identify all the key elements that you need to visualise on the dashboard so that you can classify related components in the same category within the dashboard. Discussing an outline with your stakeholders to eliminate clusters (e.g., having too much information in one place) is often helpful, as this allows data to be read and interpreted easier. Too much information on one dashboard can make it harder for your readers to find the desired information, so separate different metrics in different sections.

Use interactive tools

In google sheets, use data validation to add interactivity to your dashboards. You can generate a neat drop-down menu from which the user can select a parameter so that any charts will update automatically. It’s an easy process but surprisingly influential. Checkboxes and scroll bars are also used on dashboards to specify the results on the page.

Analyse the data

When you select from a drop-down list or use a scroll bar or select a checkbox, formulas update based on the results and give you the revised data in the dashboard.

VLOOKUP is a vertical lookup formula that searches the columns of a range, and when it finds the result, if there is one, it returns the value in that row from the column of the range that you prescribe with the index value.

How to create VLOOKUP in Excel

In the Formula Bar, type =VLOOKUP().
In the (), enter your lookup value, followed by a comma.
Enter your table array or lookup table, the range of data you want to search, and a comma: (A2, A120:G24, G40)
Enter column index number.
Enter the range lookup value, either YES or NO.

How to create VLOOKUP in google sheets

In Google Sheets,

Click Add-ons > Multiple VLOOKUP Matches > Start

And,

Select the range with your data (A1:C5).
Specify how many matches to return.
Choose which columns to interpret the data from.
Set one or more conditions.

HLOOKUP is a horizontal lookup execution of the VLOOKUP formula. It searches the range horizontally. HLOOKUP is rarely used but assists in finding a particular person’s progress. INDEX & MATCH are two formulas that combine to create flexible lookup solutions. They are more flexible and avoid some of the pitfalls than VLOOKUP. However, they are a little more complex to execute as they encompass two formulas.

Create visualisations

Charts used on dashboards are visually pleasing and make the dashboard easy to interpret and understand.

Choose a chart: Excel and google sheets furnish many chart options, and you need to use the correct chart. For instance, if you have to show a trend, you need to use a line chart, but if you want to highlight the actual values, a bar chart could be the perfect option.

Combination charts allow users to compare values and draw meaningful insights, such as showing the marketing figure as a column chart and growth as a line chart.

Dynamic charts allow the user to make selections and update the chart. A dynamic chart is a regular one whose data updates in the back end when you make selections.

Show trends with sparklines

Sparklines are small, simple charts without axes which prevail inside a single cell. They are a quick way to visually show a result without needing the complexity of a chart. They suit well for datasets based on a timescale. For example, you can create a sparkline that shows a change in values over time or a sparkline that offers a percentage change in values over time. You can also select a theme for your sparkline, which changes its background and border colours. You can also use sparklines to compare numbers, such as the difference between two-day temperatures. To create a sparkline, click on the data you want to chart and select “Create Sparkline.” This will open a box where you can choose the type of sparkline you wish to create. Every kind of sparkline has various purposes.

To insert sparklines in google sheets,

=sparkline(data,[options])

To insert sparklines in Excel,

Insert > Sparkline type > Line or column

Share and publish

Dashboards get broader views when shared and published. Share via Excel or google sheets.

You can publish your Google Sheets dashboard as a web page, or implant it as an element on another page, by clicking:

File > Publish to the web

Conclusion

Functional dashboards are a great way to monitor KPIs and keep an eye on essential business metrics. With the right dashboarding software, you can create a handy and actionable dashboard in just a few clicks. Stay updated and keep track of emerging trends by reading industry publications, discussing new technologies with your team, and attending conferences. Investing in your growth will make you a better data architect and empower your company with cutting-edge technology. You can also explore other dashboarding software and look at how they design their dashboards to learn more about making your own even better. Doing so will help you make the most of your next dashboard and get the most out of it. These tips will help you design the most functional dashboard personalised to your business.

Tagged Business Analytics Business intelligence Dashboards Functionality KPIs

Top Tips for Creating Functional Dashboards

Contents: