atom tips
  • Productivity

27th February 2017
By Matt

No matter what type of business you’re in, it’s easy for report writing to take up a lot of your time. In this post, we’ll explore how you can save hours each month by getting your tools to write reports for you.

As an online marketing agency, it’s crucial that we keep our clients up to date on results. This means that we end up writing A LOT of reports that often feature similar information. So, to make the process faster and our lives easier, we’ve started outsourcing our reporting. Not to another agency, but to good old Microsoft Word!

Building Your Excel Dashboard

In this simple example, we are going to set up a short, written report comparing week-on-week and month to date performance.

Excel Dashboard PreviewTo start with, we’ve set up a basic Excel dashboard that automatically pulls in the current week’s and previous week’s data. To this, we’ve added a column to find the difference between the data. Finally, because we’re nerds, we’ve constructed the dashboard to auto-populate with data from other sheets, based on changing the values in the corresponding ‘week’ and ‘month’ cells. The more you can automate this dashboard, the better it will work for you.

Don’t worry if you haven’t got a dashboard set up – all you really need is a sheet that contains the values that you want to report in a way that’s easy for you to understand.

Construct Your Dynamic Content In Word

Now for the fun bit: getting your report to write itself! To do this, we’re going to build a Word document that references to your Excel dashboard. In our example, we want to report the week-on-week change, so we’ve put together some simple bullet points for the basis of our comparison.

Next, we need to use ‘Paste Special…’ (Ctrl+Alt+V or Cmd+Ctrl+C on a Mac) to make our report dynamic. So, jump into Excel and copy the cell you want to insert into your report. Then hop back into Word, select where you want to insert your data, and click on the arrow beneath the ‘Paste’ icon and open the ‘Paste Special…’ window:

Making a selection in our Excel DashboardPaste Special selection for dynamic reporting

Select the ‘Paste link’ radio box to make the link dynamic (you can see the name of the linked cell at the top of the window below). Choose ‘Unformatted text’ as your formatting type and hit ‘OK’!

Paste Special dialog box for dynamic links

BOOM. You’ve just linked an Excel cell to your Word document which will update automatically when you update your spreadsheet (go on, try it) and is free for you to format however you want! Now that you’ve done one data point, you can flesh out your report with as much dynamic data as you need:

Save this document somewhere convenient and you’re good to go!

Pro Tip: Automatically highlight dynamic fields so that you don’t miss any by going:

File → Options → Advanced → Scroll to Show Document Content → Change ‘Field shading’ from ‘When selected’ to ‘Always’

The above example is a very simple way to build an automated report. We’ve used the same principle to streamline processes throughout atom42 – whether that’s turning 20 minutes of research into 30 seconds of clicking, creating beautiful dashboards for our clients, or simply for internal updates. We hope this inspires you to do the same!

Replies