SEARCH

Some may be paranoid about Google knowing so much about you, but in some peoples’ opinion, “Who cares what a server in California or somewhere knows about me?” It’s your choice…

Forget The Dictionaries
Google can replace several other kinds of searches. If you want to know what the time in New York is just now, just type in “New York time”! Similarly, if you need to know the difference in usage (or use?) between the words “usage” and “use,” you don’t need to go to an online thesaurus or dictionary. Simply type in “use usage” into Google, and you’ll get results directly from dictionaries and thesauri! Note that in this particular example, Google does the job for you, but Yahoo! doesn’t. MSN brings up the required result second, which is quite nearly there.

Force Keywords
Not all words in a search string come up in search results. You’ll sometimes find that somewhere down in the results, there are pages which don’t contain one or more of your search terms at all! This is especially true for searches with many keywords. It suffices to say that it’s Google’s way of giving you the most relevant results. To force a certain keyword or keyphrase to be included in the search, use the ” ” sign. For example, typing in “favourite example hardware sata mouse” (without the quotes) will bring up some results without one or more of those words. If you need the word “example” (which is a common word, so Google might leave it out), type “favourite example sata hardware mouse”.

Excluding Unwanted Results #2
To exclude irrelevant results, try using extra keywords. For example, if you want information on various fruits, such as apples, grapes and mangoes, you might key in these words into, say, Yahoo! You’ll see results that include lots and lots of fruits. Take a few of those from the first couple of results and exclude them using a minus sign.

For example, “apples grapes mangoes” (without the quotes) brings up 84,000 results on Yahoo, with lots of irrelevant fruits mentioned. Now, type in “apples grapes mangoes -bananas -pears -plums -lemons -oranges -pineapples”. Voila-just 1,100 results! Of course, this is just an example of how you can drastically trim down your results.

Ready Info
Google has some hidden gems. One amongst these is information about people, countries and such. For example, if you need to know when Mozart was born, just type in “Mozart born” (without the quotes), and Google will give you the required date of birth.

Quick Definitions
A very useful function in Google is the “define” command. If you want to quickly know what SATA is, simply type in “define:sata”.

Searching Within Sites
Some sites have a Google search on their homepage, from where you can search for stuff within that site. You don’t need to go to the site to get that functionality. Simply add “site:www.examplesite.com” to your search terms, and you’ll get results only from www.examplesite.com. You can also specify domains, such as .edu this way: “admissions scholarship site:edu”.

Related Sites
You’ve probably seen the “related” link after page results in Google. Again, you can directly get sites related to a certain sites by typing in something like “related:www.examplesite.com”.

Bring Up Only Certain Filetypes
If you want results of a certain file type, such as PDF, you can use “filetype:pdf” before your search query. This will return only PDFs. Of course, you can use this for any file type, including MP3, XLS, or anything you want.

Putting Document Titles To Use
“allintitle” searches for pages that have all your search words in the title of a Web page. So if you want sites that have “microsoft” and “knowledge” in the title, type in “allintitle: microsoft knowledge” with a space between the “allintitle” and “microsoft”.

Searching By URL
You can also search for terms that occur in the URLs of documents. For example, if you want to find the word “mac” in sites that have “apple” in the URL, where “mac” might or might not be in the URL, use this: “inurl:apple mac”. The word “apple” will then be in the URL. Note that there should be no space between “inurl” and “apple”.

MICROSOFT OUTLOOK XP

Go to Tools, click Advanced Find, and then click the More Choices tab. In the “Look for” box, click Messages. Then in the Size list, click greater than, and then type a number such as 1024, for files that are 1 MB or larger in size. Select any other search options you want, then click Find Now.

When the search is complete, you can save it as a shortcut: click Save Search on the File menu of the Advanced Find dialog box, and just save it anywhere. The next time you want to conduct the same search, double-click this shortcut.

Skip Unknown Mail Senders
If you know that only certain people are going to send you mail on a certain e-mail address, you might want to skip messages that come from people other than these. You can specify rules to relocate messages from those not on your “trusted” list to a separate folder, called, say, “unknown.” This also helps in avoiding spam. Besides, you’ll get only your most-needed mails in your Inbox, and you can browse through the files in your “unknown” folder.

First, you’ll need to create a new folder in your Inbox. This will be that “unknown” folder. In the Folder List, right-click Inbox and click New Folder. Type in the folder name, say “unknown”, and click OK. If your Folder List is not visible, click Folder List under View. Now, you’ll need to create a new rule using the Rules Wizard: go to Tools > Rule Wizard. Click New, then click “Start from a blank rule”. Click “Check messages when they arrive”, and click Next.

In the “Which condition(s) do you want to check?” box, check the “On this machine only” box. Click Next. Then, in the “What do you want to do with the message” box, click “Move it to the specified folder”. In the “Rule description” box, click Specified. Then click the folder you created-“unknown”-and click OK, then Next.

Under “Add any exceptions”, check the “Except if sender is in specified Address Book” box. In the “Rule description” box, click Specified. In the “Add Address List” dialog box, choose “Outlook Address Book”, click Add, then click Next. Type in a name for your rule, click Finish, then OK.

Recurring Reminders
Say you want to add recurring tasks or reminders to your Outlook calendar. These might be things like “Order pizza.” For this, open the Calendar in Outlook. From the menu, select Actions > New Recurring Appointment. In “Appointment Time” in the dialog box, select a Start Time. Select the same time for the End Time-this is so that Outlook will not block the time on your calendar. Make sure “0 Minutes” is displayed under Duration. Input your desired options under “Recurrence Pattern” and “Range of Recurrence”. Click OK, then type in a name for the reminder in the Subject box of the Appointment Screen. Click Save and Close.

Quickly Setting Up A Reminder
If you don’t have the time to respond to an e-mail just when you receive it, you might want to add it to your calendar so that you can be reminded to reply to it later.

Drag the message from your Inbox to the Calendar icon on the Shortcut Bar. Outlook will create an appointment that includes the text of the message. Make sure the start time and other details are entered, and also make sure the Reminder box is checked.

Saturdays And Sundays
By default, Outlook combines Saturday and Sunday into one column in the month view of the Outlook Calendar. Of course, this is customisable: you can change it to make it show a separate column for the two days.

So where’s the command? Right-click anywhere on the Calendar and select Other Settings. Remove the checkmark from the option that says Compress Weekend Days.

Minimise Outlook To The System Tray!
You might want to make Outlook go and sit in the System Tray when you minimise it. Here’s how to make it do this: open the Registry Editor. (Remember to make a backup first.) Navigate to HKEY_CURRENT_USERSoftware MicrosoftOffice10.0Outlook Preferences. Add a new DWORD value called “MinToTray”. Assign it a value of “1”.

Attachments Taking Long To Open?
Some folks might experience this problem: when you double-click an attachment, the computer seems to hang, although the attachment eventually opens. There is a way to rectify this. When you double-click on the attachment, the familiar dialog box is displayed, asking you whether you wish to open the attachment or save it to disk. Beneath that is a box that says “Always ask before opening this type of file…”. This box is checked by default. Uncheck the box. Opening future attachments could be significantly faster.

The Intrusive Read Receipt
Read Receipts can be irritating. For example, it could seem rude if someone asks for a Read Receipt and you don’t send one. At the same time, if you do send one, it will indicate what time you opened the message-an invasion on your privacy!

You can choose to mark which incoming messages have Read Receipts attached: set your Inbox to display that field. At the Inbox menu, select View > Current View > Customize Current View. Click the Fields button.
In the “Select Available Fields From” box, make sure “All Mail Fields” is selected. Select “Receipt Requested” from the “Available Fields” box. Click Add to add it to the list of fields that display in your Inbox. Keep clicking OK until all dialog boxes go away.

Setting Up An Auto-Mailer
Say you use Outlook only while you’re in office. If you plan on being out of the office for a while, you can configure the Out of Office Assistant to automatically respond to any e-mail you receive while you’re away.

From the Inbox menu, select Tools > Out of Office Assistant. Complete the dialog box and click OK. Outlook will send the response you’ve indicated for as long as “I am currently out of the office” is selected. When you return, follow the same path as above and click “I am in the Office” to deactivate the Out Of Office Assistant.

MICROSOFT WORD XP

Emphasising Text
To give your text some kind of emphasis, highlight the text, right-click, and select Font. When the Font dialog box appears, select Text Effects. A list of effects will appear such as Shimmer, Blinking Background, and more. Select your desired effect. You can see how it will look in the Preview area. Press OK to close the dialog box.

Delete The Fonts MRU List
Word maintains a list of most recently used fonts, which appear at the top when you click the fonts drop-down. You’d like to modify the list, wouldn’t you? But you can’t! However, you can turn it off completely using the Registry Editor, which you might want to do in a number of situations-for example, if you’ve just experimented with a lot of fonts, and all of them keep appearing.

Navigate to HKEY_CURRENT_USER SoftwareMicrosoftOffice10.0WordOptions. (The “10.0” becomes “11.0” if you’re using Word 2003.) Choose Edit > New > String Value, and call it NoFontMRUList. Press Enter. Now with the NoFontMRUList string value selected, choose Edit > Modify. In the Value Data box, type in “1”. Click OK and exit the Registry Editor. You can, of course, change the setting back by changing the value to “0”.

Turn off that MRU Font List

Paste Quickly
Not too many folks use the [Insert] key to switch between insert and overwrite mode. You can turn the [Insert] key as a shortcut for pasting.
Go to Tools > Options. In the dialog box that appears, click the Edit tab. Check “Use the INS Key for Paste” and close the dialog box. Now whenever you press [Insert], the contents of the clipboard will be pasted into the document.

Save All And Close All
If you’re working on several documents at once, you may want to save or close all the documents without closing Word. To do so, hold down the [Shift] key and click the File menu. The Save option will become “Save All”, and the “Close” option will become “Close All”.

Move the mouse pointer to the left of the line or paragraph until the cursor changes from the “I” looking stick to the standard arrow cursor. Click once to select the current line of text, twice to select the current paragraph, or three times to select the entire document.

Sorting Names
Say you have a list of names in a document, and need to sort them. You can accomplish this task using tables, but if you don’t want to mess with tables, here’s the alternative approach.

First make sure the names in your document are arranged so there is only one person per paragraph. Select all the paragraphs that contain names. Choose the Sort option from the Table menu. Word displays the Sort Text dialog box. Click Options. Word displays the Sort Options dialog box. Select Other.

Erase the contents of the box to the right of Other, replacing it with a single space. Word will now know that you want to consider spaces as the dividing point between the sort fields. Click OK to close the Sort Options dialog box. Then, use the Sort By drop-down lists to specify the word by which you want to sort. For instance, if you want to sort by last name-which is the word after the first space-you should choose “Word 2” in the Sort By drop-down list. Click OK, and your names will be sorted.

Table-To-Text
There will be many occasions when you want to convert a table to text. To do this, first select the entire table. Choose Convert from the Table menu, then chose Table to Text from the resulting sub-menu. Word displays the Convert Table to Text dialog box. Select an appropriate character for Word to use to separate the columns of text. Click OK.

Large Icons In Word
The icons in the Word toolbar-such as the disk icon for Save-may be too small for you, or perhaps you just like large icons. You can make them bigger. Under Tools, click Customize. When the Customize dialog box appears, choose Options and check Large Icons. The toolbar icons will instantly get big!

Get tigger icons for word

Adjusting Column Width
If you have a table in your document, you may find that your overall table width is the way you want it, but the columns within the table are not just right. As an example, you may want the columns to be the same width as each other-this without disturbing the overall width of the table. To achieve this, select the table and then choose Table > AutoFit > Distribute Columns Evenly. Done!

Freeze Tables
If you don’t want your table to readjust itself to fit whatever you put in it, Word allows you to freeze a table’s layout. For instance, you might want the cells to be a certain size, and never change even when you place a large amount of data in the cell. The first step is to adjust the column width so that the columns in your table are the way you want them. Then, choose Table Properties from the Table menu. Word displays the Table Properties dialog box. Make sure the Row tab is selected. Select the Specify Height checkbox, and enter a height for the rows. Use the “Row Height Is” drop-down to choose Exactly. Now, go to the Table tab. Click Options. Word will display the Table Options dialog box. Clear the “Automatically Resize to Fit Contents” box. Click OK to close the Table Options dialog box, then click OK to close the Table Properties dialog box.

Track Changes Options
If you use the Track Changes feature in Word, added text is shown in a different color, and deleted text is shown with a strikethrough. You may want to change how Word shows your changed text. To make your configuration changes, first go to Tools > Options. Word will display the Options dialog box. Select the Track Changes tab. Using the controls in the dialog box, indicate how you want your changes to appear when you have Track Changes turned on. Click OK.

The following are some of the options available:
*Track Changes Options: Here you specify how you want text insertions to appear. They normally appear as underlined text in a different colour, but you can instruct Word to use different types of formatting for the text you add to the document.

*Balloons: The controls in this area allow you to specify if Word should use change balloons. These balloons appear at the right side of a document and indicate the changes made at different points.
*Changed Lines: This setting is used to specify how any lines that contain changes should be noted. Normally, Word adds a vertical bar to the outside border of the line.

Use the “Track Changes”feature in word to track changes in your document

Deleting Pages In A Document
If you need a quick way to delete a page in your document, you can use the following macro:
Sub Delete_Page()
ActiveDocument.Bookmarks(“Page”).Range.Delete
End Sub
Assign the macro to a toolbar button or a shortcut key (we’re assuming you know how to do so), and you can easily delete the current page, the one in which the insertion point is located. The macro bases its determination of a page on the current pagination of the document, which is affected by the printer driver you’re using and, of course, the formatting characteristics of the document.

MICROSOFT EXCEL XP

Blank And Non-Blank Cells
Figuring out the number of non-blank cells in a range is not entirely straightforward. The COUNTBLANK function returns the number of blank cells in a range, but what if you want to count the number of non-blank cells in the same range? One way is to use the COUNTA function:
=COUNTA(B1:B13)

The problem with this formula is that it doesn’t return the complementary value to what COUNTBLANK returns. In other words, the result of COUNTA added to the result of COUNTBLANK doesn’t equal the total number of cells in the original range. The reason for this is that both COUNTBLANK and COUNTA treat formulas differently. COUNTBLANK includes, as blank, formulas that return a blank value. COUNTA does not consider such cells blank (even though a blank is returned), so it includes them in its count. If you consider non-blank cells to be those that are not returned by COUNTBLANK, you will need to use this formula:

=(ROWS(B1:B13)*COLUMNS(B1:B13))-COUNTBLANK(B1:B13)
This subtracts the COUNTBLANK result from the total number of cells in the same range.

Counting Unique Values
Excel provides several different ways in which you can count unique values in a long list. Say you have a worksheet in which there is a list of first names, in cells A1:A100, which can contain duplicates. Say you need to determine the number of unique names in the list. First define a named range that represents the list of first names. In the following examples, we assume that the range is named FirstName. If the list contains only text entries and no blank cells, the following will provide a count:

=SUM(1/COUNTIF(FirstName,FirstName))
This should be entered as an array formula, by pressing [Ctrl] [Shift] [Enter]. If the list contains blank cells, then the formula becomes a bit more complex. The following long array formula will work if there are blanks:

=SUM(IF(FREQUENCY(IF(LEN(Countries)>0,MATCH(Countries,Countries,0),””), IF(LEN(Countries)>0,MATCH(Countries,Countries,0),””))>0,1))

Enter your formula. Select the cell containing the formula and press [F2]. This brings Excel to Edit mode. Hold down [Shift] as you use the cursor control keys to select the entire formula, including the equals sign at its beginning. Press [Ctrl] [C]. Then press [Esc] to get out of edit mode. The cell with the formula is still selected.

Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box. In the Names in Workbook box, enter the name you want assigned to the formula. Select whatever is in the Refers To box at the bottom of the dialog box, and press [Ctrl] [V]. The cell reference is replaced with the formula on the Clipboard.

You need to make sure there are no dollar signs in the formula. If there are, select and delete them. This method of using formulas, therefore, does not work well with absolute references.

Now, whenever you want to use the formula, you simply enter an equal sign and the name you gave to the formula. Even though the name shows in the cell, the formula assigned to the name is actually used in doing the calculation. Since the formula uses only relative references since you got rid of the dollar signs, it is always relative to where you use the name in the worksheet.

Formulas And Values
Excel normally displays the results of your formulas, but you can also force it to display the formulas themselves. If you’re like most people, you choose Tools > Options, then on the View tab, make sure the Formulas checkbox is selected. A much faster way to get the same result is to press [Ctrl] [`]. The second key is the one to the left of [1]. The shortcut is a toggle, which means you can press it repeatedly to switch between the display of formulas and results.

Using the TEXT function
Say you want a description for your data. One approach is to put the description near the cell containing the data-for instance, a numeric value could go in cell B1, and the unit description in cell C1.

Use the text function in Excel

Another approach is to put the description text and the numeric value together. Creating text strings does this. So to display “2 2 is 4″, you’d use =”2 2 is ” & 2 2

The disadvantage of this approach is that formatting the value takes more effort; since the result is a text string, numeric cell formatting does not apply. For example, considering the above formula, say you need to display two decimal places. You might select Format > Cell > Number tab and then choose a Number format that has two decimal places, but the results would not change. (The result of the formula is text, not a number.)

To affect the value formatting, use the TEXT function. To force the above results to display the value to two decimal places, use the following formula.

=”2 2 is ” & TEXT(2 2, “0.00”)

Here’s an example that displays “Today is” along with the current date. Enter the following formula:

=”Today is ” & TEXT(NOW(),”dddd, mmm dd, yyyy”)

The quotation marks are important, since you’re constructing a text string.

Finding The Smallest Or Largest Values
There may be times when you need to derive the smallest or largest value from a range, unless the smallest or largest value is zero. For instance, you might have a range of values such as {0, 3, 1, 4, 2}; in this case, the lowest value is zero, but the value you want returned is 1. There is no intrinsic function within Excel to return such a value. However, you can use a formula that will do the trick. Assuming that the range of values you want to analyze are in A4:A8, the following formula will return the lowest non-zero value:

=IF(MIN(A4:A8)=0,SMALL(A4:A8,COUNTIF(A4:A8,”=0″) 1),MIN(A4:A8))

This formula uses the MIN function to determine if the lowest value in the range is zero. If it is, then the SMALL function is used to derive the lowest value, excluding the zeros. The COUNTIF function returns the number of zeros in the range, and therefore tells SMALL which item from the range to pick.

A small change to the formula allows it to be used to return the largest non-zero number in a range:

=IF(MAX(A4:A8)=0,LARGE(A4:A8,COUNTIF(A4:A8,”=0″) 1),MAX(A4:A8))

These formulas will work for any range, unless the range is made up entirely of zeros. In that instance, a #NUM! error is returned.

Concatenating Cell Text
It’s easy to combine the contents of your cells. Say you have a list of last names in column A, a list of first names in column B, and a list of salutations in column C. If you wanted to derive a full name for these people, you could use the following formula:

=C4 & ” ” & B4 & ” ” & A4

Concatenate text from different columns and display the result in another

The result is that Excel combines the values from the specified cells and places spaces between them. The ampersand character (&) is used to indicate that Excel should add text together to create a new text value.
More Useful Averages
The average value for a set is in many cases determined by removing the highest and the lowest scores and then averaging the rest. You may need to do similar types of averages. To perform this type of averaging, you can use the SMALL function. Consider the following formula, which assumes you want to find an adjusted average of the range A10:A14:

=(SUM(A10:A14)-SMALL(A10:A14,1)-SMALL(A10:A14,2))/(COUNT(A10:A14)-2)
The SMALL function is used to determine the two lowest values in the range, and these are subtracted from the overall sum of the range. The resulting value is then divided by the COUNT of values in the range. Note that the COUNT value is decreased by 2 to compensate for the fact that you are ignoring the two lowest values.

Another way to calculate the same average is to use an array formula:

=AVERAGE(IF(A10:A14>SMALL(A10:A14,2),A10:A14))

Since this is an array formula, you need to enter it by pressing [Ctrl] [Shift] [Enter]. It examines each of the values in the range and only considers them for use in the average if they are larger than the second smallest value in the array.

While the array formula is shorter than the longer regular formula, there is one thing to remember: the array formula will produce an undesired result if there is a two-way tie in the second-lowest value in the range, or a three-way tie in the lowest value. For instance, if the values being averaged are 3, 2, 10, 3, and 7, then the array formula will produce an average of 8.5. This is because only the values 10 and 7 are above the second-lowest value, and the average of those two is 8.5. If you use the longer formula, then the average returned is 6.666667, which is the average of 10, 3, and 7.

If these formulas don’t work, check to make sure you have the Analysis ToolPak installed. The SMALL function is a part of the Analysis ToolPak. Choose the Add-Ins option from the Tools menu and make sure there’s a checkmark next to the Analysis ToolPak option.

What Is Excel Doing?
It’s often frustrating to figure out exactly how Excel arrives at a particular result, particularly if the formula is complex. But Excel provides a tool you can use to help figure out what is going on when it evaluates a formula. To access this tool, select the cell containing the formula you want to evaluate. Choose Formula Auditing from the Tools menu. Excel displays a sub-menu. From the sub-menu, choose Evaluate Formula. Excel displays the Evaluate Formula dialog box.

At this point, Excel shows the full formula from the cell, and part of it is underlined. This underlined area represents the part of the formula that Excel will next evaluate. This allows you to see what intermediate steps Excel follows in arriving at a result. Every time you click the Evaluate button, Excel replaces the underlined portion of the formula with a result.

Nothing you do with the formula evaluator actually affects the formula in your worksheet; it remains unchanged. Instead, Excel simply shows you what happens as it works through each part of the formula to arrive at a result.

From CAPS To Sentence Case
Some people type everything in CAPS, and if you receive a spreadsheet from one such person, what do you expect? You can use a macro to select a range of cells and convert any constants to “proper case.” After you’re done, the first letter of each word will be uppercase, and the rest will be lowercase. If a cell contains a formula, it is ignored. Here’s the macro.

Sub MakeProper()
Dim rngSrc As Range
Dim lMax As Long, lCtr As Long
Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
lMax = rngSrc.Cells.Count
For lCtr = 1 To lMax
If Not rngSrc.Cells(lCtr).HasFormula Then
rngSrc.Cells(lCtr) = Application.Proper(rngSrc.Cells(lCtr))
End If
Next lCtr
End Sub