Excel Tips.docx

Create A Drop-Down List Of Possible Input Values Category: General | [Item URL] If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros. Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell.

Here's how to create a drop-down list: 1. Type the list of valid entries in a single column. If you like, you can hide this column (select Format, Column, Hide). 2. Select the cell or cells that will display the list of entries. 3. Choose Data, Validation, and select the Settings tab. 4. From the Allow drop-down list, select List. 5. In the Source box, enter a range address or a reference to the items that you entered in step 1. 6. Make sure the 'In-cell dropdown' box is selected. 7. Click OK. If your list is short, you can skip step 1 and type the list entries directly in the Source box in step 5, separating items with a comma. The Data Validation dialog box has two other tabs. Click Input Message to add a prompt that will appear when a user selects a cell. Click Error Alert to specify a custom error message if the user's entry is invalid.

The handy data validation feature suffers from one serious flaw. If you paste an entry into a cell that uses data validation, the validation isn't performed. And if you select that cell again, the drop-down list no longer appears. Fortunately, you can circumvent this problem by protecting the worksheet: Select Tools, Protection, Protect Sheet.

SUMIF Function In Excel 2010 

by Usman Javaid on March 01, 2010 MS Office 3 Comments

In Excel 2010, by using SUMIF function we can calculate the sum of multiple data/entries based on desired condition. As name implies, it sums-up the data if the criteria is met. It takes three arguments in which we need to select range of data, criteria ,and sum_range to find out the desired result. This post elaborates a simple usage of SUMIF function. Launch Excel 2010, open a datasheet on which you want to apply SUMIF conditional logic. For Instance: We have a worksheet containing customer records with fields; Names, Items, Price andPaid?.

Now we want to check the total amount paid by customers and total amount customers still owes. For that we need to add two new labels, Total Amount Received, and On Credit.

Now we need to write a formula for Paid Amount, which yields sum of amount that has been paid by the customers. Syntax:

SUMIF(range, criteria, sum_range) Formula: =SUMIF(D2:D11, “Yes”, C2:C11) In formula, D2:D11 contains keywords Yes and No that show that whether customer paid the amount or still owes, C2:C11 contains Price of the corresponding product. By applying this formula it will show that sum of the amount paid by the customers by searching Yes in Paid? column and adding corresponding values in Price column. It will evaluate that 774$ has been paid, as shown in the screenshot below.

Now we will apply same formula to see how much amount customers still owes, for that we need a slight change in condition.The formula will search No in paid? column and sum up corresponding values in Pricecolumn. =SUMIF(D2:D11, “No”, C2:C11) It yields sum of amount owed by customers as shown in the screenshot.

Create or remove a drop-down list Show All

To make data entry easier in Excel, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want. To create a drop-down list from a range of cells, use the Data Validation command in the Data Tools group on the Data tab. 1. To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example:

A 1

Sales

NOTE

2

Finance

3

R&D

4

IT

You may want to sort the data in the order that you want it to appear in the drop-down list.

2. If you want to use another worksheet, type the list on that worksheet, and then define a name for the list. How? 3. Select the cell where you want the drop-down list. 4. On the Data tab, in the Data Tools group, click Data Validation.

Issue: The Data Validation command is unavailable. 5. In the Data Validation dialog box, click the Settings tab. 6. In the Allow box, click List. 7. To specify the location of the list of valid entries, do one of the following: 



If the list is in the current worksheet, enter a reference to your list in the Source box or click in the Sourcebox and then select your list in the current spreadsheet to populate the reference automatically. If the list is on a different worksheet, enter the name that you defined for your list in the Source box. In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter=ValidDepts.

8. Make sure that the In-cell dropdown check box is selected. 9. To specify whether the cell can be left blank, select or clear the Ignore blank check box. 10. Optionally, display an input message when the cell is clicked.

How to display an input message 11. Specify how you want Excel to respond when invalid data is entered, by doing the following: 1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected. 2. 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. 3. Type the title and text for the message (up to 225 characters). 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." NOTES





 

To delete a drop down list, select the cell with the list. Click the Data tab, and then in the Data Group, clickData Validation. In the Data Validation dialog box, click the Settings tab, and then click Clear All. Learn more about removing data validation. The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list. The maximum number of entries that you can have in a drop-down list is 32,767. If the validation list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, seeProtect worksheet or workbook elements.