Excel Dashboard Software

When visualising the typical dashboard layout, most of us will be familiar with the standard division between charts and tables. Generally a dashboard tab will consist of one or more charts, which will occasionally have tables attached to them to provide supplementary information. However it is not uncommon to find simple charts contained within these tables also, especially bar and line charts.

Placing a chart inside a table can provide a wealth of information that would be almost impossible to include effectively otherwise. It also means that the import of the data can be quickly and easily understood by the dashboard user, who obtains a clear and immediate overview of the figures involved. Typically horizontal bar chart elements are inserted into the table, next to each data entry. The magnitude of the bar chart element next to each entry indicates how this data point compares to the other entries on the table, based on a given KPI. For example if each column on the table focusses on a different KPI, a bar chart element of a specific magnitude will have to be placed in each column attached to a specific data point, allowing its performance in terms of each KPI to be instantly compared with the other data points in the table.

The efficacy of these charts can be increased if colour coding is also used. For example you can highlight underperforming data points using a specific colour, which is usually red. Of course performance is determined by each perfomance indicator, so any given data point may perform well in some areas but poorly in others. If you are already using bar chart elements in each KPI column, it is straightforward to highlight underperforming chart elements by rendering them in a different colour.

One of the primary functions of an Excel dashboard is that it can allow for a certain degree of forecasting. While this is of course not an exact science, it can be useful when deciding on long term business strategies. The basic theory behind this approach is that past results generate a particular trend, which can then be used to guess at possible future results.

There are a few approaches to choose from when generating a chart of this kind. If you decide to use your current data to predict a possible future outcome, you must first generate a line chart that displays your existing data. From there you will need to generate a line of best fit. If your data forms a perfect straight line there will be no need to do this, but in a real world scenario this is very unlikely. Therefore your line of best fit will be an approximation which you can use to determine an overall trend.

A very simple way of creating a line of best fit is to create a new data set, with a point identical to the first point on your original data set and a second point identical to the end of your data set. In this way you can create a line between both points, which will be a line of best fit. From there it is a simple process to extend the line of best fit to some point in the near future. This should give you an indication of what kind of figures you can expect at that time.

Dashboards are primarily business intelligence software applications, and their primary function is to relay data in a clear, concise and intelligible manner. While it is generally recommended that dashboard designers avoid fancy graphics and distracting effects, this does not mean that all dashboards should be bland, boring and ugly. A standard dashboard can be rendered more attractive and user friendly using just a few simple WordArt techniques, without cluttering the display or reducing functionality.

WordArt is best applied to headings and logos, but can also be used for labelling of axes and certain data points. The key to using this feature effectively is to be restrained at all times. While moderate and subtle usage of WordArt can result in a professional-looking dashboard excessive use will have the opposite effect, leaving you with an amateurish mess of colours and shading that will look dated and cluttered.

In order to format your text using WordArt simply go to Insert and choose WordArt. A drop down menu will appear offering you a number of options. Select one and you should then see a box in which you can type. Once you have entered your text you can drag the text box around the screen, rotate and resize it. Once you have placed your WordArt you can always go back and edit it by double clicking on the text. This will provide you with a full set of options once more.

If you wish to make a good sales dashboard, there are a number of things you need to consider. On primary consideration, you will want your dashboard to of course let you know whether sales are up or down. While this should of course be the focus and ultimate aim of a dashboard of this type, fixating on this aspect can in fact be a mistake.

Many sales dashboards make use of gauges to demonstrate whether sales have increased or decreased. However this leaves out a large amount of information, which will probably of use. A line chart demonstrating a visible trend line will be much more useful than a gauge chart, providing you with an overview of your business’ historical performance. However avoiding using gauge charts is not the only error that can be made when creating a chart of this kind.

While you will need a chart that displays your sales trend, you may wish to find out what exactly has caused your decrease or increase in sales. The vast amount of sample sales charts seem to focus almost exclusively on performance, without providing sufficient background information on the various elements that contribute to these results. A well laid out sales dashboard should provide information on sales demographics and other KPIs that influence your sales numbers. They should also make use of charts that can give clear comparisons.

Previously we discussed what not to do when incorporating animations into your dashboard charts. However business intelligence software can be extremely useful and many of its features can add to your reports greatly if used in the correct manner. One of the key uses for an animated chart is as a means to display time development. If you create a chart that demonstrates how a trend evolved over time, this is an excellent candidate for an animation. A movie showing how the situation developed through the medium of your chart would be a highly effective and entertaining way for you to get your message across to the users of the dashboard.

When creating an animated chart, it is important to include certain features. It should be possible to pause the chart at any point, so as to analyse its evolution at every step. It is also important that you animation run smoothly. A flash chart that develops in fits and bursts and has gaps in its data, will prove detrimental to your dashboard. Make sure that your chart operates smoothly and that it has a polished and professional appearance, otherwise it will look unprofessional and poorly planned.

Given the tone of most of the posts on this blog, you may be forgiven for thinking that the instant reaction here will be that animation should be avoided at all costs. However many business intelligence software applications can use animation effectively, but only if applied appropriately and in moderation.

To many people, the concept of an animated dashboard sounds chaotic, distracting and slightly gimicky. The vast majority of us want functional dashboards that give us the data we need with the minimum amount of hassle, and in the clearest manner possible. When considered in this way, an animated dashboard sounds exactly the opposite of what we might want.

There are a number of caveats when it comes to animation on dashboards. If you create a flash chart, it should be for a valid and functional reason. All too often users are seduced by the fun features of animation, and stick such charts in wherever they can without any thought for function. Animation for animation’s sake is basically a waste of time. A movie showing your chart appearing out of nowhere and building itself in segments is not really informative. Your audience will simply be waiting for the movie to stop so that they can analyse the completed chart. Like annoying unskippable cut scenes in games, for the first one or two occasions it will be a novelty and then it will start to get frustrating.

Unfortunately dashboards are not designed to be entertaining. If you do choose to use animation in your dashboards, make sure that it adds to the impact of your chart, instead of being a gimicky sideshow.

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.

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.