Monthly Archives: April 2014
When you first come across named ranges they don’t seem to be the most useful of things in Excel, yet the more you learn in Excel the more uses and benefits you will discover in using them.
So what is a named range?
Basically, it is a name that has been given to a single cell or group of cells in a worksheet. But why call it by something that is longer than its address?
Let’s look at an example naming a single cell.
In A1 let’s enter the UK VAT rate (sales tax) of 20%. In some cells elsewhere in the worksheet I have some values and I want to calculate the VAT on each of those.
You can see my values in column A, the calculated VAT in column B and for ease of understanding I have displayed the formula in column C. Basic stuff you might say…a simple calculation using $ symbols to fix the cell address of the 20% rate.
Now let’s give our 20% cell a sensible name that reflects what it contains e.g. VAT_Rate. Note the use of an underscore in the name. When creating named ranges do not use spaces…it won’t work. Also be aware that calling a cell VAT20 for instance, which may seem like a sensible name, will actually take you to cell address VAT20. Don’t forget that if you are using 2007 or later, columns go all the way to XFD so add underscores or something to the name that will not turn it into a cell reference.
To give your cell a name, the easiest way is to click in the NAME BOX.
Where you see the cell address, start typing the name you want to give to your cell remembering the rules about names I mentioned earlier. Make sure you press ENTER otherwise the name won’t be saved.
If you want to check that the name works, click on the arrow on the right hand side of the NAME BOX, click on the name you have just created and all being well the cell you gave a name to will be highlighted.
“Fantastic!” I hear you say…”but you have just spent about 30 seconds creating and testing a name that is three times the length of the actual cell address and no obvious benefits…would have been quicker typing $A$1!”.
And so it would seem. But let’s rewrite our formula…
Start writing your formula exactly as before but instead of typing in $A$1, start typing the name you assigned to the cell. As we are entering a formula, when I type the first letter of the name it comes up with a list of functions, and rather conveniently, named ranges beginning with the same letter…
Either arrow down to the name or type the name in full and press enter as you would for any normal formula once all the cell references etc. have been entered. And because I’m using a name, it has a fixed location so no need for those $ symbols.
The end result in column B is no different to what we had before, but when you look at the formula, instead of some cell reference we can now see a name that gives us an indication of what is actually being calculated. So there is advantage number 1 of a NAMED RANGE. If you are used to building more complex formulas, then they will become much shorter and what they are calculating becomes much more obvious.
The second advantage is that I can now make reference to my cell anywhere in the workbook without the need to know where it is, or even ty and find it to click on it to build it into any of my formulas.
Just type the name as part of your formula as before, select the name from the list that appears, and done! Simple, and if you look at the formula you know exactly what it is calculating – the name tells you.
If you forget a name that you have created, you can bring up a list of all named ranges in your workbook by pressing the F3 key as you build your formula. Click on the name you want to use and click on OK.
So far I have only named a single cell, but you can create named ranges for entire blocks of cells, and you can even have overlapping named ranges.
Here I have manually named a block of cells containing sales data for “North”. I could then repeat this for each column, however, I will show you a more efficient way of doing this a bit further on, but here it is just to show that blocks of cells can be named too.
I can now write formulas that refer to the block of named cells;
…which will sum up the cells A2:A12. No need for me to physically select any cells, just use the name and once again when I look at my formula I know exactly what it is adding up.
Hopefully by now you can start seeing some of the advantages of using named ranges.
A bit of a random geek fact for you: if you zoom down to 39% or less, named ranges are actually displayed in the worksheet (works best with blocks of cells rather than single ones).
The quickest way to name a range is to simply type the name in the NAME BOX and press enter but you can also do it through the NAME MANAGER or DEFINE NAME in the FORMULAS tab.
Use the dialog box to name your range, decide on its scope i.e. is referencing the name limited to a single worksheet or can it be used across the entire workbook? You can add comments, personally I never bother but the choice is yours. And finally which cell(s) does your name refer to? Click on OK when done. The new name you have created will then appear in the drop down list in the NAME BOX.
If you go through the NAME MANAGER click on NEW in the top left hand corner to open the same dialog box as above.
If you want to EDIT or DELETE your named ranges you need to come through the NAME MANAGER. Unfortunately, you cannot delete named ranges via the name box.
If you have dozens of named ranges, you can use the FILTER button to limit the list by filtering on different criteria.
I mentioned earlier a quick way to generate range names. Previously I created named ranges by selecting the cells myself and using the NAME BOX to assign a name. That’s fine if you only have 2-3 to create but what if you have loads of columns or rows that you would like to refer to by name?
Using a basic two-way table (you can do this on any size of table by the way) highlight the whole table. On the FORMULAS tab, click on CREATE FROM SELECTION or if you like keyboard shortcuts use Ctrl + Shift + F3 and you will see this…
Decide which row/column values you want to use as range names and tick/un-tick the relevant boxes in the dialog window. Click on OK. Nothing apparent happens until you check out the NAME BOX or the NAME MANAGER.
We now have a whole bunch of newly created named ranges using the row and column headings from the table!
Test them out if you want by clicking on any of the names and see which cells are highlighted. You can now start writing formulas using as many names as necessary;
Now tell me that’s not better than =SUM(Sheet4!B2:B5,Sheet4!D2:D5)!
Finally, be aware that all the methods I have shown you here apply to FIXED dimension ranges i.e. the cell(s) you select and name remain unchanged unless you go into the NAME MANAGER and edit the REFERS TO information adding further rows/columns. You can create dynamic ranges but I will write about those separately as they are quite a big topic in their own right.
A named range CAN be automatically resized as long as new rows/columns are added WITHIN the boundaries of the original named range. If my named range is from A1 to D20, as long as I insert or delete rows between rows 1 and 20, or insert/delete columns between A and D, the range will include those automatically. If I add data from row 21 or column E and beyond I would need to manually change the REFERS TO references in the NAME MANAGER.
In a previous blog I showed you how to create a shiny sphere which you can use in a variety of ways. This time, I will show you how to create a transparent tube/cylinder.
Go to the INSERT tab and click on SHAPES. From the window that appears, click on the DONUT.
Draw a perfectly round DONUT by holding down the SHIFT key as you click and drag.
The DONUT ring is quite thick and we need to reduce this a bit so we end up with the image on the right (above). To do this, click and drag the yellow grab handle towards the outside of the DONUT.
We will start with a basic cylinder for now, but I will show you how to add something inside the cylinder a bit further on.
Right click on your DONUT and select FORMAT SHAPE.
Select 3-D ROTATION, and from the presets, and from the group called PERSPECTIVE, pick the one called PERSPECTIVE RELAXED.
You now get something like this…
You can always change the perspective manually later to suit your particular requirements, but this will do for now.
At this stage we can add a little interest to the shape by adding a bevel, but it’s not absolutely necessary. If we do though, we do it by selecting the 3-D FORMAT option and selecting one of the bevel options from the TOP BEVEL button. This will give us…
Now we need to add some depth to our shape. Amongst the 3-D FORMAT options you’ll see DEPTH. Pick a colour so it matches with what will be the top of our cylinder and type in a value to give some depth to the DONUT. In this example I will set it to 250 pt.
Try out the different MATERIAL and LIGHTING options available to you as well as the ANGLE of the light to see a variety of different effects.
At this point you are thinking “well…that’s pretty…but what do I do with it?”
This is where having something in the cylinder might be of use. For instance you might want to represent profit levels, or levels of readiness for a project and want something a little more interesting than just 50% written across the screen.
Start off exactly as we did before to create the empty cylinder but this time add in a coloured CIRCLE inside the DONUT. Once you’ve done that, GROUP the two shapes together. Continue as above adding bevels and depth to the grouped shape. You should now have something like this…
At the moment our contents “fill” the cylinder. We can adjust this level to whatever we want to give the impression of different values i.e. 75%, 50% etc.
Click TWICE (not a double click) to select the “filling”. Change the DEPTH to whatever level you want to show. Here, I will set it to 125 pt (or 50% of the full cylinder).
Problem is, the DEPTH varies starting from the top, not the bottom which would make more sense. To lower the “filling” go to 3-D ROTATION and go to the setting DISTANCE FROM GROUND. Enter a NEGATIVE value until you hit the bottom of the cylinder.
Apply different values to your “filling” to represent different values as I have done in the example above. I have also changed the perspective slightly to give a better view of the levels in each cylinder.
So with a few simple techniques you can create your own custom 3-D shapes that represent your data in a more visually interesting way than just a handful of bullets with numbers, and you don’t have to go and buy some random stock photo that doesn’t really show what you want the way you imagined it.