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.

Merging PostScript (.ps) files

I recently had to merge the output of a bunch of academic papers I had written in Latex. Each file uses a document class from a range of academic journals (has its own title, abstract, bibliography etc).

I could have made a single Latex file and shoe horned each individual file into it, but I knew there had to be a better (less labour intensive) way.

Using Latex you can create pdf (using dvipdfm) or ps (using dvips) files. There are various ways to merge pdf files (pdfsam) and ps files (using ghostscript).

However the quality of the merged file I produced was always very poor. Eventually (after much googling) I found a solution

gswin32c.exe -dNOCACHE -dNOPAUSE -sDEVICE=pswrite -dBATCH

The “-dNOCACHE” option preserves the quality of the output file. The output file however is very large.

To ensure the page numbers in the merged document are continuous you can use the Latex command “\setcounter{page}{X}”

The Mighty Middle Click

I recently discovered a nice set of shortcuts that use the middle mouse button click:

• In Firefox or Internet Explorer, middle-clicking on a link will open that link in a new tab.
• In Firefox or Internet Explorer, middle-clicking on a tab will close that tab. This also works to close files in Visual Studio.

I’ve started to use these and find myself using them all the time. Apparently middle-click is useful in Windows 7 as well.

For more juicy Firefox shortcuts:
• Ctrl-T for a new tab
• Ctrl-W to close the currently active tab
• Space and Shift-space to page up/down
• Ctrl-F to find
• Ctrl-Tab/Ctrl-Shift-Tab to go forward/back one tab

And my two best Windows shortcuts, which I can’t live without:
• Ctrl-Shift-Esc – bring up Task Manager
• F2 in Explorer – rename a file

Thanks also to for some nice tips.

Drawing figures for scientific publications

I recently had to modify a picture (figure) as part of a resubmission to an academic journal.

The picture was created using XFig some time ago. XFig is one of the most widely used applications for creating figures for academic publications, and easily the best application I have used for this purpose. Its ability to incorporate Latex scientific formulas (use “xfig -specialtext -latexfonts -startlatexFont default” when launching XFig) and fonts, means that figures can be easily and beautifully integrated into Latex documents.

As I now have a windows machine, and am definitely no expert on linux (I am slowly trying to remedy this) I was dreading having to jump through the hoops to get XFig working on my new windows box (See Part of the process involves installing Cygwin (a Linux-like environment for Windows). Not being familiar with linux this process seems quite convoluted (and I have been down this path before).

Googling XFig also brings up WinFig. Which is supposed to be very similar to XFig but runs on MS Windows. After downloading WinFig I quickly found out that you can only save figures with 15 or less objects in them without paying for the full version (making the free version not very useful). Something that the homepage neglects to mention.

I soon realised that because I had already installed virtual box and installed ubuntu (the process was very pain free) I should definitely try to use XFig within Ubuntu. Installing XFig within Ubuntu is what one my colleagues would call automagical – with a terminal inside Ubuntu type “sudo apt-get install xfig”, then as mentioned before type “xfig -specialtext -latexfonts -startlatexFont default” and I was cooking straight away.

In order to open my file in XFig in Ubuntu I was hoping to be able to share some folders with MSWindows, and then mount them inside Ubuntu. Alas, despite all my efforts I have still not been able to get this to work. Email to the rescue, emailed them to myself, opened my gmail in ubuntu, saved the file and the problem was solved.

The moral of the story (well this blog) is that if you are trying to get XFig working on windows, – don’t. Use the power that virtual box gives you and run XFig in Ubuntu within virtual box on your Windows machine! Now to move back to the Latex editing applications in linux and away from those I have been using with MS Windows!