Free Intermediate Excel Training Online Lesson Three: Inter-sheet-formulas

2KO Africa welcomes you back to our free Intermediate Excel training online. We are based in Cape Town, and have been offering classes from beginner to advanced level for 20 years. Our style of training is varied, and students can learn online, in class with an instructor, blended learning or we can do the training at your premises for groups.

 

Lesson 3

Inter Sheet Formulas in Excel

When you have a scenario where you have multiple sheets and you require formulas that reference data in different sheets, the process to follow is explained in the lesson below. In essence, not much changes from formulas using cells in the same sheet.

 

Consider the image below. We are required to pull across some of the data in the Africa sheet.

 


Simple Link from One Sheet to Another?

We have previously looked at a simple link formula. The formula to link cells between sheets is straightforward.

 

Steps:

 

1) Select the cell where the answer is to appear

2) Press = on keyboard

3) Select the cell to link to

4) Press ENTER key

 

 

In the image above, one needs to insert Cape Town's Net into cell A2. If one uses the steps above the result will be:

 

 

The formula in cell A2 would read as follows:

 

=SA!B4

 

Let's expand that formula though. Assume that you need a total (sum) of the three Net Profit/Loss for each region, and the answer is to be placed into A4. The formula would proceed as follows:

 

Steps:

 

1) Select the cell where the answer is to appear i.e A4

2) Type =sum( on keyboard

3) Select the cells to be summed (namely B4:D4 on the SA sheet

4) Press ENTER key

 

The resulting formula appears in cell A4 of the Africa sheet, and the formula reads as follows:

 

=SUM(SA!B4:D4)

 

The reason the SA! precedes the cell references, is because the cells are on a different sheet to the formula.

 

 

 

Practice on your own

Click here to download the practice file for this exercise called cities-2.xlsx:

 

  1. Got to the sheet called Consolidate

  2. Create a formula in B3 that calculates the Profit or Loss for Cape Town

  3. Repeat for Joburg and Durban in the two cells that are below that

  4. In cell B7 insert a formula that calculates average income for the three regions

  5. In cell B8 insert a formula that calculates average expenses for the three regions

  6. In cell C3 insert a formula that calculates the income for Cape Town in US dollars, at the rate supplied in C1

  7. In cell D3 insert a formula that calculates the income for Cape Town in British pounds, at the rate supplied in D1

  8. Repeat for Joburg and Durban or copy the formulas where possible

 

 

To start lesson 4, click here

To go back to the index of free online excel lessons from 2KO Africa in Cape Town, click here