Charts without cells, part I
- Jun. 09, 2014
 | Example files with this article: |
Introduction
In the past, I blogged about the possibility to create charts in Excel without physically storing the data in cells. Yes, this is possible! In the meantime, I haven’t done much with it. But now it’s time to explain you in detail how this is done. This topic will be consisting of 2 articles. In the first part (this article), I show you step by step how you can set up a chart that is nice and that you don't create every day (that is, I suppose you don't do that). The data that we plot in the chart will be stored in cells. In part 2 of the topic, we assume that the data that we store in cells (to be plotted) is not to be stored in cells, and still we generate exactly the same chart!
This is the chart, taken from the internet, that we want to create in Excel:
Step-by-step instructions
Starting from a blank sheet, here are the exact steps to replicate such a chart using Excel 2013.
First we insert a new scatter chart:
Hold down the Alt key and drag-and-drop the chart until it coincides with let's say cell E2 (exact location is not so important). While keeping the Alt key pressed, resize the chart until the border coincides with physical cells (again, unimportant for the rest of the article). You can resize the chart if wanted, or do this at a later stage.
For the base data, type the following in the cells A1:B4:
| Category | Value |
| A | 5 |
| B | 11 |
| C | 17 |
Also, add the following XY data points for our scatter chart in cells A7:B40 (remember that you can download the file on top of this page to save time):
These are in fact the data that we will do away with in the second part of the article: the generate the same chart without cells A7:E40 !
Now that we have a XY points for our chart, let's plot them on the chart. For category A, right-click the empty chart area and choose "Select data…":
For the Legend Entries (Series), press Add… and fill/make selections to come to the following (your sheet name might differ from Sheet1):
Repeat the above step for categories B and C, each time selecting the correct ranges. You should now have something like this:
Hmmm, this doesn't really look like the chart above. So let's continue. now it's time to create 3 shapes. I use rectangular shapes like the ones you see in the orginal chart, but you are free to chose other shapes or even a picture file. Insert a new rectangular shape:
Like you did above, hold down the Alt key and draw a shape that sticks to the borders of 1 cell. Set the Shape Fill, Shape Outline and Shape Effects of your liking. When done, copy paste the shape 2 times and only change the Shape Fill for the 2 other shapes (in layman's terms, just give the shapes a different color).
Now back to our chart! Select the first shape, copy it (Ctrl-C) and select a data point in the chart for category A. Then paste (Ctrl-V). The result while pasting the custom markers for categories A and B would be:
Remember that you paste the custom markers in a static way: changing the shape will not change the marker that you see in the chart. You need to copy/paste the new shape over the old marker. Next, let's get rid of the horizontal and vertical gridlines. Select 1 of the horizontal gridlines and press Delete (Del). For the vertical gridlines, do the same. For the horizontal axis, we will do some actions as well. Double-click a label in the horizontal axis (0 to 9 currently) and set properties for the axis in the pane (or is it called a 'pain', rather):
- For the Label Position: set it to None (hence, we remove the labels 0 to 9 for the X axis) (screenshots below)
- For the Maximum of the X-axis: set it to 10 (does not matter too much honestly)
- For the Line, we will make an arrow-type of axis that goes from grey to black (see original chart). We will need to set a number of things in the properties for the axis. The linear gradient is the best choice here, with Gradient stops at every 25%, each time a darker grey and black at the 100% stop.
Now here's the exercise for you: do the same for the vertical axis :-) Remove labels and implement an upward arrow for your axis. No need to change the Maximum of the axis, though.
Next step is removing the line border of the Plot area, since we are using the pane to format chart elements. Also as we go, we can set a light grey background for our chart (the chart area fill).
Finish
That's it, we are done. If your end result matches the one in the download file, you successfully did all steps. Observe that I changed the presentation a little bit: for each category, the first bar is the highest and the second bar the smallest. But you can also do it the other way round. You see, charts that are not standard in Excel do not need to be difficult.
Introducing part II
In part II we will plot the same data (5, 8, 17 for categories A, B, C respectively), but without calculating the XY data points in cells A7:E40. We will use defined names to do the heavy work for us.
