CXO

Use dynamic ranges to make your Excel spreadsheets easier to understand

Give your numbers more distinct meanings in Excel with dynamic ranges. By using the OFFSET function and naming feature, you can give labels to your formulas, making them easier to find and reference.

You can make Excel formulas easier to understand by using named ranges. For example, the formula =Sum(Sales) is much easier to understand than =SUM(B2:B102). But what if the range for Sales is always changing?

For example, suppose Column A lists order dates and Column B lists the corresponding sales for each date. As sales are made, you enter them into the spreadsheet. By combining Excel's OFFSET function with the naming feature, you can create dynamic ranges that can be used in formulas as if you were working with cell references. Follow these steps:

  1. Go to Insert | Name | Define.
  2. Click in the Names In Workbook text box and enter Order_Date.
  3. Click in the Refers To text box and enter the following formula:
    =OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),1)
  4. Click Add.
  5. Click in the Names In Workbook text box and enter Sales.
  6. Click in the Refers To text box and enter the following formula:
    =OFFSET(Order_Date,0,1)
  7. Click Add, and then click OK.
  8. In cell E1, enter Total Sales.
  9. In cell F1, enter =Sum(Sales).

A running total of the sales will appear in F1 as you add orders to Sheet1. Be careful not to enter spurious values anywhere in columns A and B; they may be falsely included in the Total Sales calculation.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox