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.
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.
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, February ‘13, 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 Jeff@Leadspringmedia.com.
If you liked these tips, check this one out.