Don't stop with simple validation controls. In this article, you'll learn how to use custom formulas to limit data input.
Data validation is one of Excel's most powerful tools, allowing you to set limits for input. For instance, you might limit dates to a specific period or integers to an upper and lower limit using the built-in settings. What you might not realize is that you can enter custom formulas when the built-in settings aren't adequate. In this article, I'll show you how to put this feature to use by accommodating a budget limit.
I'm using Excel 2016 (desktop) on a Windows 10 64-bit system, but this feature is available in earlier versions. 365's browser edition supports data validation in use, but you can't add or manipulate data validation. You can work with your own data or download the demonstration files.
The example data
To illustrate combining formulas and data validation, we'll use the simple budget shown in Figure A. (The data set is a Table object, but using a Table isn't necessary for this technique to work.)
We'll add validation to this simple budget.
Initially, the budget uses a conditional format to alert you when expenditures exceed the budget. This is a common use of conditional formatting. Applying this conditional format is simple:
- Select D3:D7.
- Click the Home tab and choose New Rule from the Conditional Formatting dropdown in the Styles group.
- In the top pane, select the Use a formula to determine which cells to format option.
- In the lower pane, enter the formula =$D3>$C3.
- Click Format, click the Fill tab, choose a color, and click OK twice.
You can't really limit expenditures after the fact, so the simple conditional format in the Expended column might be adequate. But suppose you want to restrict Budget values. Specifically, let's suppose you want to reject a budget item if it pushes the total (C7) to exceed a specific limit.
Add data validation
At this point, you might consider more conditional formatting, but that route allows users to enter values you might not want. Instead, let's use a validation control that limits entries by comparing the results of accepting that value against a limit.
The total budget is the result of a SUM() function; it isn't a fixed value, so we don't want to change it. Instead, we'll add an input value (Figure B) that you can modify as your budget changes.
Add an input value for your total budget.
Next, we'll use a custom rule that takes the following form:
Keep in mind that the validation in column C and the conditional formatting in column D aren't connected. Now, let's create a data validation control that limits budget values as follows:
- Select C4:C7.
- Click the Data tab and choose Data Validation from the Data Validation dropdown in the Data Tools group.
- From the Allow dropdown, choose Custom.
- In the Formula control, enter =SUM($C$4:$C$7)<=$C$1 (Figure C).
- Click the Error Alert tab and enter Item Error as the title and the simple error message This item exceeds your budget limit (Figure D).
- Click OK.
This formula will reject any budget item that causes you to exceed your limit.
Enter a simple error message.
The control is ready to test now. Select any budget item in column C and increase it a bit. As you can see in Figure E, the control rejects the higher value and shares a bit of information as to why. Click Cancel. The budget is already at its limit. Excel will reject any attempt to raise the value of any budget item. The only way to increase a budget is to reduce one of the other budget items. Or, you can increase the overall budget limit in C1. Of course, you can spend whatever you like and the conditional formatting in column D will continue to alert you as costs exceed the budget.
The control rejects an item.
This powerful feature is easy to implement and versatile. Read the following articles to learn more about Excel's data validation feature:
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at email@example.com.