Boost your productivity with this Excel trick

Despite alternatives such as Google Sheets available, Microsoft Excel is still an incredibly popular piece of software. The platform itself is a common sight in numerous businesses and is reportedly used by more than 750 million individuals throughout the world.

Indeed, 70% of companies use Excel for mission critical processes and has even been described as the “world’s most popular productivity tool”. However, are you getting the most out of it?

If you want to boost your productivity – and impress your boss in the process – the computer training specialists at Wise Owl are here to demonstrate how Excel can do just that. In fact, the big secret to using Excel effectively is to understand how to use range names.

Suppose that you have a spreadsheet listing out the regions throughout the country.

You want to calculate the average area, and show this in the shaded cell. To do this you could use this formula:

=AVERAGE(C5:C8)

However, this is hard to read: what are cells C5, C6, C7 and C8 anyway? A better solution is to give this block of cells a range name, so that you can use this formula instead:

=AVERAGE(Areas)

Creating a range name

The simplest way to create a range name is to select the cells containing (in this case) the regional areas:

You can then type in the range name in what’s called the Name Box:

When you’ve typed in the name (which can be pretty much anything you like, but can’t contain spaces) be sure to press the ENTER key to confirm it.

Going to a named range

Once you’ve created a range name like this, you can easily go to it by using the drop-down arrow to the right of the Name box:

All of the ranges that you’ve created will be listed in alphabetical order. When you select one, Excel will highlight that range for you (even if it’s on a different worksheet in the same workbook). You can also press the F5 key to go to a named range – try it!

Using named ranges in formulae

Once you’ve created a named range like this, you can use it in a formula. Just start typing your formula, and use the name you’ve created for your cells, rather than the cell references:

If you insert or delete any rows or columns within a named range, it will behave just like the cell references it represents (so for example if you add another region between South and West, this region’s data will automatically be included in the average statistic – the region will stretch to accommodate its new cell).

An even quicker way to create ranges

However, there’s actually an even quicker way to create ranges. To see how this works, select the block of cells shown in the screen shot below:

What you’ll notice is that this includes not only the 3 blocks of cells for which we want to create range names, but also the titles at the top of each column. So this method will create:

• A range name called Regions to refer to the four regions in B5:B8;
• A range name called Areas to refer to the four areas in C5:C8; and
• A range name called Order to refer to the four orders in D5:D8.

Now that you have these cells selected, it’s time to choose the magical option to assign the names from the top row to represent the blocks of cells beneath them. To do this, choose this option from the ribbon:

That is, choose the Formulas tab on the ribbon, then choose to create range names from your current selection of cells. For this case, this is what Excel will show:

Excel has guessed what you want to do, but actually guessed wrongly. It’s true that the top row contains names that you want to assign to the columns beneath them, but not true that the left column contains names you want to assign to the rows to the right. Therefore, untick the second box to get:

Select OK, and you will automatically have created three ranges names. Since this is sometimes a bit hard to believe, use the drop-down arrow next to the range name box to see them listed:

You’ve just crossed the line from a casual Excel user to an Excel professional!

This article was provided by Andy Brown, director at Wise Owl Training. The organisation provides computer training for businesses and individuals.