Give Instruction with Summary Sheets

Locked lesson.

Upgrade

Quick reference

Give Instructions with Summary Sheets

Help others understand your workbook by adding a helpful summary sheet. 

When to use

All workbooks of more than one worksheet should have a summary sheet to help users understand what to do with the worksheet and which parts should be changed/updated. 

Instructions

Summary sheets are a great way of helping others understand our workbook. Summary sheets can be used to display helpful information and give instructions as well as let others know what cells should be changed. 

A good summary sheet will contain some basic information about the purpose of the workbook, clickable hyperlinks to assist with navigation, a key or legend to explain the formatting, instruction as to what should be changed, and save location information. This is just a guide, we can add anything that will be useful to our summary sheet. 

Add Hyperlinks

It's important, particularly in workbooks that contain many worksheets, to provide an easy way for users to navigate (jump to) specific worksheets from the summary sheet.

We can do this by adding hyperlinks to worksheets contained within the workbook. 

  • On the Summary sheet, add the text e.g. 'Source Data'. 
  • Select the cell. 
  • Right-click and select Link. 
  • Choose Place in this Document. 
  • Select the worksheet to link to. 
  • Click OK. 

Hints & tips

  • Use the keyboard shortcut CTRL+K to quickly add a hyperlink. 
Login to download
  • 00:04 Summary Sheets are a really great way of giving instruction to anybody
  • 00:09 who might be using your workbook as to how that workbook is to be used.
  • 00:14 We can use Summary Sheets to provide guidance,
  • 00:17 to let other people know which cells can and can't be edited.
  • 00:21 We can also use summary sheets to provide useful links,
  • 00:24 maybe to different worksheets within the workbook, or
  • 00:27 maybe give them information about version control and how this file should be saved.
  • 00:31 So let's take a look at an example of what I mean.
  • 00:34 So I have the course file open for this particular lesson.
  • 00:38 And once again, you can find this in the Course Files folder if you'd like
  • 00:41 to follow along with me.
  • 00:42 And this is a workbook that contains a few different worksheets.
  • 00:46 Now notice at the bottom, I'm currently clicked on
  • 00:49 this Summary Worksheet which currently doesn't really have anything on it.
  • 00:53 If I click on the next tab, this is where I have my source data.
  • 00:57 So maybe I'm a business analyst for Marco's Pizza Place, and
  • 01:02 it's my job to gain some insight into Marco's Pizza Place's sales figures.
  • 01:09 And you can see here that I have quite a lot of data that I'm working with, but
  • 01:13 I've got it all stored on its own source data worksheet.
  • 01:15 I've then gone in and I've created some pivot tables to further analyze this data.
  • 01:21 And then based of those pivot tables,
  • 01:24 I've created three pivot charts to visually illustrate some key metrics.
  • 01:29 And then combine them all a really
  • 01:32 nice looking Marco's Pizza Place Sales Dashboard.
  • 01:36 And we got some slides up here which enables me to filters this data
  • 01:41 really easily.
  • 01:42 Now, I'm pretty proud of this worksheet.
  • 01:44 But one of my biggest concerns is that,
  • 01:47 when I upload this to the team's shared folder, that they're going to go in and
  • 01:52 break something and completely mess up all of my hard work.
  • 01:55 So one of the things I can do to help prevent that is to add a summary sheet
  • 02:00 that contains instruction as to how this worksheet is to be used.
  • 02:04 So I'm going to jump across to the completed file for this lesson so
  • 02:07 that you can see the types of information that I've added to this summary sheet.
  • 02:12 So this is exactly the same file, I still have the same worksheets at the bottom,
  • 02:16 but I've added some additional information onto the summary sheet.
  • 02:20 Now haven't added a great deal,
  • 02:21 this is really just an example to show you the types of information that you can add.
  • 02:25 So I've got some general instructions at the top here.
  • 02:29 This spreadsheet is to be used to keep track of pizza sales each month.
  • 02:33 And then I've given more instruction to anybody who's looking at this file.
  • 02:38 Add each month's data to the spreadsheet and then go in and
  • 02:42 refresh all charts and tables.
  • 02:44 So I'm giving them a very clear path as to how to use this spreadsheet.
  • 02:49 I'm telling them,
  • 02:50 add your latest figures to the bottom of the source data spreadsheet.
  • 02:54 And I could make that even clearer by adding a direct link to that spreadsheet
  • 02:59 in here.
  • 02:59 I'm then telling them that they need to go in and they need to refresh all of
  • 03:04 the pivot tables, so that they update with that new data and
  • 03:08 all of the pivot charts so that feeds through correctly to the sales dashboard.
  • 03:12 So very clear instruction there as to what people should be doing with this workbook.
  • 03:17 I then have underneath some helpful links which will jump people to
  • 03:21 the different worksheets.
  • 03:23 So maybe they're only interested in adding data to the source data worksheet.
  • 03:28 Now, I only have a few tabs in here, but
  • 03:31 some workbooks can have quite a lot of tabs.
  • 03:33 I'm scrolling backwards and forwards trying to find the tab that you're looking
  • 03:37 for can be quite time consuming.
  • 03:39 So I've been helpful here, and I've added in some hyperlinks.
  • 03:43 So if they want to go directly to that source data worksheet,
  • 03:46 they can simply click on it and it jumps them to this worksheet.
  • 03:49 So let me quickly show you how to add in these links if you're not sure.
  • 03:54 So I'm quickly going to remove the hyperlink from this
  • 03:59 first item here, source data.
  • 04:02 If I want to add a link to another worksheet within the same workbook,
  • 04:06 I can simply right-click, and click on link.
  • 04:09 And it's going to open up the insert hyperlink dialog box.
  • 04:14 Now, I want to link to another place in this document.
  • 04:17 And you'll see as soon as I select that option,
  • 04:20 I then get- To choose which worksheet I want to link to.
  • 04:24 So I want to link to the Source Data worksheet.
  • 04:27 I can even select the cell that I want to link to in that worksheet.
  • 04:31 So if I need them to jump straight to a specific cell on that worksheet,
  • 04:34 I can do that by clicking on the link in the worksheet, I could type that up here.
  • 04:37 Now for this, I'm just going to leave it on cell A1.
  • 04:40 We're going to select Source Data.
  • 04:42 Click on OK, and it adds that link in.
  • 04:44 So when they click on it, it's going to jump them to the relevant worksheet.
  • 04:47 And notice that my cursors focus is pull to cell A1.
  • 04:53 So really simple to add hyperlinks in.
  • 04:56 Now underneath that I've also provided some save information, so
  • 05:00 I've told them exactly where they need to be saving this file, which folder.
  • 05:05 And I've also added when this file was last updated.
  • 05:09 Now of course, there is a lot more instruction that you could add
  • 05:13 to this summary sheet, but this is a great example of a good start.
  • 05:17 We are going to add some more to this Summary Sheet as we go through the rest
  • 05:20 of this section.
  • 05:21 But hopefully, that gives you more of an idea as to why we add Summary Sheets,
  • 05:27 and why they can be really useful when we are sharing workbooks and
  • 05:33 worksheets with other team members.

Lesson notes are only available for subscribers.

Gift this course