Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

'Be prepared to be amazed' - Microsoft's Excel tips

by
27th Oct 2005
Save content
Have you found this content useful? Use the button above to save it to your profile.

At a recent IRIS user conference in London, Microsoft Office presenter Simona Millham wowed the crowd with a half-hour session on some of the less well known features in Microsoft Excel. This article presents some of the highlights.

Some of Millham's tips have already been documented by AccountingWEB members in our Excel navigation tips Expert Guide, but she still came up with a few new tricks that will delight regular users. As Millham told the audience, "Be prepared to be amazed." A document containing all of her tips is available as a Word download.

Keyboard shortcuts

  • Ctrl Shft # [hash] - Clear number formatting - "Comes into its own when you don't know what's wrong with a cell."
  • Ctrl Enter - Fill selection with entry - useful if you want to populate a block of cells with dummy data
  • Alt= - Insert AutoSum
  • Ctrl Page Down - Move to next sheet
  • Ctrl Up/down/left/right arrow - Move to end of entered data
  • Ctrl ;[semi colon] - Insert current date
  • F11 - Instant Chart
  • F4 - Convert references to absolute
  • Ctrl Shft 8 - Select region

    AutoFit Column Width
    Double click the dividing line between columns in the column heading to automatically size the column.

    Format Painter
    The Format Painter is used to copy formatting (text, number and cell) from one cell to another and is excellent for formatting troublesome cells:

  • Select the cell with the formatting on that you want repeated
  • Click on the Format Painter button (the paintbrush icon) and double click if you want to apply formatting to a number of non-adjacent cells. The Format Painter also works in Word and PowerPoint
  • Select the cells where you would like the formatting to be repeated.

    Fast Data Entry
    Select a range and enter data, moving through the range by either pressing the Enter key (to move vertically) or the tab key (to move horizontally).

    Searching for Functions
    There are hundreds of Excel functions. To search for a suitable one to use, click the little fx button in the formula bar and enter in the dialog box which appears, type in the type of function you are searching for.

    Named Ranges
    Named ranges are used to quickly navigate a spreadsheet, and in some Excel functions:

  • Select the area to be named
  • Click in the Name Box (just above the column A heading)
  • Type in the name (one word only) and press Enter
  • To quickly go to a named range, click on the down arrow of the name box and choose the name.

    IntelliMouse
    If you have a mouse with a wheel, roll it to scroll through the spreadsheet. Hold down the control key while you roll, to zoom. Click and drag the wheel to pan a large spreadsheet

    Charts

  • Create an instant chart by selecting the required text (include your titles but usually not your totals) and press the F11 function key to enter "charting heaven"
  • To select cells that are not next to each other, click on the cells with the Control key held down
  • Don't forget that you can copy and paste an additional series in later, as long as your new data selection is the same shape as the original chart data. For those who are interested in knowing these things, Microsoft insiders refer to the cell highlighting indicators as "marching ants", or with Millham's preferred term, "tingles".
  • Experiment with chart types, and different fill effects for your data.

    Lists
    To make a list easier to work with, try applying an AutoFormat. You'll find it in the Format menu. Click anywhere in the column you want to sort by, and click the required sorting button:

  • Having sorted, you might find it useful to add Subtotals. Find this option in the Data menu.
  • To summarise a list, create a PivotTable or PivotChart. Click in your list and find these options from the Data menu.
  • Filter your list by choosing AutoFilter from the Data menu.

    Hidden Button Faces
    This is useful if you want to customise your toolbars and cut down on the number of buttons that you have showing. Hold down the Shift key and click the Print button ' it will change the to the Print Preview button, and vice versa. There are other pairs of buttons too.

    Subscribe to the ExcelZone newswire
    To keep up with all spreadsheet developments, click the button below to subscribe to the free fortnightly ExcelZone newswire. The system will take you back to the AccountingWEB home page after it adds your name to the ExcelZone subscription list.

    Subscribe to the ExcelZone Newswire

  • Tags:

    Replies (8)

    Please login or register to join the discussion.

    avatar
    By David Carter
    14th Nov 2005 11:54

    some readers are nervous
    Stephen, don't want to sound bad-tempered etc, but in a recent thread a lady has said she is a bit scared of asking a question on Any Answers because she is worried she might be "getting flamed".
    We don't want to put people off from asking simple questions because they feel it will make them look stupid.

    Thanks (0)
    avatar
    By stephen.smith.ramesys
    14th Nov 2005 16:52

    David, I totally agree with you!
    My issue with the article was that it was entitled "Be prepared to be amazed". I honestly don't think many users of Excel would be amazed. They would be amazed when they see a pivot table for the first time but not when they see a keyboard shortcut (in my opinion). The headline drew me into reading the article and I was disappointed with the content.

    I don't think my comment would put off anybody from asking a question. Apologies if it did!

    Thanks (0)
    avatar
    By David Carter
    11th Nov 2005 16:18

    It's all useful stuff
    Whenever AW publish something like this, we always got a lot of testosterone-filled males jumping in and saying "Cor, don't you even know that!" or the like.

    The fact is, however, that most of us have to learn Excel from others as we go along. When people share their knowledge like this there is almost always at least one new tip you didn't know, and that makes it worth while. David Carter [IT consultant editor]

    Thanks (0)
    Dennis Howlett
    By dahowlett
    01st Nov 2005 03:45

    Are you serious about this?
    It's good to see 1000+ readers are sufficiently curious to see how Microsoft can 'amaze' the author. I got bored real quick with reading about stuff I thought most people knew.

    Microsoft hasn't had an original idea in its collective brain since Bill Gates did the licesning deal of the last century and consigned IBM to a miserable existence when it comes to dekstop and laptop machines. But apart from that, isn't it about time practitioners thought about amazing their clients?

    Thanks (0)
    avatar
    By stephen.smith.ramesys
    28th Oct 2005 13:16

    Totally agree with Alan!
    Can't believe many people would be amazed by these tips. They are mostly pretty basic.

    Thanks (0)
    avatar
    By Alan Davies
    28th Oct 2005 11:39

    amazed!!
    I'm amazed that most people aren't already using most of these - if people actually were amazed may I suggest a little training may be required.

    I am not entirely convinced by keyboard shortcuts either - its usually far easier to use the mouse as this is what you hand is normally on anyway. This may just be my preference though.

    Thanks (0)
    avatar
    By AnonymousUser
    28th Oct 2005 15:09

    download
    my download does not work either

    Thanks (0)
    avatar
    By a.nesbitt.forrester-boyd
    28th Oct 2005 11:17

    Broken link?
    The link to the Word download is not working on my computer. Is this me or you?

    Thanks (0)