I've been using Microsoft Excel and Google Sheets in my business for over a decade. And as I've learned better ways to clean and validate data, it's increased productivity, decreased human errors, and generally caused a lot of joy! 🥳
In this article, we'll look at two ways to validate and/or apply conditional formatting to a sample order form to prevent errors and speed up fulfillment.
You can find the Excel sheet we're using for this tutorial here.
You may download a local copy to tinker with by selecting File, Save As, Download a Copy:
You can find a Google Sheets version of the same thing here.
You may download or make a copy online by selecting File, Download or File, Make a copy.
I'll discuss the Excel version from here on, making reference when something differs in Google Sheets.
Video Walkthrough
Oh, and here's an enjoyable video walkthrough should you feel so inclined. 😁😁
Setup
I've created a three column order form where a store may inventory their product and enter an amount to order. The third column is used by the warehouse to enter how many were actually delivered. This is a real world setup that we'll use in simplified form for this tutorial.
It can be difficult for fulfillment if there are zeros entered into the order column. Instead of allowing this, we'll use a couple tools to show how to control values in a cell. No matter how clear the directions are, someone will always forget and enter a zero.
Conditional Formatting
By applying conditional formatting, we can effectively white-out the cells that contain zeros (or any negative values).
From the Home Ribbon in Excel and the Format menu in Google Sheets, select Conditional Formatting.
If you don't see the conditional formatting as an option, it'll be over in the styles dropdown or in the far right in a three-dotted dropdown, depending on whether you've got the classic or the new style of ribbon displayed.
If you want to change your ribbon's layout, select this dropdown arrow at the far right of the ribbon:
Once you're in the conditional format menu, click Manage Rules. This will let you specify the formatting depending on a ton of options.
This is where Microsoft Excel does have a leg up on Google Sheets. Excel has more options laid out in a more intuitive way. You can do the same things in each program, but Excel has organized theirs a little better in my opinion.
We are going to select the Order column as our range and then highlight cells with cell values of less than or equals to zero.
At other times, you'll be using conditional formatting to make data visualization using colors and color scales, but in our case, we want to blot out the zero value.
To do this, I've simply selected a white fill color and a white text color. 🤔
And now, voilà! If a zero amount is entered, it will simply white out to not distract from the fulfillment center:
Data Validation
The second option at our disposal is data validation. You can find this on the data tab in the ribbon, and if you're not seeing it, you can find it by exploring the same ribbon options I detailed above.
This will give us a host of options to select to validate the data going into a specified range. There are many options to choose from for our data.
In our case, we want to make sure they are whole numbers greater than zero. Sort of the opposite of the conditional formatting we did above.
Another nice Excel feature that's missing at the time of this writing in Google Sheets is the ability to put an Input Message into the data validation.
Now, whenever you are on cell in the data validation range, a friendly box will pop up with directions reminding you to not order a zero amount. 😀
Data validation in Excel defaults to blocking any input that doesn't adhere to the defined conditions.
So, you'll receive an ugly pop up preventing you from entering a zero.
We can improve upon this by setting a custom message here too, though. And we can select whether to block it outright or to allow a zero to be entered after the warning pops up. Effectively allowing the warning to be ignored in the event that there's a reason to do this.
And finally, we can couple any of these options with our conditional formatting so that if we do only warn against the entry, we still blot it out with the white text and white fill color.
The accompanying Excel Sheet and Google Sheet contain four columns of each of the above examples for you to see in action.
I hope this has been helpful for you!
Please come see my video tutorials on YouTube. I'd appreciate a like and subscribe as I'm growing my tech education channel there!
Have a great one!