Duncan Williamson: Excel Shortcuts and Other Tips xlshort.html

The purpose of this page is to let you in on a few useful shortcuts and other tips that you might find useful as you learnt o use Excel spreadsheets. This page contains a table of shortcuts: these are the ones I use just about every time I use Excel: give or take; and then I have added a few other useful odds and ends that should improve your efficiency a bit.

Shortcuts

The way to use the following table is to identify the action you would like to take, such as making the contents of a cell or range bold; or to Copy the contents of a cell or range. Having identified the action to take, look at the shortcut and then take that action.

For example, to make something bold, select whatever it is and the press the Ctrl key and keeping it pressed, press the letter B key ... now let go of both keys and hey presto it's BOLD.

Some shortcuts are simple, such as bold, italics, underline and so on whilst others are more complex, such as saving a range or an object as a picture.

Remember the golden rule though: try, try and try again! These shortcuts are well worth learning.

Action Shortcut

Bold Ctrl+B

Italic Ctrl+I

Underline Ctrl+U

Save Ctrl+s

Print Ctrl+p

Cut Ctrl+X

Copy Ctrl+C

Paste Ctrl+P

to top (home) of sheet Ctrl+Home

to end (last active cell) of sheet Ctrl+End

first sheet to second Ctrl+PgDn

second sheet to first Ctrl+PgUp

move right one screen on same sheet Alt+PgDn

move left one screen on same sheet Alt+PgUp

Go to a cell F5 … enter destination

Save a cell, range or objects as a Picture Press the Shift key and keep it pressed then press Edit+Copy Picture and press OK when the dialogue box appears

Select an entire row Press the Shift key and keep it pressed then press SpaceBar

Select an entire column Ctrl+SpaceBar

Select entire sheet Ctrl+A

Find names of all sheets in a file Right Click on the Sheet Navigation Arrows

Edit cell contents F2

Enter the SUM function Click on the Sigma Icon

Split a cell Like this Type line one then Press Alt then press Enter

Open a new, blank, file Ctrl+N

Open an existing file Ctrl+O

Undo previous action(s) Ctrl+Z … keep pressing to undo 1 then 2 then 3 actions or as many actions as you like

Redo previous action(s) Ctrl+Y … keep pressing to redo 1 then 2 then 3 actions or as many actions as you like

Find Help F1

Exit Alt+F4

Manual Calculation F9

Go to top, bottom, left or right of a range Ctrl+Arrow Up or Down or Left or Right as appropriate

Set area for printing Select the area then Alt+F+T and then press Enter

Print preview Alt+F+V and then press Enter

Use the paint brush to copy a format several times Double click the paintbrush Icon and then click, click!

Copy an entire sheet EITHER: Ctrl+A and then paste to another sheet OR: Click on Sheet Tab and drag it to your chosen destination then let go of tab

A Really Simple Bar Chart

Impress your friends with this one! Rather than having to set up an entire graph with loads of formatting wouldn't you sometimes like to be able to draw a very simple bar chart ... like this one:

How do we do that? Just enter the following function as shown here, copy it down and Bob's your Uncle.

You can use the "|" symbol or any other letter, symbol or number you like as the character to repeat. We think that it looks more like a bar chart when we use the | (it looks like a stretchy colon on the keyboard).

This REPT function simply REPEATS whatever you tell it the number of times you tell it to. In this case, we have said repeat the "|" symbol 6 times in cell C3, 9 times in cell C4 and so on and we got those numbers from cells B3 and B4 respectively. All clear? Piece of cake, eh?

Hang on a minute, though, how did you get red and blue bars, I didn't?

You spotted the conditional formatting then. Isn't that a nice touch? Here's what I did to get the bar chart to look like that:

Select you new Chart and Format Conditional Formatting. Then do what we did, as you can see here and click OK:

Conditional Formatting can be absolutely magical and we recommend that you learn how to use it. Unfortunately, you can only set three conditional formats at any one time: this means that you can only get four different formats to be set. What? Four? You said three ... think about it 007!

Conditional Formatting by Itself

Well since you asked, let's look at conditional formatting by itself, then!

Imagine we had a set of results that we needed to analyse to tell us, eg, how much of a bonus to pay, whether we had found errors ... whatever you like. We have four ranges to program:

Here are the data we have collected, in the range A17:C21:

Why not have a go at programming the conditional format yourself?

Did you get this?

Notice that this time we use the Cell Value Is Condition but before we used the Formula Is Condition. Again, not difficult to understand, but it might take a bit of try, try, try again!

Ah, now we can see where the four conditions we mentoned in the previous section comes from! The fourth condition isn't really programmed, is it: it's whatever's left if the three conditions don't include every value we can get.

This is what it all looks like now that it's been conditionally formatted:

Make sure you can see the four different formats!

Note, you can set up conditional formatting as many times as you like on any one sheet, and the three conditions limit applies to any one range.

Custom Formatting

Did you know you could create your own formatting rules in Excel? Imagine, for example, that you live in a country that has a currency that Excel has never heard of ... let's call it the Splodge, Sp for short. So you want to enter how many Splodgee there are to a Pound in your spreadsheet but you know that if you just type Sp100 in a cell, Excel won't know that it's a number and not a word: it will think it's a word because it begins with Sp.

Do this, then:

Enter 100 in any cell you like then you can either