Linking spreadsheet data to documents and slides

Most reports start life in a spreadsheet. The figures are calculated there, checked there and, too often, copied out of there by hand. Linking replaces the copying: the document shows the value held in the sheet, and when the sheet changes, so does the document.

The idea is simple. Making it reliable is less so. Links break when rows move, when someone renames a tab or when a figure is formatted one way in the sheet and another way on the page. What follows is the groundwork that keeps a linked report trustworthy, whichever tools you use.

Build a reporting tab

The single most useful change is to stop linking to the working parts of a spreadsheet at all.

Create one tab whose only job is to hold the figures the report shows. Call it something plain, such as “Report”. Every number, table and chart in the document reads from this tab and nowhere else. The calculations, raw exports and scratch work stay on other tabs, free to change shape without affecting the report.

A reporting tab gives you three things. You can see in one place everything the document depends on. You can rebuild the calculations behind it without touching a single link. And anyone checking the report knows exactly where to look.

Lay it out for linking, not for reading

The reporting tab is not meant to be pretty. It is meant to be stable.

• One value per cell. “₹42.6 lakh (up 4%)” in a single cell can’t be linked cleanly. Put the value, the comparison and the change in separate cells.
• Fixed positions. Headline figures sit in the same cells every cycle. New data is added elsewhere and pulled through by formula.
• Tables with a fixed shape. A table of the top ten accounts should always be ten rows. If the number of rows varies, the document’s layout will too.
• A label beside every value. Anyone opening the tab should be able to tell what each cell is without tracing formulas.

Name what you link

Link to names, not cell addresses. A named range such as weekly_revenue or top_accounts_table survives rows being inserted above it. A link to a bare cell reference may quietly start pointing at the wrong number.

Agree a naming pattern and keep it: lower case, underscores, the period in the name where it matters. revenue_month, revenue_month_prior and revenue_month_change sort together and explain themselves.

Decide what to link

Not everything in a report needs a live connection. A useful sorting:

Element	Link it?	Why
Headline figures	Yes	They are what readers look at first and quote onwards
Comparisons and changes	Yes, calculated in the sheet	A typed “up 4%” goes stale the moment the figure is revised
Tables	Yes, as a fixed-shape range	Retyping a table is where transposed digits creep in
Charts	Yes, built from the sheet	A pasted chart image never updates
Figures inside sentences	Only if essential	Better to write the sentence so it doesn’t need the number
Headings and commentary	No	These are judgement, and should be written fresh

The row about sentences deserves attention. A number in prose is the easiest to forget and the most misleading when stale, because readers assume someone wrote it this week. The commentary should be phrased so it doesn’t depend on the exact figure.

Format at the source

Decide in the spreadsheet how each figure should appear: decimal places, units, lakh and crore or millions, currency symbol, percentage sign. Then let the document display it as it comes.

If the sheet holds 4261873 and the document is expected to show “₹42.6 lakh”, there is a conversion happening somewhere, and conversions done in two places drift apart. Do it once, in the reporting tab, ideally in a cell that holds the formatted value.

The same figures in slides

Reports often become presentations: the monthly pack becomes the board deck, the client report becomes the review meeting. The same reporting tab can feed both.

Link slide figures and charts to the same named ranges as the document. If a number appears in the report and on a slide, it should come from one cell. That is the only way to be sure the deck and the document agree. If you are building the deck from the workbook for the first time, turning a spreadsheet into report slides covers what to keep and what to leave behind.

Charts for slides usually need to be simpler than charts for a page: fewer series, larger labels. Build a separate, slimmer chart range on the reporting tab for slides, rather than squeezing the report chart. Choosing the right chart for a recurring report covers which chart shapes cope best with new data each period.

Check the links before you send

Linked reports fail quietly. A broken link may show an old value, a blank or an error code, and a busy preparer can miss all three. A short routine helps:

1. Confirm the “as at” date shown in the document matches the period.
2. Compare three or four headline figures against the sheet by eye.
3. Look for blanks or error codes in tables.
4. Open the document as a reader would, not in edit mode, and scan every page.

When someone renames a tab or a range, update the links the same day. A note at the top of the reporting tab, “Linked to the weekly report: don’t rename”, prevents most accidents.

The point of the exercise

Linking is not about saving the half hour of copying, though it does. It is about making the document and the data say the same thing every time. Live Documents is built on that principle, keeping figures tied to their source while the words stay yours.