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.
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.
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.
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
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.
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.
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.
On other occasions, conditional formatting can be used for color-coding data or creating color scales to aid data visualization.
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.
With this tool, we have various options to choose from to validate the data within a specified range.
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.
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.
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!