In Excel, you can use formulas to perform calculations or manipulate data from different worksheets or workbooks. One of the common tasks is to reference a cell or a range of cells from another sheet and get its value or values. This can be useful when you want to consolidate data from multiple sources, compare data from different periods, or create summary reports.
There are different ways to reference a cell or a range of cells from another sheet in Excel formula, depending on your needs and preferences. In this article, we will explain the basic theory behind cell referencing, the procedures to follow, and some examples to illustrate the concepts. We will also show you some alternative approaches that you can use to achieve the same results.
A cell reference is a way to identify a cell or a range of cells in a worksheet. It consists of the column letter and the row number, such as A1 or B2. A cell reference can be either relative or absolute. A relative cell reference changes when you copy or move a formula to another cell, while an absolute cell reference remains the same. You can make a cell reference absolute by adding a dollar sign ($) before the column letter and/or the row number, such as $A$1 or $B$2.
To reference a cell or a range of cells from another sheet, you need to include the sheet name followed by an exclamation point (!) before the cell reference, such as Sheet1!A1 or Sheet2!B2. If the sheet name contains spaces or special characters, you need to enclose it in single quotes (‘), such as ‘Sheet 1’!A1 or ‘Sheet 2’!B2.
You can also reference a cell or a range of cells from another workbook by including the workbook name followed by the sheet name and the cell reference, such as [Book1.xlsx]Sheet1!A1 or [Book2.xlsx]Sheet2!B2. If the workbook name contains spaces or special characters, you need to enclose it in square brackets ([]), such as [Book 1.xlsx]Sheet1!A1 or [Book 2.xlsx]Sheet2!B2.
Procedures
To reference a cell or a range of cells from another sheet in Excel formula, you can follow these steps:
- Start by typing an equal sign (=) in the cell where you want to enter the formula.
- Switch to the sheet that contains the cell or the range of cells that you want to reference. You can do this by clicking on the sheet tab at the bottom of the workbook, or by using the keyboard shortcut Ctrl+Page Up or Ctrl+Page Down.
- Select the cell or the range of cells that you want to reference. You will see the sheet name and the cell reference appear in the formula bar.
- Press Enter to complete the formula and return to the original sheet. You will see the value or values from the referenced cell or range of cells in the formula cell.
Alternatively, you can type the sheet name and the cell reference manually in the formula, without switching to the other sheet. However, this may be prone to errors, especially if the sheet name or the cell reference is complex or long.
Example
To illustrate how to reference a cell or a range of cells from another sheet in Excel formula, let us consider the following scenario:
You have two worksheets in a workbook, named Sales 2020 and Sales 2021, that contain the sales data of a company for the year 2020 and 2021, respectively. The data is organized in a table with four columns: Month, Product, Quantity, and Revenue. You want to create a summary report in a new worksheet, named Summary, that shows the total revenue for each product for both years.
Here is how you can do it:
- In the Summary worksheet, enter the product names in column A, starting from cell A2. You can copy them from the Sales 2020 or Sales 2021 worksheet, or type them manually.
- In cell B1, enter the label “2020” to indicate the year.
- In cell B2, enter the formula =SUMIF (Sales_2020!B:B,A2,Sales_2020!D:D) to calculate the total revenue for the product in cell A2 for the year 2020. This formula uses the SUMIF function to sum the values in column D of the Sales 2020 worksheet, where the values in column B match the value in cell A2 of the Summary worksheet.
- Copy the formula in cell B2 and paste it in the cells below, until cell B6, to calculate the total revenue for the other products for the year 2020.
- In cell C1, enter the label “2021” to indicate the year.
- In cell C2, enter the formula =SUMIF (Sales_2021!B:B,A2,Sales_2021!D:D) to calculate the total revenue for the product in cell A2 for the year 2021. This formula uses the SUMIF function to sum the values in column D of the Sales 2021 worksheet, where the values in column B match the value in cell A2 of the Summary worksheet.
- Copy the formula in cell C2 and paste it in the cells below, until cell C6, to calculate the total revenue for the other products for the year 2021.
The result should look like this:
| Product | 2020 | 2021 |
|---|---|---|
| A | 1200 | 1500 |
| B | 800 | 900 |
| C | 600 | 700 |
| D | 400 | 500 |
| E | 200 | 300 |
Alternative Approaches
Besides the method described above, there are some other ways to reference a cell or a range of cells from another sheet in Excel formula. Here are some of them:
- You can use the INDIRECT function to create a cell reference from a text string. For example, the formula =INDIRECT (“Sales_2020!D2”) returns the value in cell D2 of the Sales 2020 worksheet. This can be useful when you want to create a dynamic cell reference that changes based on a condition or a variable.
- You can use the VLOOKUP or HLOOKUP function to look up a value in a table and return a corresponding value from another column or row. For example, the formula =VLOOKUP (A2,Sales_2020!B:D,3,FALSE) returns the revenue for the product in cell A2 of the Summary worksheet, by looking up the value in column B of the Sales 2020 worksheet and returning the value in column D. This can be useful when you want to perform a lookup operation across different worksheets.
- You can use the SUMPRODUCT function to multiply and sum the values in two or more arrays. For example, the formula =SUMPRODUCT ((Sales_2020!B:B=A2)*(Sales_2020!D:D)) returns the same result as the SUMIF formula in cell B2 of the Summary worksheet, by multiplying and summing the values in column D of the Sales 2020 worksheet, where the values in column B match the value in cell A2 of the Summary worksheet. This can be useful when you want to perform a conditional calculation across different worksheets.
