Cell references in Google Sheets - A comprehensive guide

In this tutorial, you will learn how cell references work in Google Sheets, specifically the differences between absolute and relative cell references. This knowledge is essential for effectively using formulas and aggregation functions. In simple terms, you will understand how to correctly reference cell addresses to perform precise calculations in your tables.

Key Takeaways

• Relative cell references change when you drag the formula down or to the side.
• Absolute cell references remain constant no matter where the formula is positioned.
• Using the dollar sign ($) allows you to lock cell references.

Step-by-Step Guide

Step 1: Creating a Basic Formula

Start by creating a simple SUM formula. Go to cell C15 and type the formula =SUM(C3:C14). This formula calculates the revenue from January to December. Verify the input and confirm it by pressing Enter. You should already see the sum of cells C3 to C14.

Step 2: Dragging the Formula to the Right

Now, drag the formula to the right to also calculate the profit. Click on the lower right corner of cell C15 and drag it to D15. Notice how the cell references automatically adjust. This demonstrates how relative cell references work.

Step 3: Adjusting the Cell Range

If we only want to calculate the sum up to November, go back to cell C15. Change the formula to =SUM(C3:C13) to exclude December. This adjustment allows you to work with cell references and make changes quickly.

Step 4: Calculating the Proportional Revenue

To calculate the proportional revenue for January, compare the revenue from January (C3) with the total revenue (in C16). Enter the formula in cell C17: =C3/C16. Now you want to drag this formula down to calculate the proportions for the other months.

Step 5: Understanding the Issue with Relative Cell References

When you drag the formula down, you will notice that you receive an error message. The reason for this is that the cell reference C16 also moves down and does not remain constant. This leads to incorrect results.

Step 6: Using Absolute Cell References

To fix the problem, you can lock the cell reference for the total revenue. Click inside the formula in cell C17 and press F4. This will change the cell reference to $C$16. Now this reference remains constant no matter where you move the formula.

Step 7: Dragging the Formula Down

Now drag the formula in C17 down. You will see that the proportions are correctly calculated because the reference to C16 remains constant and does not change.

Step 8: Calculating Profit Share

If you now want to calculate the profit share, you can apply the same technique. Copy the formula from C17 and paste it into D17. Make sure to choose the correct profit cell reference and also press F4 for the total profit.

Step 9: Further Adjustments

When you drag the formula down or to the right now, it will work correctly. Make sure to use the dollar sign for cell references correctly to control which cell references should remain constant.

In this lesson, you have now learned how cell references work in Google Sheets and have worked with both absolute and relative cell references.

Summary

To efficiently use cell references in Google Sheets, it is crucial to understand what relative and absolute cell references are. While relative references are flexible and adjust when the formula is moved, absolute references remain unchanged. Properly setting cell references enables you to perform precise calculations, even if values in your table change. This ensures that your calculations are always correct.