This is an example of the default settings for a bar chart in Excel. This is functional but could be far more effective; here are my main steps I use to create quick, effective data visualisations.
Continuing the theme of automatically updating charts, we’re going to look at how to make a chart deal with a dynamically changing data range. This is useful for situations where you have multiple measures that each have a different number of independent variables (the variables plotted on our x-axis, which was the month of the year in our last example). If you use the method as lined out in the last tutorial for this you will find that the chart updates automatically, but the number of variables shown on the x-axis will not change. To solve this, we will need to use Named Ranges; OFFSET; and COUNT to produce the ranges that will feed the automatically updating charts.
Following on from our last series of HOW TO… Excel, we’re going to expand the concepts used to create the dynamic data table and create another sheet for the same workbook that will be the home of an automatically updating chart! Unlike pivot charts, this will be able to update as soon as new data is inputted into the data backsheets, and will be connected to the same ‘linked cell’ as our previous combo box, so the changes will be persistent throughout the workbook. This will allow us to create a consistent and pleasing user experience for whom ever will be using the tool.
This is a continuation of the previous tutorial.
Due to the volatility of the INDIRECT function, it may be necessary to avoid using it when trying to create large dashboards which will be handling and outputting large amounts of data. So, we’ll need an alternative in these cases. One of the ways this can be done is by using the SUMIFS function, and structuring our data to be compatible with the SUMIFS function.
Creating a full and cohesive user experience in an Excel based dashboard can be really difficult if you don’t know where to start. Often, the easiest way to approach the situation will be to utilise Pivot Tables and Pivot Charts, which come with useful tools like slicers to dynamically filter the data with a touch of a button. I have a few pet peeves with Pivots however, mainly being that they require data be in a particular layout; that they can be restrictive on the type and layout of the output they produce (although they do provide a good level of options); and when updating or changing data within a spreadsheet Pivoted data does not refresh unless it’s explicitly told to (which can create a jarring experience when handling and presenting larger data sets).