Dr. Winston's Excel Tip: 3 Tricks to Help You Work Across Microsoft Excel Worksheets

Excel has three dimensions: rows, columns and worksheets. Most of us are familiar with how to use the powerful and indispensable Copy command to “copy” a formula across columns and down rows. In this article, we explore three tricks that can help you work across worksheets.

Copying a Worksheet Setup Across Worksheets

The workbook ‘Threedimmonthstemp.xlsx‘ contains a blank worksheet for the months January-June and a blank summary worksheet. Suppose we want each of our monthly worksheets to track Units Sold, Price, and Revenue in the cell range B1:B3.

To accomplish this goal proceed as follows:

1. Click on the January worksheet tab and hold down the Shift key and click on the June tab.
2. Fill in cells A1:A3 of the January worksheet as shown in Figure 1. Also, enter numbers in cells B1 and B2 and in B3 enter the formula ‘=B1*B2’.
3. After clicking on the Summary worksheet (this will undo the selection of the January-June worksheets) you will find that each month’s worksheet looks like Figure 1.

Writing Three Dimensional Formulas That Work Across Worksheets

Once you have set up your monthly worksheets in identical fashion, you might want to create a Summary worksheet that adds up revenue from each month.

Here are the steps to accomplish this:

1. Put your cursor anywhere – we chose cell E5 in the Summary worksheet and begin typing ‘=SUM(‘.
2. Go to the January worksheet and click on cell B3.
3. Hold down the Shift key and click on the June worksheet.
4. Type a right parenthesis to complete the formula. You will see the formula ‘=SUM(Jan:June!B3)’.

This formula will sum up the contents of cell B3 in the worksheets contained between January and June (including January and June.) We get a total of ‘6*50 = 300’. Of course, if you add a new worksheet between January and June, the contents of cell B3 in the new worksheet will be included in the total.

Three Dimensional Formulas with Wildcards

The workbook ‘Threedwildcard.xlsx‘ contains 6 worksheets that contain either the text string rev (for revenue) or cost (for cost). Each worksheet includes the cost or revenue for the given month in cell C4. If we want to total all revenues and costs in a summary worksheet, we’d complete the following:

1. In cell E8 of the Summary worksheet, enter the formula ‘=sum(‘rev*’!C4)’. This formula adds the contents of cell C4 in each worksheet (yielding 160) containing the text string rev (not case sensitive). Note that the formula shows up as ‘=SUM(revjan!C4,revfeb!C4,revmarch!C4)’ because these are the cells summed by the formula.
2. In cell G8 of the summary worksheet, enter the formula ‘=sum(‘cost*’!C4). This formula adds the contents of cell C4 in each worksheet containing the text string cost, yielding a total of 109. Note that the formula shows up as ‘=SUM(costjan!C4,costfeb!C4,costmarch!C4)’ because these are the cells summed by the formula.

Many accounting and finance professionals encounter workbooks with many worksheets. For example, you might work with a 12-worksheet workbook containing revenue during each month of the year, or a 50-worksheet workbook with each worksheet containing revenue in each state. Hopefully, these 3 tricks will make it easier for you to detail with those troublesome multiple worksheet workbooks!

Ready to learn more about Microsoft Excel? Join one of our Microsoft Excel for Accounting Professionals webcasts, hosted by the AICPA, and earn 2 CPE credits.