Archive | May, 2013

Tame your computer – simple cell check

28 May

Are you using somebody else’s Excel files, but don’t have a clue what’s going on? Or perhaps the formulas in the workbook are rather complicated and all over the place, making it hard to tell which cells are used.

Maybe you use the Show Formulas button or the (rather unintuitive) keyboard shortcut CTRL+` (see tip  238 if you don’t know what I’m talking about) but did you know you can display arrows that indicate what cells affect the value of the currently selected cell or those cells that are affected by the value of the currently selected cell?

Here’s how:

  1. Select the cell you want to check.
  2. On the Formulas tab in the Formula Auditing group, click on the Trace Precedents button to display arrows that indicate what cells affect the value of the currently selected cell.  AND/OR
  3. On the Formulas tab in the Formula Auditing group, click on the Trace Dependents button to display arrows that show those cells that are affected by the value of the currently selected cell.

A blue tracing arrow is displayed, connecting the selected cell to each of its precedents or dependents, assuming, of course, that the cell has a precedent or dependent.

When you double-click the blue line the cell you toggle between the two cells.

To remove tracing arrows from a worksheet, click on the Remove Arrows button in the Formula Auditing group on the Formulas tab, or click on the arrow next to the Remove Arrows button and click on Remove Precedent Arrows or Remove Dependent Arrows.

By the way, we’re celebrating ten years of top tips to help you ‘tame your computer’. My enthusiasm for making technology work well for people has never waned and I look forward to continuing to help you make the most of your software. You can see the latest tip and a ‘shortcut of the week’ at www.roem.co.uk.  The full archive can be found on http://roem.co.uk/hints.php.  If you would like to start the week with your own copy of the weekly tip, please send a blank email to subscribe@roem.co.uk.

Advertisements

Tame your computer – on your marks

21 May

Are you using somebody else’s Word documents, but don’t have a clue what’s going on? (Did they use tabs, or spaces or tables? Is there a page break or section break?) Or perhaps you are creating an index and cannot bear the fact that it automatically turns on the display of paragraph marks and other non-printing formatting marks as soon as you mark an entry for an index?

You might know you can show or hide formatting marks by clicking on the Show/Hide button in the Paragraph group on the Home tab. (The button looks like a reversed P.) But do you know its keyboard shortcut, saving you time – and frustration?

Here’s how:

  1. Press CTRL +  SHIFT + 8 to switch on.
  2. Press CTRL +  SHIFT + 8 to switch off. (It’s a toggle.)

You sometimes see that the shortcut is listed as CTRL + * (for example, hover over the Show/Hide button on the ribbon) but as you cannot use the asterisk on the numeric keypad of your keyboard I thought this might be easier to understand.

By the way, this also works in Outlook, which is good to know as Outlook has no Show/Hide button.

Tame your computer – comment combination

5 May

In Excel, comments can be useful to give a suggestion for a change, a question to one of your colleagues, or any other text that relates to part of the worksheet. During a recent training session one of my course participants asked whether it was possible to add a single comment to more than one cell.

Here’s how:

  1. Right-click the first cell with which the comment is to be associated.
  2. Select the Insert Comment option from the shortcut menu.
  3. Type the text of the comment.
  4. Copy the cell containing the comment. (CTRL+C, anyone?)
  5. Select all cells that should have the same comment. (Hold down your CTRL key to select cells that are not next to one another.)
  6. Right-click any of the selected cells and select Paste Special to open the Paste Special dialogue box.
  7. Select the Comments radio button.
  8. Click OK.

Thanks for the inspiration, Maxine!

By the way, if you want to print the comments, you might want to have a look at tip 142. An oldie, but a goodie!