# inter

W
Shared by:
Categories
-
Stats
views:
32
posted:
6/24/2011
language:
English
pages:
55
Document Sample

```							       Welcome to Intermediate Excel
By the end of this Workshop you will be able to work effectively with:

Pivot Tables
Formulas and Functions - including relative and absolute referencing
Grading and Excel: Find highest score; Drop lowest score etc.
VLOOKUP
Calendar dates & calculations
Concatenation
Format chart axis, legends, chart types
Top 5 Tips and Solutions
The Excel Help Resources
Excel
vely with:

referencing
Last Name Name ID Quiz
Bo      John ecstudent02 30
Leung Erica student_04 30
http://bliss.sandiego.edu/Office2007/inter_excel/Inte Sjostrom       dpstudent08 0
Lachman          3
rmediate Excel.pdf                                     Cheng Eric dpstudent01 9 2
Mason Kelly student_05 28
Let's review some features and functions of Excel Ng         Ren dpstudent09 82
2007 using the table on the right
RamirezLuis dpstudent07 62
Pulido Laurie lpulido    25
1) Reformat the column spacing so there are no      Chen Craig student_06 24
overlapping areas                                                            2
Wishna Victor dpstudent03 3
2) Reorder the columns: Switch the position of      Angel Oliver ecstudent04 22
the Last Name and First Name columns                              d
Knox Amandapstudent02 2  2
3) Sort the columns first alphabetically and then                 ecstudent01
Andalo Christopher       21
by Quiz results                                     BringdalRobby ecstudent0320
4) Filter the columns first alphabetically and then Ziegler Bob st02bziegler19
by Quiz results                                                              1
Berlin Elena dpstudent06 8
Shaw Kristie st05kshaw 18
Charts - Let's make a chart.                                                 1
JohnsonCraig dpstudent05 7
Stewart Abe st1astewart17
*Make a bar chart comparing student quiz                           s
Carter Meghant06mcarter  16
scores.                                                                       1
Sather Elliot dpstudent04 5
Welsh Mark ecstudent05   14
Elliz   Jill  st04jellis 10
Kennedy Hanna st03hkennedy 9
SchmidtMarkus dpstudent108
J
Simmonseffrey jsimmons 0
Last Name     First Name                 User ID               Quiz Quiz2 Quiz3
Stewart       Abe                        st1astewart             17    19    22
Knox          Amanda                     dpstudent02             22    17    24
Ziegler       Bob                        st02bziegler            19    18    18
Andalo        Christopher                ecstudent01             21    25    16
Chen          Craig                      student_06              24    23    18
Johnson       Craig                      dpstudent05             17    21    19
Berlin        Elena                      dpstudent06             18    21    19
Sather        Elliot                     dpstudent04             15    16    22
Cheng         Eric                       dpstudent01             29    26    27
Leung         Erica                      student_04              30    25    28
Kennedy       Hanna                      st03hkennedy             9    17    14
Simmons       Jeffrey                    jsimmons                 0     0     0
Elliz         Jill                       st04jellis              10    19    17
Bo            John                       ecstudent02             30    26    27
Mason         Kelly                      student_05              28    27    28
Shaw          Kristie                    st05kshaw               18    24    19
Sjostrom      Lachman                    dpstudent08             30    26    25
Pulido        Laurie                     lpulido                 25    26    19
Ramirez       Luis                       dpstudent07             26    28    22
Welsh         Mark                       ecstudent05             14    22    21
Schmidt       Markus                     dpstudent10              8    15    14
Carter        Meghan                     st06mcarter             16    17    19
Angel         Oliver                     ecstudent04             22    28    26
Ng            Ren                        dpstudent09             28    26    27
Bringdal      Robby                      ecstudent03             20    25    23
Wishna        Victor                     dpstudent03             23    21    19

Excel Order of Precedence
Operator       Operation                 Order of Precedence
:             Range                      1st
<space>       Intersection               2nd
,             Union                      3rd
--            Negation                   4th
%             Percentage                 5th
^             Exponentiation             6th
* and /       Multiplication and division 7th
+ and --      Addition and subtraction   8th

&             Concatenation              9th
= < > <= >= <> Comparison                10th
Try it!
Consider the formula =3+5^2 What is the answer?
If you calculate from left to right, the answer you get is 64
But, by referring to the Order of Precedence table, you see that exponents are
handled before addition and the answer Excel arrives at is 28. (5^2 equals 25,
and 3+25 equals 28)

Try it!
1) Click in Cell G10, and enter or Copy/Paste the various references above to
view the ranges within cells. Finally, right-click G10 and select Clear Contents
2) Click in the Cells listed below, complete the operations, pressing ENTER
after each:

Cell            Operation
G10             =29+26+27 [Manual Entry]
H10             =D10+E10+F10 [Manual Entry w/ cell references]
I10             =SUM[TAB] D10, E10, (Click on F10) [Function w/ cell

For a wonderful resource on formulas, click on the link below.
http://www.informit.com/articles/article.aspx?p=328639
Formulas & Functions Review

Formulas define useful relationships between cells in a
spreadsheet. Formulas perform calculations . Most results
are answers to specific questions like: Are expenses over
or under budget, and by how much? Should we invest
funds in this and what will be the future value of an
investment? What is the mean among a range of student

Functions are prewritten formulas that simplify the
process of entering calculations. Using functions, you can
quickly create formulas that might be difficult to build
yourself.

Relative Cell references
=D10             the cell in column D and row 10
=D10,D20         cell D10 and cell D20
=D10:D20         the range of cells in column D and rows
10 through 20
=D15:F15         the range of cells in row 15 and columns
D through F
=D10:F20         the range of cells in columns A through E
and rows 10 to 20

Math Operators
Oper Name     Example Result
+                    15      15
–       Subtracti            5          5
–       on
Negation          (10) –10
*       Multiplic           50         50
/       ation
Division             2          2
%     Percenta           0        0
ge
^     *Expone      100000 100000
ntiation=x^y means that the
*The formula
value x is raised to the power y. For
example, the formula =3^2 produces
the result 9 (that is, 3*3=9). Similarly,
the formula =2^4 produces 16 (that is,
2*2*2*2=16).
Comparison Operators
Operat Name           Example    Result
or
=      Equal to          FALSE      FALSE
>      Greater than       TRUE       TRUE

<      Less than         FALSE      FALSE
>=     Greater than      FALSE      FALSE
or equal to

<=     Less than or       TRUE       TRUE
equal to

<>     Not equal to       TRUE       TRUE
Try the three ways to enter formulas (via
functions, using                                        Last Name   First Name    User ID        Quiz        Quiz2 Quiz3 Quiz Total
Stewart     Abe           st1astewart           17       19    22
Knox        Amanda        dpstudent02           22       17    24
1) Addition Manually: Click on Cell: K3. Within the     Ziegler     Bob           st02bziegler          19       18    18
Formula bar,                          add the three     Andalo      Christopher   ecstudent01           21       25    16
scores in the row using (+,-,*,/) operators and         Chen        Craig         student_06            24       23    18
using cell references only.                             Johnson     Craig         dpstudent05           17       21    19
2) Addition: Manual Function Entry - Click on Cell:     Berlin      Elena         dpstudent06           18       21    19
K4. Add the three scores in the row typing an           Sather      Elliot        dpstudent04           15       16    22
equals sign and the word SUM. Enter the range of
Cheng       Eric          dpstudent01           29       26    27
3) Addition: Automatic Function Entry - Click on        Leung       Erica         student_04            30       25    28
Cell: K5. Add the three scores in the row using the     Kennedy     Hanna         st03hkennedy           9       17    14
AutoSum function
4) Using the fill handle          around Cell: K5,      Simmons     Jeffrey       jsimmons               0        0     0
pull it down to K28 to apply the formula down all       Elliz       Jill          st04jellis            10       19    17
rows.                                                   Bo          John          ecstudent02           30       26    27
5) Using a function, beginning with cell L3, find the   Mason       Kelly         student_05            28       27    28
average score for each student .                        Shaw        Kristie       st05kshaw             18       24    19
Sjostrom    Lachman       dpstudent08           30       26    25
Pulido      Laurie        lpulido               25       26    19
Understanding Error values: ######, #REF!, #name        Ramirez     Luis          dpstudent07           26       28    22
and the Error Button:                                   Welsh       Mark          ecstudent05           14       22    21
Schmidt     Markus        dpstudent10            8       15    14
1) ##### - The column is not wide enough to             Carter      Meghan        st06mcarter           16       17    19
display the number. Increase column width, shrink       Angel       Oliver        ecstudent04           22       28    26
contents to fit the column, or apply a different        Ng          Ren           dpstudent09           28       26    27
number format.
Bringdal    Robby         ecstudent03           20       25    23
2) #REF!- A cell reference is not valid. Cells may      Wishna      Victor        dpstudent03           23       21    19
have been deleted or pasted over.
3) #NAME?- A cell reference is not valid. Cells may
have been deleted or pasted over.
Addition: Clear the error messages by clicking on
the Error Button     & correcting any errors
Topic
Online access only                and Online.

Microsoft's Office Online Training.                                         http://office.microsoft.com/training/T
raining.aspx?AssetID=RP10278612103
You can construct hyperlinks in two styles:           Dates and Formulas    3&CTT=6&Origin=RC102786151033

http://office.microsoft.com/training/T
Pop on the Headphones for a Microsoft lesson Calculating Dates using raining.aspx?AssetID=RP10278614103
covering Formulas and Dates!                 Formulas                3&CTT=6&Origin=RC102786151033

Up until now, we have been using Excel's Relative Reference format. There is another known as the
Absolute Reference format where Excel uses the physical address of the cell within formulas. You tell the
program that you want to use an absolute reference by placing dollar signs (\$) before the row and column
of the cell address. No matter where you copy or move this formula, the cell reference doesn't change. The
cell address is said to be anchored.

You can quickly change between the relative and absolute reference format sof a cell address by using the
F4 key. When editing a formula, within the formula bar, place the cursor to the left of the cell address (or
between the row and column values), and keep pressing F4. Excel cycles through the various formats.

Try it!
1. Enter a number in D7
2. Within E7, enter a multiplication operation that references the number in D7 (for example: =D7*65)
3) Place your cursor within the formula by either double-clicking within E7 or clicking once on E7 and next
within the formula within the formula bar.
4) Press the F4 key (Along the top of your keyboard) to cycle through the different formats.
5) Try grabbing the copy fill handle and dragging downward. What happens? Click once on E7 and copy to
another cell using Absolute Referencing.
Last Name            First Name                        User ID                                 Quiz Quiz2
Elliz                Jill                              st04jellis                                 10    20
Schmidt              Markus                            dpstudent10                                 8    15
Kennedy              Hanna                             st03hkennedy                                9    17
Carter               Meghan                            st06mcarter                                16    17

Key: A1, D2, G1 or I2 etc mean any cell
A\$2 is a Absolute reference
.1,.2 are any two % weights

Issue                 Formula Syntax                          Notes

1. Use "Percentage" number
formatting to convert ratio to a
percent.
=A1/A\$2 (A2 is total points)
Find Percentage                                        2. Use the \$ in cell references to
=PRODUCT (A1,1/A\$2)
make sure formulas refer to the
same formula. For instance A\$2
would hold the reference to row 2

=(SUM(D2:F2)-                     You can subtract the result of the       Try it! Click in
Drop Lowest          MIN(D2:F2))/(COUNTA(D2:F2)-1)     MIN function then divide by the          function on left using the corre
number of scores (COUNTA).               cell references for Jill Elliz
This formula subtracts the second
smallest score and the first smallest
=SUM(A1:G1)-                      score from the total. To subtract
Drop Lowest 2
(SMALL(A1:G1,2)+SMALL(A1:G1,1))   more scores, use a SMALL functions
for each score place to remove.

You can use either the MAX function
Find Highest Score   =MAX(A1:G1)                       or find the first largest number
(LARGE function).

=LARGE(A1:G1,1)

This formula adds the second highest
and the frist highest score. To add
Find Highest 2       =LARGE(A1:G1,2)+LARGE(A1:G1,1)
more scores, add a LARGE function
for each rank.
Average of Scores    =AVERAGE(A1:G1)
You must find the sum of each
course component first, then
Weight by Percentage =.1*A1+.2*G1+...                  multiply it by the decimal point
corresponding to the percentage
weights.

Note that the desired grades "PASS"
and "FAIL" are marked by quotes in
Pass/Fail Formula    =IF(A2>=70%, "PASS","FAIL")
the formula to indicate that text
should be written into the cell
=IF(I2>=90%,"A",IF(I2>=80%,"B",IF As you come to a nested formula,
)                                 then fill in the formula.

All the above, plus
& Analyzing Student
Performance !
Quiz3 Drop1
15 17.50
14
14
19

Click in G2 and apply the
on left using the correct
ell references for Jill Elliz
Let's look at concatenate: How to merge two fields      Last Name    First Name    User ID      Quiz @sandiego.edu
together. We'll use Autofill to help take the           Stewart      Abe           st1astewart    17 st1astewart@sandiego.edu
tedium out of the process.                              Knox         Amanda        dpstudent02    22
Ziegler      Bob           st02bziegler   19
Now, let's create an email address for each             Andalo       Christopher   ecstudent01    21
student based on their User ID.                         Chen         Craig         student_06     24
Johnson      Craig         dpstudent05    17
1) Create a new column with the heading: USD            Berlin       Elena         dpstudent06    18
Email                                                   Sather       Elliot        dpstudent04    15
2) In the first cell below that heading, type:          Cheng        Eric          dpstudent01    29
@sandiego.edu Won't work? Highlight the                 Leung        Erica         student_04     30
column and change it to Text (Home/Number)              Kennedy      Hanna         st03hkennedy    9
Simmons      Jeffrey       jsimmons        0
3) AutoFill the cells below. (Click on the
Elliz        Jill          st04jellis     10
@sandiego cell to highlight it, click on the Autofill
Bo           John          ecstudent02    30
box located on the bottom right of the cell and
drag down to fill the remaining cells                   Mason        Kelly         student_05     28
Shaw         Kristie       st05kshaw      18
4) Create a new column with the heading: Email.         Sjostrom     Lachman       dpstudent08    30
Highlight and reformat the area below it as             Pulido       Laurie        lpulido        25
General.                                                Ramirez      Luis          dpstudent07    26
Welsh        Mark          ecstudent05    14
Schmidt      Markus        dpstudent10     8
Carter       Meghan        st06mcarter    16
Angel        Oliver        ecstudent04    22
Ng           Ren           dpstudent09    28
Bringdal     Robby         ecstudent03    20
Wishna       Victor        dpstudent03    23

5) Click within the first cell below Email. Next, click on the Formulas tab. Click on the Text Function then click on the text function: concatenate. For text
fields one, choose the user ID of the first person within the User ID column. For text field two, choose the first @sandiego.edu. Within the append the
column. Click on OK. The first user ID and Append text is merged. Highlight and drag the Autofill box surrounding the merged data down to apply the
formula to the rest of the cells below Email.
on: concatenate. For text
Within the append the
ta down to apply the
Level             Program           Country of Citizenship   Number of Students
Undergraduate   Industrial Engineering    United Arab Emirates                       1
Undergraduate   Accounting and Finance    United Arab Emirates                       1
Undergraduate   Industrial Engineering    Saudi Arabia                               1
Graduate        MS Real Estate            Thailand                                   1
Graduate        Supply Chain Management   Peru                                       1
Undergraduate   Accounting and Finance    Sweden           1
Undergraduate   Mechanical Engineering    United Kingdom   1
Undergraduate   Fine Arts & Art Studies   South Korea      1
Graduate        Peace and Justice         Sierra Leone     1
Doctorate            Nursing                   Thailand       1
Undergraduate        Political Science         Saudi Arabia   1
Short-Term Scholar   English Language          Taiwan         1
Undergraduate        Electrical Engineering    Saudi Arabia   1
Graduate             Peace and Justice         Latvia         1
Doctorate            Nursing                   Thailand       1
Short-Term Scholar   LLM                       Argentina      1
Short-Term Scholar   Law                       Denmark                1
Short-Term Scholar   Law                       Denmark                1
Short-Term Scholar   English Language          China                  1
Short-Term Scholar   English Language          Norway                 1
Short-Term Scholar   English Language          Taiwan                 1
Short-Term Scholar   English Language          Libya                  1
Short-Term Scholar   English Language          Norway                 1
Short-Term Scholar   MBA                       France                 1
Short-Term Scholar   MBA                       France                 1
Researcher           Law                       South Korea            1
Professor            Law                       Israel                 1
Short-Term Scholar   Finance                   Kuwait                 1
Short-Term Scholar   Law                       South Korea            1
Undergraduate        Liberal Studies           South Korea            1
Undergraduate        International Relations   Czech Republic         1
Graduate             Peace and Justice         Nepal                  1
Graduate             Peace and Justice         Uganda         1
Graduate             Peace and Justice         China          1
Short-Term Scholar   English Language          Taiwan         1
Graduate             Peace and Justice         Netherlands    1
Short-Term Scholar   English Language          Taiwan         1
Short-Term Scholar   English Language          Saudi Arabia   1
Short-Term Scholar   English Language          Norway         1
Short-Term Scholar   English Language          Norway         1
Short-Term Scholar   English Language          Taiwan         1
Short-Term Scholar   English Language          Brazil         1
Short-Term Scholar   English Language          Saudi Arabia   1
Professor                                      Morocco        1
Short-Term Scholar   English Language          Norway         1
Short-Term Scholar   Finance                   Morocco        1
Short-Term Scholar   Communications            Mexico         1
Short-Term Scholar   English Language          Taiwan         1
Short-Term Scholar   Trans-Border Institute    Mexico         1
Short-Term Scholar   English Language          Taiwan         1
Short-Term Scholar   English Language          Kuwait                 1
Undergraduate        Finance                   United Arab Emirates   1
Undergraduate        Accounting                United Arab Emirates   1
Short-Term Scholar   Supply Chain Management   United Kingdom         1
Short-Term Scholar   English Language          United Arab Emirates   1
Short-Term Scholar   English Language          South Korea            1
Undergraduate        Finance                   United Arab Emirates   1
Undergraduate        Finance                   United Arab Emirates   1
Undergraduate        Industrial Engineering    Saudi Arabia           1
Short-Term Scholar   English Language          South Korea            1
Graduate             Supply Chain Management   Mexico                 1
Status   Transferee   Creating a Pivot Table Report
F-1      Yes          A comparison of a typical dataset and a pivot table created from that dataset
F-1      Yes
F-1      Yes          Creating a Pivot Table Report
F-1      Yes          With the table of data open within Excel
F-1      Yes          1. Find a table of data you wish to "pivot." We will use the International Students Table at left.
F-1      Yes          2. Click the Insert tab. In the Tables group, click the arrow on PivotTable, and on the menu, click
F-1      Yes          3. Drag select the entire International Students Table. The Table/Range box shows the range of the
4. New Worksheet is selected for you as the place where the report will be placed, however, click on
F-1      No
the upper left corner of the pivot table. Click OK.
F-1      Yes
F-1      No           [A layout area appears as the location for the new PivotTable, and on the right is the PivotTable Field
F-1      Yes          source data: Each title is a field in the list].
F-1      No
F-1      No           5. In the field list, select the check boxes next to the fields that you want to add to the report. In our
F-1      Yes          6. To compact your new table, right-click a row header and select Expand/Collapse>Collapse Entire F
F-1      No
F-1      No           Table 1: 2009 - International Students at USD Table 2: Create a Pivot Table from the dataset
F-1                   What are PivotTables reports?
F-1      Yes          A PivotTable report is an interactive way to quickly summarize large amounts of data. Use a PivotTab
• Querying large amounts of data in many user-friendly ways.
F-1
• Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and
F-1      Yes
• Expanding and collapsing levels of data to focus your results, and drilling down to details from the s
F-1      Yes          • Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the sourc
F-1      No           • Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of d
Other    No           you want.
F-1      Yes          • Presenting concise, attractive, and annotated online or printed reports.
F-1      Yes
F-1                   Fields are automatically added to the report. Fields that are non-numeric are added to the Row Labe
F-1                   fields, Excel places them on the inside of fields already on the PivotTable report, building a hierarchy.
F-1                   o To remove a field from the report, clear the check box next to the field name in the field list.
F-1                   o To remove all the fields from a report so that you can start over again, click the Options tab
F-1      Yes          group, click the arrow on the Clear button, and then select Clear All.
F-1
F-1
F-1      Yes
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
Other
F-1
Other
Other
F-1
F-1
F-1
F-1
F-1   No
F-1
F-1
F-1
F-1
F-1   No
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1   No
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1       Yes
F-1
J-1       No
F-1
F-1
F-1
F-1
F-1       Yes
F-1
F-1
F-1
F-1
F-1 ELA   No
F-1
F-1
F-1       Yes
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
F-1
J-1
F-1
F-1
F-1
F-1       Yes
F-1       Yes
F-1       Yes
F-1       Yes
F-1       Yes
J-1
F-1       Yes
J-1
J-1       No
F-1
F-1
F-1       Yes
F-1       Yes
Other
F-1       Yes
J-1
J-1       No
F-1       No
F-1       Yes
F-1       No
F-1 ELA
F-1 ELA   No
F-1 ELA   No
F-1 ELA   No
F-1 ELA   No
F-1       No
F-1       No
F-1       No
F-1       No
F-1
F-1
F-1
F-1
F-1       No
F-1
J-1       No
J-1
J-1
J-1
F-1
F-1       No
F-1       No
J-1
J-1       No
F-1       No
F-1       No
Other     Yes
Other     Yes
F-1       Yes
F-1       Yes
F-1
F-1       Yes
F-1
F-1       Yes
F-1       No
F-1       Yes
J-1
J-1
J-1
F-1     Yes
F-1     Yes
F-1
F-1
F-1
F-1     No
F-1
F-1
F-1
F-1
F-1     No
F-1
F-1     No
F-1
F-1
F-1     Yes
F-1     Yes
F-1     No
F-1
F-1
F-1     Yes
F-1     Yes
F-1
F-1     No
F-1     No
F-1     No
F-1     No
F-1     No
F-1     No
F-1     No
Other   No
F-1     No
F-1     No
Other   No
F-1
F-1     Yes
F-1     No
F-1     Yes
F-1     No
F-1     Yes
F-1     No
F-1     No
Other   No
F-1     Yes
F-1       No
F-1       No
F-1       No
F-1
F-1       No
F-1       No
F-1       No
Other     No
F-1       No
F-1       No
F-1       No
F-1       Yes
F-1       Yes
F-1       Yes
F-1 ELA   No
F-1       No
F-1       No
F-1       Yes
J-1
J-1       No
F-1       No
F-1 ELA   No
F-1 ELA   No
F-1       No
F-1 ELA   No
F-1 ELA   No
F-1 ELA   No
F-1 ELA   No
F-1 ELA   Yes
J-1
F-1 ELA   No
J-1       No
J-1       No
J-1       No
J-1       No
F-1       No
J-1       No
F-1       No
J-1       No
J-1       No
J-1       No
J-1
F-1       No
J-1       No
F-1 ELA   No
J-1       No
J-1       No
F-1       Yes
J-1       No
F-1       No
F-1       No
F-1       Yes
F-1 ELA   No
F-1       Yes
F-1       No
F-1       Yes
F-1       Yes
F-1       No
F-1       Yes
F-1       No
J-1
F-1       Yes
Other
F-1       Yes
F-1       Yes
F-1       Yes
F-1 ELA   No
F-1 ELA   Yes
F-1       Yes
F-1       Yes
F-1       Yes
F-1       Yes
F-1       Yes
F-1       Yes
F-1       Yes
F-1       No
F-1       No
F-1       No
F-1       No
Other
F-1 ELA   No
F-1       Yes
F-1       Yes
F-1       No
Other
udents Table at left.
nd on the menu, click PivotTable. The Create PivotTable dialog box opens.
shows the range of the selected data.
placed, however, click on Existing Worksheet and click on cell H6 to indicate

PivotTable Field List. This list shows the column titles from the

add to the report. In our case, select them all
Collapse Entire Field

from the dataset

ts of data. Use a PivotTable report to analyze numerical data in detail and to
ecially designed for:

s and subcategories, and creating custom calculations and formulas.
own to details from the summary data for areas of interest to you.
nt summaries of the source data.
nd interesting subset of data to enable you to focus on the information that

e added to the Row Labels area of the report. As you add more non-numeric
port, building a hierarchy. Numeric fields are added on the right.
me in the field list.
Options tab on the Ribbon under PivotTable Tools. In the Actions
Anonymous Student Quiz Performance

Student ID     Quiz Total
st04jellis
ecstudent02
student_05
st05kshaw
dpstudent08
lpulido
dpstudent07
ecstudent05
dpstudent10
st06mcarter
ecstudent04
dpstudent09
ecstudent03
dpstudent03
st1astewart
dpstudent02
st02bziegler
ecstudent01
student_06
dpstudent05
dpstudent06
dpstudent04
dpstudent01
student_04
st03hkennedy
jsimmons
Try it!
Scenario: You are assisting a faculty member in completing a spreadsheet that lists two items only: A Student
ID and a Total Quiz score. Using the completed table on the Formula & Function Practice Worksheet Tab,
create a VLOOKUP for the Quiz Total column within the table located within this VLOOKUP Worksheet.
1) Click within cell C5 on this VLOOKUP Worksheet .
2) Click on the insert function command next to the formula bar, choose the category Lookup &
Reference from the drop-down menu, scroll to the bottom and select VLOOKUP. Click OK.
3) Lookup_value: Click within the empty box and click on the first item you want to use as a reference
key in this worksheet that corresponds to the same information in the other Formula & Function
Practice worksheet. In this case, clicking on cell B5 in this worksheet will work, because the same
reference data is located on the other worksheet
4) Table_array: select the Formula & Function Practice Worksheet from the tabs below and select the
segment of the table that VLOOKUP will search. In this case, from G3 to K28
5) Col_index_num: Count the number of columns to the right of your previous selection (beginning
with G3) and enter the number
6) Range lookup: We want an exact match, so enter the word: False or the number equivalent 0 (zero)
and click OK

What is VLOOKUP?
It is a function that pulls data from a column of data from the same, or another worksheet. Because it pulls
data from a column and columns are vertically aligned, it is called VLOOKUP. If you were pulling data from a
row, you would use a related function known as HLOOKUP
The 4 elements required by VLOOKUP - Syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Here’s what a completed formula may look like:
=VLOOKUP(B4,Sheet!\$A\$2:\$B\$5,2,FALSE)

Where:
Lookup_value: The reference value to initially search for in the first column of the table array. Similar to a
Key value in relational databases, like an index, it is used to match data to ensure that the correct values are
returned. Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in
the first column of table_array, VLOOKUP returns the #N/A error value.
Table_array: This is the worksheet and columns from which we pull the data. Click on the worksheet where
the data is located, then select the data (not column headers). Ideally, the Lookup (reference ) value will be
located in the leftmost column in that table,so selecting the entire table is simple, otherwise, if the value you
wish to copy is to the right of the lookup value, then select the lookup value as if it were the leftmost column
and proceed to select the data you wish to include in the search.
Col_index_num: This is the column number in table_array, relative to your selection, from which the
matching value must be returned. A col_index_num of 1 returns the value in the first column in your
selection; a col_index_num of 2 returns the value in the second column in table_array selection, etc. Count
the number of columns over to the values you wish to lookup and enter that number here. If col_index_num
is:
• Less than 1, VLOOKUP returns the #VALUE! error value.
• Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
Range_lookup: A logical value that specifies whether you want VLOOKUP to find an exact match or
an approximate match:
• If TRUE or the area is left blank, an approximate match is returned. If an exact match is desired,
then enter FALSE.
then enter FALSE.
Student
Top head-scratching issues and their solutions!

1) Split Text across multiple columns                           Nancy Garcia
a) Select the cell, range or column
containing the text you want to
split. In this case - G3
b) On the Data tab, in Data Tools
group, click Text to Columns and
Space as the delimiter, and changing

2) Merge a range of cells into a single cell
a) Select the range of cells by clicking
and dragging
b) Right-click within the selected cells
and select Format Cells>Alignment
c) Check the Merge Cells box & click OK
*Undo Merged cells by repeating the
above steps and unchecking the

3) Updated Current Date/Time inserted into
a cell
a) Click on a cell to the right and enter
=NOW and hit the Tab key twice
to enter today's date and time.
b) Use =TODAY for only today's date.
c) Reformat the Cell (Rt. click Format
Cell>Number to the type of date
you wish to display.
d) Ctrl ; will insert a fixed date

4) Quick Math without formulas
You've been told that expenses are now
increasing by 5% and that our budget needs         Multiplier Prices to increase by 5%
to reflect this.                                                                  \$25.47
a) Enter 1.05 into cell F35                                                    \$14.99
b) Select F35 by clicking on it once                                            \$6.32
Right-click>Copy                                                            \$15.04
c) Select the cells containing the prices                                      \$10.00
Right-click>Paste Special
e) Choose the Multiply option and click

5) Add a Drop-Down List - No Macros!
Looks great and helps minimize errors too
a) Enter items for your drop-down list           Month:
into beginning with H45 (Months)
-Type January, then use the copy
pull-down to enter all months below
pull-down to enter all months below
(through H56)
b) Click on G45. On the ribbon's Data
Tab click on Data Validation within
the Data Tools group
c) Click the Settings tab
d) In the Allow field, select List
e) In the Source field, drag-select
January through December or type
Bakery Inventory and Cost
Item#        Item Category   Item Type         Current Cost Inventory Final Cost   Formatting Charts
1   Mix             Chocolate          \$       5.25         4 \$ 21.00     In this section we will l
4   Flour           Pastry             \$       7.00         2 \$ 14.00
6   Icing           Buttercream        \$       1.75         3 \$    5.25   Try it!
9   Sprinkles       Sugar Free         \$       1.05        22 \$ 23.10
10   Toppings        Candy              \$       1.30        19 \$ 24.70     1) Create two charts us
11   Yeast           Winklers           \$       7.25         3 \$ 21.75
2) Chart Tools
you'll find the comman
Final Cost of Inventory                                   Chart Tools go away. To

3) Change the chart vie
\$25.00                                                                          After you create a char
Chocolate      the Design tab, in the D
\$20.00
Pastry
\$15.00
4) Change the chart lay
Buttercream
\$10.00                                                           Sugar Free     layout. Double
Candy          button to see all the lay
\$5.00                                                                          Another way to apply l
Winklers
\$-
5) Change the look of a
Item Type
To change chart colors
see all available colors.
(If you don't see colors
Final Cost Comparison                                     the Themes group. Wh
You see the theme's ef
like to apply it to the ch
*Note: The colors from
Pastry
6) Format chart titles
Buttercream    There are a number of
Sugar Free     1. First, select a title.
2. Then, on the Format
Candy
pick a gradient, or a tex
Winklers       to add visual style to ti

7) Font Changes
To make font changes,
then go to the Font gro
formatting options.

8) More formatting op
1. On the Format tab, i
1. On the Format tab, i
Formatting Charts
In this section we will look at various ways to alter the layout, look and types of charts

Create two charts using the table on the left: One bar chart and one pie chart. Then play with the following:

Chart Tools
After your chart is inserted, the Chart Toolbar appear, with three new tabs: Design, Layout, and Format. On these tabs
you'll find the commands you need to work with charts. When you complete the chart, click outside the chart. The
Chart Tools go away. To get them back, click inside the chart. Then the tabs reappear.

Change the chart view
After you create a chart, you can make your chart compare data in more than one way. Double-click in the chart. On
the Design tab, in the Data group, click Switch Row/Column. Switch it back.

4) Change the chart layout - axis
You can add chart and axis titles, add a data table, delete gridlines, or add data labels quickly by changing the chart
layout. Double-click in the chart. Then on the Design tab, in the Chart Layouts group, click the More down-arrow
button to see all the layouts. Switch between a few and click the one you want.
Another way to apply layouts is to click the Layout tab and make selections individually in the Labels and Axes groups.

5) Change the look of a chart
To change chart colors, click in the chart. Then on the Design tab, in the Chart Styles group, click the More button to
see all available colors. Then click the ones you want.
(If you don't see colors you want, you can get other colors by clicking the Page Layout tab, and then clicking Colors in
the Themes group. When you rest the pointer over a theme, the theme is shown in a temporary preview on the chart.
You see the theme's effect before you apply it, saving you the step of undoing it if you don't like it. Click the one you
like to apply it to the chart).
*Note: The colors from the theme will be applied to other elements you add to the worksheet, such as tables, cell

6) Format chart titles
There are a number of options if you want to add formatting to chart or axis titles.
1. First, select a title.
2. Then, on the Format tab, in the WordArt Styles group, click the arrow beside Text Fill to add a color. You can also
pick a gradient, or a texture. Or you can click the arrow beside Text Outline or Text Effects in the WordArt Styles group
to add visual style to titles.

7) Font Changes
To make font changes, such as making the font larger or smaller, or to change the font face, click the Home tab, and
then go to the Font group. Or you can make the same formatting changes by using the Mini toolbar. The toolbar
appears in a faded fashion after you select text. If you point at the toolbar it becomes solid, and then you can select
formatting options.

8) More formatting options
1. On the Format tab, in the Shape Styles group, you can for example, click the arrow on Shape Effects, point to
1. On the Format tab, in the Shape Styles group, you can for example, click the arrow on Shape Effects, point to
Shadow, and then rest the pointer on the different shadow styles in the list. You can see a preview of the shadows as
at. On these tabs
he chart. The

n the chart. On

ging the chart
arrow

and Axes groups.

More button to

icking Colors in
ew on the chart.
ck the one you

s tables, cell

. You can also
dArt Styles group

Home tab, and
The toolbar
you can select

s, point to
s, point to
In the 2007 Microsoft Office system, you can use passwords to help prevent other people from opening or modifying your
documents, workbooks, and presentations. Keep in mind that Microsoft cannot retrieve forgotten passwords!

Try it!

1) Click the Microsoft Office Button , point to Prepare, and then click Encrypt Document.
2) In the Password box, type a password, and then click OK. You can type up to 255 characters. By default, this feature
uses AES 128-bit advanced encryption. Encryption is a standard method used to help make your file more secure.
3) In the Reenter password box, type the password again, and then click OK.
To save the password, save the file.

2) Click the Microsoft Office Button , point to Prepare, and then click Encrypt Document.
3) In the Encrypt Document dialog box, in the Password box, delete the encrypted password, and then click

1) Click the Microsoft Office Button , click Save As, and on the bottom of the Save As dialog, click Tools
2) On the Tools menu, click General Options. The General Options dialog opens.
3) Under File sharing, in the Password to modify box, type a password.
5) Click Save.
*Note To remove the password, repeat these instruction and then delete the password from the Password to modify
Click Save.
e from opening or modifying your

acters. By default, this feature

ord, and then click OK.

o allow others to modify the

Tools.

0   1-Jan    Conditional Row Color Based on Cell Value
0   2-Jan
0   3-Jan     Conditional Formatting:
0   4-Jan
0   5-Jan     1. Home Tab --> Conditional Formatting --> New Rule.
0   6-Jan     2. Use a Formula to Determine Which Cells to Format.
0   7-Jan     3. Insert this Formula: =INDIRECT("A"&ROW())=1
0   8-Jan     4. Beside the Preview, Select "Format" and choose a color scheme.
0   9-Jan     5. Make sure to apply the rule to the entire worksheet.
0   10-Jan    6. Click "OK."
0   11-Jan
0   12-Jan
0   13-Jan
0   14-Jan
0   15-Jan
0   16-Jan
0   17-Jan
0   18-Jan
0   19-Jan
0   20-Jan
0   21-Jan
0   22-Jan
0   23-Jan
0   24-Jan
0   25-Jan
0   26-Jan
0   27-Jan
0   28-Jan
0   29-Jan
0   30-Jan
0   31-Jan
0   1-Feb
0   2-Feb
0   3-Feb
0   4-Feb
0   5-Feb
0   6-Feb
0   7-Feb
0   8-Feb
0   9-Feb
0   10-Feb
0   11-Feb
0   12-Feb
0   13-Feb
0   14-Feb
0   15-Feb
0   16-Feb
0   17-Feb
0   18-Feb
0   19-Feb
0   20-Feb
0   21-Feb
0   22-Feb
0   23-Feb
0   24-Feb
0   25-Feb
0   26-Feb
0   27-Feb
0   28-Feb
0   1-Mar
0   2-Mar
0   3-Mar
0   4-Mar
0   5-Mar
0   6-Mar
0   7-Mar
0   8-Mar
0   9-Mar
0   10-Mar
0   11-Mar
0   12-Mar
0   13-Mar
0   14-Mar
0   15-Mar
0   16-Mar
0   17-Mar
0   18-Mar
0   19-Mar
0   20-Mar
0   21-Mar
0   22-Mar
0   23-Mar
0   24-Mar
0   25-Mar
0   26-Mar
0   27-Mar
0   28-Mar
0   29-Mar
0   30-Mar
0   31-Mar
0   1-Apr
0   2-Apr
0   3-Apr
0   4-Apr
0   5-Apr
0   6-Apr
0   7-Apr
0   8-Apr
0   9-Apr
0   10-Apr
0   11-Apr
0   12-Apr
0   13-Apr
0   14-Apr
0   15-Apr
0   16-Apr
0   17-Apr
0   18-Apr
0   19-Apr
0   20-Apr
0   21-Apr
0   22-Apr
0   23-Apr
0   24-Apr
0   25-Apr
0   26-Apr
0   27-Apr
0   28-Apr
0   29-Apr
0   30-Apr
0   1-May
0   2-May
0   3-May
0   4-May
0   5-May
0   6-May
0   7-May
0   8-May
0   9-May
0   10-May
0   11-May
0   12-May
0   13-May
0   14-May
0   15-May
0   16-May
0   17-May
0   18-May
0   19-May
0   20-May
0   21-May
0   22-May
0   23-May
0   24-May
0   25-May
0   26-May
0   27-May
0   28-May
0   29-May
0   30-May
0   31-May
0   1-Jun
0   2-Jun
0   3-Jun
0   4-Jun
0   5-Jun
0   6-Jun
0   7-Jun
0   8-Jun
0   9-Jun
0   10-Jun
0   11-Jun
0   12-Jun
0   13-Jun
0   14-Jun
0   15-Jun
0   16-Jun
0   17-Jun
0   18-Jun
0   19-Jun
0   20-Jun
0   21-Jun
0   22-Jun
0   23-Jun
1   24-Jun
0   25-Jun
0   26-Jun
0   27-Jun
0   28-Jun
0   29-Jun
0   30-Jun
0    1-Jul
0    2-Jul
0    3-Jul
0    4-Jul
0    5-Jul
0    6-Jul
0    7-Jul
0   8-Jul
0   9-Jul
0   10-Jul
0   11-Jul
0   12-Jul
0   13-Jul
0   14-Jul
0   15-Jul
0   16-Jul
0   17-Jul
0   18-Jul
0   19-Jul
0   20-Jul
0   21-Jul
0   22-Jul
0   23-Jul
0   24-Jul
0   25-Jul
0   26-Jul
0   27-Jul
0   28-Jul
0   29-Jul
0   30-Jul
0   31-Jul
0   1-Aug
0   2-Aug
0   3-Aug
0   4-Aug
0   5-Aug
0   6-Aug
0   7-Aug
0   8-Aug
0   9-Aug
0   10-Aug
0   11-Aug
0   12-Aug
0   13-Aug
0   14-Aug
0   15-Aug
0   16-Aug
0   17-Aug
0   18-Aug
0   19-Aug
0   20-Aug
0   21-Aug
0   22-Aug
0   23-Aug
0   24-Aug
0   25-Aug
0   26-Aug
0   27-Aug
0   28-Aug
0   29-Aug
0   30-Aug
0   31-Aug
0   1-Sep
0   2-Sep
0   3-Sep
0   4-Sep
0   5-Sep
0   6-Sep
0   7-Sep
0   8-Sep
0   9-Sep
0   10-Sep
0   11-Sep
0   12-Sep
0   13-Sep
0   14-Sep
0   15-Sep
0   16-Sep
0   17-Sep
0   18-Sep
0   19-Sep
0   20-Sep
0   21-Sep
0   22-Sep
0   23-Sep
0   24-Sep
0   25-Sep
0   26-Sep
0   27-Sep
0   28-Sep
0   29-Sep
0   30-Sep
0   1-Oct
0   2-Oct
0   3-Oct
0   4-Oct
0   5-Oct
0   6-Oct
0   7-Oct
0   8-Oct
0   9-Oct
0   10-Oct
0   11-Oct
0   12-Oct
0   13-Oct
0   14-Oct
0   15-Oct
0   16-Oct
0   17-Oct
0   18-Oct
0   19-Oct
0   20-Oct
0   21-Oct
0   22-Oct
0   23-Oct
0   24-Oct
0   25-Oct
0   26-Oct
0   27-Oct
0   28-Oct
0   29-Oct
0   30-Oct
0   31-Oct
0   1-Nov
0   2-Nov
0   3-Nov
0   4-Nov
0   5-Nov
0   6-Nov
0   7-Nov
0   8-Nov
0   9-Nov
0   10-Nov
0   11-Nov
0   12-Nov
0   13-Nov
0   14-Nov
0   15-Nov
0   16-Nov
0   17-Nov
0   18-Nov
0   19-Nov
0   20-Nov
0   21-Nov
0   22-Nov
0   23-Nov
0   24-Nov
0   25-Nov
0   26-Nov
0   27-Nov
0   28-Nov
0   29-Nov
0   30-Nov
0   1-Dec
0   2-Dec
0   3-Dec
0   4-Dec
0   5-Dec
0   6-Dec
0   7-Dec
0   8-Dec
0   9-Dec
0   10-Dec
0   11-Dec
0   12-Dec
0   13-Dec
0   14-Dec
0   15-Dec
0   16-Dec
0   17-Dec
0   18-Dec
0   19-Dec
0   20-Dec
0   21-Dec
0   22-Dec
0   23-Dec
0   24-Dec
0   25-Dec
0   26-Dec
0   27-Dec
0   28-Dec
0   29-Dec
0   30-Dec
0   31-Dec
New Rule.
o Format.

hoose a color scheme.
worksheet.
1

Red

Blue

Green

User Chose:   option 1

```
Related docs
Other docs by shuifanglj
Chris_Couch_2007
General Engineering Technolgy