Tame your computer – table top trick

26 May

CleverclogsTipTime2The other day one of my delegates (thanks, Kate) asked whether it was possible to get the totals of a list of numbers in a column in a table at the top, rather than at the bottom. This is something you can easily do in a PivotTable (I’ll save that for when I write a tip about my favourite PivotTable design settings), but there isn’t a setting in a table. OK, you could use the Subtotal functionality, available from the Outline group on the Data tab. Or use the DSUM function. But as the total row functionality in a table reflects the filter – if any – I thought you might like a simple workaround.

Here’s how:

  1. If necessary, format your data as a table and add a total row. (CTRL + T  and CTRL + SHIFT + T spring to mind; see tip_561.php )
  2. Put your cursor in a relevant cell in a column above the table. (If necessary, insert a couple of blank rows.)
  3. Type = and click on the relevant cell in the total row, which could – for example – be a COUNT, a SUM or an AVERAGE.
  4. Press ENTER.

See http://www.roem.co.uk/tip572.xlsx for an example. (If you get a warning that the file may contain viruses or otherwise be harmful I can assure you that you can click on OK to open it.)  The file shows a filtered table with a row at the top, counting the number of females with the total spend on their salaries as well as an average of their medical insurance. Use the filters as normal to automatically change the figures to match specific criteria.

Related tips

Tame your computer – double vision

16 May

CleverclogsTipTime2As a training consultant I end up working in lots of different offices, with lots of different set-ups. At one of my clients’ offices I was recently moved to a desk with two computer monitors. So there I was, finally able to move windows from one screen to the other. But as my favourite keyboard shortcuts to arrange windows side-by-side suddenly didn’t make sense anymore, I wasn’t enjoying it as much as I would have expected … pressing the Windows key and the left or right arrows only nudged the active window to the edge of the monitor and I had to press the keyboard combination again to move it to the other.

So I was pretty chuffed when I found out there is a keyboard shortcut that moves a window from one monitor to another.

Here’s how:

  1.  WIN + SHIFT + left arrow
  2.  WIN + SHIFT + right arrow

Happiness is.

Related tips:
Top ten Microsoft Windows shortcuts
Preview and flip through your open windows – part II 
Preview and flip through your open windows
Arrange windows side-by-side

Tame your computer – go back in time

8 May

One of my own all-time favourite ways to go back to a web page I’ve visited before is to simply type part of its title in the browser’s address bar. A list of websites you visited containing that title pops up and you can use your arrow keys or your mouse to open it.

If you like this way of working, you might want to change the number of days that pages are kept in the history. Mine were set at 20 days – the default – but you can go as high as 999.

Here’s how:

  1. Click on the Tools button. (Or press ALT + X.)
  2. Click on Internet options (or press its underscored letter o).
  3. On the General tab, under Browsing history, click Settings.
  4. On the History tab change the number of days you want Internet Explorer to keep track of the pages you visit. (If you don’t want a webpage history, you can set the number of days to 0.)
  5. Click OK twice.

Please be aware that there is a chance that this option has been disabled by the IT department where you work.

Related tips:
Reopen recently closed tabs
Bring back the menu bar
Quickly access your Web browser history
Find websites you visited in the past
Why and how to clear your “cache”

Tame your computer – keep your Options open

22 Apr

CleverclogsTipTime2Do you remember Clippy – the animated paper clip – killed off by Microsoft in 2003?

OK, it might have been annoying, but it offered some great time-saving features that were not easy to ignore. Clippy, Rocky the Dog, Links the Cat, The Genius or whatever Office Assistant you had selected were replaced by tiny smart tags. But I frequently encounter delegates who are unaware of these subtle alternatives.

So here are my favourite three:

  1. Paste Options.
  2. Auto Fill Options.
  3. Insert Options

Next time paste doesn’t do what you want, or you fill cells with data that didn’t follow your expected pattern or you don’t like the format of inserted rows or columns, look out for “Clippy in disguise”. By all means ignore it if all’s fine, but click on the small Options button below the selection and check out the list of choices.

Have a look at the following tips for inspiration:

Tame your computer – go to it

6 Apr

CleverclogsTipTime2There are various nifty built-in navigation tricks you can use as you deliver your PowerPoint presentation. The one I frequently used to use was right-clicking anywhere on the screen and selecting Go to Slide, as well as Last Viewed. But I say “used to use” as that option is no longer available in later versions of PowerPoint. And as I don’t like the See All Slides alternative – it shows a thumbnail of all slides, by which time I worry nobody in the audience is paying attention anymore – I’ve found a new way to refresh my memory to jump to a specific slide.

Here’s how:

  1. Start the slide show as normal.
  2. Press CTRL + S.
  3. Double-click the slide you want to display (or click it and press the Go To button or G).

This obviously relies on the use of titles when you create your slides. If you don’t and you happen to know the slide’s number, you can simply type the slide’s number and press ENTER to jump to it.

Related tips:
Navigate through your slides while presenting
Navigate to a specific slide during a presentation
Techniques for running panic-free presentations
Black out the screen during a presentation 
Keyboard shortcut to start slide show from current slide
Save your presentation so that it always opens in Slide Show view

Tame your computer – line them up

20 Mar

CleverclogsTipTime2This week’s tip is inspired by Stefanie (thanks, Stefanie!) who told me about “geschützter Leerschritt”, known as non-breaking spaces in English.

As with all word-processing programs, it is not necessary to press ENTER at the end of a line – Word (and Outlook) automatically moves the insertion point from line to line. But there might be times you don’t want this to happen. For example, if the text is “100 pounds” you might want to keep the two words on the same line to improve readability. If you use a non-breaking space between 100 and pounds and it would not fit at the end of a line, the two words are moved in its entirety to the next line.

Here’s how:

  1. Click where you want to insert the non-breaking space.
  2. Press CTRL + SHIFT + SPACEBAR.

For those of you who prefer to use your mouse:

  1. Click where you want to insert the non-breaking space.
  2. On the Insert tab, in the Symbols group, click Symbol.
  3. Click More Symbols.
  4. On the Special Characters tab, click Nonbreaking Space  and click Insert.
  5. Click Close.

Related tips:
What’s the fastest way to insert check boxes?
Quickly show (or hide) paragraph marks and other hidden formatting symbols
Assign keyboard shortcuts to symbols or special characters
Delete unwanted copyright symbols

Tame your computer – jump to it

12 Mar

CleverclogsTipTime2What I often hear from my course participants is that they came away with new tricks, tips and shortcuts that they never might have found on their own. That’s why I started publishing weekly tips back in 2003 (no, that isn’t a typo) and a shortcut of the week in 2013.

The other day I realised I’d never published the keyboard shortcut to enable you to quickly jump between the beginning and end of your document, which can be really useful in big Word, Excel and PowerPoint files. I also use it when I want to start reading at the end of a long “backwards and forwards email conversation”. Oh, and I know the line above reads “Microsoft Office”, but it also works in PDF files and in most browsers.

Here’s how:

1.       Press CTRL + HOME to jump to the beginning.

2.       Press CTRL + END to jump to the end.

For those who use a Mac and cannot spot a Home or End key … press the Function key and the left arrow key to jump to the top of a page, and Function and right arrow to jump to the end of a page. (With thanks to Erin for the inspiration!)

Keyboard shortcuts may sometimes be unintuitive or hard to remember, but remember I drip-feed a new shortcut on my home page  – every week. If you are unsure about any of the shortcuts, please do not hesitate to get in touch.