Skip to main content

33  Workbooks for sharing

Workbooks in Excel serve multiple purposes and multiple audiences. They might be just a set of numbers for you – I have an Excel file with grades of every student in the class – this isn’t to share with anyone else, it’s just for me. Or they might be something for you to share with a colleague who is knowledgeable about the data. Or they could be to share with an external audience.

In previous modules, we have focused on the data and the analysis. In this module, we will focus on the workbook itself – how to make it readable and understandable for someone else.

33.1 Start With the Audience

When thinking about the structure of the workbook, you should always consider who will be reading it. Here are some viable formats for the three audiences that I mentioned above:

  • Only you: A temporary scratch file can use short labels and minimal formatting. Although there is a temptation to think that you don’t need to write in any structure for yourself – keep in mind that the you who will read this file in a month or a year is not the same as the you who wrote it (be kind to yourself).
  • Colleagues: Use clear sheet names, a short README, consistent labels and enough formatting to make the workflow understandable without an accompanying email. You can spend less time on the aesthetics of the final output than you would for an external audience, because your colleagues understand the data and the analysis. Locking the cells that hold formulas is still worth doing – it is protection against an accidental edit, not a comment on who you are sharing with.
  • Clients, regulators or the public: When presenting to an external audience you want a polished interface: borders around tables, easy navigation around the worksheet, and perhaps locking certain cells. You would also want to identify your sources. Polish and aesthetics are not just about making it look pretty – they are about making it readable and understandable.

33.2 Three examples of workbooks for external audiences

Let’s look at three examples of well-formatted workbooks. The workbooks look different because their readers need different things. One is for readers to take in information at a glance, another is for readers to input their own data and assumptions, and the third is for readers to use as a source of data for their own analysis.

Elevator capacity in Manitoba

This worksheet provides data on the capacity of grain elevators in Manitoba. The purpose of this worksheet is to provide a clear and easy-to-read summary of the data. It is not intended for analysis, but rather for presentation. The worksheet is well-formatted, with clear headings and labels, and it is easy to navigate.

Let’s note some of the design features here:

  • A header with an image and a link to the source.
  • Vertical lines in the table.
  • Bolding of column names
  • Clearly labelled worksheets

Download this workbook · Open online (File → Save a Copy to view formulas and edit)

Source: Manitoba Agriculture, Foresight and Analysis – Markets and Statistics.

Crop planning guide

The Saskatchewan Crop Planning Guide is a decision tool. Producers can enter their acres, expected yields, costs and prices; the workbook turns those assumptions into crop budgets. It opens with a disclaimer, lists assumptions separately and repeats the same layout across crop sheets. Its formatting identifies input areas, but cells are not locked.

Again, let’s note some design features:

  • Clear worksheet labels
  • Good bordering and shading to identify categories
  • A “My Farm” column for you to enter your own farm’s data

Download this workbook · Open online (File → Save a Copy to view formulas and edit)

Source: Saskatchewan Ministry of Agriculture – Crop Planning Guide and Crop Planner (2026 edition).

RM data

The RM data we have been working with is meant to be read straight into R and modelled, so it is laid out for a machine rather than a reader. There is one header row and nothing else – no title block, no shading, no merged cells. This makes it easy to directly read the data into R or another statistical program.

Download this workbook · Open online (File → Save a Copy to view formulas and edit)

Source: Government of Saskatchewan – Saskatchewan’s Dashboard: RM Yields. Extract filtered to 2024–2025.

33.3 Reading your own workbook as a stranger

The author knows why a sheet exists, which cells are safe to edit and where a number came from. The reader does not.

Before handing over a workbook, ask:

  • Who will open it, and what do they already know?
  • What decision are they trying to make?
  • Will they read, enter values, refresh data or carry out a new analysis?
  • How much time will they spend finding the answer?
  • What mistake are they most likely to make?

Then open the file as though it arrived without an explanation. Start on the first sheet and see whether the next action is obvious.

33.4 Formatting a workbook

We covered the basics of cell formatting back in Section 1.5. This section is about the things that matter once somebody else is going to open the file.

The workbook at the end of this chapter puts all of it together, so it is worth opening alongside this section.

Appearance

  • Use one visual system. Give headers, inputs, formulas and warnings consistent styles across sheets.
  • Freeze the header row. Long tables should keep their column names on screen while you scroll. Select the row below the headers and choose View > Freeze Panes.
  • Use number formats deliberately. Show dollars, percentages, dates and decimal places appropriate to the measurement.

Structure and worksheets

  • Begin with a README. State the purpose, author, update date, sources and contents.
  • Use descriptive sheet names. Summary and Raw_yields are more useful than Sheet1 and Sheet2.
  • Keep raw data unchanged. Put corrections and calculations elsewhere so every result can be traced back.
  • Label every input and output. Include units in headers and chart axes.
  • Document assumptions. Put rates, prices and other inputs in labelled cells rather than burying them inside formulas. The Saskatchewan Crop Planning Guide above is a good example of this type of documentation

Use borders and shading judiciously

Borders and shading should tell the reader something. A heavy rule under a header row says “the data starts here”. Light banding across alternate rows helps the eye travel along a row without losing its place. A fill colour on input cells says “you may type here”.

What they should not do is decorate. A border around every cell turns the sheet into a grid of boxes and makes the real structure harder to see, and a different fill on every column is noise. Pick two or three uses, apply them consistently, and leave everything else plain.

Locking Cells

You may want to lock your worksheet so that other users cannot accidentally change formulas, data, or other important information.

In some worksheets, you may want to allow users to change certain cells, such as input cells, while locking other cells. This is particularly useful if you are sharing a workbook with an external audience. The following instructions are for Microsoft Excel, but other spreadsheet software will have similar functionality.

To lock a worksheet, you can select the Review ribbon and click Protext Worksheet or right click on the worksheet tab and select Protect Sheet.

If you want certain cells not to be locked yo ucan format those cells to be unlocaked. To do this, in the Format cells dialog box, select the Protection tab and uncheck the Locked checkbox. Then protect the worksheet as described above. This will allow users to edit the unlocked cells while preventing them from changing the locked cells.

An example of a workbook for an external audience

The following workbook contains:

  • A table of contents with hyperlinks
  • A clear visual style (with light and dark green borders)
  • Data and analysis separated into different worksheets
  • All worksheets are protected except for the input cells in the Expected profit calculator.

Download this workbook · Open online (File → Save a Copy to view formulas and edit)