Named Ranges and the SUMIF Function

Named ranges are useful in Excel as their use can make formulas much more understandable. You can name:
areas of the spreadsheet, such as your data
columns and rows
or individual cells that may act as constants, for example the VAT value

I’ve used the following named ranges in the SUMIF example below:
Named Ranges in Excel

As usual with Excel, there are a number of ways to do the same thing. To name a range you can highlight the area and then either:

1. Insert>Name>Define and then choose an appropriate title
or
2. Go to the name box in the top left hand corner of the formula bar and type a name in the cell

To confirm the region that the named ranage refers to, use the drop down Name Box and select the range of interest.

Named Ranges in Excel

The SUMIF function is in my list of all time useful functions, the excel help is very good, but here is my tutorial with real world finance examples. The SUMIF function adds the cells specified by a given criteria.

The formula syntax is:

SUMIF(range,criteria,sum_range)

where
“Range” is the range of cells you want evaluated.
“Criteria” is the criteria in the form of a number, expression, or text that defines which cells will be added. “Sum_range” are the actual cells to sum.

Here are some uses of the SUMIF function, utilising the data sown above.

1. To return total spend for 7001 SLA’s:
=SUMIF(CC,7001,Actual)

where CC = 7001 return the sum of Actuals

2. To return the total annual budget for Hilliary Hospital SLA and Additions:
=SUMIF(JC,TRAH,AnBud)

where JC = TRAH return the sum of AnBud

Sub Totals and Pivot Table Reports

Removing sub totals from pivot table reports

When you add detail to pivot table rows, Excel will automatically add a subtotal row. This can very quickly litter your pivot table with unnecessary detail.

Pivot table littered with subtotals

I remove the unwanted subtotals by right clicking on one of the totals and selecting “Hide”.

Removing subtotals from pivot table

Removing subtotals from pivot table

Adding sub totals to pivot table reports

Having removed all my unwanted subtotals, I will typically decide to rearrange the ordering in my pivot table and find I am missing a vital subtotal. It is possible to reapply subtotaling though – so don’t panic.

Following on from the examples above, it may be preferable to have the Cost Centre description appearing first rather than the cost centre code (which means nothing to anyone outside of finance).

Inserting subtotals in pivot tables

To add the subtotal against this description, right click in any of the description values, choose “Field Settings” and then make sure the radio¬†box labeled “Automatic” is selected.

Inserting subtotals in pivot tables

Inserting subtotals in pivot tables

This enables you to add and remove subtotals at will.

Inserting subtotals in pivot tables

Colourful Spreadsheets

If you want to produce Excel reports with impact, you have to consider formatting as well as data content. One of the flaws of Excel is the
rather garish colour palette provided as standard, which seems to favour the production of colour clashing disasters.

Although it is possible to choose custom colours when formatting regions of your workbook it does require quite a lot of effort. Its a
shame you can’t select themes, as you can with other microsoft products eg Publisher, and have the colour palette change accordingly.

Excel palette

All is not lost though as you can create and modify your own palettes:
Open a spreadsheet and head for TOOLS > OPTION and select the COLOUR tab. You should now be presented with the standard colour scheme. You can click on each of the coloured squares and modify them, MODIFY > CUSTOM and enter the specific RGB values.

You’ll start yawning pretty quickly as it is a fairly tiresome task, mind you, once it is done you can save the completed workbook as a template and select your desired palette every time you start a new spreadsheet. If you really can’t be bothered, you are in luck as I’ve uploaded a blank workbook with the above colour scheme already sorted out. You can download it and apply the colour scheme to your own reports:
Custom Colour Palette Excel Spreadsheet

When a spreadsheet with the desired colour scheme is open you can copy it to any other open workbook, from TOOLS > OPTIONS > COLOUR just choose the spreadsheet with the desired colour scheme using the “Copy colours from” dialog box.

I’ve prepared a couple of additional colour palettes and thought I may as well share them online:

Here’s a bluer version:
blueish-palette.jpg
and a greenish version:
greenish-palette.jpg
Just click the image to download the relevant spreadsheet.

Features test

The Postie program has much to recommend it, there seems to be a heck of a lot of control features enabling you to post fairly nifty blog messages via email.

Not having a lot of luck getting Cronless to work but I’m hoping I can trigger the wp-mail.php from my mobile which would suffice as a temporary measure.

What no Status Bar?

The status bar is a jolly handy part of the excel spreadsheet window. I’m sure you use it all the time and would miss it terribly if it disappeared. Well I do and it did! It’s easy enough to get back provided you know what its called but how many of you can point to the status bar right now?

So the status bar is right at the bottom of the spreadsheet window, it acts as a sort of message bar but also has some great short cut features. It enables you to perform a single calculation on a range of highlighted cells – so typically you would select a few cells and then read the Sum of the values on the Status Bar. If you right click on the status bar you can select a host of different calculation such as min, max, average ……

Looks like the status bar is even more whizzy in Excel 2007 as you can select multiple calculations to show up for each selection.
Anyways, if you lose the status bar, head to the View menu and make sure the Status Bar option is ticked. Easy, but only if you know its name. It took me ages to find it as I had to wade through about a 1000 google results for the query: “Sum selected range in bottom of excel screen”.

Status Bar

Values Stored as Text

One of my work colleagues is driving me nuts! On an almost daily basis he drags me over to his desk to correct the exact same problem, he thinks his VLOOKUP isn’t working properly but it is always the case, that in one sheet, his data is stored as text.

I can see the problem from across the room as his spreadsheet is littered with little green arrows informing him “value stored as text”!!!!! The exclamation marks are mine but by golly they are needed. I go across, convert his dodgy column to values and Bobs your Uncle, the VLOOKUP starts spewing out the answer.

This is going to keep rearing its ugly head for him, it seems to be an occupational hazard that data from certain sources will be interpreted as text by Excel. My problem is how to find the simplest technique of converting all these figures back to values so that he can start doing it himself. These are all the tricks I use:

1. Insert an additional (temporary) column to the right of the column containing the values formatted as text. Enter the formula =VALUE(cell ref). Copy this down to the bottom so you have a new column containing the text as values. Copy the new column and paste over the dodgy column using Paste Special As Values. Delete the temporary column. This is my method but my colleague isn’t loving it.

2. Enter the value 1 in an empty cell, select and copy. Now select the range of cells to convert to numbers. Choose Paste Special from the Edit menu, check the Multiply radio buttonand click on OK. This automatically forces Excel to replace the contents of the cell with the numerical equivalent of the text that was previously there. Quite a neat trick I think, perhaps I will try him with this one.

3. Another method is to use the Text to Columns option in the Data menu. This throws up the sort of dialog box used when importing text or csv type data. It works but you have to be careful if your column to be converted has any extraneous spaces or tabs which may result in data to the right of the affected column to be overwritten.

4. Final tip. The liitle green triangles in the top left of the cell, inform you of the format error. They also offer the option of “Convert to Number”. You could go through 1 by 1 clicking this option or if you are a bit nifty you can highlight the affected figures, hover over the error message and then select the “Convert to Number” option. This is definitely the easiest option but I often find it a bit fiddly to get the message to appear.

Value stored as text