Not really Excel...

...but related, since it concerns Data Visualisation.

Before you hit that charting button next time, you better take a look at these first!

Blogs
Moritz Stefaner
Infosthetics

Sites
What have I been listening to? from Lee Byron
America by Numbers
Excellent talk from Hans Rosling on 3rd world myths

Counting and Summing on Multiple Criteria

Many of you will have wanted to use the COUNTIF and SUMIF functions on multiple columns but will have been annoyed about the necessity to introduce very long formulas or additional concatenation columns. This excellent hack allows you to perform COUNTIFs or SUMIFs on multiple columns using the SUMPRODUCT function. Credit to Charley Kyd over at ExcelUser for explaining this one:

SUMPRODUCT hack at ExcelUser

Count Unique Items

This is a phenomenally simple way to count the number of unique items in a column. I have seen some ridiculously long formulas for this in the past and this beats all of them:

{=SUM(1/COUNTIF(rngToCheck,rngToCheck))}

How simple is that?!

NB: This is an array formula, so don't actually type the curly brackets - they will appear when you enter the formula by pressing Ctrl+Shift+Enter all at the same time.

Example

Take the situation here:

Note the lack of curly brackets - DO NOT TYPE THESE IN.

Now, press Ctrl+Shift+Enter to enter the formula, and the curly brackets will magically appear:

Fill Down

What I mean by "Fill Down" is to take the contents in one cell and quickly fill the rest of the column below that cell with the same value. You are many examples of when it is useful to do this, but copying a formula is certainly one of them.

Lets say you have to folling table of data, and have just written a formula in cell D2:


The first thing you could do is copy the cell (Ctrl + c), select the range you want to copy the formula into and paste it in (Ctrl + v).

But what if your column of data is very large? You don't want to have to select the whole range by hand. What you can do is this: put the curser on the cell D2. Move your mouse pointer so that it is over the bottom right corner of the cell. A smaller black "+" will appear. Now double click the left mouse button. Excel will automatically fill every cell in column D, down to the point where there is a break in the data in column C.

This is fine if there are no breaks in the data in colunm C, but what if you have the following situation?

Here, the table of data goes down to row 13, but there is no entry in column C on rows 7 and 11. If we use the double-click method above, only D2 to D6 will be filled.

We can get round this by using the following method:

1. Select the range you want to fill the formula over. You must start your selection with the cell in which you have your formula to be copied. This is an important point - you will see why in a minute.
   
   
2. Press the F2 button on your keyboard. This has the effect of opening the "active" cell for editing. You should see the cursor in the cell.
3. Now, press the Ctrl + Enter buttons at the same time. Whatever was in that first cell is now in all of your cells.
   

You may be wondering "If that just copies the formula, then I'll just have C2*B2 in all my cells. Not very useful." Not true. Excel automatically changes the contents of your formula as it goes down the page. The references to the cells C2 and B2 that are contained in cell D2 are known as "relative" references, in that they are relative to cell D2. If you look in cell D13 you will see the formula C13*B13.

[More on "relative" and "absolute" cell references to come...]

Keyboard Shortcuts

Did you know...you can completely control your computer with your keyboard! Before mice were invented, this is exactly what people had to do. And you still can. How exciting.

But why would you want to give up (or at least reduce) the use of your mouse? Well, it's all about speed. With a mouse, you have to

• find the pointer on the screen
• find where you want to click
• move the mouse and visually check that it is where you want to click
• click.

The reason for the visual error checking is that the mouse could be anywhere and could go anywhere on the screen, but you need to make sure it is over that particular little spot you want to click on. And to acomplish one action, you usually have to go through several menu options, so have to repeat the cycle several times.

Your keyboard does not move, however, so there is much less hand-eye-screen checking to do, so it is faster. And I have the science to back it up! If you repeatedly perform the same actions, your muscles remember how to move. This is how you are able to drive without looking at your feet - the pedals don't move so after performing the actions of accelerating, breaking and changing gear for a while, you can do so automatically, without even thinking. This is known as muscle memory.

Now, this isn't some masochistic exercise in committing to memory long lists of keystrokes. Yes, it will take a while to get used to them, and to stop reaching for the mouse, but once you've learned a few, you will find they are much, much quicker than using the mouse.

A word on notation:

• if I use the "+" sign, it means press these keys at the same time.
• if I use the "," sign, it means press them one at a time, in order.
• if I use the "[]" signs, it means you have more than one choice - I hope it is clear from the context what that choice is.

General shortcuts (these are true of all Office applications, and many other non-Microsoft applications, too)
Cut: Ctrl+X
Copy: Ctrl+C
Paste: Ctrl+V
Undo: Ctrl+Z
Redo: Ctrl+Y
Save: Ctrl+S
Print: Ctrl+P
New workbook (or new document in Word, new Email in Outlook, etc): Ctrl+N
Find: Ctrl+F
Replace: Ctrl+H
(note that with Find and Replace you can choose to look in either the cell value, or the cell formula, and can search the sheet you are on, or the whole workbook)

Select...
Select entire row: Shift+Space
Select entire column: Ctrl+Space
Select all: Ctrl+A
Select all contiguous cells: Ctrl+Shift+Space
Select multiple cells: Shift+[arrow key]
Select contiguous row, column, or both: Ctrl+Shift+[combination of one or more arrow keys]
Jump to next populated cell, or last cell in contiguous group: Ctrl+[arrow key]

Insert...
Insert row above: Alt,I,R
Insert column to left: Alt,I,C
Insert AutoFilter: Alt,D,F,F
Sort data: Alt,D,S
Create PivotTable: Alt,D,P
To insert a copied cell over a range, first select the range, the press Ctrl+Enter
(this one is very useful)

Right mouse key (RMK)...
(many of you have a right mouse key on your keyboard - looks like a menu with a mouse pointer over it: )
Paste Special: RMK,S,
Paste Special (Values only): RMK,S,V,Enter
Paste Special (Formulas only): RMK,S,F,Enter
Paste Special (Values only, and transpose): RMK,S,V,E,Enter
Delete (entire rows/columns, first select them): RMK,D

Other...
Format cells: Ctrl+1
Create a chart: F11
(this is great for quickly checking a series of data)
Edit cell contents: F2
Repeat an action: F4

If only

Making decisons on what course of action to take is something we do almost every second of the day. It is one of the things that makes us intelligent and adaptable.

A simple example one might make of a Saturday afternoon:

"If it's sunny I'll go to the park. Otherwise I'll go to the pub."

Computers, wanting to imitate intelligence, also have a process for making decisions: the IF statement. Broken down into computer jargon, it looks like this:

"If expression is TRUE, then do statement1, else do statement2.

In our Saturday afternoon example

• expression = "It is sunny"
• statement1 = "go to the park"
• statement2 = "go to the pub".

The IF statment is implemented in Excel like this:

=IF(logical_test,[value_if_true],[value_if_false])

So with our example, we could write something like this:

=IF(Weather=Sunny,GoToPark,GoToPub)

Now, we probably won't be using Excel to decide what to do with our saturady afternoon, but we might like to use Excel to help us decide if a number is "High" or "Low". Lets assume we have a column of numbers in column C and we want to define them as being either high or low, with a high number being anything over 10. In cell D2 we enter the formula

=IF(C2>10,"High","Low")

First, the logical_test C2>10 is evaluated. Excel looks at what is in cell C2 (lets say that it is the number 6) and checks if this is greater than 10 or not. Since 6 is not greater than 10 the logical_test is FALSE, and the IF function jumps to value_if_false, which returns "Low". This word appears in cell D2. (By return I mean to say that the formula sends the value to the spreadsheet and you see it in the cell.)

If we [fill this formula down] the column, column D will now hold the values "High" and "Low", depending on the value in column C.

Perhaps we want to extend this a little so that a number above 10 is "High", a number between 10 and 4 is "Medium", and a number below 4 is "Low". This could be used as the basis of some trafic-light style display. We can do this by putting a second IF statement inside the first. This is known as 'nesting' funtions' and looks like this:

=IF(C1>10,"High",IF(C1<4,"low","medium"))

Instead of a simple output for value_if_false, we have put anoter IF statement there. So, if the first IF statement is FALSE, the second IF statement is evaluated. Let me write the formula above out in long hand so you can see what is going on more clearly:

If C1 is greater than 10, return "High". Else, if C1 is less than 4 then return "Low". Else return "Medium".

You don't need to explicitly test to see if the number is between 10 and 4, because by the time the functions gets to that part, you have already caught numbers higher than 10 and lower then 4, leaving only... numbers between 10 and 4!

We could now use this new column as the basis for [conditional formatting].

It is possible to nest 7 IF statements in Excel 2003 (64 in Excel 2007). However, past 3 or 4 nested statements, things start to get a little messy and complicated, and (depending on your needs) it may be easier to use a function such as the [VLOOKUP] function, or a [User Defined Function].

--- --- ---

[Note] Items that are surrounded by square brackets in this post are intended as future posts. When I get them up I will link to them.

So what's the point then?

The point is this: Excel can do a lot more than you think it can! It's about as far from being a "fancy calculator" than a Ferrari is from being a Micro Machine.

Millions use Excel every day, and those millions waste many hours each from being ignorant to Excel's varied and plentiful tools and capabilities. If you hate wasting time and would rather not be wrestling with Excel as the sun starts to fade and the offices empty, then this blog is for you.

You will not be wasting your time learning what Excel can do for you. It is one of the most successful, versatile and prolific programs ever written, so any skills learnt are by nature transferable.

Now, sit back, breathe deeply, relax, and walk the path to Zen Grand Mastery.