Ever wanted to create a chart like this in Excel?
In this example we show the Revenue and Expenses in a X,Y chart (or scatter chart) where the data points are shown as icons (or images). Using the horizontal and vertical lines we can make it more like a quadrant chart as well.
This post describes the steps to recreate a chart like this.
Create the dataset
First we create a dataset containing the values that we want to show.
Marketing Channel | Expense | Revenue |
Marketing | 11 | 45 |
Marketing | 14 | 9 |
Sales | 2 | 7 |
Sales | 6 | 14 |
Operations | 19 | 24 |
Operations | 36 | 1 |
Operations | 4 | 48 |
Operations | 42 | 43 |
Purchasing | 34 | 48 |
Purchasing | 45 | 10 |
Purchasing | 21 | 9 |
Purchasing | 35 | 18 |
Purchasing | 18 | 35 |
Purchasing | 25 | 27 |
Call Center | 25 | 12 |
We use this dataset by adding it into the range "A1:C16" in our Excel sheet
Create the default Chart
Now select this range "A1:C16" in Excel and go to the menu bar. Select Insert, go to Charts and from the Insert Scatter (X,Y) or Bubble Chart choose the Scatter chart.
This will create a default chart, which is not exactly what we are looking for.
We now have to tweak the chart to achieve the X,Y chart with icons.
Change the chart dataset series
In the first step we will have to change the series of the chart, as we want to see the Revenue on the Y-axis and the Expenses on the X-axis (or reverse the steps in case you want to see the Revenue on the X-axis and the Expenses on the Y-axis).
Right-Click in the chart and choose Select Data.
Now remove the the Legend Entries (Series) voor Expense and Revenue.
As we have multiple Marketing Channels in our dataset, we want to group them together in the chart, so they can all have the same formatting.
For this we add a new series and use the following values
Name | Value | Type of Value |
Series Name | Sheet3!$A$2 | The first cell label for 'Marketing' |
Series X values | Sheet3!$B$2:$B$3 | The values of the Expense column for 'Marketing' without headers |
Series Y values | Sheet3!$C$2:$C$3 | The values of the Revenue column for 'Marketing' without headers |
Add the same for all other marketing channels so the end result looks like:
After adding the marketing channels as series, the chart will now look like this. Look at how the x- and y-axis have changed to show values between 0 and 50, which is corresponding with our dataset. Also we can see the channels as part of the legend and the data points as different colors in the chart.
Adding Horizontal and Vertical Lines
In the chart we would also like to see a horizontal line on value 25 and a vertical line on value 25.
For this create two small datasets:
X | Y | |
Horizontal Line | 0 | 25 |
50 | 25 | |
Vertical Line | 25 | 0 |
25 | 50 |
Add them in your Excel sheet. Then right-click your chart and choose Select Data.
Add two new series, where the series label is either the value 'Horizontal Line' or 'Vertical Line' from your dataset.
Then choose the X and Y values same way as how you added the series for the channels.
The chart X and Y max values have now changed from 50 to 60. Change this by selecting the chart, then choose Format Chart Area. In the second line of menu options choose the dropdown and select 'Horizontal Value (Axis)'. Then click on the most right icon for 'Axis options'. Expand the item Axis Options and set the Maximum back t 50. Repeat the same for 'Vertical Value (Axis)'.
Now, select any of the two markers in the chart for the vertical line. Both of the data points should now be selected.
Right-Click 'Format Data Series' if the panel is not still open on the right side.
Under Line choose solid line instead of no line. Change the width to 1.5pt.
Then under Marker choose Fill - No Fill and Border - No Line.
Do the same for the horizontal line.
In the legend you will see that both the options for horizontal and vertical line are available.
We don't want to see this, so click in the legend on any of the two options and click delete.
Do the same for the other value.
The chart should now look like:
Create Icons
In the next step we are going to create the icons that we want to show in the chart.
In the menu go to Insert and then choose Icons.
Choose any icon of your liking and click Insert.
Resize the icon so it will be approximate 50% of the size.
Then under Graphics Format go to Graphics Fill and choose a color of your liking.
Right-Click the icon and choose Convert to Shape.
Under Insert menu choose insert a Textbox. Draw the textbox in your sheet.
Add text to the Textbox, for example Operations or any of the other labels that you are using. Make the text smaller and format it accordingly to your liking. In this case it is set to Calibri, 9 pts., and Bold.
Resize the text box, so the text is still shown on one line.
Place it on top of the icon.
On the right-side in Format Shape choose No fill, and No Line.
Select the textbox and the icon, right-click and select Group.
Copy the icon as per the number of labels you have.
Change the color of each icon by selecting the icon part or the text part.
These are our examples.
Make sure to save each (grouped) icon as a separate file on your local drive.
Adding icons to the chart
The last part we are going to change the chart, so it reflects the icons we created.
For this, click on any of the markers in the chart. It will automatically select all markers from the same series.
Then in the menu on the right-side for Format Data Series choose 'Series Options' and then the first icon for 'Fill & Line. Choose the marker option.
Expand the first item Marker options.
Choose Built-in
Where you see the marker (Type), open the dropdown and select the last option for 'image'.
Choose Form a file and search for the location where you have stored the icons.
Then click Insert.
The chart will now show the icons for the series that you have selected.
Under Border choose 'No Line' to remove the border.
Repeat the same for all other labels and the chart will now show the icons for all series.
Lastly, click on the legend (in full) and delete it. We don't need this, as the legend items are now clear from the icons itself.
Comments