Guide Users To Enter Correct Values Into Numbers Spreadsheets

If you are designing a Numbers spreadsheet to give to users that allows them to enter values then you may want to prevent them from entering values outside of a range. You can use conditional highlighting to change the color or style of a cell if the user enters a bad value. You can use a formula in another cell to indicate a bad value, plus instructions. You can also use sliders, steppers and pop-up menu cell formats to limit values.
You can also watch this video at YouTube (but with ads).
Video Transcript: Hi, this is Gary with MacMost.com. Let me show you how you can discourage users from entering in bad values in cells in Numbers. 
MacMost is brought to you thanks to a great group of supporters. Go to MacMost.com/patreon to read more about it. Join us and get exclusive content.
So I can't limit the values entered into cells in Numbers. You can discourage bad values using a number of different techniques. Here's a simple table and I've entered  some values in as examples. The idea is I want to make sure that the values in column B are numbers between one and nine. So I have mostly good values in here but I have one that's too big and I have one that's too small and I've got on that's blank. Now I can't go and set limits for this to force somebody to enter in values between one and nine. But I can use a few techniques to discourage them from entering wrong values.
The first technique we're going to look at is Conditional Highlighting. So I'm going to select all the cells in here. I can select the entire column by clicking on the column head but that includes the Header row there. So instead I'm just going to select the cells and make sure I select all of them. Then then I'm going to go to Format, Cell, and then Conditional Highlighting. Now I'm going to Add a Rule. So the first rule I'm going to add is pretty simple. I'm going to choose numbers here on the left, I'm going to choose Not Between. Then I'm going to say it needs to be between one and nine and if not it's going to do something like do a red fill here. You can see it even updates as I'm working here. So I can see the three bad values. I can hit Done. Now when I go and change a value, like I'll change this to five, you can see it corrects itself. If I change a value here to outside the range it makes that one red so I can see there's an error. So it's really obvious when there's an error. Now if I hit Return here to add a new value the Conditional Highlighting will follow it since it's in all of the cells. It's immediately red because it's blank which could be a good indicator that I need to enter a value here. As soon as I enter a value in and hit Return, since the value is a good one, it's not red anymore.
So already this is pretty useful. But let's say I want to modify it a bit. Let's say I don't want it to be red if it's a blank cell. So I want to select all of the cells again and I'm going to go to Conditional Highlighting, Show Highlighting Rules since there's already one there that's what the button says now. I'm going to Add a Rule. In this rule I'm going to go and say blank and is blank. Then if it is blank instead of red fill I'm going to change it to be something else. I'm going to change it to Bold. I don't want it to be Bold either but by choosing Bold I turned off the red fill. Now I can go to Custom Style and I can turn off the Bold as well. Basically it's going to be the default style.
Now what I want to do is I want to put this rule first because the rules are going to execute in order and if a rule is matched it will stop executing the rules. So if the cell is blank, custom style of really no style, and then rule 2 will never execute. So now if I go and enter in another row there you can see that one's blank until I enter something in. If it's too big of a value it's going to give me red. If it's in the range it will be blank. You see the one that was blank already shows no red there. So this is whether or not you want to allow blank values.
Now you can also allow special values. Like say I want numbers between one and nine but 55 is also okay. So I can allow that as well. I can select all these and do the same thing again. Show Highlighting Rules. I will Add a Rule and I'm going to say Numbers equal to 55. Then I'm also going to make sure I switch to something that's not a fill there. Bold. Then I'm going to switch away to Custom Style and turn off Bold and then set that to be the first rule there. Now you can see the 55 is there. So then if I do another row and enter 55 in, it accepts it. Do another number that's outside the range it doesn't accept it.
So this is all fine but it's also a little mysterious, right? If the user didn't read the instructions they might not understand why this is red and this is red and these others are not. So what you can do is you can use another cell to actually give them instructions. I'm going to click here in this cell and I'm going to start a formula by hitting the equals key and I'm going to do an IF statement. An IF statement will check for a condition and put one value in the cell if it's true and one if it's false.
So then I'm going to do parenthesis. There's actually two conditions I want to look for. So I'm going to do OR and parenthesis and the first condition is if the cell to left of it is less than one and comma because the second condition is next and that is if the same cell is greater than nine. End the parenthesis for OR and then comma. Now what text I want to have in the cell IF it is true. So if it's less than one and greater than nine. Then I'm going to put instructions. That's all in quotes and then a comma and then the instructions for if everything is correct is nothing. So quotes with nothing inside. Then I'll close the parenthesis.
Now I'm going to take that formula there and I'm going to paste it in all of the cells. I'm going to enlarge the column here. You can see that I get this everywhere the value is not between one and nine. Now I have that special rule for 55. So I'm not taking that into account there. I would need to add that as another OR in there if I wanted that to be included. But the idea here is that if somebody enter's in a wrong value, like zero or 44, they not only get red but they also get instructions.
You could further go in and use special characters in here. Like I'm going to use Control Command Space at the beginning of this. I'm going to search for an arrow and I'm going to use this left arrow here. Enter that in plus a space. Now I copy and paste the formula throughout and you get a little arrow there. So it's even a little clearer. So now as I go to the next line you can see it even appears when it's blank because I'm not testing for that. Which is nice because it gives me instructions.
If everything here is fine you would enter a new value like this and it would give you a prompt. Go and do a new one it gives you a prompt again. If you get it wrong it's red and you still get the prompt there. So this really helps to guide the user of the spreadsheet so they enter the values that are within the proper range.
Now a completely different technique is to Format, Cell, and change the data to either Slider or Stepper. Either one of these will allow you to set a minimum and a maximum. So I can set one for the minimum and nine for the maximum and increment of one and now what happens is these cells, instead of you typing in them, you get this slider here. You can do the same things if you do Stepper. But Stepper has these little arrows. Now you can't go outside the range. If you try to enter in a number it will actually stick within that range. If you're going to do a collection of values you can actually do a Pop Up Menu and then you can enter in different values. So you can click  the Plus button and enter in all the values, even include odd values like 55 there, get rid of this one here. So I have a nice list in the order I want. Now you have this Pop Up Menu where you get to choose from one of these. In that case you can't even type a value.
Guide Users To Enter Correct Values Into Numbers Spreadsheets

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
