Note that the Sheet! bit tells Excel that the cells are on Sheet2 and the A1:A10 bit tells Excel that it’s dealing with the range of cells A1 to A10.īy default, cell references in Excel are relative, however, we can use absolute references in Excel too. The formula would beĭont worry too much about how to create formulas, the important part is the Sheet2!A1:A10. Don’t forget the exclamation mark after the worksheet name.įor example, let’s suppose that we are working on Sheet1 and we want to sum the values of cells A1 to A10 on Sheet2. The reference to this cell is simply Sheet2!E12. We are creating a formula in Sheet1 that uses data in cell E12 on Sheet2. A relative cell reference is the most widely used cell reference in formulas in Excel. Suppose that we are using two worksheets in the same workbook: Sheet1 and Sheet2. Just what is the difference between an absolute and a relative cell reference in Excel. Let’s now look at references to cells on different worksheets. Peter Kalmstrom shares an exercise on how to manage formulas with relative cell references in Excel 2016 and shows in a demo how to do it. So far we have looked at cell references on the sme worksheet. The range goes from cell D2 to D4 so the range is denoted by D2:D4. What if we want to refer to a range of cells?

Data in cells on other worksheets in the same workbookĬ3 refers to the contents of just one cell.

Data that is contained in different areas of your worksheet.

The cell reference is therefore C3 and we can use C3 to refer to the data in that cell.

For example, the cell below occurs at the cross section of column C and Row 3. You can easily tell what the cell reference is by looking at the cross section of the row and column for that cell. Cell references are useful for Excel to identify data for use in formulas to calculate results based on your data. A cell reference refers to a particular cell or range of cells in your worksheet.

Relative cell reference excel 2016