Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Creating a simple data table

From: Computer Literacy for the Mac

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

Image for Computer Literacy for the Mac
Computer Literacy for the Mac

55 video lessons · 24801 viewers

Garrick Chow
Author

 
Expand all | Collapse all
  1. 2m 49s
    1. Welcome
      1m 9s
    2. Using the assessment files
      1m 7s
    3. Using the exercise files
      33s
  2. 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. 20m 58s
    1. Understanding the operating system
      3m 3s
    2. Understanding files, folders, and directories
      4m 49s
    3. Understanding your home folder (your user folder)
      5m 21s
    4. Using your desktop
      3m 11s
    5. Taking out the trash (recycle bin)
      2m 21s
    6. The right click
      2m 13s
  4. 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. 36m 22s
    1. Understanding computer ports
      2m 59s
    2. Setting up a printer
      3m 7s
    3. Printing your documents
      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. 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. 19m 31s
    1. Understanding email servers and clients
      2m 11s
    2. Setting up your email application
      4m 15s
    3. Receiving and reading email
      2m 21s
    4. Composing new email messages
      5m 52s
    5. Reply vs. Reply All
      2m 11s
    6. Dealing with spam
      2m 41s
  8. 8m 24s
    1. Understanding search engines
      1m 24s
    2. Conducting basic searches
      3m 51s
    3. Conducting advanced searches
      3m 9s
  9. 24m 21s
    1. Using word processors
      4m 22s
    2. Formatting text
      7m 7s
    3. Using spreadsheets
      3m 36s
    4. Creating a simple data table
      7m 37s
    5. Formatting a data table
      1m 39s
  10. 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. 10m 52s
    1. Common obstacles in sharing files
      1m 37s
    2. Creating PDFs for document sharing
      5m 35s
    3. Compressing files
      3m 40s
  12. 1m 3s
    1. What's next?
      1m 3s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
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.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

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

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

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.


Mark all as unwatched Cancel

Congratulations

You have completed Computer Literacy for the Mac.

Return to your organization's learning portal to continue training, or close this page.


OK
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.

Get started

Already a member ?

Become a member to like this course.

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

Get started

Already a member?

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

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

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 preferences from the dropdown menu.

Continue to classic layout Stay on new layout
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.

Control your viewing experience

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.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

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

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.