Contents
Of course, the concept of a range in Excel is one of the key ones. What it is? We all know that a sheet is made up of cells. Now, if several of them contain some information, then this is a range. In simple words, these are two or more cells in a document.
Ranges are actively used in formulas, and can also be used as a data source for graphs, charts, and other visual ways of displaying information. Let’s take a closer look at how to work with a range.
How to select cells, rows and columns
A cell is an element that contains or may contain certain information. A row is cells in a row. Column, respectively, in a column. Everything is simple.
Before you enter data or perform certain data with a range, you need to learn how to select cells, columns, and rows.
To select a cell, you need to click on it. Each cell has an address. For example, the one located at the intersection of column C and row 3 is called C3.
Accordingly, to select a column, you must click on the letter that displays the name of the column. In our case, this is column C.
As you might guess, to select a line, you need to do the same, only with the name of the row.
Cell range: example
Now let’s look at some operations that can be performed directly on a range. So, to select the range B2:C4, you need to find the right corner of cell B2, which in our case serves as the top left cell, and drag the cursor to C4.
Important! Not a square in the lower right corner, but simply, as it were, pull this cell. The square is an autocomplete marker, it’s a little different.
A range does not always consist of cells that are in close proximity to each other. To select it, you need to press the Ctrl key and, without releasing it, click on each cell that should be included in this range.
How to fill in a range
To fill the range with certain values, you must take the following actions:
- Enter the desired value in cell B2. It can be either numeric or text. It is also possible to enter a formula. In our case, this is number 2.
5 - Next, click the autofill marker (just the same box that we previously asked not to click) and drag it down to the end of the range.
The result will be the following. Here we have filled in all the required cells with the numbers 2.
Autocomplete is one of the most requested features in Excel. It allows you to write to the cells of the range not only one value, but also a whole set of data that correspond to a certain pattern. For example, the number series is 2, 4, 6, 8, 10 and so on.
To do this, we need to enter the first two values of the sequence in the vertically adjacent cells and move the autofill marker to the required number of cells.
Similarly, you can fill in the range with the desired dates, which also follow a certain pattern. To do this, let’s enter the date June 13, 2013 and the date June 16, 2013 in US format.
After that, we carry out the already familiar drag and drop.
Range shift
To move a range, just follow a few simple steps. First you need to select the required range and hold down one of its borders. In our case, the right one.
Then you just need to move it to the right place and release the mouse.
Copying and pasting a range
This is also one of the fairly common operations that Excel users perform with ranges.
To do this, you need to select the range, right-click on it and click “Copy”. You can also use the keyboard shortcut Ctrl + C.
You can also find a dedicated button on the Home tab in the Clipboard group.
The next step is to paste the information you need elsewhere. To do this, you need to find a cell that will serve as the upper left corner of the range, and then call the context menu in the same way, but at the same time find the “Insert” item. You can also use the standard Ctrl + V combination, which works in absolutely any program.
How to insert a specific row or column
Inserting a row or column is done in a similar way. First you need to select them.
Only after that you need to right-click and click on the “Insert” button, which is located just below.
In this way, we managed to insert a line.
Named Ranges
As the name suggests, named refers to the range that has been given a name. This is much more convenient, since it increases its information content, which is especially useful, for example, if several people are working on the same document at once.
You can assign a name to a range through the Name Manager, which can be found under Formulas – Defined Names – Name Manager.
But in general, there are several ways. Let’s look at some examples.
Example 1
Suppose we are faced with the task of determining the volume of sales of goods. For this purpose, we have a range of B2:B10. To assign a name, you must use absolute references.
In general, our actions are as follows:
- Select the desired range.
- Go to the “Formulas” tab and find the “Assign Name” command there.
- Next, a dialog box will appear in which you must specify the name of the range. In our case, this is “Sales”.
- There is also the “Region” field, which allows you to select the sheet on which this range is located.
- Check that the correct range is specified. The formula should be: =’1season’!$B$2:$B$10
- Click OK.
19
Now you can enter its name instead of the address of the range. So, using the formula =SUM(Sales) you can calculate the sum of sales for all products.
Similarly, you can calculate the average sales volume using the formula =AVERAGE(Sales).
Why did we use absolute addressing? Because it allows Excel to hardcode a range that won’t change when copied.
In some cases it is better to use a relative link.
Example 2
Let’s now determine the amount of sales for each of the four seasons. You can get acquainted with the sales information on the 4_season sheet.
In this screenshot, the ranges are as follows.
B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10
Accordingly, we need to place formulas in cells B11, C11, D11 and E11.
Of course, to make this task a reality, you can create multiple ranges, but this is a little inconvenient. Much better to use one. To make life so-so easier, you need to use relative addressing. In this case, it is enough to simply have one range, which in our case will be called “Seasonal_Sales”
To do this, you need to open the name manager, enter a name in the dialog box. The mechanism is the same. Before clicking “OK”, you need to make sure that the formula is entered in the “Range” line =’4seasons’!B$2:B$10
In this case, the addressing is mixed. As you can see, there is no dollar sign in front of the column name. This allows you to sum values that are in the same rows but different columns.
Further, the procedure is the same.
Now we need to enter the formula in cell B11 =SUM(Season_Sales). Further, using the autocomplete marker, we transfer it to neighboring cells, and this is the result.
Recommendation: If you press the F2 key while a cell containing a formula with a range name is selected, the correct cells will be highlighted with a blue border.
Example 3
A named range can also be used in a complex formula. Let’s say we have a large formula where a named range is used multiple times.
=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)
If you need to make changes to the used data array, you will have to do this three times. But if you give a name to the range before making changes directly, then it is enough to change it in the name manager, and the name will remain the same. This is much more convenient.
Moreover, if you start typing a range name, Excel will automatically suggest it along with other formulas.
Automatic ranges
Often, when working with information in a spreadsheet, it is not possible to know in advance how much data will be collected. Therefore, we do not always know which range to assign to a particular name. Therefore, you can make the range automatically change depending on how much data is entered.
Suppose you are an investor and you need to know how much money you received in total during the investment in a particular object. And suppose you have such a report.
To do this, there is a function “Dynamic names”. To assign it, you need to do the following:
- Open the Assign Name window.
- Fill in the fields as shown in the screenshot.
26
It is important to note that instead of a range, a formula with a function is used DISPOSAL together with the function CHECK.
Now you need to enter the SUM function with the name of the range as an argument. After you try this in practice, you can see how the sum changes depending on the number of elements entered.
As you can see, there are many interesting ways to interact with ranges. We hope that you liked this guide from basics to professionalism and found it useful.