Such Happiness In Thought Happens

January 31, 2008

Excel time savers

I noticed that at the moment my tips and tricks have focussed on Outlook and Word, so I thought I would give a few tips in regard to Excel that you may or may not find useful. These will mainly relate to calculations, but there will be a few other things as well.

I decided to do this as well as I taught a friend on the weekend a few things about the new version of Excel (Excel 2007) and I want to make sure she has a place to refer back to if she doesn’t use everything I taught her immediately. And then at work today, some of the spreadsheets were having problems so I had to see what was going wrong with them and I showed a few tricks that I regularly use and the user did not about them either.

Alt = (AutoSum)
We all know how useful AutoSum is when needing to add cells in Excel. But did you know that there is a keyboard shortcut to do the AutoSum? Alt+= will fill the current cell you are in with =Sum(Cell Range).
So no need to move your hand to the mouse and click on the AutoSum symbol!

Ctrl ~
This little trick is great for those of you who do a lot of calculations in Excel and you need to quickly see which cells contain formulas, which contain values.
Each cell can be individually selected, and you can then look at the Formula Bar to see what the cell contains. But this tip will change the whole worksheet to show the contents of all cells. Use Ctrl and the ~ symbol on your keyboard. The squiggle is technically called a tilde, and is to be found above the Tab key, to the left of the 1 key.
Use Ctrl ~ to view the contents of all cells. Then use Ctrl ~ once more to change the worksheet back to what it was.

Navigation Keyboard Shortcuts
I mentioned some navigation keyboard shortcuts earlier this week with Ctrl Home and Ctrl End. Here are a few more that are useful in Excel.
Ctrl Page Up – This moves you one worksheet to the left.
Ctrl Page Down – This moves you one worksheet to the right.
Alt Page Up – This moves you one screen to the left.
Alt Page Down – This moves you one screen to the right. ie. view the next column that is currenty off the screen
Tab and Enter – Tab moves you one cell to the right. If you continue to tab to move to the right, when you have finished press Enter. The active cell is now one row below from where you originally started.

F3/F4 Function buttons
If you use Names in Excel you know how useful they can be. If you use a lot of Names in the workbook it can be difficult to remember exactly how they are spelt when using them in calculations. The next time you need to use a Name in a calculation, click on the F3 function button.
This brings up in a new Dialog Box all Names that are to be found in the workbook. Choose the one you want to use and press OK. The Name is now found in the calculation.

Some people may know about the F4 key, or they may know about absolute references. When you copy a formula from one cell to another you use a thing called relative references. Sometimes you don’t want that to work. For example a list of employee wages and only one cell with the tax rate; grocery items and GST component, etc. This is when you want a part of the calculation to be fixed (absolute), not relative. To achieve this you put dollar signs ($) as part of the cell reference – for example $A$1. What some people aren’t aware of though is that by pressing the F4 function key when in their formula, it will put in the dollar signs for them.

For example write the following into a spreadsheet:
Product     Price ex GST   GST Amount    Price inc GST
Monitor     $399
Keyboard/mouse $29
Computer   $1254

Somewhere else in the spreadsheet write in one cell GST, and in the cell next to it 10%. Then when you write the calculation for the Monitor GST amount, you would have =A5*D5. Immediately after clicking on the cell that contains 10%, you hit the F4 function key. Now you have $D$5. Then when you copy the formula down (see more below on this), all the calculations will use only cell D5, not E5, F5, etc.  

Double Click on autofill
In the above example I have a number of cells that require the same calculation of Price ex GST * GST. The quick way of doing the same calculation is to drag the little square in the bottom right hand corner of the active cell (the black lined cell) down to the other cells that require the same calculation. What some people don’t realise though, is that a quicker way to achieve the same result is to double click on the little square in the bottom right hand corner of the active cell.
The double clicking works when you have a reference to the left, that is consecutive cells one underneath each other, that the calculation will be going beside. You double click, and the cell contents are copied down to the other cells extremely quickly.

How to get 000 to show at the start of a number
Sometimes you will want to write a number of numbers into a cell that start with zeros, for example employee numbers, part number, etc. Unfortunately when you type 0001258, when you leave the cell you get 1258, the starting zeros have disappeared. This is meant to happen. If you went to Format Cells on that cell, it would say either it is using the General Number format or the Number Number format. Numbers cannot start with zeros.
So how do I get the zeros at the start of the number? 2 ways –

  1. Start your typing with an apostrophe (‘) then continue with the number.
  2. Format the cell or column with the Text Number format. ie right click on the cell or column, go to Format Cells. Select the Text option under the Number tab.

Both these methods will result in a little green triangle in the top left hand corner of the cell if you are using Excel 2002, 2003 or 2007. This is a warning to inform you you may have a potential problem. Either ignore the symbol, or click on the yellow diamond with an exclamation mark, and from the drop down list choose “Ignore Error

Create a chart quickly
And to finish up with I would like to share the secret of creating a chart quickly without needing to use the Chart Wizard. You highlight the cells you want to have in a chart, then press the F11 function key. Wala – in a new sheet, you will have a 2D Column chart of the data you highlighted. If you want to make modifications to the chart, keep right clicking – right click on the series, right click the plot area, right click outside the chart and you will have the option to change the chart (Chart Type), the data to generate the chart (Source Data), options for the chart like where the Legend goes, what title you would like (Chart Options), and lastly where the chart should be located (Location)

I hope that has given you some new tricks to try out the next time you are doing something in Excel. Leave a comment with which ones you use, a question, or your own quick tips for Excel.

Advertisements

4 Comments »

  1. When you say F4 function are you talking about the top of the keyboard? my F4 is labled NEW when I use it nothing happens I’m try to insert the $ into a cell. Any answers for this?

    Comment by margaret — June 11, 2008 @ 1:01 pm

  2. Hi Margaret,
    Yes I am referring to the function button at the top of the keyboard. And it may be labelled with the word New, or in my case my F4 button has the word Save.

    If you press the button and nothing happens, your keyboard has been programmed to do the action of the wording. To access the normal controls for the function keys you need to turn off the function button. There will be a button somewhere on your keyboard, normally at the top, that when you press it your function keys revert back to normal.
    I also know this works when one of my lights disappears on the keyboard.

    So Margaret to use the F4 key you will have to press another button each time you turn on your computer, that will deactivate the function wordings, and make the function keys revert back to normal and then F4 will work again.

    Duane

    Comment by Duane — June 11, 2008 @ 5:05 pm

  3. Hi Duane
    This is not really an Excel question but applies to various applications, but it might be related to Margaret’s question about the function keys. When I hit the print screen button and try to copy whatever’s on the screen onto a wordpad or word document, it doesn’t work. Usually, the copy and paste features are not even active. Is this a problem with my keyboard, computer, or just a wrong setting.
    Thanks a lot
    Glenda

    Comment by Glenda Williams — September 29, 2009 @ 10:03 am

  4. I am happy I found your site on facebook. Thank you for the sensible critique. Me and my husband were just preparing to do some research about this. I am very glad to see such reliable info being shared freely out there.
    Best Regards,
    Dru from Orlando city

    Comment by Alexis — February 24, 2010 @ 11:18 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: