Unit 7: Easy Spreadsheet
TEACHERS SLIDE
UNIT OBJECTIVES
►Use pivot table and determine the functions such as:
summarizing, sort, organize, and grouping of data;
►Use Vlookup and retrieve data from a table;
►Create different excel projects such as: market list,
class record, bills and charts, and filter.
Why use MS Excel?
Lesson 1: Excel Pivot table
TEACHERS SLIDE
PivotTable takes data and summarizes
it so you can make sense of it, all
without typing any formulas.
Pivot
Table
Pivot
Table
Think of little legos; these little legos act as your data. You are not changing anything to the lego, you are
just reorganizing or arranging these little legos to make sense of the legos available. Your lego building
presentation can be simple or sophisticated.
Pivot
Table
Think of little legos; these little legos act as your data. You are not changing anything to the lego, you are
just reorganizing or arranging these little legos to make sense of the legos available. Your lego building
presentation can be simple or sophisticated.
Enter your data
Still not getting it? Let’s follow
this example for you to clearly
understand how to use a pivot
table.
The first step to making a good
PivotTable is to make sure that
the data is in good shape. So,
make sure you do the following
before you make one:
Highlight your cells
1. Once you have entered data
into your Excel worksheet,
highlight the cells you would like
to summarize in a pivot table.
Usually, it is recommended to
highlight all the data.
2. Go to Insert > PivotTable and
click OK.
PivotTable Fields
1. You will see the PivotTable
Fields. At the top, you will find
the fields or column labels from
your data.
2. The first thing you will do is
create a value field. You will do
that by dragging the Amount field
or column label to the Values area
box.
PivotTable Fields
3. Then you will want a condition
to divide the value field by. And
to do that, drag any of the other
fields down to the Rows area. For
example, the Buyer.
PivotTable Fields
4. Now that the Buyer field or column label is in
the Rows area, you will see that the PivotTable
shows each buyer’s amount. It is easy and you
can change the table’s look by dragging the Date
or the Type or both to the Rows area. It is easy
and it does not require typing any formulas.
5. Keep in mind that whenever you create a
PivotTable, Excel puts the PivotTable on a new,
separate sheet to the left of the sheet you are
currently on.
ACTIVITY 1: INSTRUCTIONS. Wise Owl Travel Agents. Create a Pivot Table form, copy the data below, then use
the filters within the average prices of holidays that have a Travel Method of Plane and a Resort Name that begins
with the letter S.
1. Copy the data on your Excel.
2. Confirm that there are 3 holidays in total, by using the drill-down feature.
3. Your pivot & drill-down sheet should resemble this.
Lesson 2: Excel Vlookup
TEACHERS SLIDE
VLOOKUP
VLOOKUP
VLOOKUP is one the most widely
used functions in Excel. In a big
data sheet, it lets you look up a
value in a column on the left,
then returns information in
another column to the right if it
finds a match. The formula for
VLOOP is as follows:
VLOOKUP Function
1. First, you need data on your Excel.
In this example, you follow this data
to look up for fruits.
2. In cell B7, enter
=VLOOKUP(A7,A2:B5,2,FALSE). The
correct answer for Apples is 50.
VLOOKUP looked for Apples, found it,
then went over one column to the
right, and returned the amount.
VLOOKUP AND #N/A
Invariably, you will run into a situation
where VLOOKUP can't find what you
asked it to, and it returns an error
(#N/A). Sometimes, it is because the
lookup value simply does not exist, or it
can be that the reference cell does not
have a value yet. Look on this data on
Excel.
There is nothing wrong in the VLOOKUP
formula in the formula bar but there is
#N/A on cell B8, because there is the
reference cell A8 does not have anything
to look up in cells A2:B6.
VLOOKUP AND #N/A
1. If you know your lookup value exists,
but want to hide the error if the lookup
cell is blank, you can use an IF
statement. In this case, wrap your
existing VLOOKUP formula like this in cell
B8:
=IF(B8="","",VLOOKUP(B8,A2:B6,2,FALSE))
2. This says, "If cell C43 equals nothing
(""), then return nothing, otherwise
return the VLOOKUP's results." Note the
second closing parenthesis at the end of
the formula. This closes the IF
statement.
VLOOKUP AND #N/A
3. If you're not sure your lookup value
exists, but you still want to suppress the
#N/A error, you can use an error
handling function called IFERROR in cell
G43:
=IFERROR(VLOOKUP(F43,F37:G41,2,FALS
E),""). IFERROR says, "If the VLOOKUP
returns a valid result, then display that,
otherwise, display nothing ("")". We
displayed nothing here (""), but you can
also use numbers (0,1, 2, etc.), or text,
such as "Formula isn't correct".
Activity 2: INSTRUCTIONS. Use a VLOOKUP function in Microsoft Excel to
categorize a list of values using Poohsticks.
1. Create a lookup table containing scores and ratings based on
the following categories:
0-4 = Pooh
5-9 = Could do better
10 - 14 = Doing better
15 or more = Tiggerific
2. Create a VLOOKUP function to calculate a rating for each player as follows:
The function should look up the score for each player
