VIEWS: 3 PAGES: 14 POSTED ON: 11/6/2011
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!
Pages to are hidden for
"Section1"Please download to view full document