Range Operations in Excel

In this post, we would be examining range operations in excel. To begin, let us define what a range is in excel.

What is a Range

A range is a collection of 2 or more cells. Range in excel is used mainly when writing functions, it can be used to create dynamic charts, for pivot table configuration and to bookmark data.

Types of Range

There are two types of range in excel namely contiguous and non contiguous range. Now let’s do a deep dive into each of these categories:

Contiguous range

A contiguous range is a collection of cells that are next to each other, either horizontally or vertically. The easiest example is when you highlight cells manually from top to bottom, left to right or right to left. For example, the range A1:A10 above is a contiguous range when selected alone. So also is the range C1:C10 a contiguous range when selected alone.

Non-Contiguous range

A non-contiguous range on the other hand consists of two or more separate blocks of cell. These blocks can be separated by rows of columns. For example, in the image above, when range A1:A10 and range C1:C10 are taken together, that constitutes a non-contiguous range because they are separated by column B

Selecting Ranges with Mouse / Touchpad

In excel, both contiguous and non-contiguous ranges can be selected using the mouse or trackpad and there are some instant benefits to this. In subsequent paragraphs, we would examine how to highlight each type of range and the benefits of doing so.

Selecting a Contiguous Range

It is very easy to select a contiguous range, all you need to do is pick a starting cell and then right click and drag either from top to bottom or left to right.

The image on the left (first image for mobile users) is a contiguous range of B2:B7 whilst the image on the right (second image for mobile users) is a contiguous range of B2:G2

Note: selecting a Contiguous range is not only limited to a single column or row, we can highlight multiple rows or columns as a contiguous range so far they are not separated by any cell, row or column. Examples are the images below.

The image on the left (first image for mobile users) has a range of B2:F4 whilst the image on the right (second image for mobile users) has a range of B2:C7

Selecting a Non-Contiguous Range

Remember we have established that non-contiguous ranges are separated by a cell, row, column or combination of row and column. How then do we select them since selecting contiguous range required us dragging our selection continuously. Well, it’s easy, just follow these steps:

Step 1: Identify the first cell for the first range and select it.

Step 2: Now that you have identified the first cell, drag either from top to bottom or left to right. In our example, we would drag it down to row 10

Step 3: We have selected the first range, supposing we want to add range D2:D10, all we need to do is to hold down CTRL and highlight D2 to D10

Instant Benefits of Highlighting a Range in Excel

Selecting ranges in excel allows you a quick glance of some key metrics if dealing with numbers. These metrics are three in number and can be seen at the bottom of the document window when you highlight a range with numbers in it. The metrics are

• Sum;
• Count; and
• Average

As stated earlier, highlighting a range with numbers give you a quick view of key parameters namely the average, count and sum of the set of numbers

Filling a Range (How to Auto Fill in Excel)

Excel gives you the ability to fill a range by dragging and releasing, and in the examples below, you would see how.

Fill Subsequent Cells with First

This is for when you want to fill subsequent cells in a range with the value in the first cell

For example, assume we have the number 50 in cell B2, how do we fill the cell B3 to B10 with this same number? Easy, all you need to do is to execute the following steps:

Step 1: Enter the value 50 into cell B2

Step 2: Click on the lower right corner of cell B2 and drag down till row 10 (the last row in our target ending cell B10)

Step 3: You’re done, you should see 50 from Cell B2 to B10

Auto Fill as a Sequence

This works for a sequence of numbers or dates. Once you fill the first two cells, then you can highlight those two cells and drag down as detailed above for excel to populate with the same spacing. For example, if the first 2 numbers are 1 and 3 (difference of two), excel fills the third cell with number 5, the fourth with number 7 and so on.

Number Sequence

Below are the steps for filling a number sequence as discussed above:

Step 1: Input numbers in the first and second cell of the planned congruous range. In our example, we are choosing 1 as the first number and 3 as the second number. Our last row would be row 10

Step 2: Highlight the cells B2 and B3, click on the right corner of cell B3 and drag down till B10 and you’re done

Date Sequence

The steps in filling a date sequence is similar to that of filling a number sequence. All you need to do is to enter the first two dates and drag down as discussed in the previous sequence.

In our example, assume the starting cell as a date of 1st of May 2022, and subsequent cells in the sequence should be one week after the previous date.

Step 1: Input the dates (1st of May 2022 and 8th of May 2022) into the first and second cells

Step 2: Highlight the cells B2 and B3, click on the right corner of cell B3 and drag down till B10 and you’re done

Named Range

A named range is a collection of cells that form a range and have been given a name. They are usually used when writing formulas in excel. The modeler can define ranges to make it easier for others to understand.

Take for example the snapshot of our Instant benefit of highlighting a range section, we have a list of enquiries to a call center and the count in a given year. We can calculate KPIs for the call center like the highest number of enquiries, lowest number of enquiries, average number of enquiries and sum of enquiries and we don’t need to select the range every instance once we define the range from the get go. What do I mean? See the guide below

Step 1: Define the range by highlighting the relevant cells and giving it a name you would remember in the name box. In the example below, I am defining cell B4:B24 as enquiries. To do this, type your chosen name (enquiries in this case) into the name box at the top left corner and press enter

Step 2: Type in the formula using the name you defined instead of the actual range

Remember “enquiries” is the named range B4:B24. In that case, the formulas used for cells G7:G10 would be as follows:

Copying a Range

Copying a range is as simple as copying any cell and pasting it somewhere else. You however need to ensure that in case of an analysis that picks a cell outside the range as input, you anchor ($ sign in front and at the back of the Column letter) said cell because excel would pick the equivalent number of rows down / up and equivalent number of columns to the right / left of the cell outside the copied range. Confusing? Dont worry, would discuss in detail after I show you how to copy a range.

Step 1: Highlight the range you intend to copy. From the example above, assume we want to copy the range B4:B24 to column G

Step 2: Right click and select copy or hit Ctrl + C

Step 3: Navigate to cell G4 or whichever cell you desire to paste, right click and click past under paste options. You can also hit Ctrl + V to paste

Step 4: That’s it, you are done.

Copying a range with dependencies outside the range

Ok, back to what we were discussing about anchoring cells outside the range we intend to copy. Let’s imagine that in our previous example, we forecasted a 5% increase in all enquiries for 20X3 on column C and later on we want to copy all enquiries for 20X3 to column G. What do we do? Read on to find out.

In the image above, all enquiries under 20X3 are dependent on the growth rate OF 5% we have specified in cell F8. We are projecting an increase in growth rate of 5% from 20X2 and the formula used for Package clarification Enquiries under 20X3 for example is:

Now let’s copy C4:C24 to column G

You can see from the result that something is not right. That is because we didn’t anchor / fix cells outside the range we were copying. For starters, the formula in column C is dependent on the previous year enquiries in column B. Copying to column G will then see excel move references initially picking column B to now pick column F because it is one column to the left of where we are pasting it.

The same applies to the cell containing the growth rate. Excel would count the equivalent number of columns to the right of column G and pick that as the growth rate cell. See below >>>

As you can see from the above, the growth rate cell in column F is three columns to the right of column C (the original range we copied), and because of that, excel picks three columns after destination G as the growth rate.

Excel’s default when handling references is relative cell reference and in the example above we need to maintain absolute reference for the growth rate cell and column for 20X2 enquiries. To correct this, we need to anchor column B for each row in column C and then anchor cell F8 that contains the growth rate %

Anchoring Columns or Rows

In our example above, we only need to anchor the column B for every formula in our range C4:C24. This means that as I copy the formula from C4 to C5, it should pick column row 5 of column B. In essence, the row reference is relative whilst column reference is absolute.

Anchoring rows only is the flip side, column reference would be relative and row reference would be absolute. The third anchoring is absolute anchoring where both column and row reference are absolute.

Column Anchor

To anchor a column only, take for example column B, let a $ (dollar) sign appear before the column letter as seen below.

Row Anchor

To anchor a row only, take for example row 4, let a $ (dollar) sign appear before the row number as seen below.

Absolute Anchor

Absolute anchor is anchoring both the row and column meaning that no matter where you copy the formula to, it would still pick the same cell reference. To achieve this, let a dollar sign appear before the column letter and before the row number as seen below.

Back to our Example

As stated above, we need to anchor column B in our formula, and to do this, enter into the formula in C4, click on B4 in the formula bar and press F4 three times

Good, but we are not done yet. We still need an absolute anchor for cell F8. To do this, click on F8 in the formula bar and press F4 once

Now press enter and copy and paste the formula in C4 to the range C5:C24

Almost done, just need to check that everything is working well. Highlight range C4:C24 and paste it in column G

You should see the exact same numbers as those on column C.

Moving a range

Moving a range can be done in two ways. One is with your mouse / touchpad and the other with your keyboard.

Moving a range with your mouse / touchpad

Step 1: To move a range with your mouse / touchpad, highlight the range and click on the border of the range

Step 2: Drag the range to its new location. For example let’s move the range in column G to column J.

That’s it, you are done.

Moving a range with your keyboard

Step 1: Highlight the range and press Ctrl + X to cut. You should see some cell selector dots (marching ants) at the borders of the range.

Step 2: Navigate to the new location and paste with Ctrl + V

Voila, and you are done.