I have worked on pulling together a quarterly report over the past two weeks. It was the tail-end of a task left by my manager before he went on a holiday. It has been a study in both the power and the limitations of Excel for me.
If you just want to read about reports, skip over the next digression.
I am not a big fan of Excel.
The fact that Excel is the source of a heinous amount of busy-work in my job probably is a reason for this, more than any inherent quality of Excel.
On the other side of that equation, on more than one occasion now, Excel has been very helpful with some data I had to analyse. The power of ad-hoc Pivot tables to aggregate large quantities of raw data on-the-fly and come to some sensible conclusions from it are not to be sneezed at. So, I’m also starting to love Excel.
The key point on which my feelings for Excel hinge seems to be whether it is being used as a tool for coördination or a tool for analysis. Whenever I deal with a spreadsheet that is a shared coördination point between multiple people, things almost always just go sour sooner or later. But when I use it purely for analysis, it’s actually powerful and helpful.
This point is somewhat relevant for the following.
After extensive experimentation on the quarterly report, I can first of all say; the best way to create an ad-hoc report with charts is to not use Excel at all.
As of Word 2013, using the “Chart” button from the “Insert” ribbon is the easiest way to create quick embedded charts that you can format with all the flexibility of Excel. It basically performs a light-weight embed of the relevant Excel features right into Word.
And if you need to edit the content of the chart later, just select the chart, right-click, and select “Edit Data”.
Everything else in the chart can be tweaked by selecting elements and adjusting the styling through the ribbon or the pop-up controls around the chart. It doesn’t lead to an easily re-usable result, but then… this is an ad-hoc report, right?
The reason I do not favour this approach for recurring reporting is that with all the source data and representation intermingled, it is just much to easy for things to turn into a mess. Especially if multiple sets of hands are involved.
I was pretty much convinced that for recurring reports, the best approach would be to put the data in Excel, and then produce the report in Word.
Approach 1: Copy/Paste
The simplest bad idea is to create a separate Excel sheet with all the source data and charts in it… and then copy/pasting the charts across into a word document each time the recurring report needs to be produced.
This… almost works.
But you’ll leave yourself at the whim of the sizing/stretching gods for the results in Word. If you want a chart to be the width of the page, you’ll have to carefully manipulate the source material until it looks right on the page. And then you have to repeat this each time the report is produced.
The chances of turning in a shoddy report, especially under time-pressure, is quite great. I’d recommend against doing this to yourself, even if it is easier to get up and running the first time. You’ll pay the price many times over for the lifetime of the report.
Approach 2: Embed Excel
I had a very crafty idea to bundle my document and charts in a portable fashion.
First: embed the Excel spreadsheet within the Word document (just drag-and-drop it across).
Second: open the embedded Excel spreadsheet, select each chart I want to use, then “Paste Special” a link to this chart into the document.
Third: save the Word document.
I was amazed at my genius. Everything seemed to work. Whenever I edited the source material in the embedded Excel spreadsheet, it’d update throughout the document, and yet I only had one file to worry about; the Word document containing it all.
This victory turned to ashes as soon as I re-opened the Word document.
It turns out the links I had embedded all had a process ID in them of the instance of Excel that opened the embedded spreadsheet. Re-opening the document changed the process ID, and all my charts were orphaned in Word.
I wish that Microsoft had a container format for related documents. A format that can contain a bunch of related Word/Excel/whatever documents with relative links among themselves that remain valid when you move the container around.
Approach 3: Link Excel
The solution that works best, with some limitations, is to place a Word and Excel file along each other. Put all the source material in the Excel file, then copy/paste-special links into the Word document.
These links are all to fully qualified paths, which initially led me to fear that it was going to break as soon as anything moved. And this is mostly true; if I just move the Excel file, the Word document can no longer find the source material. But as long as I copy both files alongside each other in the same directory, without changing the file name of the Excel sheet… Word does some magic link resolution that also scans the local directory for the right target document.
This still does not work within SharePoint as far as I can tell. Links are fully qualified to the target document, and moving them around in SharePoint will break all the links permanently.
So, the approach I have chosen:
- Create template Excel and Word documents on local drive
- Upload to SharePoint as a templated solution
- When producing the report: save both locally, edit Excel data, refresh Word document (CTRL+A, F9)
- Upload the Excel and Word to a target location in SharePoint without changing the Excel filename
Every time the full paths are saved on a local drive, the links will work due to the files being side-by-side. Whenever they are in SharePoint, you just have a correct static document without linking.
I think this is about as much elegance as I can hope for here.
Still… a container with relative links would be awesome, Microsoft…