Excel For Mac Carry A Formula Across Sheets

  • Excel Data Analysis Tutorial
  • Advanced Data Analysis

Dragging the AutoFill handle is the most common way to apply the same formula to an entire column or row in Excel. Firstly type the formula of =(A1.3+8)/5 in Cell C1, and then drag the AutoFill Handle down to the bottom in Column C, then the formula of =(A1.3+8)/5 is applied in the whole Column C. Insert a formula. You can create simple or complex arithmetic formulas to perform calculations on the values in your tables. Click the cell where you want the result to appear, then type the equal sign (=). The formula editor opens. Drag the left side of the formula editor to move it. To create a formula across multiple worksheets − Define names for the price values of the products in the product catalog worksheet. Set the Scope as Workbook for all the names. Once again select all the four worksheets – East, North, South and West. In the East worksheet, for each product, in the price column, give the formula as the Price.

  • Excel Data Analysis Resources
  • Selected Reading

In certain situations, you might have to

  • Set up workbooks in which several worksheets have a similar format or structure.
  • Get information for these worksheets from another worksheet.
  • Summarize the results from these worksheets into a summary worksheet.

Excel For Mac Carry A Formula Across Sheets Excel

For example, you might have to track sales information region wise and month wise in separate worksheets. The price of each product is taken from the product catalog that is set up across the company in a separate worksheet. Finally, you have to summarize the results across all the regions into a summary sheet.

In this chapter, you will learn how you can accomplish this in easy steps. You are going to summarize results from April 2015 to March 2016, i.e. financial year 2015-16.

The First Step

The first step is to set up a product catalog. Follow the steps given below −

  • Start with a blank workbook.
  • Set up product catalog worksheet with products and prices.
  • Name the worksheet Product Catalog.
  • Assume the catalog is revised on the first of every month.
  • Provide a place holder for last updated on.

The price of a product at selling time is determined by the current cost of the product.

Multiple Worksheets with same Structure

Next, you have to set up worksheets for the Regions – East, North, South, and West, in that order with same structure.

  • Add 4 blank worksheets.
  • Name the worksheets East, North, South and West.

These four worksheets should have the same structure.

  • Click the tab East. The East worksheet opens.
  • Press the shift key and click on the tab West. All the 4 tabs will be selected.

Now, whatever editing you do in the East worksheet will get automatically reflected in the other three selected worksheets.

In the East worksheet,

  • Add the column headers – S. No., Month, Product, Price, No. of Units, Total Amount.
  • Add the S. No., Month April and the 4 Product Names.
  • Format the Table.

The same structure appears in the other worksheets North, South and West.

Creating a Formula across Multiple Worksheets

To create a formula across multiple worksheets −

  • Define names for the price values of the products in the product catalog worksheet.
  • Set the Scope as Workbook for all the names.
  • Once again select all the four worksheets – East, North, South and West.
  • In the East worksheet, for each product, in the price column, give the formula as the Price Value Name.

As we defined earlier, the price of a product is as per the product catalog that is updated on the first of every month.

  • Repeat the same steps for each Month

Therefore, for the worksheets for the regions East, North, South, and West, you have successfully set the same structure and placed the price information for each product based on the month from product catalog worksheet.

The Product Catalog can be in another Workbook too.

Calculations in the Worksheets

The next step is to fill in the information of No. of Units sold for each Product in each Month and in each Region. Therefore, you need to work separately on these worksheets.

For each region, for each product −

  • Fill No. of Units sold.
  • Calculate the corresponding Total Amount as Price*No. of Units.

In each worksheet (East, North, South and West), calculate subtotals month-wise −

Note − You can use Subtotal on a single worksheet but not on multiple worksheets. Hence, you have to repeat this step for North, South and West worksheets.

Click the Outline Level 2. You will get all month-wise Totals.

Now, you are ready to summarize the results from all the four worksheets – East, North, South and West.

Summarizing Data in Multiple Worksheets

The following steps will show you how to summarize data from multiple worksheets.

  • Add a worksheet and name it Summary.
  • Create the structure for Summary worksheet.

In the column- Total Sales, in the cell C3, type =sum(

  • Select the worksheet East.
  • Select the cell G7.
  • With East tab pressed, click the tab West.
  • The tabs East to West get selected.
  • The formula in the formula bar appears as

=sum(‘East:West’!G7)

Note that you are still in the East worksheet. Press Enter.

You will be in the Summary worksheet. In the formula bar, you will see the formula as

=SUM(East:West!G7)

Excel formula for sheet title

The calculated value appears in the cell C3.

Excel For Mac Carry A Formula Across Sheets Sold

  • Copy the formula to the cells C4 to C14.
  • Click Show Formulas in the Formula Auditing group under the FORMULAS tab.

All the formulas in the column Total Sales appear.

This is exactly how you wanted to summarize the results from each region.

  • Click in the cell C15.
  • Type =sum(C3:C14)

Your summarized results are ready in the Summary worksheet.

When starting to use Excel, we use formulas to manipulate data from other cells in the worksheet. After a while, however, this is no longer enough, as we need to combine data from multiple sheets, or even from other Workbooks.

In this article, we’ll tackle this issue and see how we can access the contents of any worksheet.

Formulas for Linking Data – An Example

Imagine that your supervisor in the fictitious WYZ Widget Company needs you to calculate how much of the assigned budget was actually used (you can find the file here).

Let’s look at the budget sheet (it’s the first in the workbook):

Excel Formulas Across Tabs

And compare it to the Actual Expenses sheet (the next one in the file):

Well, comparing it by eye just won’t do. We need a new table in which the difference is computed.

We’ll start with an empty table; that is, a table with the same headings for the columns and rows, but no data (available as the third sheet in the file):

In each cell of this table, we want to have the difference between the allocated budget and the actual spending. To do that, we’ll create a formula that deducts the amount of the second sheet (Expenses) from the first sheet (Budget).

To do this , we do the following:

  • We select cell B5
  • Type in ‘=’ (to start off the formula)
  • Click on the Budget sheet tab to display the budget sheet; If you follow these steps you’ll note that the Formula Bar now contains =Budget! – this will be explained in a moment
  • We’ll now click cell B5. The Formula Bar should now display =Budget!B5
  • Type in – , which will be added to the Formula Bar: =Budget!B5-
  • Click on the Actual Expenses sheet’s tab; The sheet’s name will be added to the Formula Bar: =Budget!B5-‘Actual Expenses’!
  • Finally we’ll click on the B5 cell of this sheet and press the Enter key; the Difference sheet will be displayed again, and the B5 cell will display a zero amount

Here’s how it should look (check out the formula bar for the formula we created for cell B5):

Exclamation Points to Sheets

OK, what did we do here?

Sheets

Let’s look at the formula we’ve created and explain the parts.

The first part of the formula we got from the Budget sheet:

Budget!B5

When we use a reference to a cell on the current sheet, we just type in the name (or select it using the mouse); when we want to refer to a cell in a different sheet in the same file, we preface it with the name of the sheet and an exclamation mark.

The second part, which we deduct from the first, is:

‘Actual Expenses’!B5

So, now we have single quotes around the name. Why? Because the name includes a space. Any sheet name that contains a non-alphanumeric character needs to be quoted, so Excel won’t get confused.

Filling better

Now we can copy this formula to the rest of the cells in the table by using the fill handle.

Excel Formula For Sheet Title

Grab the fill handle and drag it down to cover the whole column. Excel is smart enough to replace the row numbers in each cell, so we get the difference between the appropriate entries in the two sheets:

You can also select the whole column and go on to fill the whole table by dragging the fill handle to the right up to the December column.

Excel For Mac Carry A Formula Across Sheets Printable

Summary

In this article, we learned how to reference data in other sheets, and that Excel’s fill handle is smart enough to work with those references. Will this technique be useful for you? Would you like to learn more about linking data between sheets? How about linking data between workbooks? Please share your thoughts in the comments…

About the Author: Joseph Reese is the founder of Excel-Formulas.com an Excel consulting firm. Head over to his website if you want to improve your performance with Excel.

Comments are closed.