Archive | May, 2018

Tame your computer – zero in on Excel

31 May

I love Excel but there are a couple of things that really annoy me. One of these things is that you cannot add a zero in front of a number, which I often want to use for telephone numbers and invoices. For example 01223123456 should not be turned into 1223123456. And James Bond’s code number should not be changed to 7!

Oh, and how about large numbers such as credit cards that are turned into some weird code?  For example 1234567898765432 being displayed as 1.23457E+15.

There are various ways you can tackle this, by means of custom formats and the TEXT Function. But if you never need to use the number in a calculation, there is a much easier way.

Here’s how:

  1. Type an apostrophe (‘) in front of the number.

Excel will treat the cell as text (hence the fact that you cannot use it to do a calculation) and the number will be left-aligned. You can obviously reset the alignment by clicking on the Align Text Right button.

Related tip

Tip # 193: Display your numbers with leading zeros

Tame your computer – ultrafast update

22 May

Long documents such as business proposals, technical reports or other big Word files containing several sections or chapters often require a table of contents. Perhaps you even included a list of all the figures or tables used in the document. Or you might have inserted cross-references to other pages or a list of keywords found in the document corresponding with the page numbers.

As you may know, these references are inserted into your document as a field, which means that you need to update them as the document progresses. You can obviously right-click the reference and select Update Field, but if you have used a variety of fields throughout your document this will be cumbersome. So why not ensure you update all references in one go?

Here’s how:

  1. Create your Table of Contents, Table of Figures, Cross-references, Index etcetera, as normal.
  2. Press CTRL + A.
  3. Press F9.
  4. If prompted to update the Table of Contents and/or Table of Figures, select the appropriate radio button and press OK.

To make sure that you don’t forget to update your fields before you print your document, you can set Word to update them automatically.

Here’s how:

  1. On the File tab, click on Options.
  2. Click on Display or type the letter d.
  3. Under Printing options, select the check box for Update fields before printing. If prompted, click on the relevant radio button.

Oh, and remember, if you use Word’s built-in Styles you can take advantage of quickly creating a Table of Contents and other fabulous options available in the navigation pane (see tip_415.php). And if Microsoft’s choice of formatting is not what you want, you can easily modify them.

Related tips:

Move or copy chunks of your document without using Cut and Paste or Copy and Paste
Applying and modifying Heading Styles to titles and subtitles

Tame your computer – with a double-click trick

1 May

Back in 2006 (no, this isn’t a typo!) I wrote a tip on how to quickly move to the end of a range of cells in a row or column using keyboard shortcuts. But as I know not everyone is a fan of shortcuts I thought I’d share a “double-click trick” that almost does the same.

Here’s how:

  1. Point to any of the borders of a cell and wait until the mouse pointer appears as a combination of an upward-pointing arrow and four-headed arrow.
  2. Double-click any of the cell’s borders – left, right, top or bottom.

Your cursor will jump to the edge of the range of cells. Simply double-click again to jump in your preferred direction.

Remember, blank rows make it difficult and messy to filter and manipulate data. If you use empty rows for visual purposes, use colours or borders instead.

Related tips:

Quickly move to the end of a range of cells in a row or column
Quickly delete empty rows