We probably use office programs - word processors, spreadsheets, email and presentation applications - more than any other kind of software on our computers. Of this kind of software, Microsoft's Office suite is the most popular.
It can, however, be hard to get to grips with all the time-saving features; all those menus, toolbars and buttons can seem overwhelming at times, particularly if you are just starting out.
Once you delve a little deeper and discover Office's hidden shortcuts and tricks, however, you can make your software work a lot harder for you and make your life easier in the process.
Use our handy guide to the 100 most useful hints, tips and shortcuts in Word, PowerPoint, Excel and Outlook and you will know just how to get the most out of your Office applications.
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'. If you are a Word 2000 user, you can download a Microsoft add-on to customise the Places Bar
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.
Word shortcut keys
Most of us spend more time using Microsoft Word than any other Office application, so make use of our handy guide to Word's shortcut keys and cut down the time you spend hunting for what you want among the many menus and toolbars.
Ctrl+B Make selected text bold
Ctrl+U Underline selected text
Ctrl+I Make selected text italic
Ctrl+L Align selection or paragraph to the left of the screen
Ctrl+E Align selection or paragraph to the centre of the screen
Ctrl+M Indent paragraph or selection
Ctrl+1 Single-space all the lines in selection
Ctrl+2 Double-space all the lines in selection
Ctrl+5 1.5-space all the lines in selection
Ctrl+Space Toggle AutoCorrect
Ctrl+Del Delete the word to the right of the cursor
Ctrl+Backspace Delete the word to the left of the cursor
Ctrl+Shift+8 Toggle hidden characters that mark spaces, carriage returns, and so on
F7 Run a spelling and grammar check
Shift+F7 Use the Thesaurus
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'. If you are a Word 2000 user, you can download a Microsoft add-on to customise the Places Bar
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.
Word shortcut keys
Most of us spend more time using Microsoft Word than any other Office application, so make use of our handy guide to Word's shortcut keys and cut down the time you spend hunting for what you want among the many menus and toolbars.
Ctrl+B Make selected text bold
Ctrl+U Underline selected text
Ctrl+I Make selected text italic
Ctrl+L Align selection or paragraph to the left of the screen
Ctrl+E Align selection or paragraph to the centre of the screen
Ctrl+M Indent paragraph or selection
Ctrl+1 Single-space all the lines in selection
Ctrl+2 Double-space all the lines in selection
Ctrl+5 1.5-space all the lines in selection
Ctrl+Space Toggle AutoCorrect
Ctrl+Del Delete the word to the right of the cursor
Ctrl+Backspace Delete the word to the left of the cursor
Ctrl+Shift+8 Toggle hidden characters that mark spaces, carriage returns, and so on
F7 Run a spelling and grammar check
Shift+F7 Use the Thesaurus
EXCEL
13. 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. You can download and install new Smart Tags from the Microsoft Office website. You will find tags there for both Excel and Word.
14. 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.
15. 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.
16. 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.
17. 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.
18. 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.
19. 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.
20. 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.
21. 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.
22. 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.
23. 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.
24. 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.
25. 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.
26. 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.
27. 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;?-?;@'.
Excel shortcut keys
Don't get bogged down in Excel's options and commands. Use this guide to its keyboard shortcuts to fly around the keyboard and get your work done without having to take your hands away to move the mouse all the time.
Ctrl+- Delete the current cell or selection
Ctrl+Shift++ Insert cell or selection
Ctrl+; Insert current date at the selected cell
Ctrl+Shift+; Insert current time at the selected cell
Ctrl+K Insert a hyperlink or web link
Ctrl+Tab Switch to the next worksheet in the workbook
Shift+F3 Open the formula window
F11 Create a chart
Ctrl+Space Select all of the current column
Shift+Space Select all of the current row
Ctrl+Shift+1 Format the current cell with commas
Ctrl+Shift+4 Format the current cell as currency
Ctrl+Shift+5 Format the current cell as a percentage
Ctrl+Arrow key Move to the next used cell in the direction of the arrow key
Ctrl+F Open the search box
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. You can download and install new Smart Tags from the Microsoft Office website. You will find tags there for both Excel and Word.
14. 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.
15. 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.
16. 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.
17. 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.
18. 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.
19. 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.
20. 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.
21. 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.
22. 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.
23. 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.
24. 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.
25. 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.
26. 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.
27. 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;?-?;@'.
Excel shortcut keys
Don't get bogged down in Excel's options and commands. Use this guide to its keyboard shortcuts to fly around the keyboard and get your work done without having to take your hands away to move the mouse all the time.
Ctrl+- Delete the current cell or selection
Ctrl+Shift++ Insert cell or selection
Ctrl+; Insert current date at the selected cell
Ctrl+Shift+; Insert current time at the selected cell
Ctrl+K Insert a hyperlink or web link
Ctrl+Tab Switch to the next worksheet in the workbook
Shift+F3 Open the formula window
F11 Create a chart
Ctrl+Space Select all of the current column
Shift+Space Select all of the current row
Ctrl+Shift+1 Format the current cell with commas
Ctrl+Shift+4 Format the current cell as currency
Ctrl+Shift+5 Format the current cell as a percentage
Ctrl+Arrow key Move to the next used cell in the direction of the arrow key
Ctrl+F Open the search box
Posted in: Office tricks
0 comments:
Post a Comment