Guys,

In this thread we put Microsoft word, Excel, Powerpoint, Access tips and tricks.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Some tips in Word (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.

__________________
Regards,
devarajan

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 here.

__________________
Regards,
devarajan

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.

__________________
Regards,
devarajan

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.

__________________
Regards,
devarajan

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'.

__________________
Regards,
devarajan

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

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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;?-?;@'.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.

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.

__________________
V.Vadivelan

There never a wrong time to do the right thing.