Range in Excel – A Complete Guide to Working with Range and Cell

puneet-gogia-excel-champs

- Written by Puneet

Understanding Excel Range and a Cell

In Excel, a cell is like a box where you can enter data (numbers, text, symbols, or a formula). A combination of cells is called the range. That means a single cell is a cell, but when you have multiple cells, that is called a range.

understand-range-and-cell

Each cell has a specific and unique address, which you can see in the address bar. In a cell address, there’s an alphabet and a number.

In the below example, we have selected the cell C3. In this address, C refers to the column, and three refers to the row, which means columns “C” and “3”.

selected-cell

Select a Cell or a Range

To work with a range or a cell, you need to know how to select it. There are two simple ways to do this:

Using Mouse – With the mouse, you can click on the cell or the range you want to select. It is the easiest and quickest way to do it.

select-a-cell-or-range

And if you want to select a non-continues range of cells, you need to press and hold the Ctrl key from your keyboard and then use the mouse to select multiple cells or ranges.

select-non-continues-cells-range

Using Keyboard Navigation Keys – You have navigation keys on the keyboard that can help you navigate from one cell to another. And if you want to select a range instead of a single cell, you need to use the Shift and navigation keys.

keys-to-navigate-from-a-cell-to-another

Once you go to the cell from where you want to start the range selection, press and hold the SHIFT key, and then use the down key to select the range downwards. And if you want to select the range in the other direction, you can use the navigation accordingly.

While selecting a range, Excel displays the number of rows and columns in the Name box to the left of the Formula bar.

name-box-to-left-of-formula-bar

Once you finish the selection, the name box shows the active cell’s address, which is not highlighted.

There is one shortcut key (Ctrl + A) that can be super helpful. When you use this key, it selects all the cells in the current worksheet.

Rows and Columns

A worksheet has rows and columns in which cells are structured. The horizontal structure of cells is called a row, and the vertical structure is called a column.

rows-and-columns

There are over 1 million rows (1048576) and more than 16 thousand columns (16384). So, you can imagine how much data you can store in a single worksheet in Excel.

Select a Complete Row or a Column

There are two major ways to select a row or a column. The first is using the mouse and the second is using the keyboard shortcut. With the first method, you can select a row or column by clicking on the row or column header.

click-on-header

But if you want to use the keyboard shortcut, you need to select the cell for the row or the column and is the following keyboard shortcut:

  • To select the column – Ctrl + Space
  • To select the row – Shift + Space

Edit a Cell

Now, if you want to edit a cell, there are simple and easy ways. The first way is to use the mouse, and the second is to use the edit button from the keyboard.

Edit a Cell with a Mouse – To edit a cell with the mouse, you only need to double-click on the cell you want to edit. It edits the cell instantly; you can enter data by typing.

edit-a-cell-with-mouse

Edit a Cell with Keyboard – You can also use the F2 key to edit a cell. When you press it, edit the selected cell. But there’s one more way which is way easier than all: START TYPING. When you select a cell and start typing, Excel automatically edits that cell and enters the data you are typing.

Edit a Cell from the Formula Bar – You must click the formula bar and type the data you want to enter.

edit-cell-from-formula-bar

Enter Data in a Cell

As I said, you can store your data in a cell, so you need to know how to enter data in a cell.

  • Enter by Typing – Earlier in this post, we discussed that you can enter data directly into a cell. It is the easiest way to enter data in Excel. However, this method is not useful when entering large data in your sheet.
  • Copy-Paste Data – If you have a large data set that you want to enter in the sheet, the best way is to use the copy-paste method. All you need to do is select the range where you have that data, use the keyboard shortcut Ctrl + C to copy it, and then go to the destination cell or the range and use the keyboard shortcut Ctrl + V to paste it.

If you want to be an advanced Excel user, you can learn power query, which allows you to get data in Excel in multiple ways.

Referring to a Contiguous Range

To refer to a range in a function or a formula, you have two different ways:

  • Mouse: You can select the range using your mouse while entering a function or a formula.
  • Type: You can also type the address of the range with your keyboard while entering a function or a formula.
referring-continuous-range

Referring to a Non-Contiguous Range

To refer to a non-continues range use the below steps:

  1. First, select the first cell or the range and type a comma.
  2. After that, press and hold the Ctrl key and select the second range in your worksheet, and again type a comma.
  3. In the end, select the third range of cells.
referring-non-continuous-range

Another way you can enter the address of the first range, then use a comma and type the second range, and now use a comma again and type the third range of cells in the formula or function.

Copy and Paste Cells and Range

As discussed earlier, you can use the copy-and-paste method in Excel to add data. And there are two ways to do that. The first way is to use the keyboard shortcuts (Ctrl + C to copy and Ctrl + V to paste) discussed above.

And the second way is to use the right-click menu where you have the copy-and-paste option. To open the right-click menu, you need to press right-click on the mouse and then use the option you want.

copy-and-paste-cells-and-range

You also have both options in the Home Tab > Clipboard. There, you have both options to use.

Format a Cell or a Range

Excel allows you to format a cell or a range with multiple options:

  • The font color, style, size, and alignment.
  • Bold, italic, underline.
  • Cell color, borders, indent, and warp text.
format-a-cell-or-range

On the home tab, you have all the options listed for the formatting. You need to select the range or the cell on which you want to apply the formatting.

formatting-options-in-home-tab

You can also open the “Format Cells” dialog box (Ctrl + 1), where you have all the formatting to apply to the range or cell.

RelatedAutoFormat in Excel

Sort a Range

When you have data in a sheet, Excel allows you to sort that data in a particular order. Let’s say if you have numbers and you want to sort those numbers in a descending order, you can use the sorting option. Go to the Data Tab and click on the Sort button.

sort-a-range

You can select the column in the SORT dialog box to sort the entire data.

sort-dialog-box

Select the order from the order drop down, and then click OK to sort the data. It is a complete guide on sorting for you to learn sorting in detail.

Apply Filter to a Range

When you have a large data set, it is obvious to analyze data to understand it. And one of the basic ways to analyze data is to filter it.

Once you select a range or a single cell from the range, you can use the keyboard shortcut Alt > D > F > F.

apply-filter-to-a-range

Now, when you use the shortcut, you’ll get the filter button on the first cell of the column (Header). And you can click on that button to open the filter.

filter-button-on-first-cell

You can also apply the filter from the data tab, click on the Filter button, and then the same button to remove the filter.

apply-or-remove-filter-from-data-tab

Columns Width and Row Height

As we have learned, a cell and a range is structured in rows and columns. Excel allows you to change the height and width of a cell (row height and column width).

columns-width-and-row-height

Apart from this, you can also use the cursor. All you need to do is hover your cursor at the right corner of the column or lower side of the row header.

adjust-row-or-column-using-mouse

After that, you need to expand it to change the width and height of it.

Insert/Delete a Row or a Column

While working with ranges, you can add and delete rows and columns in a sheet. When you select a column or a row and right-click, click on the insert option to insert a new column or a row.

insert-or-delete-row-or-column

You can also use a keyboard shortcut to add rows and columns.

  • (Select a Row and Ctrl + Shift + =) – Select the entire row and then use the shortcut to add a new row below the selected row.
  • (Select a Column and Ctrl + Shift + =) – Select the entire column and use the shortcut to add a new column next to the selected column.

Note – Select multiple rows or columns if you want to insert multiple rows or columns. Let’s say you want to add five rows; in this case, you need to select five rows and then use the insert option.

Below the “Insert” option, you have the delete option, which you can use to delete a row or a column from the sheet.

Hide Row or a Column

In the right-click menu, you can hide a row or a column. All you need to do is to select a row or a column, right-click and then click on the “Hide” option.

hide-row-or-column

It will instantly hide the selected column or the row. And if you select multiple columns and rows, it will hide all those in one go.

In the below example, you can see that column B is hidden, but there is a thin line between columns A and C. This thin line helps you identify if a column (or a row) is hidden.

hidden-column

To unhide column B, select columns A and C and then use the Unhide options from the right-click option.

unhide-from-right-click-option

Merge Multiple Cells into One

You can merge two or more cells into a single cell in Excel. On the Home Tab, you can find the merge option.

merge-multiple-cells-into-one

Select those cells first and then click on the merged cells button.

select-cells-and-click-merge

And the same option to unmerge cells back into multiple single cells. The merge cells option is useful when adding long text or numeric values in a single cell. And if you don’t want to merge the cells, use the wrap text option.

Create a Named Range

Excel also allows you to create a named range, which means giving a relevant name to a range. The easiest way to create a named range is to select the range first, click on the address box, and then enter the name that you want to give to the range.

create-a-named-range

In the end, hit enter to create the named range.

Last Updated: November 04, 2023