Range in Excel
Whenever we see the word range in excel, we refer to it as a cell or a collection of cells in an excel spreadsheet. It can also be used to refer to the adjacent cells or non-adjacent cells in the dataset. In excel, each range has its defined set of coordinates or positions, unlike A4:A7, B5: F9C, etc.
You can perform many operations with ranges in your Excel worksheet, unlike copying the dataset, moving data from one position to another, formatting cells, and even you can name your range. In this tutorial, we will briefly cover all the topics about ranges.
Select a Range
While working with excel, you may want to select a multi cell range so you can easily make a command for all the cells at once. For example, let's suppose you can highlight the headers in the cell range A2:E2. You select the range and change the background colour of the cells.
Following are the steps to select a range in Excel:
A. Select contiguous range of cells
Similarly, you can select any range of cells in your Excel worksheet.
B. Select non-contiguous range of cells
Types of Ranges
Move a Range
By default, if you move a range of cells in excel, it will move the data from one location to another along with its formatting such as font, text or number format, cell borders, font colour, etc.
Follow the given below steps to move a range of cells in Excel:
Note: After moving the cells, all the data and formatting will be automatically removed from the original range (C1:C6).
Copy/Paste a Range
By default, if you copy a range of cells in excel, it will copy the data from one location along with its formatting such as font, text or number format, cell borders, font colour, etc. and paste it to its new location.
Follow the given below steps to copy & paste a range of cells in Excel:
That's it, your data (along with its formatting) will be pasted to the new location of your excel spreadsheet.
Note: You will notice that the selected range of cells (B1:C6) still has a dotted border. It means the excel range is still copied in your clipboard, and you can again paste it anywhere within your excel worksheet. Therefore we need to remove the data from our clipboard. To clear the clipboard content, press the Escape key from the keyboard. And as soon as you do that, the dotted border about the range will longer be seen.
Named Range in Excel
A named range is an amazing excel feature used to define the name for a collection of cells or ranges in a worksheet. Named range works as an added advantage as it helps to calculate functions and formulas quickly.
To add a named range in your Excel worksheet, follow the below steps:
1. Select the range of cells for which you want to define the name.
2. Go to the ribbon toolbar located at the top of your Excel window. Click on the Formulas tab -> Defined Names group -> Define Name option.
3. The New Name window will open (as shown below). In the descriptive name textbox, enter any suitable name for the range. In our case, we have entered Student_Marks as the name for the selected range.
Note: The name textbox can hold only up to 255 characters.
4. After specifying the name, it's time to specify the range of cells from which you want to apply the name; therefore, in the "Refers to" box, select the range from your Excel worksheet.
5. Once done, click on the OK button.
6. The window will be closed, and when you return to the spreadsheet, you will notice the name Students_Marks is highlighted in the Name box for the selected range of cells (as shown in the image below).
Note: If you have named any range, you will see the range name in the Name box whenever you select that column.
7. Now that we have defined the range's name, we can directly use the name Student_Marks in formulas to refer to the named range of cells. For example, type the below formula in your excel worksheet.
8. The SUM formula will quickly calculate the sum of all numbers present in a defined range and will give you the following result.