Excel Dashboard Software

When creating a chart for a dashboard or another business intelligence software solution, there are a number of factors to consider. Not least of these is how you go about ordering your data for display on the chart. It may seem like this question has on obvious answer, but in fact this can be a complicated issue, which benefits from thought, consideration, and experience.

If your data set has time as a variable and you wish to display a coherent time line, then data sorting is not an issue. You can simply allow one of your axes to represent time and lay out your data chronologically. The difficulty arises however if you have a set of data points taken at the same point in time, or if you do not want to take the chronological approach.

At this point you may be tempted to opt for an alphabetical layout. Surely your dashboard will gain from an additional level of orderedness, and your results will be more clear and coherent? Unfortunately this is not the case. In many situations there is a specific pattern or order already contained in your data, which will be instantly revealed if displayed in the right way.

When it comes to sorting data for a dashboard chart, there are a number of things to look out for. Firstly, you should consider if there is any form of order or logic that can be applied to the data you have collected? Can you group it based on gender, magnitude or some other factor? It can help to consider the overall aim of your chart. What are you trying to tell people? How is your data relevant to your dashboard as a whole? All of these considerations will help you when you are laying out your chart, and will also help you to choose appropriate chart types for specific data sets.

When using business intelligence software you are bound to come across countless line charts. These are undoubtedly some of the simplest and most common charts in use at the moment, especially on dashboards. Line charts are by nature simplistic, uncluttered and clear. Nothing can be more straightforward than a trend line, visually representing progress over time with a single line.

If you choose to use a line chart in a dashboard, it is important that you appreciate the simplicity of this approach. Like any chart, a line chart should state its information as clearly and succinctly as possible. You should never try to add to a line chart in any way. Any added features will just clutter your chart and reduce its overall effectiveness considerably.

When creating a line chart, try to keep labelling and text to a minimum. Of course your chart should have a title, and each axis must be clearly labelled. Units should also be displayed. However you should make sure that this text does not dominate the chart. It should be coloured in such a way that it is clear, but not so that it is attention grabbing. However you should also avoid legends of any kind. If you have more than one series on a single chart, label each one directly and briefly.

As the goal with all charts is to avoid clutter, you should try not to have too many series on the same chart. If these trend lines are overlapping each other or passing very close together it will be difficult to get a clear overview of the data. If you do use more than one series, make sure that each trend line is coloured so that it contrasts sharply with its fellows.

If one of your axes represents time, make sure that all data points are equally spaced. If you do not do this, users will get a false impression of the timeline. You should also be aware that axis limits are variable. Choose the range of each axes based on the data at hand, and fit it appropriately to best display your data.

When using a business intelligence solution like a dashboard it is important to appreciate that they are highly visual media. The first impression that the dashboard makes is based on its appearance, and this is dictated by choice and layout of charts as well as choice of colours.

Of course when you generate a chart for your dashboard, you can allow the colour scheme to automatically be chosen. While this is easier in many ways, it can result in a dashboard that contains a variety of differently coloured charts. This will often look confusing and incoherent, and can drastically reduce the effectiveness of the dashboard. If you approach your dashboard with a particular colour scheme in mind however, you can use the colours to draw attention to certain points and trends in the data. Colours such as red should be used with consideration, as these are often the areas of the dashboard that will instantly draw attention.

Another important aspect in relation to colour schemes in dashboards is the colour scheme used for structural elements such as axes and axis labels. These items should be visible, but should not draw attention. Therefore they need to be coloured in such a way as to be clearly legible, but without dominating the chart. Again it is useful to adopt a universal colour scheme for items such as this across the dashboard. Finally, the background colours of the dashboard and the charts should never be ignored. Ensure that these are neutral and that they contrast with the other colours in such a way as to throw the data into sharp relief.

A recent publication from the University of Saskatchewan examined the effectiveness of embellished charts in comparison to the more traditional minimalist charts most commonly used for business intelligence purposes. The principal aim of this study was to see how aspects such as clarity, information transfer and recall were affected by the addition of embellishments.

The standard wisdom on this question is that embellished Excel charts are often more confusing. The aim of a chart or graph is to get its message across quickly, simply and effectively. Many people are concerned that embellished charts are too cluttered, which results in confusion. It is therefore generally accepted that a chart that incorporates a number of additional unnecessary features will be less effective in terms of clarity and intelligibility. Such charts are also more time consuming and less cost effective, as they take longer to create and may take longer for users to interpret.

However the University of Saskatchewan study yielded some unexpected results. It was found that on average embellished charts were just as easily interpreted by users as the minimalist ones. However, it was also found that the level of recall was much better in the case of the embellished charts up to three weeks later. Put simply, the use of additional features made the charts more interesting and more memorable.

This paper has proved controversial in many ways amongst the business intelligence community. There is now a section of chart designers who believe in creating eye-catching and memorable charts, in order to promote recall. However their detractors point out that this approach may not be feasible. For example, if everyone is trying to create a memorable chart, and if every dashboard features a collection of such embellished charts all that is create is clutter and noise. In the midst of a selection of minimalist charts, a single embellished chart will of course stand out and prove more memorable. However a cluster of such charts becomes confusing and defeats the purpose of embellishment.

Another argument against embellishment is that there is no need for business intelligence charts to be memorable. By the time two or three weeks have elapsed a number of newer more up-to-date charts will have been generated, and the data of the previous charts may be obsolete or redundant. Clearly choosing minimalist or embellished charts is a personal decision, but in terms of dashboards it may be best to steer clear of gaudy attention-seeking charts, especially if you will be displaying more the one chart at a time.

If you are considering trying out some business intelligence software you may be wondering what the benefits really are. You will probably more than likely wonder if there are any risks involved also, especially if you are going to be using your dashboard to deal with sensitive information. Many sites and articles will tell you that there are great benefits involved with using a dashboard solution such as Excel Dashboard, but not very many specify exactly what those benefits are.

• Saves Money
• Bridges gap between planning and action
• Allows long term planning
• Greater focus on performance and analysis

Saves Money:

This is one of the principle benefits of most forms of information technology. Whereas previously hours of manpower would have to be invested into collecting and charting the data in just one single graph, business intelligence solutions like Excel Dashboard can provide up-to-date detailed reports in seconds. When the cost of paying a team of accountants to create a single report is considered, business intelligence solutions are an undeniable bargain.

Bridges gap between planning and action:

In most large organisations there is a vast gap between the managerial staff who make decisions and those further down the ranks who are in charge of putting these decisions into action. With effectively laid out dashboards, higher up executives can get realtime data of the day-t0-day dealings of any aspect of the business and instantly see how their plans are being put into effect.

Allows Long Term Planning:

Using the analytical features of Excel Dashboard enables dashboard users to extrapolate data forward in time and gain predictions on future performance. Even without activating these analytical features, it is possible to plan ahead for possible issues and problems based on the volume of data and detail in each report.

Greater focus on performance and analysis:

With an Excel dashboard generating KPI reports and other business related reports, the managerial staff of any business will find their focus shifting towards correctly analysing the data provided. Once the benefits of a dashboard are recognised, the emphasis will be on using the data provided effectively in order to boost performance.

The benefits of dashboard solutions are clear and valuable. In the next post we will discuss any risks that should be accounted for, such as data security etc.

Creating an Excel Dashboard can sometimes be a daunting experience, especially if you are unused to working with business intelligence software. However there are some simple and straightforward steps that can be followed which will make this task considerably easier, especially for novices.

1. Data
2. Dashboard Layout

Data:

The basic foundation of any dashboard is data. Put simply, you need to have the kind of data that can be plotted on a chart in some manner. This generally means that each data point will need at least two components, corresponding to ‘x’ and ‘y’ points on a simple graph. If you have the kind of data that is listed in a spreadsheet format, then you can create an Excel dashboard with it.

Dashboard Layout:

After you have organised your data sufficiently, you must then decide on the layout of your dashboard. It is a good idea to let your data guide you at this point. Consider the manner in which you want the dashboard to perform, and what kind of information you want to be receive from it. You should then choose a layout and a selection of charts which adequately represent your data in the most efficient and clear manner. Remember that a successful dashboard is one that has neither too much or too little content. It must also be clear and easy to understand.

At this point you may wish to select a template, that will provide you with a readymade dashboard layout. Templates can be useful in helping you to acquire an overview of how your finished product will look, and in helping you to quickly develop a prototype. The dashboard layout can then be tweaked and adjusted according to the specific design requirements, and edited in order to optimize the effectiveness of the dashboard solution.

Previously some common errors that can be made by people unfamiliar with business intelligence software were discussed. To follow on from this we will now look at three more errors that can have a detrimental effect on the effectiveness of your Excel dashboard.

• Poor choice of charts
• Sparse data
• No clear conclusion

• Poor choice of charts

We have already looked at how the inclusion or irrelevant or unnecessary charts can clutter and confuse your Excel dashboard. However even when appropriate and important charts are included in your dashboard it is still possible to make some mistakes when putting them together. Remember that the last thing that you want is for your dashboard to look overly complicated or even intimidating. Be sure to choose charts that are clear and easy to follow, and avoid having data points too closely spaced together. You want your dashboard to be attractive to its users, so that they will use it at every opportunity and gain the greatest benefit from this invaluable tool.

• Sparse Data

Another mistake that can be made when putting together a dashboard is leaving out too much. This is not as common as cluttering, but it is equally serious. At all times when creating a dashboard it is important to keep in mind the ultimate function of the dashboard. If it is designed to provide an overview of KPIs or sales performance, it must include all data that is relevant to that topic. A single chart with a line going up or down is one of the worst examples of a dashboard, as the user is not availing of all of the detailed analysis and forecasting possibilities of the business intelligence software. It is also important to include enough data points on each chart. Again be careful not to clutter, but also do not under-represent your data either.

• No Clear Conclusion

And finally, the cardinal sign of dashboard design – no clear conclusion. Imagine a dashboard with attractive charts and a good layout, that due to one factor or another is completely useless. For example there may not be enough data, some or all of the charts may be irrelevant to the conclusion that is required, or the charts may be pertinent but completely irrelevant to each other. A good dashboard provides the user with a clear conclusion, backed up by easily accessible data. If the user has to trawl through each and every graph to try to work out where they stand on a particular issue then the dashboard is a failure.

When designing an Excel dashboard the key approach is to always keep in mind what the finished product should do. Whenever a decision is to be made, the designer should review the criteria for the dashboard and then choose accordingly. If this simple rule is followed the Excel dashboard will be an exceptional asset to any business or organisation.

When used properly, dashboards are highly effective business intelligence tools. However there are many mistakes that can be made when designing a dashboard,  that can ultimately result in a less effective and sometimes even confusing layout.

Some of the primary things to avoid when creating dashboards are:

• Clutter
• Lack of Focus
• Irrelevant charts

• Clutter:

When you design your Excel dashboard you should keep in mind that you want reports that are clear, succinct and to the point. What you definitely do not want, is a dashboard that is so full of various charts and data that it is impossible to get a clear overview.

• Lack of Focus:

To create an effective dashboard, the designer must first know exactly what information he or she wants to receive from the dashboard. There is no point including a chart on the amount of water purchased for the staff canteen, or the figures spent on a specific advertising campaign, when this sort of data can be included in more general charts focussed on expenses. If you want that kind of specific detail, you can include it through drill down features, or even create a separate dashboard for detailed expenses. Always remember that a dashboard should display KPIs (Key Performance Indicators), that help managers and executives decide what changes and decisions should be made to boost the company’s performance. The designer of the dashboard therefore needs to know exactly what the KPIs are, so that the dashboard can reflect the relevant information.

• Irrelevant Charts

Again this is a very key error that can easily happen when putting together an Excel dashboard. Even small business generate a huge wealth of information, and just about any aspect of a business’ day-to-day operations can be charted and potentially included in a dashboard. It is therefore necessary to be clinically precise when creating your dashboard. Remember you can always go back and edit the specifications if you find that you have included certain charts that are irrelevant to your decision making, or if you have omitted data that is highly important. Business intelligence software such as an Excel dashboard can make an immediate difference to how you run your business, but it is important to constantly adapt your software to your needs and to your operation.

Business Intelligence software such as dashboards were developed to help the executives of large organisations interpret the masses of data generated by their operations on a near constant basis. The better and more effective the dashboard, the more informed the users and therefore the better placed they were to make judgement-based decisions.

At the current time, dashboards are graphic and integrated representations of the various figures and performance indicators of a given enterprise. They can be grouped into two categories, data-based and model-based. Data-based dashboards are directed more towards relaying and interpreting data from databases, data warehouses and OLAP programs, while model-based dashboards are designed to analyse using a set of rules, algorithms, forecasting, decision trees and simulations.

In the current business climate, it is more important than ever for decision makers to be as informed as possible when it comes to the performance and status of each aspect of a large organisation. In order to make an informed decision elements such as forecasts, performance metrics and current trends need to be taken into consideration. A competent and well developed dashboard application can provide all of these features.

The first Dashboards begin to be used for the purposes of business intelligence in the mid 1980s. However these programs had limited capabilities, and were often limited to executives within an organisation. Since then dashboards have developed and become wide spread.

Dashboards currently fall under the category of Business Intelligence Systems, with multiple layers that are tailored to various users within a company.  They are designed to integrate data from multiple sources, such as documents, communication programs and data files in order to create reports and highlight potential problems.

As business intelligence software developed, programs were able to create basic reports from data files. However these reports were limited to certain areas, and the applications were not capable of creating comparative analyses. Nowadays one of the key features of business intelligence and dashboard technologies is the ability to perform comparative analysis. These multi-layered reports are presented visually. The two key aspects of dashboard technologies are now visualization and integration. In each dashboard key performance indicators are highlighted and displayed, and any changes or complications are immediately visible.