Excel 2010 worksheets with solutions

Document Sample
Excel 2010 worksheets with solutions Powered By Docstoc
					                                                    Excel 2010 Introductory Class



Move the white cross and left click to select a cell.
Also use arrow and tab keys to move cursor from cell to cell

Double click on column divider to expand cell for all text

White cross in cell - click and hold left mouse button to highlight
Compass points on cell outline - click and hold left mouse button to drag cell
lower RH corner of cell - black cross - click and hold to copy contents to other cells

All new Excel spreadsheets have three 'pages or sheets.' - Sheet1, Sheet2, Sheet3
 Right click Sheet1 to rename sheet

to select multiple rows or columns hold the shift key




                                                             Page 1 of 22                C. Cooke 8/2011
                                             Excel 2010 Introductory Class



Lunch Costs
for the Week


Monday         $    7.99 > $6.50
Tuesday        $    6.50
Wednesday      $    6.99
Thursday       $    7.50
Friday         $    6.00
Saturday       $    6.95
Sunday         $    7.00
avg            $    6.99
high           $    7.99
sum            $   48.93



               Column1   Column2   Column3    Column4




                                                     Page 2 of 22            C. Cooke 8/2011
Excel 2010 Introductory Class




        Page 3 of 22            C. Cooke 8/2011
                                       Excel 2010 Introductory Class

Oprah      Winfrey
Tiger      Woods
Steven     Spielberg
Howard     Stern
Dan        Brown
Bruce      Springsteen
Donald     Trump
Muhammad   Ali
Paul       McCartney
George     Lucas
Elton      John
David      Letterman
Phil       Mickelson
Brad       Pitt
Peter      Jackson
Jay        Leno
Celine     Dion
Kobe       Bryant        Highlight the columns you want to split/separate into two new columns
Michael    Jordan        click on the 'text to columns' button
Johnny     Depp          select 'delimited' and go to next step
                         Check off 'space' as your delimiter - you should see a blackline appear dividing your first and
Jerry      Seinfeld      last names.
Simon      Cowell        select any formatting necessary - we are using 'general' and select finish
Michael    Schumacher
Tom        Hanks
Denzel     Washington




                                                Page 4 of 22                                                         C. Cooke 8/2011
                                                           Excel 2010 Introductory Class

First Name     Last Name           Street Address          City          State   Zip Code   Phone
Kyle           Anderson            48 Eisenhard Drive      Ivyland       PA      18974      364-4785
Steve          Blanton             36 Mathew Circle        Richboro      PA      18954      322-9863
Taylor         Denton              29 Mathew Circle        Richboro      PA      18954      355-1445
Kaitlyn        Gallagher           11 Mathew Circle        Churchville   PA      18966      357-5858
Krystal        Gayle               5 Mathew Circle         Churchville   PA      18966      357-4789
Kristen        Halson              36 Barley Road          Ivyland       PA      18974      355-5894
Andrew         Jackson             168 Strawberry Lane     Ivyland       PA      18974      364-6952
Sarah          Lee                 300 Bless Circle        Richboro      PA      18954      322-6985
Alyssa         Milano              82 Cherokee Drive       Churchville   PA      18966      322-5830
Bob            Newhart             125 Tanyard Road        Ivyland       PA      18974      322-1010
Kyle           O'Grady             126 Milbob Drive        Ivyland       PA      18974      357-9089
Devyn          O'Hara              18 Cameo Drive          Churchville   PA      18966      355-4567
Eric           Redson              119 Tanyard Road        Richboro      PA      18954      322-8925
Samantha       Reilley             47 N. Traymore Avenue   Ivyland       PA      18974      364-8274
Marc           Santora             81 Sharon Drive         Richboro      PA      18954      357-6598
Dan            Smalley             69 Lehigh Drive         Richboro      PA      18954      364-8969
Casey          Smith               7 Douglas Road          Richboro      PA      18954      355-1234
Casey          Stengel             11 Skyview Drive        Ivyland       PA      18974      364-9052
Danny          Thomas              27 Silo Hill            Richboro      PA      18954      355-6565
Jeana          Tyson               305 Bless Cirlce        Churchville   PA      18966      357-2525


               1 sorted by last name
               2 filter only Richboro

Notice only latest filter is available.



Also select filter on "HOME" tab




                                                                    Page 5 of 22                       C. Cooke 8/2011
                                         Excel 2010 Introductory Class

Nitrogen Oxygen    CO2       other
        75      15       7           3          To Graph Data
                                                1. Highlight the eight cells containing information
                                                   beginning with Nitrogen. (include headers)
                                                2. Go to the Insert Ribbon
                                                3. Click the choice arrow under Pie and choose
                                                   Exploded Pie under 3-D
                                                4. In the Chart Tools Ribbon under Design click Move Chart
                                                   This will move the Graph to a new page
                                                5 - insert column graph
                                                6 - under design choose chart layout 5
                                                7- under layout - change axis title to percentage




                                                 Page 6 of 22                                                C. Cooke 8/2011
             Chart Title
      Nitrogen   Oxygen   CO2     other




                    15%




                                          7%


                            10%




75%
                                          3%
                                                      Excel 2010 Introductory Class

            Income           Salaries       Utilities       Supplies                                   Monthly Profit
January      $ 1,256,821.00 $ 48,795.00 $ 5,987.00 $               9,865.00                               $1,192,174.00
February     $ 1,358,790.00 $ 51,794.00 $ 6,598.00 $               9,658.00                               $1,290,740.00
March        $ 2,648,730.00 $ 69,875.00 $ 4,986.00 $               6,985.00                               $2,566,884.00
April        $ 1,579,358.00 $ 69,785.00 $ 6,598.00 $               7,987.00                               $1,494,988.00
May          $ 1,698,702.00 $ 45,795.00 $ 6,985.00 $               7,589.00                               $1,638,333.00
June         $ 2,009,860.00 $ 64,579.00 $ 9,769.00 $               8,976.00                               $1,926,536.00
July         $ 1,897,654.00 $ 56,794.00 $ 2,649.00 $               8,976.00                               $1,829,235.00
August       $ 1,498,358.00 $ 95,794.00 $ 9,865.00 $               9,876.00                               $1,382,823.00
September    $ 1,987,695.00 $ 84,679.00 $ 7,857.00 $               6,986.00                               $1,888,173.00
October      $ 1,769,852.00 $ 54,976.00 $ 5,987.00 $               8,697.00                               $1,700,192.00
November     $ 1,893,791.00 $ 21,698.00 $ 6,972.00 $               8,596.00                               $1,856,525.00
December     $ 1,589,768.00 $ 78,965.00 $ 4,897.00 $               8,497.00                               $1,497,409.00
            total income     total salaries total utilities total supplies
             $ 21,189,379.00 $ 743,529.00 $ 79,150.00 $ 102,688.00                                      $ 20,264,012.00
                                                                                            Tax         $ 1,215,840.72




            fill in totals in row 15 (B through E)
            calculate monthly profit (place answer in G2 for row 2 then grab bottom corner and drag down to G13)
            HINT: profit is income minus expenses
            change cells to currency format
            calculate 6% tax in G16




                                                               Page 8 of 22                                               C. Cooke 8/2011
                                                                 Excel 2010 Introductory Class

             Tuesday       Wednesday Thursday Friday    Saturday
High Temp               73         78       79       78        73 average Hi temp                          76.2
Low temp                57         57       58       58        50 average Low temp                           56


             1. Place the cursor in cell H2
             2. Go to the Formula Ribbon and click the choice
                arrow under AutoSum
             3. Choose Average - It will place a formula from cell B2 to G2.
             4. To choose the days of the week, while the formula is active
                move the mouse to cell B2, click and hold the left mouse button
                and highlight from cell B2 to F2. The formula will read B2:F2
             5. Hit Enter.
             6. Click on cell H2, Place the cursor in the lower right corner
                of the cell, click and drag down to cell F3.


             7. now to create your graph - highlight A1 through F3
             8. go to insert - column - cylinder - 1st graph
             9. click on graph - click on LAYOUT under chart tools and select 'data labels' and 'show'
             10. now click on one of the numbers above the blue cylinders - go to format - shape fill - and select yellow
             11. now click on one of the numbers above the red cylinders - shape fill - green

                               78          79          78
            80     73                                               73
            70
                        57          57          58          58
            60                                                           50
            50
            40
                                                                                         High Temp
            30
                                                                                         Low temp
            20
            10
             0




                                                                          Page 9 of 22                                      C. Cooke 8/2011
                                           Excel 2010 Introductory Class


          12   150   Calculating Min
          27    85
          24    65   You can use the MIN function to find the lowest number in a series of numbers.
sum       63   300
                      1. Move your cursor to cell A7.
average   21   100    2. Type Min.
min       12    65    3. Press the right arrow key to move to cell B7.
max       27   150    4. Type = MIN(B1:B3).
count      3          5. Press Enter. The lowest number in the series, which is 12 appears.

                     Calculating Max
                     You can use the MAX function to find the highest number in a series of numbers.

                      1. Move your cursor to cell A8.
                      2. Type Max.
                      3. Press the right arrow key to move to cell B8.
                      4. Type = MAX(B1:B3).
                      5. Press Enter. The highest number in the series, which is 27, appears.

                     Note: You can also use the drop-down menu next to the Sum icon to calculate mins and max.

                     Calculating Count
                     You can use the count function to count the number of items in a series.

                      1. Move your cursor to cell A9.
                      2. Type Count
                      3. Press the right arrow key to move to cell B9.
                      4. Go to the Formula Ribbon and click the down arrow next to the AutoSum icon.
                      5. Click Count Numbers.
                      6. Highlight B1 to B3.
                      7. Press Enter. The number of items in the series, which is 3 appears.




                                                   Page 10 of 22                                                 C. Cooke 8/2011
                                                          Excel 2010 Introductory Class

            Actual Expenses Predicted Expenses
Books              $20,000.00             $19,000.00 Over Budget                   ($1,000.00)
Paper              $5,000.00               $5,500.00   OK                            $500.00
Fasteners          $2,500.00               $2,600.00   OK                            $100.00
Pens               $5,000.00               $5,600.00   OK                            $600.00
Folders            $1,000.00                $900.00    Over Budget                  ($100.00)

Coffee             $1,000.00                $500.00 Over Budget-who cares?          ($500.00)


Predicted Expenses of                              3

more than $5,000


# of items over budget                             3




                                                                                                 see cells C9 & C12




                                                                 Page 11 of 22                                        C. Cooke 8/2011
                                                        Excel 2010 Introductory Class




Conditional IF formula syntax
No spaces in any formula
All formulas begin with the = sign
position cursor in cell D2

After IF, open a parenthesis and set the parameters.
In this formula the question is whether Actual
Expenses are greater than Predicted Expenses.
The logical test is followed by a comma.
Open a quote and type the TRUE value then close
quote. In this case "Over Budget"
Separate the values by a comma.
Open a quote and type the False value then close
quote. In this case "OK"
Close the parenthesis and hit Enter.
Autofill the formula from C2 to C7.

COUNTIF
Counts the number of cells within a range that meet a
specific criteria.
see cells C9 & C12




                                                               Page 12 of 22            C. Cooke 8/2011
                                       Excel 2010 Introductory Class


Miscellaneous Tips:
# Workbook layout - View - Page layout - print titles create Headder/footer for each page If all sheets are selected t

# Printing - each page can be set to a different orientation


# Formatting and errors (from HELP)




                                              Page 13 of 22                                     C. Cooke 8/2011
Excel 2010 Introductory Class




  The Auto Fill Options




  The Trace Error




  When you click the arrow next to the button, a list of options for error checking



  The Insert Options


       Page 14 of 22                                       C. Cooke 8/2011
Excel 2010 Introductory Class


  button might appear next to inserted cells, rows, or columns.

  When you click the arrow next to the button, a list of formatting options appea




  1. Click the File tab, and then click Options.




  The Formatting Options




  When you click the arrow next to the button, a list of scoping options appears.


  Colored triangles that you might see in your worksheet

  The colored triangles that can appear in a cell are green (error) and red (comm
  Green Triangle



  Red Triangle



       Page 15 of 22                                      C. Cooke 8/2011
Excel 2010 Introductory Class




       Page 16 of 22            C. Cooke 8/2011
                                                          Excel 2010 Introductory Class




e layout - print titles create Headder/footer for each page If all sheets are selected then the titles will be on all sheets

et to a different orientation




          The buttons that can appear next to a cell are as follows: AutoCorrect Options, Paste
          Options, Auto Fill Options, Trace Error, Insert Options, and Formatting Options.



          The AutoCorrect Options
          button might appear when you rest the mouse pointer on the small blue box under text
          that was automatically corrected. For example, if you type a hyperlink or an e-mail
          address in a cell, the Autocorrect Options button might appear. If you find text that
          you do not want to be corrected, you can either undo a correction manually in the cell
          or turn AutoCorrect options on or off.



          To manually undo an AutoCorrect operation on a cell, edit the text and make sure that
          the cursor is not at the end of the text in the cell. Then, press ENTER. To turn
          AutoCorrect options on or off, click the AutoCorrect Options button, and then make a
          selection from the list.


          The Paste Options




                                                                 Page 17 of 22                                       C. Cooke 8/2011
                                                 Excel 2010 Introductory Class


button appears just below your pasted selection after you paste text or data. When you
click the button, a list appears that lets you determine how to paste the information into
your worksheet.


The available options depend on the type of content that you are pasting, the program
that you are pasting from, and the format of the text where you are pasting.



The Auto Fill Options
button might appear just below your filled selection after you fill text or data in a
worksheet. For example, if you type a date in a cell and then drag the cell down to fill
the cells below it, the Auto Fill Options button might appear. When you click the
button, a list of options for how to fill the text or data appears.


The available options in the list depend on the content that you are filling, the program
that you are filling from, and the format of the text or data that you are filling.



The Trace Error
button appears next to the cell in which a formula error occurs, and a green triangle
appears in the upper-left corner of the cell.


When you click the arrow next to the button, a list of options for error checking appears.



The Insert Options


                                                        Page 18 of 22                        C. Cooke 8/2011
                                                   Excel 2010 Introductory Class


button might appear next to inserted cells, rows, or columns.

When you click the arrow next to the button, a list of formatting options appears.


 Note If you do not want this button to be displayed every time that you insert
formatted cells, rows, or columns, you can turn this option off.

1. Click the File tab, and then click Options.
2. Click the Advanced category and then, under Cut, copy, and paste, select or clear
the Show Insert Options buttons check box to turn this option on or off.


The Formatting Options
button is used to change the scoping method for conditional formatting data in a
PivotTable report.


When you click the arrow next to the button, a list of scoping options appears.


Colored triangles that you might see in your worksheet

The colored triangles that can appear in a cell are green (error) and red (comment).
Green Triangle
A green triangle in the upper-left corner of a cell indicates an error in the formula in the
cell. If you select the cell, the Trace Error button appears. Click the arrow next to the
button for a list of options.
Red Triangle



                                                          Page 19 of 22                        C. Cooke 8/2011
                                                   Excel 2010 Introductory Class



A red triangle in the upper-right corner of a cell indicates that a comment is in the cell. If
you rest the mouse pointer over the triangle, you can view the text of the comment.




                                                          Page 20 of 22                          C. Cooke 8/2011
    # merge and center
    highlight two cells then select merge and center to make column labels spans two columns
    NOW - edit the table below to create a table for students which contains entries for tallying
      (make sure you fix the cell alignment to show all of the titles)
    if you wish to get fancy you can highligh the background of the data cells.

S             1Q                       2Q                       3Q
t
u
d
    homework        tests     homework       tests     homework       tests
e
n
t
mn labels spans two columns
h contains entries for tallying homework and test grades across all four semesters of the school year




                       4Q


                                        Totals
             homework        tests

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:21
posted:7/31/2012
language:English
pages:22