From the course: Working with Computers and Devices

Validate and format content

From the course: Working with Computers and Devices

Validate and format content

“

- [Narrator] When creating a spreadsheet in Excel, it can be useful to format cells to only accept certain types of data, especially if other people will be using the worksheet you're creating and you want to make sure they can't fill in any incorrect information. For example, I'm creating the spreadsheet here to determine which of two jobs will be the most profitable for me. I've already entered the hourly wage amounts, but notice I can also select either of those cells and enter words instead. I'll click undo. Now, since there's no reason to enter anything but numbers in these cells, I'll format them so that's all they'll accept. So I'll drag to select the two cells for hourly wage. And with those cells selected, I'm going to go to the data tab and look at the section called data tools. And here there's a button for data validation. Under the settings tab here, I can specify what type of input is allowed in the cell. Because this is an hourly wage, I'm going to allow decimal places. And in order to validate a cell like this that's going to accept numbers, I have to specify a range of numbers. In this case, I'll put in a range from zero to 100 as I doubt I'll be making more than $100 per hour. But I could always come back and change this if necessary. I'll click OK. So now if I try to enter anything but numbers in either of these cells, I get a message telling me that the value I entered doesn't match the cell restrictions. I'll click retry, and let's set the hourly wage to 14.75 again. When I press tab, that value is accepted. And I already have the value I want in this second cell. But it too only accepts numbers now. Now since these are both monetary values, I might want to format the cells to make their contents look more like dollar values. I'm going to select both cells again. And then under the home tab, in the number section, I'm going to select currency as the kind of number for these cells. And you can see that puts a dollar sign in front of them. If necessary, I can change the currency with this menu here. But I'll leave this set to English United States. Let's quickly format the hours per week cells in columns B and C. I'll select those two cells, and let's validate them as decimal numbers too, just in case one of the jobs requires shift in half or quarter hour increments. Again, I'll go back to the data tab. I'll click on data validation, set that to decimal, and set a range between zero and 80 hours a week. So let's say that job A is offering 40 hours per week. I'll type that and press tab. And job B is offering 37.75 hours per week. Now we can go back to the home tab, and this time I'll choose number as the format. That reformats the numbers in those cells and gives them two decimal places. You can change the number of display decimal places using these buttons here. So I could add a third decimal place if I wanted to, or I can reduce it to only one decimal place. Notice that automatically rounds up the 37.5 to 37.8. And if I lose the decimal places entirely, it rounds up to 38. But I'll keep this at two decimal places. Alright so by now, you should have the basic idea of how to enter data into a new worksheet from scratch, as well as how to validate and format the contents of cells.

Contents