By Mark Finney, IT Solutions Manager @ TP3
Looking for some Excel goodness? Here are some quick tips you might like to try to improve your productivity.
Show me the Money
Who hasn’t spent a good deal of time checking formulas by clicking on the cell and looking up at the formula bar? If you need to see your formulas all at once, just go to the Formulas tab and select Show Formulas. This will display all of the formulas right there in the cell. Even better, use the Keyboard shortcut – Ctrl + Tilde. (The Tilde is this little guy ~ just under the Esc key at the top left of most keyboards).
Pick Me, Pick Me!
Typically we use AutoSum to add a column, and then copy the formula across or down. A quicker way to do this is to select the range you wish to add, plus an extra row (and column if needed), and then use AutoSum to get column and row totals in one go. Bonus Tip – the keyboard shortcut for AutoSum is Alt =
I'm Watching You
I don’t know about you, but I'm not a big fan of split screen. It’s kind of messy and clunky. Scrolling up and down constantly is equally inefficient. If you need to see what’s happening to a distant cell (the bottom line for instance) when you adjust figures at the top of your worksheet, try the Watch Window instead. You will find the Watch Window on the Formulas tab, under the Formula Auditing group. Select Watch Window, choose the cell or range you need to watch and then click Add Watch. Once added, the Watch Window will display the results of the target cell anytime you change the source data.
Wrapped in you
If you need to control how text wraps in a cell, just do the following. Start entering the text you want, and when you get to where you need to wrap the text, press Alt + Enter. The text wraps at that point and you can continue doing this for as many lines as you need. Press Enter when you are done.
Fill ‘er up
One of the best time savers in Excel is AutoFill. Type an entry such as Monday, January, Qtr 1 etc. and then use the Fill Handle at the bottom right of the cell to extend the values down or across the sheet. What many users don’t know is that you can choose from more options by clicking the Smart Tag that pops up at the bottom right of the range. In this example I typed “Monday”, then used the fill handle to drag down a few rows. I then clicked the Smart Tag and selected Fill Weekdays to omit the weekend from my range.
Bonus Tip – use the right mouse button when dragging to have the extra options appear automatically.
I hope you find these tips useful in your daily work. I will be creating some more quick tips for both Word and Powerpoint in the coming weeks.
We run a comprehensive range of Microsoft Excel courses for everyone from beginner to advanced. Have a look at our upcoming Excel courses to learn these and many other useful functions, tips and tricks.