how do you limit what can be entered in excel for mac 2011

How Do You Limit What Can Be Entered In Excel 2011 Full Size And You

Sometimes (see below for when), Excel gets fixated on some cell formatting and starts applying it every time I fill in any new blank cell.

Excel seems to accept white cells as a valid formatting choice, and treats them with a respect it would never give to unformatted cells.

Trouble is, white fill brings certain problems of its own, so I wouldnt call it an answer.

Unfortunately it doesnt work, the same thing still happens, even after restarting Excel after disabling autocomplete (autocomplete is still turned off after restart).

I just tried it and have included screenshots showing the results at the end of the question.

Not the answer youre looking for Browse other questions tagged ms-office.

This site is not affiliated with or endorsed by Apple Inc.

If someone enters anything thats not supposed to be therelike lunch at airport instead of 15.68 on an expense reportExcel rejects the input until they get it right.

Lets say we want to make sure that people only enter numerical values that are formatted as currency (i.e., some digits, followed by a decimal point, followed by two more digits) into certain cells.

How Do You Limit What Can Be Entered In Excel 2011 Full Size And You

If your window isnt full size and you cant see the labels, its the icon with two horizontal boxes, a green check mark, and a red crossed circle.

For our expense report, were going to insist that users put in a number with two decimal values, so we would select the Decimal option.

You can also select other criteria, like making sure a cell contains text, a time or date, text of a specific length, or even your own custom validation.

Since we want a numerical value corresponding to currency, were setting the Data dropdown to the between setting.

Then, were configuring a minimum value of 0.00 and a maximum value of 10000.00, which is way more than enough to cover our needs.

For example, if we type pancakes for the Breakfast value instead of the cost of the meal, well get an error message.

Youve got two options here (and you can use both of them if you want).

You can use the Input Message tab to have a pop-up tool tip show people the type of data you want whenever they select a cell for which data validation is turned on.

You can also use the Error Alert tab to customize the error they see when they enter the wrong type of data.

Here, make sure the Show input message when cell is selected option is turned on.

As you can see below, just clicking in one of the cells pops up the message letting people know whats expected.

Make sure the Show error alert after invalid data is entered option is turned on.

You can go with a Stop (the red circle with the X), Warning (yellow triangle with an exclamation point), or Information (blue circle with a lowercase i), depending on how strong you want the message to come across.

And this is doubly true if youve set up formulas or any kind of automation tasks that rely on that data.

He spent five years writing for Android Police and his work has appeared on Digital Trends and Lifehacker.

Hes covered industry events like the Consumer Electronics Show (CES) and Mobile World Congress in person.

Since we launched in 2006, our articles have been read more than 1 billion times.

How Do You Limit What Can Be Entered In Excel For Mac 2011