A Simple Trick for Referencing Data Between Worksheets In Microsoft Excel

pastelinkI don’t work in Microsoft Excel often, and when I do, the documents tend to be pretty simple. Most of my work with Excel involves collating tabulated data and then publishing it a a Web-compatible format (the HTML <table> still has its uses).  I’ve never had any actual training in Excel (or any other spreadsheet software), but I’ve picked up enough to do the job at hand, and every now and again, I pick up a trick that makes working with data across Excel worksheets much easier.

One of the most tedious activities I’ve faced when working with Excel is to reference data between worksheets. I usually do this manually typing out the reference in the cell.  A typical cross-worksheet reference might look like this:

=’Another Worksheet’!$E$29

As you might have guessed from reading other posts in this blog, I have a tendency to commit typographical errors about every other sentence, so manually typing out a reference is a tricky proposition.

Fortunately, I picked up a new trick (and several others) for copying and pasting data references between sheets.

Rather than typing out the reference by hand, you can speed up your cross-worksheet functionality by using the following tip:

  1. Go to the sheet containing the data you want to carry over to the new sheet.
  2. Highlight the cell containing your target data.
  3. Press CTRL+C (or right click and select Copy).
  4. Go back to the sheet where you want a copy of the data to appear.
  5. Select the cell where you want to “paste” the data.
  6. Press ALT+E to bring up a floating dialogue that titled: “Office 20xx access key: ALT, E”.
  7. Press S to bring up the Paste Special menu.
  8. Press L to paste the link into the the cell.

The same task can also be accomplished (after coping the target to the clipboard) by highlighting the cell where you want referenced data to appear, clicking Edit, clicking Paste Special, and then clicking the Paste Link button.

I think the keyboard “hotkey” method is faster, but both methods are much faster than typing out the reference manually.

If you want to create references to a bulk of cells instead of copy-pasting all the values this is the option for you. Just use ALT+E S L to create an automatic reference to copied range of cells.