I have so much money in the bank I’m going to give away my number one reporting time saving secret (HAHAHAHAH not at all! I’m giving it away because I love you and I want to make your life easier). This is how you link Excel to your other reporting software so that your charts will automatically update when you add new data to your spreadsheet. If this doesn’t sound 100% thrilling to you, you haven’t stopped to consider how linking Excel to Word or PowerPoint will save you a billion hours of your life (and impress your boss and get you a raise or at least an afternoon off).

This is going to be technical. Nitty gritty. Precise.

Step 1: Make all of your graphs in Excel. Make them perfect. In the past I would get my graph about 75% done in Excel and then finish it off in my report but this won’t work. Get the fonts exactly right. Make sure the size of the graph in Excel is the size you will want it to be in the final document. This is a step I had to redo several times. That’s ok.

Step 2: Paste the graph into Word or PowerPoint using this very special paste method. Click on the graph in Excel and click Copy (Ctrl+C), then open Word or PowerPoint and look up in the menu banner for the Paste button. It will have a dropdown arrow, which you need to click. Select Paste Special. In the Paste Special box, click the radio button next to Paste Link.

PasteSpecial1

By default, the highlight will be on Microsoft Office Graphic Object. Don’t change this. Your graph will still paste in as an Excel graph, not a picture or anything like that.

Step 3: Close your Word or PowerPoint file. Open Excel to make any changes you need to the graph. Close Excel. Open Word or PowerPoint again. The pattern here is that you don’t really have everything open at once. It’s the easiest way to get things to update.

Step 4: When you open Word or PowerPoint, you might see this menu pop up:

PasteSpecial2

If you do not see this menu, in Word just click in the graph that needs to be updated and go to this Refresh Data button in your menu bar:

PasteSpecial3

 

If you don’t see the menu in PowerPoint, right click on the graph and select Update Link:

PasteSpecial4

Either way, your report program is going to think for a minute. Be patient.

Step 5: Excel crashes. Yeah, at least on my computers while this thinking is happening I get these popup boxes that tell me Excel has crashed. I just click OK or whatever.

Step 6: Despite the crash, the graphs automatically update.

Step 7: Dear god, try to email this all to someone else. It won’t work straight away. This whole linking business is only local to your machine. The recipient will have to make it local to his or her machine too. This is how to do it.

Save both files onto the new local machine.

Open the report file (Word or Publisher).

Go to the File tab and look in the lower right for this area called Edit Links to Files

PasteSpecial5

Open this up. The new menu will look like this:

PasteSpecial6

It’s listing the file that holds every graph I paste-linked. See how the source files all start with C:\ ?? That’s my own local machine, people. So the recipient will have to change each one to the recipient’s machine. Just click Update Source and point the report file to the new location of the Excel file. And click the radio button by Automatic update.

It’s going to be hard to tell which chart belongs to which file. We don’t have any handy notes in here that identify anything as “the dot plot on page 4.” The list is in the order in which the graphs appear in the report but for these reasons it can be a lot easier to have all the report graphs in the same Excel file.

It’s going to take you a minute to get your report looking great. You’ll fiddle with the chart sizes and have to paste and repaste your graphs until it’s all set. But next year? YOU JUST HAVE TO UPDATE THE TABLE. Then you take the rest of the afternoon off and send me a quick tweet about how great that was. Smooches.