As part of my training process I do what’s known as floor walking. This is when I walk about the company I have done some training at so that anyone can stop me and ask for help. I am often saddened when I meet someone who is working far too hard when there is an easy way of doing something.
One thing I find a lot is people need to create the exact same information on several sheets and they normally start with creating the first sheet then copy and pasting into as many sheets as they require. There is of course nothing wrong with this. It’s just long winded if you know that you need several sheets all with the same general information, headings and lists of personnel and the like.
I am all for working quicker and smarter and if there is an easy way to do something I’m all in. I’d like to introduce you to Grouping of sheets. If you know how to use Grouping to create your own outlines this is in a way the same thing but you group whole sheets together. When you do something in one it is done in the exact same place at the exact same time in all the grouped sheets.
Here is a scenario we will work through.
We are a used car sales company. We have a few garages around the country. We need to create a spreadsheet to calculate just how much money is coming in. ( I’m keeping this very simple.)
We have garages in Reading, Worthing and Truro. We tend to sell the same makes in each.
Here is the start to the spreadsheet for the first quarter.
This will be the same on all four sheets. Rather than creating this once and then copy and pasting into the other three sheets we will create this in one go.
- Begin with saving this file and naming it appropriately.
- Then create 4 sheets and name them Reading, Worthing, Truro and Summary respectively.
- Hold down your SHIFT key and select the first sheet “Reading”.
You will notice all the sheets are coloured white, indicating they are active.
Read the name of your file at the top of the screen in the centre. There you will see the word [Group] indicating that theses active sheets are grouped together.
Now whatever you type in this sheet will appear in exactly the same place on all the others that are grouped.
I have created three new windows by selecting the View tab and selecting the New window tool. Then I have used Arrange all in a tiled format. This is just to show you that all sheets are the same, not that you need to do this. (Though you might like this view to create the summary calculations in a few moments.)
- Type in the sales figures for each sheet.
How to use a linking formula
- Select the Summary Sheet.
- Select cell B2.
- Select Autosum.
- Select the first sheet “Reading”.
- Select the cell B2.
- Hold sown the SHIFT key.
- Select the last sheet “Truro”.
- Press enter.
This will create the formula and return you to the Summary sheet.
The formula reads as follows.
=sum(‘Reading:Truro’!B2)
- As this is a relative referencing formula and all the sheets are identically laid out you can autofill down the sheet then across to get all the results.
Any formatting that needs to be done I suggest you group your sheets so that it happens in all four sheets simultaneously. If there is something you need to create in exactly the same place on several sheets that you have created previously you can use the group feature. Even if they’re not laid out the same but there is room to do what you need in exactly the same cell on each you can group the sheets to be able to do just that.
