Data Validation for Numbers in Excel Tips and Tricks

Excel offers effective tools for controlling and validating large volumes of data input. It also provides means for simultaneously monitoring multiple cell values that don't meet specific criteria.



Data Validation in Excel

controlled data

Prepare a worksheet for income and expenses as shown below:

Income

Profit is, naturally, income minus expenses. Suppose you need to check which days the profit fell below $40. Here's the solution:

  1. Select the data in the range D2:D6 and choose "Data" - "Data Tools" - "Data Validation."
  2. In the "Data Validation" dialog that appears, configure it as shown below and click OK.
    3. Total Profit
  3. Now select "Data" - "Data Tools" - "Circle Invalid Data" from the dropdown menu and observe the result:
    4. Circle Invalid Data
  4. If needed, you can remove the red circles by choosing "Clear Invalid Circles."

Note: Circling invalid data works only in ranges where the same criteria are set using "Data Validation."

Excel's Data Validation Window

Let's simulate the following scenario: you need to purchase office supplies, consumables, and cleaning products for a small company. The goal is to separately calculate the total cost for each group of items and the overall expense.

TOTAL price

Adding new items, you want to monitor the overall expenses in real-time. Here's how:

  1. Create a workbook with sheets as shown above in the image and add several items to each sheet.
  2. In the "TOTAL" sheet, use formulas as shown in the image and navigate to cell B4.
  3. Switch to the "Formulas" tab and select "Watch Window."
    4. Watch Window
  4. In the popped-up window, click "Add Watch" and specify the cell address you want to monitor: =TOTAL!$B$4. Click "Add."

Now you can avoid uncontrolled expenses while filling the sheets with new items.

Example data controlled data

No matter which sheet you are on, the Watch Window floats above all windows, displaying the overall expense. It's easy to make it appear above the entire Excel program window by dragging the title of the Watch Window to the status bar (the area with sheet name tabs).

To panel tools

download file Download

Additionally, you can move the Watch Window to the Excel toolbar, as shown in the image above.