The ‘L’ Shaped Excel Graph
I recently had to develop a graphical report in Excel that was required to fit perfectly on a single A4 page for printing purposes. A lot of information was supposed to fit on this page, so using the space well was very important. After the initial formatting was reviewed it was decided that one graph in particular needed to be bigger and clearer. With a bit of reshuffling the final space available for this graph was a big ‘L’ shape at the bottom of the page.
A perfect amount of space but unfortunately part of the ‘L’ would have to go to waste – unless some sort of ‘L’ shaped graph could be created…
The ‘L’ Shaped Graph:
The ‘L’ shaped graph utilises the extra space by moving the title and legend away from the actual data. This results in the data having space to expand and become more readable (very important with this detailed ‘Clustered-Stacked Column Graph’). It is important to note that once created, it is very difficult to edit this graph and it should most likely be treated as read only.
How it is done:
- Finalise the data for your chart. (The legend will no longer update to reflect changes in the data once we are done)
- Create a copy of the chart
- Delete all axis labels and gridlines on the copy.
Now comes the trick – removing the data without affecting the legend:
- To do this, select each data point on the copy (not data series) and right-click: Format data point. When you first click a data point it most likely selects the entire series, so click again to target just the data point. If you were to do this for the series then the legend would update to reflect the change, which we don’t want.
- Select No-fill in the Fill tab. This will white out that data point. Do this for all the data points until just the legend and title remain on the copy.
Now to fix up the original chart:
- Select the legend and title of the original chart and delete
- Resize to utilise the space
The rest is just simple formatting to customise the graph to your needs. Here are some of the things I did:
- Select the legend of the copy and right-click: Format legend
- In Legend Options: Legend Position select ‘Top’.
- Now resize the legend of the copied chart to whatever you like. You may notice that the chart area, although blank, is still selectable and may be an annoyance while resizing other aspects of the copy. This can not be deleted however you can resize it to be very tiny in one of the corners of that graph.
- Remove the borders of both the original graph and the copy. Right-click: Format Chart area: Border Colour: No line.
- Now create borders to surround the ‘L’ shaped chart on the spreadsheet itself to make it appear like the original and the copy are joined.
The process of whiting out each data point can be very tedious. In practice I created this graph with VBA to automate the process. There are some more tricks required if using VBA for the whole graph however I won’t go into them here. Like all VBA, heavy use of the macro recorder, trial and error, a little blind luck and a lot of patience go a long way.
