Named Ranges

Did you know that you can give a name to your ranges or cells in excel? And using these named ranges, while you can work with less space for confusion, your formulas will look much nicer and easier to read.

To explain named ranges concept we will use same example data from sumif post.

Lets assume that you have an unsorted list that contains customers and monthly sales revenues which looks like:

 

You want to get total sales revenue for customer1 using sumif function. The formula for this job will be:

=SUMIF(B3:B13;customer1;D3:D13)

As you will notice it needs a little effort to read and understand this formula (will be significantly worse with longer formulas). But we can make it easier by the use of named ranges. To name your ranges select cells through B3 to B13 and while cells are highlighted type your range name into the area located left side of formula bar and press enter.

 

Now your range that contains customer names are named as “customers”. You can still address this range as B3:B13 or as of now, typing customers in any formula will have the same effect (also excel will give you auto complete for range names like it does for functions when you start writing one. It is a very nice bonus.). Now name your sales range as “sales”. Now you can type same formula as this:

=SUMIF(customers;”customer1″;sales)

See how much easier it is to read and understand this formula now. It simply says “check customer1 among customers and sum sales values you find”.

You can also find and/or create named ranges using Name Manager located in FORMULAS Ribbon.

name manager

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.