Prevent Excel errors with data validation and conditional formatting.

Prevent Excel errors with data validation and conditional formatting. I have been utilizing both Microsoft Excel and Google Sheets in my business for more than a decade. With each passing day, I have discovered and implemented more efficient techniques to sanitize and validate data which has led to increased productivity, minimized errors, and brought immense satisfaction.

In this article, we will delve into two methods of validating and/or applying conditional formatting to a sample order form for error prevention and expediting fulfillment.

Prevent Excel errors with data validation and conditional formatting.

To follow along, you may access the Excel sheet utilized in this tutorial by selecting “File,” “Save As,” and “Download a Copy” or the corresponding Google Sheets version by selecting “File,” “Download” or “Make a Copy.”

Going forward, I will primarily focus on the Excel version, occasionally making references to any differences in Google Sheets.

Prevent Excel errors with data validation and conditional formatting.

Setup

Prevent Excel errors with data validation and conditional formatting.

I’ve designed an order form with three columns that allows stores to manage their inventory and place orders by specifying the quantities needed. The third column is for the warehouse to enter the actual quantity delivered. This is a practical setup that we will simplify for the purpose of this tutorial.

However, zero entries in the order column can create complications during fulfillment. To tackle this problem, we will demonstrate how to control cell values using a few helpful tools. Despite the clarity of the instructions, someone is bound to forget and enter a zero, which is why we need to implement these methods.

Conditional Formatting

By utilizing conditional formatting, we can effectively blank out cells that contain zero or negative values. To do this in Excel or Google Sheets, navigate to the Conditional Formatting

Prevent Excel errors with data validation and conditional formatting.

Option within the Home ribbon or the Format menu. If you can’t find it, try looking in the Styles dropdown or the three-dotted dropdown depending on your ribbon’s layout.

Prevent Excel errors with data validation and conditional formatting.

Excel may have an advantage in this regard, as it offers more options in a more intuitive way.
Once you’re in the Conditional Format menu, click Manage Rules to specify formatting based on various criteria.

Prevent Excel errors with data validation and conditional formatting.

Prevent Excel errors with data validation and conditional formatting.

To blank out cells with values less than or equal to zero in our example, we’ll select the Order column as the range and apply white fill and text colors.
This method avoids distracting zero values and keeps the focus on the fulfillment center.

Prevent Excel errors with data validation and conditional formatting.

On other occasions, conditional formatting can be used for color-coding data or creating color scales to aid data visualization.

Prevent Excel errors with data validation and conditional formatting.

Data Validation

An alternative solution available to us is data validation, which can be found on the Data tab in the ribbon. If you can’t locate it, you can explore the same ribbon options I mentioned earlier.

Prevent Excel errors with data validation and conditional formatting.

With this tool, we have various options to choose from to validate the data within a specified range.

Prevent Excel errors with data validation and conditional formatting.

For our purposes, we want to ensure that the data consists of whole numbers greater than zero, which is the opposite of the conditional formatting we applied earlier.

Prevent Excel errors with data validation and conditional formatting.

Unfortunately, Google Sheets does not currently allow us to include input messages in data validation. However, Excel does have this feature, which can be useful as it provides users with friendly reminders when entering data within the validation range.

Prevent Excel errors with data validation and conditional formatting.

By default, Excel will block any input that does not comply with the defined conditions. A pop-up message will indicate that zero is not allowed in our case.

We can customize this message and decide whether to block the input outright or allow a zero to be entered after the warning pops up while still applying the warning to the data.

Additionally, we can integrate any of these options with our conditional formatting so that even if we only issue a warning, we still hide the input with white text and fill colors.

The accompanying Excel and Google Sheets contain four columns that illustrate each of the above examples.

I hope this discussion has been useful.

Please check out my tech education channel on YouTube for video tutorials. Kindly give me a thumbs up and subscribe as I continue to grow my channel.

Have a wonderful day!