Using this method, it is possible to chart categories against each other or a numerical variable using either a bubble or XY Scatter chart. Strictly this isn’t possible as both the axis in a bubble or XY Scatter Plot have to be numbers. We will achieve the seemingly impossible by giving each of the variables a number and using that as the co-ordinates in the plot.
To label the axis we will use two additional series with dummy data in aligned along the 0 values and label them. The axis will be removed and error bars used to provide a line across to each of the horizontal categories.
This is the starting data and we want to plot sales targets against Office and Quarter.
Generate Quarters from the dates
To find which quarter any given date is in we can use the rounding, so for a date in C2:
E2=ROUNDUP(MONTH(C2)/3,0)
However we want to have the year as well as the data covers more than one year and if it was as simple as Q1-4 we could use numbers on one of the axis. So we will use the formula
E2=IF(C2="","","Q"&ROUNDUP(MONTH(C2)/3,0)&" "&YEAR(C2))
This not only adds the year to the quarter it also checks that there is a date to convert.
Assign numbers to the categories
This is the heart of the method that we will use. We will use vlookups to convert the data into a series of numbers. The first step is to create the lists:
To make life easier we will assign the names “Offices” and “Quarters” to the ranges L3:M5 and O3:P7 respectively by selecting the range and then typing the name into the address box at the top left of the sheet.
To find the number for the quarter we use the following VLOOKUP:
F2=VLOOKUP(E2,Quarters,2,FALSE)
This looks up the quarter/year combination in the relevant list to generate the number which will be used on the X axis.
The number for the Y axis (offices) is given by:
G=VLOOKUP(A2,Office,2,FALSE)
Using these formula we can build the data for the chart to end up with:
This is sufficient to produce a simple chart, to see this select the X, Y and Value columns and chose to insert a bubble chart
However we’re looking to produce a rather more complex chart so we need to add more series for the axis
Label the categories
To label the categories we need to produce some dummy data to place data points on the axis. To do this we create two new series of data and add them to the chart.
These provide two new series of data grouped on the Y and X axis respectively. The easiest way to add these to the chart is to simply copy a new series, for instance F10: H12 and then paste it onto the chart, however this isn’t certain to be successful in adding separate series as Excel can be a bit clever at times and decide this is all part of the original series. If using this method check by clicking on “Select data” and making sure there is a new series.
For safety we will add the new series using the more manual but more certain way, using the “Select Data” dialogue.
Click on the Add button to display the “edit series” dialogue and enter the details for the series:
- Name: either type it in or select cell L2 so you can easily edit the series name
- X Values: Select F10:F12
- Y Values: Select G10:G12
- Bubble Size: Select H10LH12
- Hit OK to close the dialogue
Then add another series for the Quarters information and we have a chart with 3 series of data, one for the actual data and two for the category labels.
Label the series
Now we can use functionality which is fairly recent in Excel; it came in with 2013 (I think) and is certainly available in 2016 and 365 – the ability to assign a range of cells to act as labels for scatter and bubble plots.
To use this select the data series, we will start with the Y axis (offices) and right mouse to chose “add data labels”. Then select the data labels and right mouse again to chose “Format Data Labels”.
- Click off the check box by the Y values
- Select Left for the label position
- Click on Value from Cells at the top of the list for “Label contains” and then select the labels for the series, in this case H10:H12
Repeat this for the categories on the X axis (selecting “below” for the position.
For the data select “Bubble size” for the main data series and Centre for the location and we have:
Whilst each set of data labels is selected to make the above changes you can easily change the font size by simply changing it in the Font section of the Home ribbon.
Now that the series are labelled we no longer need to be able to see them so select each of the axis series in turn and set the marker fill to “no fill” and they disappear.
Remove the axis and adjust the chart area
Before deleting the axis we need to adjust the Y axis so that
- Set the minimum to 0
- Set the major intervals to match those used to distribute the offices (e.g. 2)
Adjust the X axis so that the minimum is 0. We let excel set the maximum as it will then match the greatest value needed by the highest bubble.
Then delete the axis and adjust the chart area dimensions so that the labels on the axis series can display.
Add lines to connect the data to the office category labels
The chart can be left as it is however being able to connect the bubbles to the Y axis categories is a nice addition. To do this we use error bars.
To add error bars click on “Add Chart Elements” and then error bars, more error bars and select the data series. Then chose to Format the error bars and select the Y Error Bars from the drop down at the top of the formatting pane (which will say “error bar options”). This selects the vertical error bars which can otherwise be a bit difficult. Now press DEL to remove them.
Then select the X Error Bars from the drop down and chose:
- Direct: Minus
- End Style: your choice
- Error Amount: Percentage – 100%
Now we have the horizontal bars we don’t need the horizontal grid lines so select them and DEL.
For a final flourish we can select the whole chart space (not just the plot area) and format it to have a gradient fill
To have multiple data series
In the example at the top of this blog we have a chart which also differentiates between the different types of sales targets and uses different colours to show this. This is simply an expansion of the technique we’ve just used, just with more data series for each of the types of data.
Setting up the data requires a new set of columns for each of the new categories. We use a function in excel =NA() which generates an error or null value which can’t be charted where there is no data to be displayed. Each cell compares the column heading (category) with the data for the row and if there is a match displays the data to be charted and if not shows #NA. The formula is:
J31=IF($I31=J$30,$H31,NA())
Copied and pasted across all the category section results in:
Note that in this example the data series for the axis remain the same as used in the example above.
After going though all the steps above, with the slight variation that we need to format each of the data series and apply error bars to each of them, we have the following chart:
