This lesson will cover the basics of using ranges in Excel, including how to select and manipulate ranges of cells. You will learn how to name ranges for easier reference in formulas and how to link data between different worksheets. By the end of the lesson, you will be able to effectively manage data within ranges, create meaningful names for those ranges, and establish links between worksheets to streamline data management and analysis.

1. Working with Ranges

• Selecting a Range:

  • Open your Excel app and start a new workbook. Input numbers in cells A1 through C10.

  • Click and drag your mouse over the cells you want to include in your range. For example, to select cells A1 to B5, click on A1 and drag to B5.

• Adjusting a Range:

  • To extend the selection, hold down the Shift key and use the arrow keys or mouse to include additional cells such as through C10.

• Quick Selection Techniques:

  • Select an entire column: Click the column heading (e.g., A). 

  • Select an entire row: Click the row heading (e.g., 1).

  • Select all cells in a worksheet: Click the Select All button (located at the top-left corner of the sheet, between the row numbers and column letters).

2. Naming Ranges

• Why Name Ranges:

  • Named ranges make formulas easier to read and manage. Instead of using cell references like A1:A10, you can use descriptive names like “SalesData.”

• Creating a Named Range:

  • Select the range you want to name (e.g., A1:A10).

  • Go to the “Formulas” tab on the Ribbon and click “Define Name.”

  • In the “New Name” dialog box, enter a descriptive name (e.g., “SalesData”) and click OK.

  • Alternatively, you can enter the name directly in the Name Box (located to the left of the formula bar) after selecting the range.

• Using Named Ranges in Formulas:

  • Instead of using cell references, type the name you assigned. For example, =SUM(SalesData).

3. Linking Between Worksheets

• Why Link Worksheets:

  • Linking allows you to consolidate and reference data from multiple sheets, making your workbook dynamic and easy to update.

• Creating a Link to Another Worksheet:

  • Select the cell where you want the linked data to appear.

  • Type = to start a formula, then navigate to the worksheet containing the data you want to link to.

  • Click on the cell or range in the other worksheet that you want to link to, and press Enter.

  • Example: If you want to link to cell A1 on “Sheet2,” your formula will look like this: =Sheet2!A1.

• Updating Linked Data:

  • Any changes in the source cell will automatically reflect in the linked cell.

Practical Exercises

• Exercise 1: Select and name the range A1:A10 in “Sheet1” as “SalesData.” Use this named range in a formula to calculate the total sales.

• Exercise 2: Create a link from cell B1 in “Sheet1” to cell C1 in “Sheet2.” Modify the data in C1 and observe the change in B1.

• Exercise 3: Select a large dataset and practice adjusting the range. Use quick selection techniques to streamline your workflow.