Operations with Ranges in Microsoft Excel

Operations with Ranges in Microsoft Excel


In Excel, a range is a collection of two or more cells that you work with as a group rather than separately. This enables you to fill the range with values, move or copy the range, sort the range data, filter the range to show only certain values, insert and delete ranges, hide entire rows or columns, and merge two or more cells.

You learn these and other range techniques in this chapter, and in later chapters you learn techniques such as formatting a range, applying a formula to a range, and building a chart from a range.

Select a Range: To work with a range in Excel, you must select the cells that you want to include in the range. After you select the range, you can fill it with data, move or copy it to another part of the worksheet, format the cells, delete the data, and so on. You can select a range as a rectangular group of cells, as a collection of individual cells, or as an entire row or column.

Fill a Range with the Same Data: If you need to fill a range with the same data, you can save time by getting Excel to fill the range for you. The most common method for filling a range in this way is to use Excel’s AutoFill feature, which makes it easy to fill a vertical or horizontal range with the same value. However, Excel also offers an alternative method that enables you to fill any selected range. See “Select a Range,” earlier in this chapter, to learn how to select a range of cells.

Fill a Range with a Series of Values: If you need to fill a range with a series of values, you can save time by using Excel’s AutoFill feature to create the series for you. AutoFill can fill a series of numeric values such as 5, 10, 15, 20, and so on; a series of date values such as January 1, 2011, January 2, 2011, and so on; or a series of alphanumeric values such as Chapter 1, Chapter 2, Chapter 3, and so on. You can also create your own series with a custom step value, which determines the numeric.

Move or Copy a Range: You can restructure or reorganise a worksheet by moving an existing range to a different part of the worksheet. For example, if you have two related ranges that are far apart on the worksheet, you can move one of them so that the ranges appear close to each other. You can also make a copy of a range, which is a useful technique if you require either a duplicate of the range elsewhere, or if you require a range that is similar to an existing range.