Tips and Tricks

Office, Word etc
Subject	Detail
SUM Function in Excel	It is often useful to add several figures from one Cell in several Sheets in an Excel Spreadsheet, Eg a separate spreadsheet for each month has a total, and you want to do an Annual Return. The equation for this is =SUM(Jan-99:Dec-99!G10) where the sheets for each month are called MMM-99 and the total is in Cell G10.
Play a sound or message when your mouse is held over a cell in Excel	Ever need a friendly reminder when you are in a certain place in your spreadsheet. Click in a cell, choose Insert \| Note and choose 'Record' or 'Import'. Record allows you to record a message. Import allows you to insert a WAV file. The Windows WAV files are normally in C:WINDOWSMEDIA folder and all have .WAV extensions. Once inserted, each time your mouse is held over the cell, the sound will be played.
Recently used Files in Word	The default number of Recently used Files under the File Menu in word is only 4. This can easily be increased by choosing from the main menu, Tools \| General and incrementing the number beside the 'Recently Used File List' label.
Place Clipart as background behind your text in Word	If not already on, turn on your 'Drawing' Toolbar, (Right Click over the toolbars, and click beside 'Drawing'). Click on 'Text Box' in the Drawing Toolbar, and drag out a rectangle in your document. Choose Insert \| Object, Clipart, and your desired picture. This will appear inside your text box. While it is selected, click on 'Send behind Text' in your Drawing Toolbar. You can then click outside the text box and type over top of the Clipart.
Auto-Correcting in Word	Do you have a particular phrase or word which you always want to type or correct? This is easily done with the Word feature Auto-Correct under the Tools \| AutoCorrect Menu. Enter this and type the word that you want to replace in the "Replace" box. Type the phrase or correct spelling in the "With" box. Then each time you type the word it is replaced for you.
Joining Text together in Excel	If you need to combine text in different cells, there is a function called CONCATENATE(<text>,<text>,<text>,...). "< text>" can be actual text in quotations or cell references. Eg A1=CONCATENATE(B1," ",C1) will join the text from B1 and C1 with a space between them, and place the result in A1
Inserting and dragging a picture in Word 97.	When you insert some clipart or a picture into Word, it generally defaults to text wrapping, so you cannot drag it freely around the screen. Right click over the picture, choose "Format Picture", Click "Position", turn on "Float over text", then Click "Wrapping" and select the required wrapping, e.g. "None", then OK. You can then drag the picture around the screen.
Handy hint from Microsoft in Word Jump to last edit point	When you are opening a document to continue editing in, use the Shift-F5 and your cursor jumps to the place in the document where it was.
Handy hint from Microsoft in Excel. Count lines instantly	Ever need a really quick way to count the number of lines or instantly total or average a range of figures, but don’t want to insert a permanent calculation. Try this: Right click in the bottom pane of Excel, and click on one of the required options, eg Count. Then when you have a range of figures in cells, select them all, and look down at the pane. The count (in this case) will be displayed while the cells are selected.
Handy hint from Microsoft in Excel. Add a calculator to Microsoft Excel	On the View menu, click Toolbars, and then click Customize. Click the Commands tab. In the Categories list, click Tools, and in the Commands list, click Custom (the one with the gray calculator graphic). Drag the selected command from the Commands list to a toolbar. (Lift your finger from the mouse when you see a plus sign next to your pointer.) Click Close. Now click the button you just added to run the calculator.
Outlook 2000 Reminder facility	Outlook 2000 has a very handy facility for setting up reminder tasks. These tasks can be set up so that they recur after specific periods of time, eg monthly, weekly etc. They can be set to start on or after certain dates, and to continue for ever, or until certain dates. The alarm can be set to notify you as soon as you run up Outlook, so you don't have to connect to the internet. To set up each task, as you can store many, run Outlook, choose Tasks, New and enter each task. Click on Recurrance and choose the specific recurring details. Then sit back and let the computer do the remembering for you.
Paste Text in Word looses formatting	If you get frustrated when you paste text from another document into yours, and the formatting is carried through, which changes the way you are trying to display your document. There is a way around this. After copying the text onto the Clipboard, and changing to your document where you want to paste it, instead of choosing Paste, go to the Edit menu, and choose Paste Special... then in the dialogue box presented, choose "Unformated Text". The text on the clipboard will be pasted in, and your own formatting will be kept.
Remove Duplicate Rows in Excel - Supplied by Microsoft.com	If you have data in Excel, which has some rows that a duplicate, you can use the following procedure to remove duplicate lines. In the list you want to filter, select the column or cells. On the Data menu, point to Filter, and then click Advanced Filter. In the Advanced Filter dialog box, click Copy to another location. In the Copy to box, type a cell reference, or click on the red, and select a cell. Select the Unique records only check box, and then click OK. The new data is pasted into the cells, without duplicate lines.
Disable Enter Network Password prompt	If you are monitoring your email all the time, you will have been annoyed at some time, with the "Enter Network Password" prompt. This prompt can pop up anytime there is a timeout or temporary network problem, or email server problem at your ISP. This is not a fault with your computer or your internet, but once the prompt appears, it does not go away. While the prompt is on the screen, your Outlook does not download any further email, effectively halting your process. I searched and tried to find a solution, but people on forums resort to reinstalling and playing with their settings, thinking it is fixed, and it often comes back. The reason is that Outlook is not broken, just stopped due to a network problem. I decided it was simpler to just write a small program to enter on this dialogue box when it pops up. This program checks every 10 minutes, and does nothing else, but automatically doing an Enter to allow your Outlook to continue downloading emails. You can purchase the program through paypal, and it will be emailed to you.