Excel has a nifty feature called Goal Seeker. If you have a particular dollar amount you want to arrive at by changing one variable (for instance, how much money can I borrow at 7% interest for 60 months in order to have a $350 monthly car payment), Goal Seeker can help you find the answer.

Using the car example above, we'll create a payment formula (Don't know how? Check out my prior blog on it: http://us.deskdemon.com/dnet/blog.php?user=msmarieh&blogentry_id=766). With your cells already filled in for the PMT formula, choose Data Ribbon - What If Analysis - Goal Seek (Tools-Goal Seek in 2003). Set cell D1 (Goal Seek always works on a cell with a formula in it) to value -$350 by changing cell C1 (our total amount borrowed). Goal Seek will then change the cell to the correct amount (in this case borrowing $17675.70 at 7% interest would give you a $350 monthly payment).

Excel has an additional feature called Scenarios which allow you to change more than one element of the formula. Choose Data - What If Analysis - Scenario Manager (Tools-Scenarios in 2003) - Add (to create a new one). Give your scenario a name. I suggest something descriptive, like interest rate changed to 6.5%. Select which cell will be the changing cell (for example A1 - the interest rate), put in the revised amount (.05416666 for 1/12 of 6.5% for instance). Note that you can select more than one cell by holding down your control button or clicking and dragging. Excel will ask you what each cell's value should be. It will then come up with an answer that matches your differences. Scenarios are essentially "what if's". What if I got a lower interest rate and took a loan for a longer period of time? What if I borrowed more, but paid it back faster? What if our company increased it's revenues by 10% and lowered expenses by 5% across the board next year?

Lots to play with for these Excel features. Take a moment to poke around with them!

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!

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.