Australian Tips

Posted on by Dick Kusleika

I’m giving a 90-minute presentation next week entitled Tips & Tricks. I’m not much of a tips guy as I prefer deeper discussions. However, tips presentations simply can’t beat because they allow me to combine disparate items. Nobody would care if I jumped from keyboard navigation to array formulas in a tips presentation. They probably expect it.

I have 35 tips prepared and I’m pretty happy with the list. The crowd will be intermediate to advance Excel users, so I won’t be showing them how to paste special. Oops, now I only have 34 tips. That’s less that three minutes per tip. My fear is that I’ll run out of tips before the 90 minutes. I’ll panic and start singing Streisand or something.

So if you have the time, throw me one or two of you favorite, lesser-known, Excel tricks.

Posted in Uncategorized

77 thoughts on “Australian Tips”

  1. Although its pretty basic, its amazing how few people know or my favourite trick:
    Selecting the current region using the CTRL * keyboard shortcut.

    The Current Region is defined as the range starting from the active cell and extending in all directions until an entirely blank row or column is found.

    In VBA the CurrentRegion property of a Range object allows you to identify the same range.

    Related tip: In order to make best use of Current Region, avoid blank rows or columns in you worksheet table. Instead increase row height or column width if extra space is required.

  2. The behaviour of Ctrl+A has changed from Excel 2003…

    Prior to Excel 2003 – Ctrl+A : selects the entire sheet
    From Excel 2003:

    If you select a cell in a table and do Ctrl+A – It first selects a current region (same as Ctrl+*)

    If you say Ctrl+A again then it selects the entire Sheet.

    If you select a cell outside a table and Do Ctr+A it selects the entire sheet….

  3. @sam:

    except (as I found the other day) when it doesn’t…

    Try this example:
    1) Start with a blank sheet
    2) Type something in A3
    3) Select A2
    4) Hit Ctrl+A
    A2:A3 is selected (ok.. that’s unusual, but ok)
    5) Hit Ctrl+A again
    … nothing happens – no selection of the entire sheet again

    Can anybody explain?

  4. dynamic range names
    automation with data tables (eg to merge hundreds of data records through a single calculation sheet)
    adjusting standard format styles, eg formatting zero’s to show as blank

  6. Dick,

    Will you be posting your presentation online? I am sure many of us would love to see it.

  7. 1. Holding the ALT key while dragging an object (for example a chart) will snap it to the grid.
    2. Holding the ALT key while resizing an object will snap it to the grid.
    Several years of Excel use went by before I noticed these two, they are great when tidying up worksheets.

    Why not present a list of relevant Excel links as a tips..? Be sure to include DDoE, Rond de Bruin, Pearsons and J-Walk to mention some.

    Tips about ASAP utilities, not a day goes by without me using it, and it’s free(!)

    For those into VBA, tips them about “Smart Indenter” and “VBE Tools” (http://oaltd.co.uk). Not sure these work in Office12 though..

  8. If you need a VBA routine to be fast, avoid using worksheetfunction, especially in XL2007. Writing your own function can speed things up by a factor of 10 or more.

  9. My two favorites are:

    1. The camera tool. Most users don’t know about it.
    2. In VBA – passing a range to and array.

  10. Show then how Ctrl+Arrow key (sometimes plus Shift) moves selection round data tables.

    This may be too basic, but in my experience many reasonably competent Excel users don’t know this one.

  11. Quickly select all formulas on a sheet: Ctrl-G, Alt-S-F, Enter

    If you press this combo with only one cell selected, you will get all formula cells on the sheet selected. If you already have a selection you will get the intersection of your selection and the formula cells on the sheet. Very very handy and quick when you want to see which of your cells are static and which are formulas. What makes this a powerful tip is that you don’t really realize how much you need it until you have it as a tool in your toolbox, then you use it all the time, especially if you’re in an analyst role.

  12. Holding Shift when clicking the Edit menu:
    Changes “Paste” to “Paste Picture”
    Changes “Paste Special…” to “Paste Picture Link”

  14. Dick – In a tips presentation, you never have to worry about finishing early. If you make it loose and informal, everyone will want to elaborate on a tip, and they will want to propose their own favorite tip, and people will try to stump you. The 90 minutes will pass in a flash.

  15. I agree with Jon – the time will fly by. You could produce a handout that had your tips on it in descending order of speaking, and that way if you did not cover all of the ones you wanted to – then they would be available to your audience.

    By the way, I would hand the sheet to the organisers at the end of your presentation – there’s nothing worse than presenting and seeing people shooting ahead to different topics to what you are covering.

    HTH & Good luck

  16. Some good ideas, but I like jkp’s – GoTo Special. Similarly, how about Find All, Shift+End then Escape to leave all the found cells selected?

    Or text manipulation with SUBSTITUTE – reliably selecting a surname, or extracting data from a chart formula?

    Or cheat and look in Bob Umlas’s book!!

  17. I would give some cool shortcuts, like ctrl + 1 for all the formatting needs,

    and some advanced things like VBA formulas are in english even though the local of the system might be french or chinese and wont let you enter an english formula in the worksheet… this might be a huge time saver when you encounter that kind of things in a multinational

  18. Also for selection for mouse rather than keyboard users: double clicking the edge of a cell is like ctrl+arrow in that direction.

    My favourite is GoTo Special which has already been covered above a few times.

    Maybe give them a link to David Gainer’s PDF of hundreds of Excel shortcut keys.

    Patrick

  19. Hi All;

    I’ve enjoyed reading these and learnt some extra neat tricks Thanks. I do Training; and depending upon your humour you could throw in Ctrl+Alt+any arrow key.(if your graphics card has the rotation option enabled then depending upon your graphics card the screen flips!(it happened accidentally recently so its good to know how to fix it)

    To enable; Right click on the Desktop/Properties/Display settings/graphics card/ Display settings/ tick rotation and ok.

    I suppose the only useful thing i can add is zooming with the Ctlr-key and turning the mouse wheel! And have you ever table-tangoed in Excel…..google it its neat.

  21. Roger:”Don’t forget Ctrl+tilde to switch to formula view, press again to go back.”

    Doesn’t work for me – “Ctrl + ~” sets the selected cell to general number format (“Ctrl + #” sets it to dd-mmm-yy format).

    Press “Ctrl + ‘” (grave accent) does the trick though…

    I expect that Dick could fill a lot longer than 90 mins if he ran through all the keyboard shortcuts he knows!

  22. I love the Ctrl + ‘ shortcut – it copies the contents of the cell above.

    More generally, though, at least doing a quick runthrough of vlookup? It’s an amazing function, and I’m always surprised when people don’t know how good it is :)

    And I agree that handing out a 1-page list of all the tips you’re planning on covering is a GREAT idea – that way people can jot down a note or two for the tips they like / aren’t aware of yet. And like others here, I’d love you to post that list :-)

  23. I frequently have worksheets with a lot of picture objects. I just recently learned that if you select one of those objects then press Crtl+Shft+Space, all objects will be selected to do you bidding. This, for me, is very useful when carrying a worksheet from one year to another and not having to delete the objects in other, less efficient ways.

  24. How about the many joys of the right mouse button. Right click on scroll bars, or the Sheet Tab Scroll Arrow. Then there is right click dragging a date or a number and the many options that allows. Finally right click and drag and drop a group of cells for paste values or formats and such. I have found that one to be a real crowd pleaser.

  25. Double-clicking the little black box in the lower right corner of the selected cells copies down as far as you have data. @ Peder Schmedling, that’s a handy tip. I actually wrote a macro to do that because it annoyed me so much.

  26. Dissect formula to look why something is not calculating correctly: Select a formula or a portion of a formula. Choose F9. The result will display instead of the formula. CAREFUL: Be sure to ESC out of it-don’t hit ENTER!

  28. @Peder – Yes, Smart Indenter and VBE Tools both work in Office 2007 (assuming appropriate security permssions are enabled to access the VBProject).

    My favourite tip is to type the name of a UDF in the F5 Goto dialog or Name dropdown and be taken to the code in the VBE.

  29. Charles, that’s brilliant! Countless times I could have used that.
    One thing I’ve been doing lately is using autofilter for visual data validation. Like if I have a column of birthdates, I click on the autofilter down-arrow for that column, the birthdays are all chronologically arranged and I can easily see if there’s an error like a birthday in the future.

  30. In Excel versions 2002 and 2003, dragging the Evaluate Formula icon to the Toolbar.
    This makes checking formulae so much easier that having to isoltae parts and press F9.

  31. If you are working with imported numbers that Excel thinks are text (maybe there are spaces on the end, etc.), you can press F2 Enter as many times as you have numbers to “kick” Excel into realizing they’re numbers. However the faster method is to put 0 in a cell, copy it, and select your number range and Paste Special -> Add.

  32. Paste Special -> Transpose is also incredibly useful in certain situations. I have to merge data sets at work quite a bit and sometimes the column orders are different. If you select the headers of one dataset, Paste Transpose somewhere else, and do the same thing with the other set, pasting it beside the original, you can easily see if your columns line up before you merge the two data sets.

  35. Make backups of all work files.

    Save often – get used to using [Ctrl]+S.

    Cross-foot sums in all tables that show column, row and table sums.

  36. Here’s some of my favs:
    – Getting the scroll wheel to work in the VBE (I use VBScroll)
    – +Scroll Wheel for zooming
    – Using Outline controls to easily show/hide support data ranges on a sheet
    – The joys of M-Z Tools (Juan Pablo, I’ll sing in YOUR choir anytime)
    –
    -The Name In Range

  37. continued:
    – Named Ranges
    – Name Manager
    – The Name In Range function from AppsPro ExcelUtilities

  38. Maybe this is what dermot means, but I’ll try anyway:

    Batch processing without VBA, using Data Tables. No restrictions of only one or two variables; just use the record number as variable and you’ll be able to process records as wide as the number of columns minus the number of result fields.
    I do have a recipe available. You’d have to practice to be able to do it in 3 minutes, but it’s very impressive to look at because once you press the last key you see a blank page getting filled with data line by line (very quickly); it’s really animated.

  39. For keyboard fans
    My favourite Autofilter Alt+D+F+F
    Also Ctrl + PgUp / PgDown to move between sheets and Ctrl + F6 to move between workbooks.

  40. One other one I use frequently, hold down the control key and scroll with the mouse wheel to zoom in 15% increments.

  41. Other favorites:

    To repeat last action: F4 key (especially with charts)

    And if sorted items include several of the same name (Column C), then to rank them in Column F:

    =IF(C2=C1,F1+1,1)

    And copy down.

  42. Not many people know this tip Dick

    When you want to start Excel in Safe mode hold the Ctrl key when
    you open Excel and say Yes in the dialog.

  43. Here’s one I haven’t seen mentioned, but it’s particularly helpful for those long formulas, especially nested ifs.

    Use ALT+Enter to get a new line in the formula editor. You can also use spaces to allign items in your formula.

    =IF(A1>B1,1,2)

    becomes

    =IF(A1>B1,
    1,
    2)

  44. Jason – (from many comments ago) don’t put zero into a cell before copying it for paste special – add. Just copy a blank cell.

    Rich – (from just a couple back) “To repeat last action: F4 key (especially with charts)” has been broken in 2007 (especially with charts).

  46. Here’s 10 tips from the top of my head.

    Tip 1:
    I have a column of values, and I want to know how many rows fail a test:
    eg. value must contain either Rabbit or Cow
    =IF(OR(A2=”Rabbit”, A2=”Cow”), 0, 1)
    Fill the formula down, highlight that column and note the Selection Sum in the status bar (bottom right of the window)

    If the result is zero, all tests passed. If not zero, you can them sort descending by that column for the culprit rows.

    Tip 2:
    To quickly count a list of items, just select the cells, but keep your mouse pressed.
    The number of rows appears in the NameBox (upper left, near the formula bar)
    But, if you select more than one page of cells, the number of rows counted becomes a mouse tooltip.

    Tip 3: If you want to save some space on the toolbar, many of the icons can be activated by clicking their pair with the shift key held.
    eg. Left-Align Text becomes Right-Align text with Shift key
    Sort A-Z becomes Z-A with Shift key

    Tip 4: If you highlight some columns, then doubleclick the line separating the column headers, it auto-fits them.

    Tip 5: You should stay away from Merged Cells. Try using Center Across Selection instead, which usually results in the same.

    Tip 6: Charting data labels. You can hide “zero” by setting the data label number format to 0;-0;;

    Tip 7: If your windows regional settings has a dash as the date separator, dates formatted as dd-mmm-yyyy may look like dd/mmm/yyyy on someone elses computer. That’s because Excel recognises the dash as a date separator rather than as the dash symbol. To keep the dash, use the Custom Number Format dd-mmm-yyyy (ie. place a backslash in before the dashes)

    Tip 8: After performing a SubTotals (from the Data, Subtotals menu), you might want to copy out just the totals rows. Adjust the outlines so that only the subtotals are showing, then from the Edit Menu, click Goto, click Special, click “visible cells only”, click OK. Now you can click Copy and it copies just those visible cells.

    Tip 9: To move an entire column to the end of the list, highlight the entire column by clicking the column letter. move your mouse to the selection border edge. Your mouse should change cursor to a multi-headed arrow. Click and hold on the borders edge. Then hold down the Shift Key and drag your mouse to the new position.

    Tip 10: To have a Data Validation Dropdown use a list on another sheet, the list must be defined as a named range.

  47. Tip 11: sometimes you want to copy formulas down the sheet, but if you copy paste, the cells the formulas reference move too.
    Here’s a way of copying the formulas down so they reference the same cells as above.
    Select the cells to copy
    Enter formula auditing mode ( Ctrl ~ )
    Copy
    Open notepad
    Paste in notepad
    Select All in Notepad
    Copy
    Back to Excel
    Select location to paste (just the first cell is fine)
    Paste

  52. When you import numbers as text.
    Edit / Replace / Find 0 / Replace with 0
    You get all the text number’s convert to numbers.

  53. Re: Rob van Gelder’s “Tip 11: sometimes you want to copy formulas down the sheet, but if you copy paste, the cells the formulas reference move too.
    Here’s a way of copying the formulas down so they reference the same cells as above.”

    – What I like to do is insert a % before the formula e.g. the cell becomes %=A1*B1 and then copy that text down the column. Then a quick find (%) + replace (“”) will change them all to formulas again!

  55. Make a Table

    Apple Mango Pear
    Red 23 45 36
    Yellow 25 22 77
    Green 15 78 99

    How do you find the price of Red Mango…..Match/Index, Sumproduct…VLOOKUP/HLOOKUP – NO

    Just Select a Cell and Type =RED MANGO (Sapce is the intersection operator)….and you get the price of RED MANGO
    (Make Sure Accept Lables in Formulas is Ticked or Define Names)

  57. Random question for the Tips and Tricks crowd from an intermediate (?) user…

    Let’s say I pull the following data out of a SQL database:

    Column A: Product Type
    Column B: FICO Band
    Column C: Month
    Column D: Customer Count

    Having slapped these numbers into an Excel workbook, what’s the best way to identify the Customer Count for Product A with 640-679 FICO scores in January? Right now I end up choosing between a tricky but relatively efficient OFFSET formula and an easy-to-write but calculation-intensive SUM array formula.

    An underlying assumption is that I’m going to need to be able to drag whatever my formula is across a table, breaking this data down to two dimensions in a variety of ways.

  59. To close all open files at once, hold the shift key and click File on the Menu Bar.

    A “Close All” Option is displayed

    You are still asked whether you want to save each file

  60. Michael, have you given Pivot Tables a try? If you just need a formula, assuming that Customer Count is numeric, you could use a SUMPRODUCT formula similar to this:
    =SUMPRODUCT(–(A2:A10=2)*–(B2:B10=”C”)*–(C2:C10=”January”)*D2:D10)

  62. I like the filling empty cells in a column with the value above.

    Select the range
    Edit|goto|special|Blanks
    type an equal sign
    hit the uparrow key
    hit ctrl-enter to fill the blanks

  63. Which reminds me…
    Filling a range with the same value/formula
    Select the range, type the value/formula, hit ctrl-enter.

    Rightclick and drag a cell by the “fill button” to see all the available options.

    Maybe spend some time on the Tools|Options (MRU to 9, windows in taskbar, editing choices, …). I bet most users don’t take the time to look there.

    Same with customizing the toolbar/QAT. There are lots of options that would make life easier–borders, formatting, …).

    And I like to hear positive feedback inside office:
    http://snipurl.com/ucyf

    And a windows tip:
    (WinXP menu)
    Get to Control Panel|Accessibility Options|Keyboard tab
    Turn on Togglekeys

  64. I would like to share this less commonly known trick which saves me a lot of time from creating base data for pivots from output of a pivot using a different (may I say weird) technique. (I have used it on big tables. More than 30k rows of base data.)

    e.g. Creating 9X1 table from 3X3 table.

    NameMonTuesWed
    John101213
    Sheila121516
    Tony121025

    Goto Data – Pivot Table – Multiple Consolidation Ranges – Create Single Page field – Add your range – New Worksheet – Finish

    In the pivot table just double click on the grand total and you have the data as

    RowColumnValuePage1
    JohnMon10Item1
    JohnTues12Item1
    JohnWed13Item1
    SheilaMon12Item1
    SheilaTues15Item1
    SheilaWed16Item1
    TonyMon12Item1
    TonyTues10Item1
    TonyWed25Item1

  65. Mark: When you get asked to save all files after a close all, and you don’t want to save any: hold the shift key and press No.

  66. Michael, re the four tables question:

    How about keeping the data in Access and running a very simple query? :-)

  67. Michael, re the four columns question:

    How about keeping the data in Access and running a very simple query? :-)

  68. Ctrl + .

    It moves the active cell of a range, clockwise to the next corner. I use it to easily change the selection dimensions in every direction.

  69. To get to navigable list of all worksheets Right Click on “VCR Controls” in the lower left corner of Excel. (These “VCR Controls” are the ones to navigate from worsheet to worksheet.)

    Also, CTL+Page Up or Page Down to move between worksheets.

  70. Hi:
    don’t worried about that, Easy way to sort out this problems is
    to introduce the addtional functions(Near 30) like 4 stepsaverage under the menu tools,Also you can give a litle tips on the VBE Alt+F11 .it will make you a real different and splandid lecture.(on the cells operate aspects,also simple IF Else, for next, do while ,also give a sample on self define function like myfunction() by using the vba.
    hope it can give you hand.

  71. One which might get overlooked, ctrl + clicking tabs, muilt copy, etc, but best of all formats are carried out on all sheets, good for formating.

  72. Off the top oif my head (the best place for useful tip to live).

    Naming long formulas.
    Using advanced filter to de-dupe a list.
    Using SUMPRODUCT instead of arrays.

    Data Validation by list (and the fact that if you name the range containing the list you can store it on another worksheet).

    Hope you enjoyed Oz, parent are going in next couple of weeks and more family is emigrating soon. Is it really that bad in the U.K.?

  75. Hi all

    I want to say a huge thanks to Dick (hi Dick – we shared a taxi from the airport in Seattle) and all the contributors here. I did ‘100 Excel tips in an hour’ at the CPA here in Adelaide last night and I couldn’t have done it without all of you :-). If anybody would like a copy of the handout, drop me an e-mail – lucy AT aneasiertomorrow DOT com DOT au

    Lucy

  77. Using F4 to toggle anchoring cell references. For example:
    Formula “=A1?
    Select the formula in the Formula Bar
    Press F4 –> formula changes to =$A$1
    Press F4 again –> formula changes to =A$1
    Press F4 again –> formula changes to =$A1
    Press F4 again –> formula reverts to =A1

    Saves tedious typing in $

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.