Excel Tips for Building Workbooks FAST

I often create Excel workbooks that contain a series of sheets for each region (or product, month, or sales person) with a summary sheet at the front that gives me a total of all the sheets.

But building workbooks like this can be onerous so I’d like to share with you a few tips for creating them quickly.

Below I am creating a budget workbook for four regions – AP, UK, Europe & US.

There’s a tab for each region and one for the summary named Global.

The formulas on the Global sheet SUM each tab in the workbook. The problem is there are lots of cells I want added up.

Taking cell C5 you could insert a SUM like this:

=SUM(AP!C5,UK!C5,Europe!C5,US!C5)

Or you could do it the quick way:

1. In cell C5 on your Global sheet start your SUM by entering =SUM(
2. Click on the AP sheet and select cell C5 – the formula will now read =SUM(AP!C5  don’t press ENTER yet.
3. Hold down SHIFT and click on the US sheet (the last sheet in your workbook) – the formula will now read =SUM(‘AP:US’!C5)
4. Press ENTER

You’ll now see the SUM of cell C5 for all of your sheets between AP and US.

You can then copy and paste formulas to the remainder of your cells on your summary sheet. Note: use Paste Special > Formulas to avoid copying formatting you don't want or need.

IMPORTANT:

• This formula sums all sheets in between AP and US so it’s important that there are no sheets in between these that you don’t want included.
• All sheets must be the same. i.e. the location of the values you want added up must be in the same cell on the AP, UK, Europe and US sheets. However it doesn’t matter where you put your SUM on the summary sheet, it just so happens that my summary sheet is laid out the same, but yours doesn’t have to be.

My budget has totals for the rows (the expense types) and columns (the months). I can quickly insert these totals by highlighting all of the cells that have values I want added up, plus an extra row and column for Excel to insert the totals.

In the screen shot below you can see I’ve selected C5:O9.

To insert the totals I simply press ALT+= or the AutoSum icon in the Formulas tab of the Ribbon:

My totals will be inserted in row 9 and column O like this:

TIP: You can use ALT+= to enter any SUM. Simply use it instead of using the AutoSum button on the ribbon.

When you’re setting up your workbook and messing around with the layout and formatting it’s easiest to work with one master sheet. Then when you’re happy with it copy it as many times as you need.

1.       To copy a worksheet right click on the sheet tab

2.       Select ‘Move or Copy…’

3. In the Move or Copy dialog box tick ‘Create a copy’ and select the sheet you want it placed before from the list, then click OK.

 
TIP 1: Once you have more than one sheet in your workbook you can copy multiple sheets to speed up the process.

Select the first sheet and then hold down SHIFT while you select the last (or as many as you need) before right clicking on the sheet tab and selecting Move or Copy… as per the steps above.

TIP 2: If you intend to print your worksheets set your print area and preferences before you copy your worksheets otherwise you’ll have to do each one individually.

When worksheets are grouped every action you perform on one sheet is applied to all sheets that in the group. That is; all formatting, all data entered, and all formulas entered are applied/entered on all sheets in the group.

To group worksheets:

1. Click on the sheet tab of the first sheet you want in the group
2. Then hold down SHIFT while you click on the sheet tab of the last sheet in the group.

TIP 1: To group non-contiguous worksheets select the first sheet then hold down CTRL while you click on the sheet tabs of the other sheets you want grouped.

Grouped sheets are identified by the slightly lighter colour of the sheet tab.  As you can see in the image below I have groped a non-contiguous range of sheets being; Global, UK and US.

TIP2: Be careful to remember you have grouped worksheets as it’s easy to forget and carry on working, then before you know it you’ve entered data on sheets you didn’t intend to.

To ungroup sheets either right click the sheet tab (any one in the group) and select ‘Ungroup’, or just click on another sheet that either isn’t in the group, or if they’re all in the group select any one as long as it isn’t the first one you selected.  Or just click randomly on the sheet tabs until they aren’t grouped anymore 🙂 This usually works for me.

For more on copying and grouping worksheets see Session 4 of our Excel training.

Not a member? Sign up for our free Microsoft Office training.