EXCEL LESSON

Aim:

  1. To use spreadsheet to enter data.
  2. Do simple calculations like sum, percentage and average on the given data.

Scenario drawing

Tejas: We have received answer sheets for the third term exams. I want to keep a record of my performance.

Jyoti: Why don’t you make a table in word processor?

Tejas: That is a good idea! My grandparents always ask me to send them a copy of my report. Now I can attach the marks file with an e-mail and send it to them.

Jyoti: Let me help you in making the marks table.

They make a table in word as shown below:

Title: Third term Performance Record for Tejas

Subject / Marks (out of 100)
English / 67
Hindi / 57
French / 83
Science / 78
Mathematics / 87
Social Studies / 69
Total
%

Tejas: I used calculator for computing the total and percentage and then entered it in the table.

Jyoti: But only today, the teacher told us that our marks are changed in Social studies.

Tejas: For me, Science marks have also changed as the teacher did not correct one question for me.

Jyoti: That means you will have to calculate the total and percentage once again.

Tejas: This is very time consuming. I wish the word processor allowed us to do calculations.

Moz: Your wish is valid, however, instead of word processor; another new application can be used for this.

Tejas and Jyoti: We would really like to learn more about it.

Moz: The new application is called a Spreadsheet application some examples are: Calc, Numbers, Excel.

------

CONCEPT BOX

The spreadsheet is a huge table with infinite rows and columns.

You can use a spread sheet to:

  • Enter data.
  • Edit data,
  • Format data.
  • perform calculations on the data,.
  • Draw charts and graphs based on the data.

------

Replace screen shot with ubuntu, label Row, Column, Cell, some mathematical options, sheets

------

Info BOX

Spreadsheet is made up of

  • Rows are horizontal (referenced by a number that appears leftmost of a particular row)
/
  • Columns are vertical (referenced by an alphabet that appears topmost
of a particular column) /
  • Cells are intersection of rows and columns.
/
Data in each cell can be:
◦Text (description of the data which is called a label)
◦Number
◦Formula (mathematical equations, for example an equation to calculate the sum of a number of cells).

------

Tejas: I see that the tool bar options are similar to the word processor.

Jyoti: The format options are same. But there are new mathematical options that we have not seen in word processor and presentation applications.

Moz: Good observation. Another point of difference is that you have pages in word processor document and slides in presentation. But a spreadsheet application has a workbook with sheets.

Skill box for creating a new spread sheet: change the bullets to reflect the steps in ubuntu

CREATE NEW WORKBOOK

------

  1. Go to File option in tool bar
  2. Select New
  3. Select Blank workbook
  4. Select create

------

Jyoti: Now let us enter the marks table in the spreadsheet. So just like I create a new word file or a presentation file, I will create a new workbook file.

Moz: You can either enter data from your notebook or copy-paste from the table in word processor.

------

skill box for data entry

  1. Select the cell where you want to enter the data
  2. Double click the cell.
  3. Enter the data from the blinking cursor
  4. Place the cursor in the next cell to enter data. You can do this by either clicking on Tab key or using a mouse to position the cursor at appropriate location. In the example, the cell F5 has an entry for marks obtained in English (Usher: replace F5 with what is applicable in the new screenshot).
  5. Give title to the table that you have created and sheet on which you enter data. For example, in this example, the workbook is titled marks and sheet is titled Tejas.

------

Tejas: This is easy. But now when I change my science marks, do I have to calculate the total and percentage again?

Moz: Not if you have used the available formula/option called ‘Sum and Percentage”.

------

Skill for sum and percentage:

This can be done in multiple ways.

Two ways to do calculate sum are:

i. Select the cells which have the marks and click on the symbol for sum (“∑”) OR

i. Select the cell where you want the answer

ii. In the cell, write the following formula: = Sum: (enter the first and last cell). For example in this table, Sum = (F5:F11)

iii. Press Enter Key

iv. The total is calculated and seen in the selected cell.

Two ways to do calculate percentage are:

i. Select the cells which have the marks and click on the symbol for sum (“%”) OR

i. Select the cell where you want the percentage

ii. In the cell, write the following formula: = [sum] * 100/Absolute Total. In the given example, it would be [total marks]* 100 / 600

iii. Press Enter key.

iv. The percentage is calculated and seen in the selected cell.

------

Tejas: Now when I increase my science marks, the new total and percentage is automatically calculated!

All screen shots to be created on ubuntu

Moz: Tejas, suppose you got 100 in Maths, what would your percentage be?

Jyoti: This is fun, I just change the Maths marks to 100 in the cell, the total is 454 and the percentage (%) is now 75.7

Moz: Does your school write the highest marks for a subject in the report? If they do, enter the highest in another column in the same table.

Tejas: Yes, they do. So I have to insert a new column between Subject and my marks.

------

Skill box to enter a new row/column

If you have to enter row/column that is outside an existing table, follow these steps:

  1. Position your cursor in that row/column.
  2. Note that the cell where you want to first entry is active. This is indicated by a blinking cursor in that cell.
  3. Start entering the data.

If you have to enter row/column in between an existing row/column, follow these steps:

  1. Position the cursor on row/column near a new row/column is required.
  2. Right click on the mouse.
  3. For row, select: add rows above/below as required. For column, select: add column to left/right as required.

You can also delete row/columns as required using steps 1 and 2. Now select the delete option and click on appropriate option.

------

Moz: Looks like you got the highest in Maths. Table is one form of representation, what other form can you use to show this data?

Jyoti: A graph or chart?

Moz: Yes, drawing charts and graphs is a very powerful tool of spreadsheet. Now let us see how to draw graphs.

------

Skill box of drawing charts/graphs (rewrite steps ………………..)

  1. Select the columns containing data for which graph has to be drawn. In this example, select the three columns: one of subject, marks and highest marks for that subject.
  2. Select Insert option
  3. Select graphs
  4. Select kind of graph you require. In this example, we will select bar graph

------

Moz: Other than a bar graph, spreadsheets give you options to draw other kinds of charts, graphs, and also allow formatting of charts. Visually these representations are easy to understand.

------

Info box for charts/graphs

Graphs can be formatted to make the following changes:

  1. Alter the colour of different bars.
  2. Add titles of x-axis, y-axis.
  3. Add title of the chart/ graph.

You can explore on your own more formatting options. Remember to use the ‘undo’ key [shortcut key: Ctrl key + Z] to resume the previous format.

------

Jyoti: We have really enjoyed learning this spreadsheet application. I want to enter my marks also now.

Moz: You can now use the same workbook to enter your marks for your previous terms also. Jyoti you can also start entering your marks on another sheet in the workbook.

Jyoti: Yes, see my new sheet with marks from all three terms. I have used some formatting options to make the table easy to read.

Moz: How is your final result calculated after the three terms?

Jyoti: For every subject, the average is calculated. The formula is: (1st term marks+2nd term marks+ 3rd term marks) / 3.

Tejas: It must be so complicated for out teacher to do these calculations for all the students of our class. Is it easy to do in a spreadsheet?

Moz: Yes, it can be done only in two steps!

------

Skill box for averages

  1. Select the cell where you want to write the average for English
  2. Enter : = ( Select all the three cells of English three terms for which you want the average)/3
  3. Press enter

Note: You need not repeat the steps for all subjects.

To do this computation for all the subjects, perform the following steps:

  1. Go to the right corner of the cell for English average[give cell number ]
  2. You see a + sign which is called a handle
  3. Drag the handle down the column to calculate the average for all subjects

------

Tejas and Jyoti: Looks like Maths is a piece of cake with Spreadsheet!

Moz: Good, in your next grade you can learn more advanced functions available in spreadsheets like sorting, filtering, using more formulae, more options in graphs and charts.

------

Learning outcome:

  1. To enter data and do basic calculations.
  2. To read data given in a spreadsheet.
  3. Draw graphs to represent the data in the table.
  4. Select appropriate labels for tables and graphs.

Worksheets

  1. Study the following screenshot and answer the following questions:

a. Mark row, column and cell in the above screenshot.

b. Fill the missing data. You have to enter the data or cell number as appropriate

33
F5
25
C5
44
E12
  1. Which symbol is used to compute total marks?

∑ / ∫ / %/π

  1. Which of the following formula is used to compute average unit test marks?

Sum C3:F3/4

Sum A1:A5/5

Sum 4D:7E/4

Sum C3:C12/4

  1. The following graph shows number of visitors to different stalls at a funfair event. Study this figure and answer the following questions

Event / Day 1 / Day 2 / Day 3 / Day 4
Shooting a balloon / 20 / 25 / 30 / 35
Throwing a ring / 30 / 40 / 45 / 45
Magic show / 25 / 30 / 30 / 35
Jugglers / 30 / 35 / 40 / 50
Dog show / 10 / 15 / 20 / 25
Face painting / 20 / 25 / 30 / 20

(Note: the above table is only for reference, so that Swati can redraw the following graph)

  1. Which the most popular stall on Day 2?
  2. Which stall is least popular on Day 4?
  3. Which stall received maximum number of visitors on all the four days?
  4. Mention the number of visitors to each stall on Day 3.
  1. The following table shows the weekly sales of products in a consumer goods company.
  1. Enter this data in a spreadsheet.

Product / week 1 / week2 / week3 / week4
Televisions / 50 / 45 / 30 / 25
Laptops / 25 / 20 / 50 / 33
Washing machine / 12 / 15 / 22 / 28
Microwave owens / 14 / 24 / 23 / 10
Mobiles / 75 / 50 / 80 / 60
  1. Find the total number of units sold for each product across the four weeks.
  2. In which week did the maximum sales happen?
  3. Which product had the maximum/minimum number of units sold across the four weeks?
  4. Draw a graph to represent the data in the above table.
  5. Which is the most popular consumer good?
  1. The following table shows food items with calories for each.

a.Enter this data in a spreadsheet.

Food Item / Food Calories
Vegetables ( per 100 gms)
Cabbage / 45
Carrot / 48
Cauliflower / 30
Cucumber / 12
Peas / 93
Potato (fried) / 450
Fast food
Cheese burger (1) / 610
Fries( regular) / 360
Fruits / Apple / 56
Banana / 153
Guava / 66
Papaya / 32

b.Design a meal consisting of items from each category. You have to ensure that you consume not more than 700 calories.

  1. Given a table of activities and calories burnt, what are the exercises that you should do?

Exercises / Food Calories burnt an hour
Climbing stairs / 350 to 800
Cycling / 300 - 600
Swimming / 500-900
Dancing / 250-400
  1. Enter the above data in a spreadsheet and enter a suitable title for the table.
  2. Format the above table such that: (i) the column headings are in bold (ii) data in first column is centrally aligned (iii) alter the width of column such that the content is easily readable (iv) borders of the table are seen clearly.
  3. Plan an exercise regimen for Sumit and Ashmit.
  4. Sumit is fond of fast food and is overweight. He has to burn 2000 calories every week with an hour of exercise per day.
  5. Ashmit has healthy food. He needs to burn 1000 calories every week with an hour of exercise per day.

(Hint: Calculate average calories burnt with each exercise)

  1. Zoya and Mike collected information on what is the most preferred means of transportation by different students. The following table shows the count (frequency) of students against each transport.

Transport / Frequency
Train / 19
Aeroplane / 24
Ship / 12
Bus / 38
  1. Enter the above data. Calculate the total number of students surveyed.
  2. Draw a graph for the above table.
  1. Match the column:

Purpose / Application / Unit of document
Word processor
Giving Presentations
Workbook
Drawing
  1. Circle which of the following tool bar is observed in a spreadsheet application

(show screenshots of word processor, ppt and excel)

  1. Imagine that you receive Rupees 100 last month. Draw a pie chart to show how you spent it. For example, you can have expenses against food, picnic, savings, stationery, birthday gifts for friends, bicycle repair, treats for friends, purchasing sport items.

  1. Explore:
  1. Explore how to copy a table made in word processor into a spreadsheet.
  2. How to insert a chart made in spread sheet into a slide.
  3. How to select a part of the Excel sheet for printing.