Index/Match function in Excel

[EXCEL TIP] Adding a drop-down list in excel

[EXCEL TIP] Adding a drop-down list in excel ---> 

Standard forms also tend to have standard answers, such as a day of the week, or a month, so a drop-down box with pre-filled selections would be useful.

Assume that you have an input cell in which the user is supposed to enter a day of the week: Monday, Tuesday, Wednesday etc.

Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell G1 and extend down to G7, but normally you would put them outside of the readable area. In Excel terminology, a rectangular group of cells (such as G1 to G7) is called a range.

Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)

Select the cell where you want the drop-down list.

On the Data tab, in the Data Tools group, click Data Validation

Excel Data validation

In the Allow box, click List.

To specify the location of the list of valid entries, enter a reference to your list in the Source box in this case it's (=$G$1:$G$7) or click in the Source box and then select your list in the current spread sheet to populate the reference automatically.

Make sure that the In-cell dropdown check box is selected.

To specify whether the cell can be left blank, select or clear the Ignore blank check box.

You can go one stage further and display an input message when the cell is clicked.

Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.

Select one of the following options for the Style box:

To display an information message that does not prevent entry of invalid data, click Information.

To display a warning message that does not prevent entry of invalid data, click Warning.

To prevent entry of invalid data, click Stop.

Type the title and text for the message (up to 225 characters).

Adding an input box to a drop down list in Excel

Note If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.

If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.

If your list of items is relatively small, you can enter the list items directly into the Source field. Just separate each list item with a comma.

Cheat Sheet: Must-Know Excel Tips and Tricks - I

Cheat Sheet: Must-Know Excel Tips and TricksPart -I 

While Microsoft Excel may not exactly have a reputation as the sexiest computer application in the PC world, it is a useful one. And since chances are good you’ll need to use it over the course of your scholarly, business or personal life, having a few tips and tricks up your sleeve is a good plan.

Jump to a Cell

To the left of the formula bar, you’ll find a cell listing that changes to show you exactly which row and cell you’re working in. By clicking on this area, you can type in a specific column number and row name to jump directly to that cell.

Fix Hinky Numbers

Occasionally, Excel will erroneously store certain numbers (especially 0) as text. This can really become a hassle if you’re trying to manipulate information within cells that contain those numbers. To fix the same error across the board, insert a new column. Next, enter a “1” into any empty cell in the new column. From here, you can copy the “1” you just typed, copy it to your clipboard and choose the numbers from the range you are looking to fix. From the drop-down menu under Paste, choose Paste Special. You can now pick options like Multiple or Values.

Ensure Empty Cells

Likewise, Excel will sometimes neglect to properly clear a cell. That is, a cell that should be empty isn’t. Regardless of whether or not anything actually appears in the cell, it doesn’t register as empty, making it impossible to properly use commands and formulas within it. To fix this spreadsheet faux pas, select the affected cells all the way down to the final row. Click the Delete key, and you should be good to go.

Delete Blank Rows and Columns

Sometimes, you’ll find your table data separated by blank rows or columns. Luckily, cleaning up this problem is quick and easy. Simply select a column and hot F5. From there, you can choose Special and then Blanks. Once these blank cells are zeroed in on, you can go to the Home tab and choose Delete.

Show Formulas

If you’re dealing with a giant spreadsheet full of complicated formulas, it’s often imperative to take a look at not just your final values, but how you got there. Hitting CTRL and ~ simultaneously will change your spreadsheet to show the formulas within each section. Use the same combination to go back to viewing your values.