Spreadsheeting Home Page
Excel Shortcuts and Other Tips
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.
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.
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.
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
then choose Custom from the list you now see whichever method you chose, click on the Type area you can see and delete whatever you see there, could be the word "General"; now type in
and click OK. Hey presto your one hundred Splodgee now become Sp100. More than that: if they are positive Splodgee, they will appear in blue and if they are negative Splodgee they will be in red and have a minus sign automatically placed in front.
Format this: [Blue]"Sp"#,##0.00;[Red]"Sp"-#,##0.00 and you get Sp100.00
Format this: [Blue]"Sp"#,##0;[Red]"Sp"(#,##0.00) and you get Sp(100.00) for a negative value.
It's worth playing around with formatting. We use it a lot as we design and develop our spreadsheets: they can make your work a lot more meaningful without any doubt.
Excel really is a powerful tool to use in your mathematical, statistical and accounting work. Here we have seen just a few examples of where we can make our work more efficient and, dare we say, more exciting! Learn the shortcuts and tips you've seen here and your world will smell sweeter without doubt!
© Webmaster Duncan Williamson 2003