Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Now let's take a look at how to create a basic spreadsheet from scratch. Again, I'm working in Microsoft Excel, but everything I'm showing you here will apply to other spreadsheet applications. I'm going to create a new blank spreadsheet by choosing File > New Workbook. You can always use the keyboard shortcut of Command+N. So here is a new spreadsheet. I'm going to increase the size, so we can see it a little bit better. Let's say I'm trying to decide between taking two jobs at an hourly rate. One of the jobs pays more per hour, but offers slightly few hours than the one that pays less.
I'm going to start by using the first row of Columns B and C as headers for the two job calculations. To type into a cell, just click it once, so I have selected sell B1 in this case. I'm going to type in Job A. Now, to move horizontally from cell to cell, press the Tab key on your keyboard. Each time you press it, you'll move one cell to the right. You can go back to the left by holding Shift as you press Tab. So I'll label cell C1 as Job B. I'm going to use column A to label all the rows I'm going to need.
So I'll click in A3, and I'll type in "Hourly Wage." Now, when you're working in columns, pressing Enter or Return on your keyboard lets you move down in a single column. Again, holding Shift lets you move in reverse. So I'm going to hold Shift, press Return a couple of times to go back up, call this one Hours/week, and I'll go down two rows, and I'll put in Annual Income label here. Now, the text here is a little bit wider than the cell itself, so I'm going to move my cursor between the headers for column A and B until I see this two-headed arrow.
I'm going to drag slightly to the right, just to widen that column a bit. So, so far I've added labels to these cells. Cells containing only text and no numbers are generally referred to as labels. Next, I'm going to add the data that I know about each job, namely the hourly wage and the hours per week. Now, since these are going to be numbers, I could format the appropriate cells to only accept numbers. It's not absolutely necessary, but it's a good habit to format your cells for the type of content they're supposed to accept, especially if other people are going to be using the worksheet you're creating, and you want to make sure they can't fill in an incorrect information.
So I'm going to drag to select the two cells in the Hourly Wage row. With those cells selected, I'm going to go to the Data menu and choose Validation. Here under the Settings tab, I can specify what type of input is allowed in this cell. Since this is an hourly wage, I'm going to allow decimal places. In order to validate a cell that's going to accept numbers, I have to specify a range of numbers. So in this case I'll put in a minimum of say 0, and a maximum of 100, as I doubt I'll be making more than $100 per hour.
But I could always come back and change this if necessary. So I'm telling Excel that I want to validate the two cells I selected to allow decimals between 0 and 100. I'll click OK. So now if I click in one of these cells and try to type in anything but numbers, and then hit Return, I get a message telling me I have to enter a decimal value between 0 and 100, just like I specified. Now, this is a little misleading, because you don't necessarily have to enter a decimal value, in the sense that if it's a whole number like 14, I could just type 14.
I'm going to click Retry here. Let's make this 14.75. We'll take an advantage of being able to enter decimal places. That value is unaccepted because it's a number. Let's make Job B's hourly wage 15.85. Now, even though these are both monetary values, they don't really look like it. I'm going to select both these cells again, and I'm going to open up in Excel here in my toolbox, which opens up the Formatting palette, and under the Number section here, I'm going to change the Format from General to Currency, as a kind of number for these cells.
You can see that adds a dollar sign in front of the two numbers. So for the Hours/week cells, I could do something very similar. So I've got those two cells selected, and let's validate them again. And we'll choose Decimal again, just in case one of the job requires shifts in says half-an-hour or quarter-hour increments. So I'll make the range a Minimum of 0 and a Maximum of say 80 hours a week. Click OK. So let's say Job A is offering 40 hours per week. I'll type in 40, hit Return.
And let's say Job B is offering 37.75, which I know is a weird number, but bear with me here. Now, if I wanted, I could again go back to the Formatting palette, with these two cells selected, and this time I'll choose Number as the format. That reformats the numbers in those cells and gives them two decimal places each. Now, you can change the number of displayed decimal places using these two buttons. So I could add a third decimal place, for example, or I could reduce it to only one decimal place. Notice that automatically rounded up the 37.75 to the 37.08.
If I lose the decimal places, it rounds the number up to 38. But I'm going to keep the two decimal places in this case. Okay. 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 the contents of the cells. Now, I could get out my calculator and figure out what my annual income for both jobs would be, but it's worth entering the proper formulas into Excel, so if the terms of the Hourly Wages or Hours/week change, I just have to plug in the new numbers to update my totals. So to add a formula to a cell, first click to select the cell.
And I'll open my toolbar, I'm going to click the arrow next to AutoSum, which gives me a list of common calculations. But what I need is a formula other than what's listed here, so I'll choose More Functions. And you can see that opens up the Formula Builder. So to figure out my annual income, I need to multiply my Hourly Wage by my Hours/week, and then multiply that by the number of weeks I'll be working in a year. So in the Formula Builder, I'm going to look for the proper formula. Here under Arithmetic, I'll select Add, Subtract, Multiply, or Divide. I need to double-click it to actually build my formula.
Now it's just a matter of building the formula by specifying which cells need to be multiplied together. I click cell B3, which is the Hourly Wage for Job A, and then I'll click B4. Notice that they have been added to the Formula Builder, but the default option is wrong. I need to multiply and not add them. So I'm going to click the menu and choose (Multiply). Notice the formula appears in the cell itself, as well as in the panel that appeared at the top of my screen. So now I need to multiply the product of B3 and B4 by the number of weeks in a year, which is 52.
So if necessary, I could add another argument, but I do already have a blank field in here, so I'm just going to click in there. And we have 52 weeks in a year, so I'll type that in, and again, I want to make sure that the operation is set to (Multiply). I can already see the result right here in the Formula Builder. I'll press Enter to close out of the formula, and just like that, the calculation is performed, and I see the Annual Income for Job A. Now, a really nice thing about most spreadsheet programs is that if you want to perform the same calculation on another set of numbers, you don't have to build the whole thing again. I'm just going to make sure this Annual Income for Job A's cell is selected, then I'm going to choose Edit > Copy.
Then I'll select the Annual Income cell for Job B and choose Edit > Paste. It's that easy. Notice in the Formula Builder, that Excel was smart enough to swap out B3 and B4 for C3 and C4, still multiplying them by 52, and again, you can see that reflected in the panel at the top of the screen, as well. Now I can plainly see that Job B will earn me more money, even though it's fewer hours. If any of this data changes, maybe Job B is reduced to say 35 hours a week, I can just type that in, and my total is updated instantly.
So that's the basics of entering your own data into a new worksheet and performing calculations.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.