Useful Hints & Tips for MS Excel

1. Use Smart Tags in XP
Office XP features Smart Tags, which are like intelligent links to websites or locations on your PC's hard disk. Excel will recognise certain words and show Smart Tag action buttons next to them. Go to the AutoCorrect options part of the Tools menu and select Smart Tags to see which words it will recognise.

2. Spot corrections and errors
If you share Excel documents with others, it can be useful to see any changes they have made. Excel 2000 used red triangular indicators to highlight cells in which there were comments. In Excel 2002, there are purple indicators for Smart Tags and green ones for possible errors in formulas. Options can be found in the Tools menu, under the Error Checking tab of Options.

3. Open older macros
You will probably not be able to open old macros (from Excel 97 or 2000 workbooks) in Excel 2002 as the program will throw up a security warning notice. If you need to use old macros, go to the Tools menu and select Options, Security, then Macro Security and make sure Low is selected. Under Trusted Sources, place a tick in the Trust add-ins and Trust Visual Basic boxes. You will then need to restart Windows before you can run your old macros.

4. View important cells
Using the Watch window you can keep an eye on important cells in a spreadsheet. Click on a cell containing data and go to the Tools menu, choose Formula Auditing and then Show Watch Window. Click on Add Watch and it will display values and formulas for any cell of any open workbook.

5. Use labels in cells
You can make Excel work with labels you have given to cells instead of having to use the cell position ('Profit' instead of 'A6', for example). Go to Options in the Tools menu, and click on the Calculation tab, then tick the 'Accept labels in formulas' box.

6. Create formulas
Create a formula by clicking on the Paste Function tool (which is marked 'fx') on the standard toolbar. If you select a function from the list, its description will appear in the dialogue box, and the Help button will explain more about the formula. Alternatively, clicking on the equals sign in the formula bar will display a list of recently used functions.

7. Links
If you are seeing error messages about broken or invalid links to other workbooks or other applications, go to the Options part of the Tools menu and choose the Workbook Options tab. Make sure the box marked 'Update Remote References' is ticked. If you tick the box marked 'Save External Link Values', you won't have to worry about maintaining links but your file may end up significantly larger.

8. Create subtotals
It's easy to create sums of columns using the AutoSum tool but what about subtotals? Creating these needn't be hard either. Just use the function =SUBTOTAL (9,B2:B10). The 9 is a function number, representing SUM, and you should replace the cell references with the ones from your own worksheet. You could place it in cell B11 and then repeat it with figures below, say =SUBTOTAL (9,B12:B20), in cell B21. If you then used the AutoSum tool in cell B22 it would just display the sum of the subtotals, from cells B11 and B21.

9. Delete vs clear
There two ways to remove information from cells: Delete and Clear. Clicking on a cell and selecting Delete (or pressing Del or Backspace) will remove the cell's value or formula, but any formatting and comments will remain in place. If you want to return the cell to its original state, with no formatting, choose Clear instead.

10. Informative printouts
Many of us have spreadsheets that spread over more than a page. If you want your column titles to print on every page, go to the File menu, click on Page Setup and go to the Sheet tab. Click on the red arrow in the box marked 'Rows to repeat at top' and select the rows that contain your column titles, then click on OK.

11. Delete comments
You can delete all the comments from your worksheet at once, for instance if you have finished the sheet and want to distribute it without annotations. Press Control, Shift and O at the same time, and this will select all the cells in the worksheet that contain comments. Right-click on one of them and select Delete Comment, then click anywhere on the sheet and all the comments will have vanished.

12. Keep track of online orders
When you order online, it can be hard to keep a record of all your orders. You can, however, transfer the table from the confirmation email the retailer sends you into an Excel workbook. Open the email and click at the start of the table, then hold down Shift and click at the end of the table. Right-click on it and choose Copy, then open a blank Excel worksheet. Right-click on a cell and choose Paste. You may have to correct the formatting for it to look better.

13. Figuring out formulas
If you have a formula that's puzzling you because you can't figure out how it was derived, click on the cell that contains it, and choose the Auditing option in the Tools menu, and select Trace Precedents. You will see blue dots in the relevant cells, with arrows pointing towards the formula. When you have finished, choose Remove All Arrows.

14. Import finance data
You can import data from most online banks and finance programs into Excel, but it's often not quite as simple as just opening the document in Excel. It will usually be in Comma Separated Value (.csv) format. In Excel, click on the File menu and choose Open, then choose 'Text Files (*.txt, *.prn, *.csv)' from the 'Files of type' box, and select the CSV file you obtained from the bank website. Once it has opened, just adjust the column widths so they look right.

15. Show zeros the door
You can remove zeros that you don't need from your cells by going to Tools, Options and then View, and removing the tick from the Zero values box. If you want zeros to appear in certain cells, give those cells the custom format '0;-0;;@'. To enter a custom format, go to Format and Cells, and choose the Number tab, then select Custom and enter the format. For dashes to appear instead of zeros, use the custom format '0;-0;?-?;@'.

Useful Hints & Tips for MS Word

1. Date
To stop Word from adding today's date to any year you type, changing, say, 'Letter 2004' to 'Letter 2004-08-22', go to the Insert menu, click on AutoCorrect and select AutoText in the next menu. Untick the box marked 'Show Autocomplete Suggestions'. If you want to use an AutoText shortcut in future, type the abbreviation and press F3.

2. Add places
Add a folder to the Places Bar in Word 2002's Open and Save boxes to help you find files quickly. Find the folder you require in Windows Explorer, highlight it and then click on the Tools menu. Now click on the option marked 'Add to My Places'.

3. Turn off fast save
Fast saves aren't much faster than normal saves and instead of properly saving your document, Word just appends anything you have added to the text to the end of the file. Nothing is ever deleted from the document file, so it can end up being huge. It's best to turn off fast saves by going to the Tools menu, clicking on Options, then on the Save tab and removing the tick from the 'Allow fast saves' checkbox.

4. No mouse styles
If you often write the same style of documents in Word, you may be aware of the Styles option, which can reduce the time you spend formatting a document. You can assign a keyboard shortcut to a style so that you don't have to use the mouse to find it by going to the Tools menu, then Customize, then pressing Keyboard and selecting Styles from the list on the left.

5. Follow style
If your styles naturally follow one another, say a particular text style always follows a particular headline style, you can cut down further on the time required to select them by going to the Format menu, and selecting Style. Choose the relevant style and click on Modify, then choose another from the box marked 'Style for following paragraph'.

6. Repeat find
To find a piece of text, press F3, enter the text you're looking for in the text box and press Enter. You don't need to keep the box open to find other instances of this text, however. Click on Cancel, and notice that the double arrows at the bottom of the right-hand side scroll bars have turned blue. Clicking on one of them will take you to the next place this text occurs, either forwards or backwards through the document, depending on which button you click.

7. AutoCorrect
AutoCorrect allows you to change text automatically as you type it, which can be handy for correcting frequently mistyped words. Click on the Tools menu, then on AutoCorrect and enter a piece of text in the left-hand column. Whenever you type it, it will be replaced by the text in the same row in the right-hand column of the dialogue box.

8. Select lots of text
It can be very difficult to select more than a few paragraphs of text at once using only the mouse, particularly if you have a fast PC, as the text will shoot past before you notice. But you can select large amounts of text easily by clicking where you want your selection to start, then navigating to the end of the intended selection using the mouse wheel or scroll bars. Then just hold down Shift and click again to select the block of text.

9. Saving grace
If you are working on several documents at once, you can save them all without closing Word in the process. Just hold down Shift and click on the File menu. You will see a new option Save All. There is also an option to Close All if you want to do so without closing Word.

10. Simple formatting
If you're going to create a number of documents that are similar in appearance, it's best to use styles to format text rather than applying the formatting yourself. This ensures that you can change it easily throughout the documents, if you need to. You can, however, use the Format Painter to copy the formats from one paragraph and apply it to another. Select the Format Painter button from the Word toolbar and click on the portion of text you want to copy a format from. Now drag the pointer over the selection you want to apply the formatting to.

11. Scraps
You can create 'scraps' in Word, which are small blocks of text from a document. Highlight some text in an open document and drag it to the Desktop, and you will see it appear as a document scrap. You can arrange and rename your scraps on the Desktop, and simply drop them back into Word documents as you need them. The scraps can be pasted into most other applications too.

12. Snappy corrections
Instead of using the spell checker once you've finished writing a document, you can correct words as you type. Right-click on a red-underlined word, and Word will show you a menu of replacements it thinks are suitable. This also works for green-underlined phrases that Word thinks are not grammatically correct. Right-clicking them will again open a menu with suggested replacements.

Office 2010 - Useful Secrets

#1: Format painter (Office)

The Format Painter tool replicates the formatting from one part of a document to another. So instead of manually redoing all the formatting yourself, you can use theFormat Painter. First, select the text whose formatting you want to replicate. Then, click the Format Painter toolbar button. Finally, select the text you want to imbue with the format. For bonus points, you can double-click the Format Painter button to replicate the formatting to multiple areas of the document!

#2: Paragraph in/out/up/down (Office)

You can easily move a paragraph in four directions by pressing Alt+Shift+[Arrow]. To increase or decrease the indentation level of a paragraph or bullet point, press Alt+Shift+Right and Alt+Shift+Left respectively. To move a paragraph up or down, press Alt+Shift+Up or Alt+Shift+Down. This works especially well in PowerPoint, where it's common to reorder bullet points or change indentation levels.

#3: Increase or decrease font size (Office)

To quickly increase the font size of selected text, press Ctrl+Shift+>. To decrease the size, press Ctrl+Shift+<. I find it easy to remember these keyboard shortcuts because the one with the greater-than symbol increases the font size while the less-than symbol decreases it.

#4: Quick Access Toolbar (Office)

Office 2007 has a Quick Access Toolbar that can be customized to include buttons for your favorite commands. The Quick Access Toolbar is in the top left corner of many Office applications. You customize it by clicking on the drop-arrow on its right.

 

#5: Fill handle (Excel)

Excel can auto-fill cells in eerily smart ways. Instead of manually typing a sequence in cells, you can simply type the first few values of the sequence and drag the fill handle to auto-fill the rest of the cells. The fill handle is the little black square at the lower right corner of a selected cell's border. Drag it to automatically fill adjacent cells.

If you drag the fill handle with only one cell selected, it will repeat that cell's value into adjacent cells. However, if you drag the fill handle with multiple cells selected, Excel is smart enough to figure out the series. For instance, in the following example, Excel will fill subsequent cells with the increasing series of odd numbers. This even works for other types of series, like dates and percentages.

#6: Moving and copying cells by dragging selection borders (Excel)

Quite possibly the most useful yet completely undiscoverable feature in Excel is the ability to move and copy cells by dragging selection borders.

 

For instance, to move row four between rows one and two, select row four and drag the selection border while holding down the Shift key in order to insert it in its new position. If you drag the border without holding down the Shift key, the selected cells will instead replace the cells you drop them on. Conversely, if you hold down Ctrl while dragging a selection border, the selected cells are copied to their new location.

#7: Status bar statistics (Excel)

The status bar in Excel shows handy statistics when multiple cells are selected. In Excel 2007, the status bar shows the selected cells' average, count, and sum. This is an easy way to quickly analyze data without authoring formulas.

#8: Clear formatting (Word and PowerPoint)

To remove formatting from selected text, press Ctrl+Spacebar.

#9: Advanced field search (Outlook)

In Outlook, you can quickly search through a mail folder by using the Instant Search box. In addition to searching for keywords, you can do a fielded search by prefixing your search text with a variety of field names.

For instance, the above example searches for all mail from people named "jimmy" sent in May with attachments that have "jpg" in the filename. I most often use this feature for two things: to easily find email from a specific person, and to find specific attachments.

#10: Presenter view (PowerPoint)

PowerPoint has for many years had a great feature called Presenter View, which allows you as the presenter to see a different view of the presentation from your audience. In Presenter View, your monitor shows not only the slides, but also your notes as well as the current elapsed time in the presentation. This makes giving a presentation far easier. To enable Presenter view, go to the Slide Show ribbon and check Use Presenter View. In that same section, you can also change the monitor which the presentation is shown on. One note: the Use Presenter View checkbox can only be checked if you already have a second monitor connected and enabled.