Range definition and usage in Excel spreadsheets

  1. How to improve the identification of a group or block of cells?
  2. Contiguous and non-contiguous ranges
  3. Range names
  4. Select a range on the worksheet
  5. Select a range to use in a formula or chart
  6. Range vs Range

How to improve the identification of a group or block of cells?

A range is a group or block of cells on a worksheet that is selected or highlighted. In addition, a range can be a group or block of cell references that are entered as an argument to a function, used to create a plot, or used to bookmark data.

The information in this article applies to Excel 2019, 2016, 2013, 2010, Excel Online, and Excel for Mac.

Contiguous and non-contiguous ranges

Contiguous array of cells is a group of selected cells that are next to each other, for example the range C1 to C5 as shown in the image above.

A non-adjacent range consists of two or more separate cell blocks. These blocks can be separated by rows or columns, as shown by the ranges A1 to A5 and C1 to C5.

Both contiguous and non-contiguous ranges can contain hundreds or even thousands of cells and span worksheets and workbooks.

Range names

Ranges are so important in Excel and Google spreadsheets that names can be assigned to specific ranges to make it easier to work with and reuse them when referenced in charts and formulas.

Select a range on the worksheet

When cells are selected, they are surrounded by an outline or border. By default, this outline or border only surrounds one cell on the worksheet at a time, the active cell. Changes to the worksheet, such as editing or formatting data, affect the active cell.

If a range of more than one cell is selected, changes to the worksheet, with some exceptions such as data entry and editing, affect all cells in the selected range.

There are several ways to select a range on a worksheet. These include using the mouse, keyboard, name field, or a combination of both.

To create a range made up of adjacent cells, drag the mouse or use a combination Shift and four arrow keys on keyboard. Use the mouse and keyboard, or just the keyboard, to create non-adjacent ranges.

Select a range to use in a formula or chart

When entering a range of cell references as an argument to a function or when creating a graph, in addition to entering the range manually, the range can also be selected with hover.

Ranges are identified by references or cell addresses in the lower left and right corners of the range. These two links are separated by a colon. The colon tells Excel to include all cells between these start and end points.

Range vs Range

The terms range and array sometimes seem to be used interchangeably between Excel and Google Sheets, as both terms are associated with using multiple cells in a workbook or file.

Specifically, the difference is that a range refers to selecting or identifying multiple cells (e.g. A1:A5) while an array refers to the values ​​contained in those cells (e.g. {1; 2; 5; 4; 3) } ).

Some functions, such as SUMPRODUCT and INDEX, take arrays as arguments. Other functions, such as SUMIF and COUNTIF, only accept ranges for arguments.

This does not mean that a range of cell references cannot be entered as arguments to SUMPRODUCT and INDEX. These functions take values ​​from a range and convert them to an array.

For example, both of the following formulas return the result 69, as shown in cells E1 and E2 in the figure.

 = SUMPRODUCT (A1: A5, C1: C5) 
= SUMPRODUCT ({1; 2; 5; 4; 3}, {1; 4; 8; 2; 4})

On the other hand, SUMIF and COUNTIF do not take arrays as arguments. So, although the formula below returns the answer 3 (see cell E3 in the picture), the same formula with an array is not accepted.

 СЧЕТЕСЛИ (А1: А5, "

В результате программа отображает окно сообщения со списком возможных проблем и исправлений.

Leave a Reply

Your email address will not be published. Required fields are marked *