Isn't it wonderful when our colleagues want to help us? Can you just imagine the joy of being able to delegate a little data entry to a co-worker? Come on, visualize it with me... That big pile of forms magically disappearing as the data is all entered in by someone else's capable hands... Ahhhh isn't that nice? Well... of course it is, until... you view the data that has been entered afterwards and you see IL, ILL, Illinois, Ilinios, etc. and you realize that it really wasn't so helpful now that you have to go through and manually edit every error... Sigh... another bubble burst.

But wait, it doesn't have to be that way. There are some steps you can take to encourage consistency with data entry. One of the starting points is setting a few validation rules.

Data validation allows you to set limits on what information can be entered into a cell. For example, you could impose a maximum dollar limit that someone is permitted to charge or you could insist that it be a whole number. Then when someone enters an incorrect amount, it would have an error message (which you can customize) pop up to tell you that it is not acceptable or it could even stop that data entry person dead in their tracks until they bring the data up to your standards. In addition, validation allows you to enter a prompt to help the user put something in the cell.

Validation is particularly useful if you are going to have someone else (or multiple people) filling in information on a spreadsheet and you want to make sure that they cannot enter something that isn't at least reasonably close to what you want.

First step, select your cells where you want to apply the data validation rule.

Let's say you want to limit your State column to a two letter abbreviation. You can do that clicking the column heading (the letter in the grey box at the top of your column), then Data - Validation. In the Allow: field, click the drop down and choose Text Length. Change the Data: field to Equal To. Type in 2 for the length. You could at this point click OK and it would apply that rule to the cells you have selected, but first, let's look at the other tabs.

The Input Message tab gives you a check box to show the input message when a cell is selected. You enter a title and an input message. Go ahead. Put a little something in there to test it. This will create a small comment on the screen that will come up when somebody clicks on a cell that has data validation applied.

The final tab is Error Alert. Again you get that check box to show the error alert after invalide data is entered. There are three styles:  Stop, Warning, Information. Each style gives you a title and message, which become a pop up window when the information is entered incorrectly.

Stop does just what it says: It stops the data enterer from inputting that data in the cell until they fix it to what you requested.
Warning prompts the data enterer with a message and asks for a Yes or No to Continue.
Information simply tells the data enterer something about the data (for instance, Expenses over $25 require a receipt.)

All that will work nicely to force someone entering data to use the two letter abbreviation for states. But wait, if you REALLY want to get fancy, you could enter on your spreadsheet all fifty states, and then in the Allow: field on the first Data Validation window, choose List from the drop down. Then you can click in the Source field, click and drag to select all those cells where you typed the states. That way, they will get a drop down list of all the states where they can choose the correct state.  That really ensures that it will be more accurate.

Check out data validation rules. I bet they will validate my theory that Excel is a pretty awesome program!

There are lots of shortcuts in Word. You are probably familiar with a few of these, but I doubt if you know them all. Note that many of these tricks actually work in one way or another in other Office programs (I'll explore some of those in a future blog posting).  Do you know any I have missed? Post them in the comments so everyone can see them!

In/On Your Text

• Single clicking your mouse places your cursor at a specific insertion point.
• Double clicking your mouse will select a word.
• Triple clicking your mouse will select a paragraph.

With Cursor in the Left Hand White Margin (changes to right pointing arrow)

• Single clicking will select the line of text (not sentence, line). Click and drag to select multiple lines of text.
• Double clicking will select the paragraph of text.
• Triple clicking will select an entire document (or else press Ctrl-A for All).

Using Shift/Ctrl When Selecting Text

Click once at the beginning of what you want to select. Scroll to the end, where you want to end your highlighting, but don't click yet. Hold down the Shift key and then click at the end of what you want to select. All text between those two clicking points will be selected. This is especially useful when you want to select across multiple pages, since as we all know, when you click and drag past one page, the cursor starts selecting at Mach 1 and you whiz past where you want to go, then when you try to go backwards, you whiz past where you want to backtrack... 

Want to modify your selection (like you included a few letters too many or two few)? Let go of the left mouse button. Press down the shift key, use the arrow keys to extend or reduce the selection. Please note that this works at the end of the selection of the direction you were going. In other words, if you selected left to right, holding down shift and using the arrow keys will extend or reduce the right end of the selection. If you selected right to left, holding down shift and using the arrow keys will extend or reduce the left end of the selection.

Pressing Shift-End will select all text to the right of the cursor to the end of the line.

Pressing Shift-Ctrl-End will select all text from the current location to the end of the document.

Pressing Ctrl and clicking your mouse will select an entire sentence (but only if you don't already have text highlighted for selection).

If you have selected text and you want to select additional text that is not next to the first text area, let go of the left mouse button after selecting the first set of text. Then press down the Ctrl button and click and drag the second set of text. If you want more text, let go of the left mouse button, keep holding down the Ctrl button, and then click and drag again (and so on). NOTE: If you accidentally let go of the Ctrl button and start to click and drag again, you will lose all prior selections and start from scratch.

Ctrl-left/right arrow will move your mouse by word. If you are selecting text and using the shift shortcut just mentioned, holding down the shift key and pressing Ctrl-left/right arrow will extend your selection by a word at a time in the appropriate direction.

MISCELLANEOUS:

Word has an extended mode. If you look down at the very bottom you will see a status bar that includes the letters EXT, greyed out. If you doubleclick that, it will turn black, which turns extended mode on. This makes your arrow keys act like you are holding down your mouse and clicking and dragging. It also allows you, for instance, to just press period and the computer will select all text to the next period. I find this mode a little cumbersome, but there are times when it is useful. Doubleclick the EXT to turn it off again or press ESC. (Bet you didn't realize all those little abbreviations that are greyed out actually do something, did you??? Ohhhh yessss).
 
And this is one of my personal favorites and one that never fails to get at least a couple "dang it, I didn't know I could do that - I've wasted hours deleting stuff manually in that situation" comments... To delete a tabbed column of text (or whatever) in the middle of your page, hold down the Alt key while you select with your mouse, starting at the top left of your selection.

There are even more ways to move through and select text in Word, but this should give you a pretty good start. :)
 

Sometimes you really want to keep text together in Microsoft Word, such as not wanting a phone number to wrap in the middle or not wanting a paragraph to be split. There are a variety of ways to do this, depending on what you are trying to accomplish. These instructions are for Word 2003 but all options are available in Word 2007 as well.

To keep text together with a space (for example, first name and last name), use a hard space by holding down Ctrl-Shift and pressing the Spacebar.

To keep text together with a hyphen (for example, a date or phone number), use a hard hyphen by holding down Ctrl and pressing the - (minus) button. Note, no shift!
 
To keep a paragraph together as one unit, click with your mouse inside the paragraph you want to keep together, then click Format - Paragraph. Click the Line and Page Breaks tab, then check mark the box of Keep Lines Together. This means that when a page starts getting full, Word will move the whole paragraph to the next page (versus moving the last line of the paragraph to the next page).

To keep a paragraph with the next paragraph (like a section heading with the section text), select the first paragraph that you want to keep with the next paragraph (don't forget to select the first paragraph and the blank line if you have one between the heading and the text!) and click Format - Paragraph. Then click the Line and Page Breaks Tab, and check the box for Keep With Next. This ensures that a page break won't occur in the middle of your header and text (or between paragraphs you want to keep together). I use this ALL the time, especially when doing reports or designing forms for instance, where I don't want the question to break from the answer lines across a page.

IMPORTANT HELPFUL HINT: If you are ever having trouble with a Table Cell/Row staying together even when you have the table property of Allow Rows To Split Across Pages checked, it is probably because the text inside the cell has been formatted with Keep Lines Together or Keep With Next (and this is not always on purpose).

SECOND HELFUL HINT: Easiest way to check for this formatting? Click the Paragraph Marker button (looks like a backwards P on your standard toolbar, right next to the zoom drop down box). Your text will have a black box next to the beginning of the paragraph if one of these options is turned on. This view will also show you hard spaces (they look like circles instead of dots) and hard hyphens (they look like longer dashes versus a hyphen).

THIRD HELPFUL HINT: Like this formatting and want to repeat it in other places? Format Painter copies Keep with Next and Keep Lines Together formatting too!

You'll note that Widow/Orphan Control options are checked by default on the Line and Page Breaks Tab. Have you secretly always wondered what the heck that even means? Wonder no more! A widow is when you have the last line of a paragraph all by itself at the top of a page. An orphan is when you have the first line of a paragraph all by itself at the bottom of the page. Word moves a second line to be with that lone line (or moves the full paragraph if needed). I don't imagine anyone really wants to turn that option off. :)

So now, as Al Green would sing... "Let's stay together, Loving you whether, whether Times are good or bad, happy or sad..." Don't we all feel that way about Microsoft Office sometimes???

Conditional formatting is formatting (that you choose) which is applied depending on what is typed in the cell (or the results of a formula). This feature is one of the best things since sliced bread. Take my word for it and take 30 seconds to check it out!!!

For example, if you set up a budget, you could set the formatting to be red if someone exceeds their budget amount. You could also have cells highlight if they are blank but have no formatting if something is typed in the cell if you want to keep track of things that are outstanding (like invitation answers, a column for tracking signatures received, etc.).

Excel 2003

To apply conditional formatting in Excel 2003, first highlight the cells that you want to be conditional, then select Format-Conditional Formatting. Excel will ask what parameters you want to set for the cell. Cell Value or Formula is (fill in the blank: between, equal, not equal, etc.) and then choose either a cell reference (i.e. the budgeted number) or a number that you type. Then click on the Format button and choose whatever formatting you want to be applied to the cells (red, bold, whatever you choose). Click OK.

When you enter a number into that cell, if it falls inside the parameters you set up (within budget) then the text will be normal. If it falls outside (exceeds budget), it will have the formatting you chose.

You can apply up to three parameters (i.e. green color for below 90% of budget, yellow color for 91-100%, red for 101%+).

To remove the conditional formatting, select the cells that have it (or use the tip I discussed in a previous blog posting to select all cells in your worksheet that have it applied: Edit - Go To - Special - Conditional Formatting).

Excel 2007

Conditional Formatting has really been beefed up in Excel 2007 and had some really nifty stuff added. This feature is applied from the Home ribbon. When you select your cells and then click Conditional Formatting, you will soon see that there are a bunch of new options in there. Check them out!

NOTE: On all of these choices, you will see "more rules" on the submenu. This allows you to set even more options on all these various rules. You can also apply more than one set of conditional formatting at a time (i.e. data bars and icon sets together).

Highlight Cell Rules - This is where you go to set straight mathematical rules, like greater than, less than, as well as text based rules, date based rules (great for highlighting past due items), etc. Note the nifty new feature to apply conditional formatting to duplicate values in a column. How handy is that when you want to see at a glance if anything has been duplicated in a long list? :)

Top / Bottom Rules - These are designed to highlight your top or bottom "x" in a column (top 10, etc.)

Data Bars - This applies colored bars to the cells, where the length varies based on the contents, relative to each other (in other words, four cells with the values of 0,2,4,6 would have 25%, 50%, 75%, 100% bars)

Color Scales - This applies colored bars with different shades applied based on the contents, relative to each other (see above).

Icon Sets - This applies colorful icon sets to the cells, based on the contents. Some of these are really cute. Most are based on the green/yellow/red color scheme or black/grey/white.

New Rules - This brings up the same submenu as clicking More Rules on the other submenus. It allows you build a rule from scratch and tweak the settings.

Clear Rules - Go here to clear all rules from selected cells or from the whole worksheet. 

Manage Rules - Go here to clear selected rules. If you have more than one conditional formatting format applied (like the previously mentioned data bars and icon sets for instance), you can select the rules individually, reorder them, and/or delete them. You can also edit individual rules from this submenu.