Tuesday, 31 March 2015

Excel Tip: You don't know what you don't know - dollar signs revisited

We're all learning, all of the time, when it comes to Excel. This week, I thought I would cover a topic I've covered before, but from a different angle.

Using the dollar sign in an Excel formula is one of those simple, but powerful, things that I constantly find that people have different layers of understanding of.

Part of the problem, is that once we think we know something, we are much less likely to learn any more about it.

I see this regarding the dollar sign all of the time, and although there is nothing complicated about it, I typically see four different levels of understanding. There may even be a fifth, as I'm still learning too!

As this is such a powerful tool, you might want to check to make sure that you're not missing any of them.

Level One
"I've seen them in other people's formulae, and wondered what they did."

I would certainly recommend reading my earlier post on them, as you are missing out on a really powerful (and simple) tool.

Level Two
"I use them to fix a reference to a cell, for when I copy a formula"

Yes, you can fix a cell by using two dollar signs, e.g. \$A\$1, but did you know that each dollar sign has its own purpose. The one before the column fixes the column, and the one before the row fixes the row.

So \$A1 will always refer to column A (and the row will change relatively), whereas A\$1 will always refer to row 1 (and the column will change relatively).

Again, I would recommend reading my earlier post for a fuller explanation.

Level Three
"I use them in almost every formula to fix cells, rows or columns. I don't know what I'd do without them!"

I was at this level for years. In fact I was at this level when I wrote the blog post referred to above! Then one day, I was looking over a client's shoulder and saw the dollar signs changing at the press of a key. It was one of those "Hang on! Show me what you did there." moments.

Using the function key F4 while on a cell reference in a formula (whether it already has dollars or not), will toggle through all of the options, e.g. if the reference is A1, then repeatedly pressing F4 will have the following effect:

First press:   \$A\$1
Second press:   A\$1
Third press:   \$A1
Fourth press:   A1

Keep pressing and it will go through that loop each time.

When I discovered this, I wrote a follow-up post. Keep learning!

Level Four
"I press F4, probably more than any other key!"

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 24 March 2015

Excel Tip: Exclude hidden rows from total

Although it is worth noting the dangers of hiding rows (see this earlier post for a particularly embarrassing example), they can be useful. If you do use them, you may want any totals on the sheet to exclude the hidden rows, so that the data presented is consistent (i.e. so the total is the total of the numbers you can see).

Fortunately, Excel provides a useful tool for this.

Normally we use the SUM command to add the values in a range, so if we wish to add up column A, rows 1 to 100, then we would use:

=SUM(A1:A100)

This is great, unless we hide row 30 for some very good reason!

If someone was to manually check your sum, they would find that the total wasn't correct (given the numbers they could see that it was supposed to be adding up). This is because the SUM command will include the hidden row.

We can, however, use a different command to do this that will exclude the hidden row. This command is SUBTOTAL.

=SUBTOTAL(109,A1:A100)

will do the same as the SUM command above, but ignore the hidden rows in the total.

I know you're thinking "Where did that 109 come from?" (like many a Battle of Britain Spitfire pilot!)

Well in this case it's not the Luftwaffe, but part of the workings of the SUBTOTAL function.

This first argument can be one of 22 numbers in the ranges, 1 to 11 and 101 to 111.

The numbers 1 to 11 cause the function to mimic the following Excel functions:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

So,

=SUBTOTAL(9,A1:A100)

would work almost identically to our SUM function, except for the following two subtle differences:

1. It will exclude rows hidden by Autofilter
2. It will also exclude any other SUBTOTALs in the range
This will not, however, exclude rows hidden using the Hide command. That's where the numbers 101 to 111 come in. These work exactly the same as numbers 1 to 11, but this time also exclude hidden rows using the Hide command.

Hence our formula above:

=SUBTOTAL(109,A1:A100)

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 17 March 2015

Excel Tip: Generating Random Numbers

In last week's post about an online random name generator, I mentioned that you could use the RANDBETWEEN function if you wanted to generate random numbers instead.

I was also surprised to realise that I hadn't already written a post on this that I could link to - so I am remedying that this week.

The RANDBETWEEN function generates random whole numbers and is really simple to use. Its syntax is as follows:

=RANDBETWEEN(bottom,top)

where bottom and top are the lowest and highest numbers that you want to generate.

So, you could generate a random number between 1 and 10 using:

=RANDBETWEEN(1,10)

or between -100 and 100:

=RANDBETWEEN(-100,100)

or if you want to generate a decimal (2 decimal places) between 1 and 10, you could use:

=RANDBETWEEN(100,1000)/100

Note that the formula will recalculate (and therefore generate a new number) every time Excel calculates. With automatic calculation on, this will be every time you edit a cell on the sheet.

Often I use the function to generate a series of random numbers to use as sample data, in which case I will usually copy them and paste them as values over the formulae to fix the numbers.

You could also combine the function with INDEX to select random items from a list.

=INDEX(A1:A10,RANDBETWEEN(1,10))

will randomly return the contents of cells A1 to A10.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 10 March 2015

Excel Tip: Handy (and free) online tool for generating test data

Just a short post this week to tell you about a very handy little tool I found online.

If you develop as many spreadsheets as me, you will come across many situations where you need to enter some sample data into a spreadsheet to test it.

This can be pretty easy if it is numbers (you can use an excel function such as RANDBETWEEN, for example), but if you need a list of names, this can be a bit more tedious.

Aside: I've just realised that I don't have an earlier post on random number functions to link to in the above pararagraph, so expect to see that in the next few weeks!

For names, I normally start making them up, but this gets harder than you think once you have exhausted Fred Bloggs, John Smith, etc! It's also not the best use of my time!

I had a spreadsheet last week where I had to test it with the names of around 50 employees, which I had to make up. I thought a quick Google might be a better idea - and I came across a simple, yet ideal, on-line tool.

The perfectly titled listofrandomnames.com fitted the bill perfectly.

Within a few seconds I had 50 names to paste into my spreadsheet. I have to say that they weren't necessarily everyday names, but that just made them a bit interesting. Who wouldn't be impressed by a moniker as splendid as Sheridan Wolfenbarger?

There are a few quick choices to make:

• 5, 10, 20, 30 or 50 names?
• Male, Female or Both?
• First Names Only or Full Names?
• And even whether you require them to be alliterative!
Then the site generates your list on screen. You can choose then to spit it out as a pdf or even have the names sprinkled through a lorem ipsum filler text. For Excel, however, the most useful option is to List in a Text Area. From here you can simply copy and paste the list into your spreadsheet! Voila, no more pondering over made up names!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 3 March 2015

Excel Tip: A quick way to add a prefix or suffix to all cells in a list

Do you ever have those situations where you need to make an amendment to the text in every cell in a range?

I know it is an issue I come across regularly. I may need to prefix all nominal ledger codes with a department reference, say, or maybe add "/1" to all existing codes in a list, so that we can add new variations later of /2, /3, etc.

We could obviously rattle down the list, hitting F2 and pasting the additional text at the start or end of each cell. However, we can be a little cleverer than that.

Say we have the second situation described above and want to add "/1" to every cell in the list. We can do this as follows.

If the list starts in cell A1, we could put the following formula in cell B1:

=A1&"/1"

This earlier post explains how to use "&" to join text together, as well as some other useful methods for manipulating text.

If we copy our formula down the whole list, column B will show the new text. We can then copy column B and paste it into column A. We will, however, need to do this using Paste Special, As Values. The easiest way to do this is to copy as normal but when pasting, right-click cell A1 and click the clipboard with "123" on it.

This will paste the results of the formula in column B (as opposed to the formula itself) as text into column A (replacing the original text). We can then delete column B (which will now be showing "/1" twice in each row as it is being added to the new text in column A).

We can use the same technique with different versions of the formula to make any number of amendments to the text.

Say we want to add a department code before the code in column A and separate the two with a hyphen, we can enter the department codes in column B and enter the following formula (in column C):

=B1&"-"&A1

Use this technique with other text manipulation functions, from the post mentioned above, to make pretty much any change you need.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".