Docstoc

Section1

Document Sample
Section1 Powered By Docstoc
					Instructions: There are 3 sections to this workbook, which are broken down by sheet. This workbook is interactive.
You should place your answers in the red boxes, which will turn green if your answer is correct. (If you need to undo your ste
While some questions may provide hints, this worksheet is meant to be vague so that you can obtain practice using Excel help
problems up online. If you become stuck after using Excel's help and using an online search engine, please come into the Qua
in UW2-134 to receive help from a tutor.

1. Transpose only the values of the Score. (Hint: Paste Special)

         3                          Start here
         6
         9
        12
        15
        18
        21
        24
        27
        30
        33
        36

2. Use Fill shortcuts.

a. For repeating the same number and for repeating the same equation: Use ctrl+R to go across columns and ctrl+D to go dow
In the first cell, type the number 10, then select that first cell and highlight
the number of cells you want that data or formula repeated in. Then use ctrl+D. (See question 3 for formula example).




b. To make a series of numbers with a constant step value, or to complete the same tasks as in part a: Select the first cell, or f
right hand corner of the last highlighted cell on the black square (your mouse should look like a plus sign), then drag to the re
Type the number 5 into the first cell, then type a 10 into the next cell. Highlight the cells containing 5 and 10, then click the bo
3. Fill in the table using absolute addressing. Calculate the sum of each row and column variable, e.g., top left corner should

                     5         14           3           7
         1
         5
         4
         2
        11
        12
         8
         7
         3

4. Calculate the total amount spent using one Excel function. (Hint: use the general function button so that you can do a sear

$ each  Quantity
 $ 1.20          4
 $ 5.00          7
 $ 4.30        23
 $ 6.60          5
 $ 4.80          4
 $ 3.00          3
 $ 2.00          6
 $ 2.25        10
 $ 7.49          9 Total
 $ 5.40          2

5. Combining formulas: Calculate the quantity-weighted average price using Excel functions.

$ each  Quantity
 $ 1.20          4
 $ 5.00          7
 $ 4.30        23
 $ 6.60          5
 $ 4.80          4
 $ 3.00          3
 $ 2.00          6
 $ 2.25        10
 $ 7.49          9 Q-W Ave Price
 $ 5.40          2

6. Put random numbers between 10 and 50 in the cells below using an Excel function.
7. Count the number of people who have pets, and sum the number of cars for people who own at least 2 cars. (Again, using

Pets         No. of Cars
No                      1
Yes                     3
Yes                     3
No                      0
Yes                     2
No                      0
No                      3
Yes                     3
Yes                     2
No                      0
# of people with pets:
total # of cars if at least 2:
workbook is interactive.
ect. (If you need to undo your steps, use the undo button, or ctrl+z.
an obtain practice using Excel help, and looking
engine, please come into the Quantitative Skills Center




ross columns and ctrl+D to go down rows.

on 3 for formula example).




s in part a: Select the first cell, or first few cells (series) and click on the bottom
ke a plus sign), then drag to the required number of cells.
ntaining 5 and 10, then click the bottom corner and drag to fill in a series with a step value of 5.
riable, e.g., top left corner should be 6.




n button so that you can do a search, or start typing an equation that uses "sum").
own at least 2 cars. (Again, using Excel functions).
1. Import the external text data: "sample data import" by saving it to your desktop first and then using the data tab. Hint: sep

                                                                              Name       Grade        Year
                                                                                         B+
                                                                              Susie Homebody          Junior
                                                                              Joe Smith B             Senior
                                                                              Jane Smith C+           Freshman
                                                                              Jack Brown A            Sophomore
                                                                                         A-
                                                                              Brian Johnson           Junior

2. Separate the Names into two columns with the first and last name. Hint: Text to Columns

First Name          Last Name           Grade                  Year
Susie Homebody                          B+                     Junior                     Susie       Homebody
Joe Smith                               B                      Senior                     Joe         Smith
Jane Smith                              C+                     Freshman                   Jane        Smith
Jack Brown                              A                      Sophomore                  Jack        Brown
Brian Johnson                           A-                     Junior                     Brian       Johnson

3. Combine (Concatenate) the first and last names into one column with a space in between.

First Name          Last Name           Name
Susie               Homebody
Joe                 Smith
Jane                Smith
Jack                Brown
Brian               Johnson

4. Conditionally format the cell below so that it turns from red to green when the number 3 is typed into the cell; like the cel




5. Lookup and enter the B grade under the Grade column.

First Name          Last Name           Grade                  Year
Susie               Homebody            B+                     Junior
Joe                 Smith               B                      Senior
Jane                Smith               C+                     Freshman
Jack                Brown               A                      Sophomore
Brian               Johnson             A-                     Junior




6. Lookup the grade of Jack.

First Name          Last Name           Grade                  Year
Susie               Homebody            B+                     Junior
Joe                 Smith               B                      Senior
Jane                  Smith            C+                      Freshman
Jack                  Brown            A                       Sophomore
Brian                 Johnson          A-                      Junior




7. Trace the precedents of the formula in the yellow cell below and enter the cell names that were referenced to calculate th

                2.5                                        5               3          8
                                                           2               4          1




8. Goal Seek an interest rate such that your payments on the $100k loan for 180 months is -$900. (Hint: Leave the equation i

Loan Amount      Term in Months Interest Rate                  Payment
 $    100,000.00              180                                 ($555.56)                               7.02%
g the data tab. Hint: separate by delimited and look at all of the choices in the next window.




nto the cell; like the cell to the right of it.
ferenced to calculate the number.




nt: Leave the equation in the payment cell)
Using your keyboard: To access commands in Excel using only the keyboard, you can see which letters to use for shortcuts by
Hitting the key once will show you the keys to access the office button, buttons on the quick access toolbar at the top, and th
For instance, if you hit "alt" and then hit "H" you can access everything in the home tab. If you select "N" instead, you can acc
Insert tab, and so on… Typing a letter from there will let you access one of the commands. Hitting "Esc" will let you go back u
Try searching through the menu with your keyboard instead of your mouse.

Comments: If you want to add a comment to a cell, select your chosen cell, and go to the Review tab. Click on "New Commen
When you've moved onto another cell, a red triangle will appear in the upper right hand corner of the cell; indicating it conta
If you leave your mouse over the cell, you will be able to see the comment.

Links: To link a sheet within your workbook, or to insert a website link, go to the Insert tab. Try linking the cell in yellow below




1. Insert a dropdown menu under Year so that a user can only choose from the items in the yellow list.
Select all of the empty cells below "Year" in the data table. Use "Data Validation" under the "Data" tab. Choose "list" in the "S
Select the list in yellow for "Source". You should see a dropdown arrow for every person under Year which only contains the o
Similar to the data table on the right.

First Name     Last Name      Grade       Year                       First Name    Last Name     Grade
Susie          Homebody       B+                                     Susie         Homebody      B+
Joe            Smith          B                                      Joe           Smith         B
Jane           Smith          C+                                     Jane          Smith         C+
Jack           Brown          A                                      Jack          Brown         A
Brian          Johnson        A-                                     Brian         Johnson       A-

Freshman
Sophomore
Junior
Senior

2. A macro is a mini program that has Excel perform a recorded series of steps.
For instance, if you often need to have your worksheets set up in a landscape format with the top row in bold with Jan-Dec en
and the leftmost column starting in row 2 to read 1-36 you can record a personal macro to do this for you.

a. First insert a new worksheet where you'll record the macro.

b. Go to the "Review" tab and choose "Macros", then select "Record Macro...".

c. In the popup window, name your macro with something you'll recognize that doesn't contain spaces.

d. If you want the macro accessible through a keyboard shortcut, you can type in your choice. However, Excel
often already uses "ctrl" plus a lowercase letter, so you may want to type in a capitalized letter. Then, to run the macro, you h
hold down ctrl, shift, and then type your letter.

e. Save the macro in your Personal Macro Workbook, and it will be available any time you open an Excel file.
f. Type in a description of what the macro does.

g. Now you can click "Ok". Anything you do from this point on will be recorded as part of your macro, so be careful!!!

h. Perform the steps you want Excel to do; in our example we have 4: 1) put the page in landscape format.
2) Make the top row bold. 3) Enter Jan. through Dec. in the top row. 4) enter 1-36 in the first column, starting in the second r

i. When you're through with the steps, go back to "View" and "Macros" and choose "Stop Recording". Make a new workbook
hich letters to use for shortcuts by hitting the "alt" key.
  access toolbar at the top, and the tabs in the ribbon.
ou select "N" instead, you can access everything in the
Hitting "Esc" will let you go back up through the menus.


eview tab. Click on "New Comment" and type in the box that appears.
 ner of the cell; indicating it contains a comment.


Try linking the cell in yellow below to cell A1 in the Section1 sheet.




"Data" tab. Choose "list" in the "Settings" tab.
der Year which only contains the options in the yellow list.


              Year




he top row in bold with Jan-Dec entered,
do this for you.




e. However, Excel
ter. Then, to run the macro, you have to


pen an Excel file.
ur macro, so be careful!!!

dscape format.
t column, starting in the second row.

ecording". Make a new workbook and test it out!

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:11/6/2011
language:English
pages:14