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 am working in Microsoft Excel, but everything I am going to show you here, should apply to other spreadsheet applications as well. I am going to create a new blank spreadsheet by clicking the Office button, selecting New and then choosing Blank Workbook. If you are using another spreadsheet application, you'll probably choose File > New Workbook, or something like that. So, let's say I am trying to decide between two jobs at an hourly rate. One pays more per hour but offers slightly fewer hours than the one that pays less. I am going to start by using the first rows of columns B and C as headers for the two job calculations.
So I'll click Cell B1 and I'll type Job A. To move horizontally from cell to cell, press the Tab key on your keyboard. Notice each time I press Tab, it moves one cell to the right. You can go back to the left by holding the Shift key down as you press Tab. So I'll label Cell C1 as Job B. And I'll use Column A to label all the rows I am going to need. I'll start with A3. This is just kind of arbitrary. I could start with A2, but I like having a little bit of space in there. Now, I'll type Hourly Wage.
Pressing Enter or Return on your keyboard lets you move down a single row. So each time I press Enter or Return, you can see I move down. And again, holding Shift while pressing Return in this case moves you back up. So, I'll make the next cell here, Hours/week, and I'll press Enter twice and type Annual Income. Now, Column A isn't quite wide enough for the text that I type. So I am going to place my mouse cursor between Columns A and B until my mouse cursor looks like this vertical line with the two arrows pointing in either direction.
Then I can click and drag Column A a little wider. Okay, so far I've added labels to these cells. Basically, cells containing only text and no numbers are generally referred to as labels. Next thing I am going to add the data that I know about each job, namely the Hourly Wage and Hours/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 are supposed to accept, especially if other people are going to be using the worksheets you are creating. You want to make sure they can't fill in any incorrect information.
So I'll first drag to select the two cells where I am going to type in my Hourly Wage information. With those cells selected, I am going to go over to the Data tab and here in the Data Tools section, I am going to click Data Validation. That opens the Data Validation window and here into the Settings tab, I can specify what type of input is allowed in this cell. Since this will be an Hourly Wage, I am going to choose Decimal. In order to validate cells like these that are going to accept numbers, I have to specify a range of numbers. In this case, I'll put in a range of 0 to 100.
As I doubt I'll be making more than $100 per hour, but I could also come back and change this if necessary and I'll click OK. So now if I click in one of these cells and try to type anything but numbers and I press Return, I get a message telling me the value I entered is not valid and that the cells have been restricted. It would be nice if it actually told you what kinds of values are acceptable but I'll just click Retry. Let's say the Hourly Wage for this Job A is $14.75. I'll press tab to enter it and move to the next cell and that value is accepted because it is a number.
Let's make Job B's Hourly Wage $15.85. I'll just press Return. Those both numbers were aligned to the right side of their cells. Now since these are both monetary values, I want to format the cells to make their contents look more like dollar values. So I am just going to select both those cells again, and then under the Home tab in the Number section of the Home tab, I am going to select Currency as the kind of number for these cells. And that puts a dollar sign in front of my numbers. So for the Hours/week cells, I can do something very similar. Let's select both of those cells and let's validate those as decimal numbers too.
So I am going to go back to Data > Data Validation > Decimals and let's make a range between 0 and 80 hours per week. I'll click OK. All right, so let's click in Job A's column and let's say Job A is offering 40 hours per week. Press Tab, and Job B is offering 37.75 per week. I know it's a weird number but bear with me here. Now, if I wanted to, I could again go back to the Home tab, select those cells, and this time choose Number as the format.
That reformats the number in those cells and gives them both two decimal places. Now, Job B already had two decimal places, but it added a .00 for Job A. Now, you can the number of decimal places using these two buttons in the Number section. So I could add a third decimal place if necessary or reduce it to only one decimal place. Notice that it automatically rounds up 37.75 to 37.8. Now if I lose the decimal places altogether, it rounds the entire number up to 38. But I'll keep this at two decimal places.
Okay, 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 of regular 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 Wage or Hours/week change, I'd just have to plug in the new numbers to update my totals. So, to add a formula to a cell, I first select the cell by clicking it, and then I'll click the Formulas tab. Next, I am going to click the arrow under the AutoSum button, which gives me a list of common calculations.
But I need a formula other than what's listed here so I'll choose More Functions. That opens up the Insert Function window. So to figure out the annual income for each job, I need to multiply my Hourly Wage by my Hours/week, and then multiply that number by the number of weeks I'll be working in a year. So it's going to be multiplication and multiplication is a basic math function. So in the select a category menu, I already have Math & Trig selected, and you can see there are other different categories we could choose from. Statistical, Database, Text and so on, but I am going to stick with Math & Trig.
And then under the Math & Trig functions, I am going to find Product, which means to multiply. I'll just double-click that. That opens the Function Arguments window. So, now it's just a matter of building my formula by specifying which cells need to get multiplied together. So, I have Number1 currently selected. So I'll click Cell B3, which is my Hourly Wage for Job A. And you can see B3 gets entered for Number1. Then I'll click in the Number2 field and select B4. Notice the formula I am building appears in the cell itself as well as in the panel that appears at the top of my worksheet.
Okay, so now I need to multiply the product of B3 and B4 by the number of weeks a year I'll be working, which we'll say is 52. So I'll click in Number3 and type in 52. Notice each time you click in one of these cells, it adds another number in case you have more numbers you need to add into your formula. But that's basically all I need here. I need to multiply B3 by B4 by 52. And I can already see the result at the bottom of the window here, Formula result 30680. So I'll click OK to close out the formula and just like that, the calculation is preformed, and I see the annual income for Job A is $30,680 a year.
Now, a really nice thing about most spreadsheet programs is that if you want to perform the same calculation on another set of numbers in your spreadsheet, you don't have to build the whole formula again. I am just going to make sure Cell B6 is selected, which is the Annual Income for Job A and under the Home tab, I am going to click Copy. You can also use Ctrl+C or choose Edit > Copy in other spreadsheet programs. Then I'll select the Annual Income cell for Job B and I'll choose Paste or you would choose Edit > Paste or press Ctrl+V. It's that easy. Notice the formula area at the top of my worksheet shows that I am multiplying C3, C4 times 52, which is swapped out for B3, B4 and 52.
Now, I can plainly see that Job B will offer me slightly more money even though it's fewer hours. And if any of this data changes, maybe Job B is reduced to say 35 hours/week, I just need to plug that in, hit Enter, and my product is instantly updated. Now lastly, I should probably reformat these Annual Income cells as Currency. I'll just select them, change it to Currency, and there we go. 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.
Your file was successfully uploaded.