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.
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.
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:
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:
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:
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:
Got to the sheet called Consolidate
Create a formula in B3 that calculates the Profit or Loss for Cape Town
Repeat for Joburg and Durban in the two cells that are below that
In cell B7 insert a formula that calculates average income for the three regions
In cell B8 insert a formula that calculates average expenses for the three regions
In cell C3 insert a formula that calculates the income for Cape Town in US dollars, at the rate supplied in C1
In cell D3 insert a formula that calculates the income for Cape Town in British pounds, at the rate supplied in D1
Repeat for Joburg and Durban or copy the formulas where possible
To start lesson 4, click hereTo go back to the index of free online excel lessons from 2KO Africa in Cape Town, click here