4 between-the-sheets tips for Excel users

Do you manage, support, or train users who use Microsoft Excel?  If so, in this edition of Jeff’s Quick Tips, here are four quick tips you can share that are life-changing time-savers for spreadsheet users of all skill levels who need Excel help.

Tip #1: The syntax is Sheet, Exclamation Point, Cell Reference (e.g., Sheet2!A1)

Here’s how you remember how to reference a cell that’s in a different worksheet: It’s sheet (worksheet name), exclamation point (!), cell reference (column letter/row number combination).  To illustrate this syntax, open a new workbook in Excel and click on Sheet2. Type 10 and press [Enter] to put the value 10 in cell A1. Click on Sheet3, type 20 and press [Enter] to put the value 20 in cell A1.

Now click on Sheet1 and type =Sheet2!A1+Sheet3!A1 and press [Enter]. Did the value 30 appear in cell A1? Congratulations! You’ve figured out the syntax that you can use in all kinds of calculations “across the sheets” when you’re building a Dashboard Sheet or an Executive Summary Sheet.

AcrossSheet1
This formula calculates the sum of  the values in cell A1 in two worksheets, Sheet2 and Sheet3.

Tip #2: If the Sheet name contains a space, you must delimit that name with apostrophes (e.g., ‘July 2013’!A1)

To demonstrate this rule, use the same demonstration workbook you created for Tip #1.  Double-click on the worksheet tab Sheet2, then type July 2013 and press [Enter] to rename the worksheet from Sheet2 to July 2013.  Now click on Sheet1, cell A1, and you’ll notice that the result calculated in cell A1 is still the same, but the formula has been changed to =’July 2013′!A1+Sheet3!A1, as shown below.

AcrossSheet2

This change made by Excel automatically tells us that when our worksheet names contain spaces, we must — when we reference cells in those sheets from some other sheet — delimit that space-infested worksheet name with apostrophes.  It’s not a big deal. It’s just required punctuation, like dots in IP and email addresses.

Tip #3: Don’t use apostrophes in your worksheet names.

Say you keep a workbook with 12 different worksheets, one for each month of the year. Don’t name them January ’13, February13, and so on. Based on Tip #2, the fact that Excel itself uses apostrophes to delimit sheet names internally, spell out the year! Make them January 2013, February 2013, and so on.

Tip #4: Hard-code (type) your multi-sheet references instead of clicking

I love mouse shortcuts, but when it comes to building formulas and adding cell references using the mouse in Microsoft Excel, I recommend that you hard-code your references to cells in different sheets by typing them out rather than trying to click on the same cell in 12 different worksheets.  Maybe it’s me, but the formula bar seems to get a little whacky when I try to build a simple =SUM function by clicking on different sheets. I find it’s much easier to just key the worksheet names, even if they contain spaces.

To illustrate this tip, suppose you have a workbook with 13 worksheets. The first worksheet is named “Summary” and the other 12 are named January 2013 through December 2013. Each of the monthly sheets are identical except for the user-provided input.

There’s a critical number in cell B14 of each monthly sheet.  How do you write a formula in the Summary worksheet that sums the values in cell B14 of each of the monthly sheets? Type

=SUM(‘January 2013′!B14+’February 2013′!B14+’March 2013′!B14+’April 2013′!B14+’May 2013′!B14+’June 2013′!B14+’July 2013′!B14+’August 2013′!B14+’September 2013′!B14+’October 2013′!B14+’November 2013′!B14+’December 2013’!B14)

It looks like a lot of typing, and it is. But once you get a full set of 12 sheet references entered, you can copy and paste and edit the formula in other cells in your Summary sheet.

Tell us what you think

Did you like these tips? Share your feedback by posting a comment below or send an email to [email protected]

If you liked these tips, check this one out.

1 reply
  1. Jeff Davis
    Jeff Davis says:

    I heard from an IT manager who said that she liked these tips, but she doesn’t think that it’s I.T.’s job to support Microsoft Office apps. She thinks it’s the Training Department’s job to get employees the training they need to be efficient Excel users. My first thought is that many small- and medium-size companies don’t have in-house training departments. So if it isn’t I.T.’s job to support the Office apps, whose job is it? We hire people every day who nod their heads “Yes, of course I know how to use the Office apps” but if we don’t skill-test them before hiring them, how do we know if they really can do the job?

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply