# Creating a simple data table

## Video: Creating a simple data table

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.

## Creating a simple data table

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.

Show transcript

#### This video is part of

Computer Literacy for the Mac

55 video lessons · 23220 viewers

Author

Expand all | Collapse all
1. ### Introduction

2m 49s
1. Welcome
1m 9s
2. Using the assessment files
1m 7s
3. Using the exercise files
33s
2. ### 1. Introduction to Computers

9m 51s
1. What's a computer?
1m 49s
2. What's inside a computer?
2m 46s
3. Laptop vs. desktop computers
1m 59s
4. Special considerations when using a laptop
3m 17s
3. ### 2. Introduction to Operating Systems

20m 58s
1. Understanding the operating system
3m 3s
2. Understanding files, folders, and directories
4m 49s
5m 21s
3m 11s
5. Taking out the trash (recycle bin)
2m 21s
6. The right click
2m 13s
4. ### 3. Introduction to Software and Applications

24m 8s
1. Understanding applications
4m 24s
2. Opening and saving files
4m 10s
3. Choosing the right tool
4m 44s
4. How to learn any application
3m 53s
5. Five things that work in all applications
6m 57s
5. ### 4. Introduction to Computer Peripherals

36m 22s
1. Understanding computer ports
2m 59s
2. Setting up a printer
3m 7s
4m 30s
4. Setting up a scanner
2m 27s
5. Scanning a document
6m 15s
6. Setting up a projector or second monitor
5m 56s
7. Using a projector
3m 43s
8. Portable storage devices
3m 53s
9. Pairing with Bluetooth devices
3m 32s
6. ### 5. Introduction to Networks and Getting Online

17m 27s
1. Understanding networks and internet access
2m 58s
2. Connecting to wired network
2m 36s
3. Connecting to wireless networks
4m 4s
4. Working in a networked environment
6m 15s
5. Staying protected from viruses
1m 34s
7. ### 6. Introduction to Email

19m 31s
1. Understanding email servers and clients
2m 11s
2. Setting up your email application
4m 15s
2m 21s
4. Composing new email messages
5m 52s
2m 11s
6. Dealing with spam
2m 41s
8. ### 7. Introduction to Internet Searching

8m 24s
1. Understanding search engines
1m 24s
2. Conducting basic searches
3m 51s
3m 9s
9. ### 8. Introduction to Word Processing and Spreadsheets

24m 21s
1. Using word processors
4m 22s
2. Formatting text
7m 7s
3m 36s
4. Creating a simple data table
7m 37s
5. Formatting a data table
1m 39s
10. ### 9. Introduction to Photo and Image Management

18m 53s
1. Importing images from a digital camera
4m 46s
2. Storing and organizing digital images
5m 11s
3. Basic image manipulation
4m 10s
4. Tagging images
2m 32s
5. Sharing images
2m 14s
11. ### 10. Introduction to Sharing Files

10m 52s
1. Common obstacles in sharing files
1m 37s
2. Creating PDFs for document sharing
5m 35s
3. Compressing files
3m 40s
12. ### Conclusion

1m 3s
1. What's next?
1m 3s

### Start learning today

Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

### What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

### Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Exercise files

How to use exercise files.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.

Congratulations

You have completed Computer Literacy for the Mac.

Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

How to use exercise files.

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

• Mark video as unwatched
• Mark ALL videos as unwatched
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

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.

Interactive transcripts

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.

## Are you sure you want to delete this note?

Thanks for signing up.

We’ll send you a confirmation email shortly.

• new course releases
• general communications
• special notices

Keep up with news, tips, and latest courses with emails from lynda.com.

• new course releases