Excel Income Tax Worksheets or Spreadsheet - DOC by ugd11381

VIEWS: 93 PAGES: 31

More Info
									Page 1
 Moving around                              3
 Personal Expenses Exercise                 6
 Changing cell widths by dragging           6
 Changing cell widths automatically         6
 Formulas with numbers                      7
 Formulas with Cell references              7
 Inserting Cell References Automatically    8
 Personal Expenses Exercise                 8
 The Sum Function                           9
 The AutoSum button                         9
 Personal Expenses 2 Exercise              10
 Accessing other built-in Functions        11
 Average Function.                         11
 Max and Min Function                      11
 If/Then Function.                         12
 The Count Functions                       12
 PMT Function                              13
 Groceries Exercise                        13
 International Academy Exercise            14
 Cut and copy.                             14
 Click and Drag.                           15
 Dragging Exercise                         15
 Inserting a new Row                       15
 Deleting a row or Column.                 16
 Absolute and Relative Cell references     16
 Personal Income Exercise                  17
 Berry Pickers Exercise                    18
 Centering across Columns                  19
 Word colour and Cell colours.             19
 Vertical Adjustment within rows           19
 Wrapping text within a cell.              20
 Number Formats.                           21
 Deleting Rows and Columns                 21
 Car Expenses Exercise                     21
 Sorting                                   22
 Garage Sale Exercise                      22
Lookup Function                            23
Harry’s Bar Date and Time exercise         24
 Minute                                    24
 Now                                       24
Using Sheets                               25
 Extra Exercises                           27
 Run a macro from a button                 27
 Run a macro from a Toolbar                27
Charting                                   29
George’s Chocolate Sales Exercise          30
 Create a custom number format             30


                                           Page 2
               Overview
               A spreadsheet is similar to a giant table with the borders extending practically to
               infinity. The spreadsheet that appears on your screen will be similar to that below.

                                                   Type in words, formulas or
                                                         numbers here




The menu bar



The toolbars


                                         Letters
                                         count the                                                           Use the
  Numbers                                Columns                                                            scroll bars
  count the                                                                                                  to move
   Rows                                                                                                       around

                                                                                Click on a cell
                                                                                to highlight it




               Moving around
               1. Open up a new spreadsheet and click your cursor in the cell B2, as seen in the
                  following picture.
               2. Note that the cell reference,
                  (B2) is seen in the Left-hand corner.
               3. Type 1 into the cell and press the Enter key

               You can move around the spreadsheet by either clicking on the cell with your
               mouse cursor, or, by using the arrow keys on the keyboard.


             In the cells shown type in the information below                                Numbers will
                                                                                            appear on the Right
                                                                                              hand side of the
                                                                                                    cell

                     Words will appear
                     on the Left-hand
                      side of the cell                                                           This is a
                                                                                              Highlighted cell

               Note that the text is on the left-hand side of the cell and the numbers are on the
               right hand side of the cell.

                                                                                                      Page 3
                    The Drag handle

                    Dragging to copy cells.
                    In the bottom right hand corner of a highlighted cell is a small square. By
                    clicking and holding down the left mouse button you can drag the contents of the
                    cell across other cells and copy them into the new cells.
This is the
Drag Handle           1. First click on the cell B2 where your 1 is, to place the box around the cell.
                      2. Click and hold your Left mouse button down on the drag handle in the
                         bottom right of the cell and drag across to H2, 1‟s will be copied across the
                       page.
                                 3. Highlight the new cells that have been added (C2 to H2) and press

            
        Hold Left                   the Delete key on the keyboard to delete them out again. We
     button down                    should be back to the 1,2,3 again from the first lesson.
          to copy



                    Filling numbers in cells
                    Not only can you copy cells across cells, you can also have numbers added
                    consecutively across cells.

                    To do this the computer needs to know by how much you want the numbers to

     
                    increase. For example do you want them to go: 1,2,3, or 1,3,5 or 1,5,9, (increasing
                    by 4).

                    1. Highlight the 2 and 3 from the first lesson.
                    2. Click and hold with your Left mouse button on the Drag Handle and drag
                       down the page to B11.
                    3. The numbers will increase down the page to 10.
 Click here and     4. Practice using other number series. In C2 type 2 and in C3 type 7. Mark both
 drag down
                       cells and drag down to C11.
                    5. What happens if you use 8 and 3?
                    6. What happens if you drag Up the page
                    7. What happens if you drag Right or Left across the page?




                                                                                                  Page 4
                 Using Fill Down.
               The Edit menu has a range of options that can be used to copy and fill cells as
                 well.
                                                      1. In F2 type in 2, and drag the cells to
                                                         copy down to F12.
                                                         2. Highlight all the cells from F2 to F12
                                                         3. Go: Edit, from the menu bar, then
                                                            click on Fill and then click Series.
                                                         4. In the Step Value window type in 5
                                                            and click OK.
                                             5. New numbers will replace the 2‟s.
                 6. Highlight the numbers again and then use 3 in the Step Value window.
Type the
number
you want
the series
to increase                           A quicker method to make a list of numbers is to click on the
by here                              number with your Right Mouse Button and drag down or
                                     across the spreadsheet. When you let go a menu will appear
                                     and click on the word Fill Series to increase the numbers by 1.
                 Practice until you are happy with the right button .


               Hold Right
              button down
                to increase
                               
                  numbers


                 Not only are numbers copied across cells but some words can be copied as well.
                 Type the words below and then pull them down the sheet using the drag handle.
                 Try also using Qtr 1, and 1st Group




                     Click here and
                     drag down




                                                                                             Page 5
         Personal Expenses Exercise
         1. Open a new spreadsheet.
         2. Type the information below into the cell references shown.

       3. For the words under Month type Jan into A4 and drag down to fill down.
         4. For the numbers under Income type 2120 into B4 and drag down to copy.
         5. For the numbers under Expenditure type 1900 into C4 and drag down to
          copy. Then with the cells still marked go: Edit and Fill and Series, and set the
          Step Value: to 5.
       6. Mark the cells A3 to D3. Click on the Bold, and Centre buttons.
       7. Click on A10, and then click the Bold and Right Buttons.

                                   8. Mark the cells A4 to A9 and click the Right
                                      Button
                                   9. Read the section below to set the cell widths
                                   10.Then save the spreadsheet by going File, and Save
                                      as.
                                   11.In the File Name field type “your name” Personal
                                      Expenses. Eg: John Personal Expenses. We will
                                      use this exercise in later lessons.



       Changing Cell widths
       You will have noticed that some cells in the exercise above are wider than other
       cells. The width of the cells can be set in a number of ways.
 Changing cell widths by dragging
       1. Move your cursor between A and B on the Column row as shown left.
       2. Click and hold down your left mouse button and drag the cell A out to make
          more room in the cell.
       3. What happens if you move the cell boundary in too far and the words cannot fit
          into the cell? (Try and then move the cells back again).
       4. What happens when you move the cells with numbers in too close?

       Changing cell widths automatically
       The cell widths can be set automatically by double clicking on the lines between
       the cells, in the same place as above. The columns then will increase or decrease
       to fit the biggest entry.




                                                                                   Page 6
                Formulas
                Formulas are calculations created on the spreadsheets. Formulas range in
                complexity from the easy ones below to extremely complicated formulas.
                Use the following buttons to create mathematical symbols in the spreadsheet:

    +       to add                                           It is important to note the order of the
    -       to subtract                                      symbols when creating formulas.
    /       to divide                                         ()     Brackets are the first priority
    *       to multiply (to make this hold down the          /       Division is next
            Shift key and press the 8)                       *       Multiplication
    %       to make a percentage (Shift and 5)               +       Addition and finally
    =       to make equals                                   -       Subtraction is the last
                                                                     priority

                Formulas with numbers
                      1. In a new spreadsheet type in the formulas on the left into the formula
                         bar remembering to always include an = sign before the formula.

                      2. Note how the differences in the answers depend on the order of the
           B             symbols
1       =(3+4)/5
2       =3+4/5
3       =3+(4/5)      Formulas with Cell references
4       =(3+4)*5      Up until now we have used numbers in our formulas.
5       =3+4*5        Now however we will practice using cell references instead.
6       =3+(4*5)
7       =(3-4)/5      1. Type in the numbers on the right (they are in
8       =3-4/5           row 2)
                2. Click on cell F2 to give it the focus.
              3. Click into the formula bar and type in the
                   formula right. (You don‟t need to have capital letters in
                   the cell references)                                             Type in here

                4. Click on the Tick, or press Enter. This will place the answer into cell F2.
                5. Using the above steps type the formula =C2+D2/E2 into the cell F3
                6. Using the above steps type the formula =C2+(D2/E2) into the cell F4




                                                                                              Page 7
 Inserting Cell References Automatically
 Instead of typing in C2 each time you want it in the formula try the following:

 1. Click in cell F5
 2. In the formula bar type =

 3. Now point to cell C2 on the spreadsheet and click it once, notice that the cell
    reference C2 will appear in the formula bar.
 4. Click back in the formula bar and type +
 5. Point to, and click on cell D2
 6. Click back on the formula bar and type *
 7. Point to, and click on the cell E2. You should have the formula
     =C2+D2*E2
 8. Click on the Tick to finish the formula,
 9. Save the exercise with “Your name” Formulas

 Personal Expenses Exercise
 1. Open the Spreadsheet “your name” Personal Expenses
 2. Click in the cell D4, under the title Savings.


 3. Type the following formula into the Formula bar: =B4-C4
 4. Click the tick to see the answer in the cell.
 5. Click on the Drag handle in the bottom right of the D4 cell and drag the
    formula down to D9.

 You should now have a table similar to the following.




                                                                             Page 8
          Functions
          A function is a pre set formula used by the spreadsheet to perform calculations.
          The most common function used is the SUM function.

          The Sum Function
          In earlier lessons we learned that we could add cells by writing the formula
          =C2+D2+E2 adding up the cells C2 to E2 or (C1:E2).

          However the computer can accomplish this automatically. By typing = to start the
          formula then SUM and type in the range of the cells you want added together
          inside of brackets. The Sum function will add this selection automatically. So the
          formula we write will look like this: =Sum(C2:E2) The Colon : means To in the
          formula giving the range from the beginning cell to the end cell.

            Open the Personal Expenses exercise
            1. Click on the cell B10



            2. In the formula bar type the following formula
            3. Click the Tick to see the answer
            4. All the cells from B4 to B9 will be automatically added together. Note that
             B4:B9 is the Range of the cells, from the top left to the bottom right.

          The AutoSum button
  The         Instead of typing in the formula it can be automatically generated by using the
AutoSum
 Button
              AutoSum button from the toolbar.

            1. Click in cell C10
          2. Click on the AutoSum button on the toolbar
                                                   3. Note the shading covers the cells that
                                                      will be added together, and that the
                                                      formula appears in the cell box C10
                                                      as well as on the formula bar
                                                   4. Click on the Tick on the formula bar
                                                      and the cells will be automatically
                                                      added up.
                                                   5. Repeat the above to add up the
                                                      Savings column D10.




                                                                                      Page 9
         Personal Expenses 2 Exercise
         1. In a new spreadsheet type in the following information below, including
            June, using the dragging to copy skills we have covered earlier to make the
            table quickly. Read the notes below on creating each area first.

       2. To make the Total Finance fields, use the AutoSum in B10 first. Click on
            B10 then click on AutoSum, the formula will be inserted automatically.
            Then click on the drag button and drag across to copy the cell to G10.
      3. Click on each cell with the formula and note in the formula bar that the
         formulas change to match the columns they are in. These are called Relative
         formulas, because the change relative to the cells they are in.
      4. Use Autosum for the Total Column by clicking in G6 and clicking the
         AutoSum button to add the Credit card Row.
      5. Drag the answer down to G10 to copy the formula
      6. Click on each cell with the Total formula and note in the formula bar that the
         formulas have changed to match the rows they are in
      7. Don‟t forget to create June, as shown on the spreadsheet.
      8. Save the exercise with your name Personal Expenses 2




Type words into B1                      Click here to adjust
make Bold and 14                             cell width
      point




                                                                               Page 10
     Accessing other built-in Functions
     There are a number of methods that can be used to insert functions into your
     spreadsheet. In this section we will examine some of the more common methods
                                                   whilst practicing the new functions.

                                                   To select a function click on the
                                                   Function     button     on    the
                                                   toolbar to open the Function
                                                   window, and then click on the
                                                   function you want to use.

                                                   The functions we will investigate
                                                   should all be found under Most
                                                   Recently Used, however there are
                                                   many more specialist functions
                                                   available to use in the other
                                                   sections.



     Average Function.
     Imagine that you want to know the average amount of money you pay each month
     for your total finance expenses.
     The Average function can answer your question for you.

   1. Using the exercise Personal Expenses 2, in Cell A12 type the heading
        Average Expenses per month
     2. Click In Cell A13 and click on the Function button to open the Function
        window, and click on Average and OK.
     3. The next screen asks for the range of the cells that you want to average.
        Highlight the Total Finance row, without including the total in F11.
     4. Click OK and note that the formula will be entered into A13 as you have
        already given this cell the focus.
     5. Click Finish. Note that the decimal points are not set. Format the answer as
        Currency by clicking the $ Button on the tool bar


     Max and Min Function
     Imagine that you also want to know the Maximum and Minimum amounts of
     money you spend on expenses each month for your total finance expenses


   1. Using the exercise Personal Expenses 2, in Cell A14 type the Heading
        Maximum Expenses per month
     2. Click In Cell A15 and using the Function window choose Max and click OK.


                                                                               Page 11
 To find the minimum amount spent each month

 1. In Cell A16 type the Heading Minimum Expenses per month


 2. Click In Cell A17 and using the Function window choose Min and click OK.


 If/Then Function.
 Using this function you can set a one cell to show different values, such as words
 or numbers, if another cell is true or false. The function uses three parts, If
 something happens, Then do something, Otherwise do something else.

 For example if the Total finance spent for six months is greater than 5351, you
 can make a warning appear that reads Warning! Over Budget otherwise it would
 say Under Budget.

 This would be written as:
 IF(G10>5351, “Warning! Over Budget”, “OK, Under Budget”)


     If G10 is      Then show ”Warning!       Otherwise show “OK,
     greater than   Over Budget”              Under Budget”
     5351


 In English, the above function says if G10 is greater than 5351, then put Warning!
 Over Budget, otherwise put OK, Under Budget. This function can be great fun
 and is worth learning to use. So gather your courage and do the following:

                                                     1. Using      the    exercise
                                                        Personal Expenses 2,
                                                        click in A18 and using the
                                                        Function window choose
                                                        IF and click OK
                                                     2. Type in the 3 fields as
                                                        shown left, remembering
                                                        to include the “ ” marks
                                                        around the words.
 3. Click OK
  Note that quote marks are only needed with text, if you replace the words with
 numbers, there is no need to use the quote marks.

 Change some cells to see if it works. Try the If statement using other words,
 numbers and fields.

 The Count Functions
 These simple functions count the number of cells highlighted.


 Using the Function Window, find and use the Count function to count how
 many number cells (cells containing numbers) are used in the Personal Expenses
 spreadsheet.

                                                                           Page 12
Count Blank, and Count If, are two other functions that are also very useful.
Count Blank, will count the empty cells, and Count If will count cells that match a
set criteria.

PMT Function
Excel has a built-in function for computing loan payments called PMT. The
                                 different fields to complete are:
                                 Rate – the interest rate for the period in which
                                 you're interested, divided by 12 months – for
                                 example one year at 10%, is 10% divided by 12 or
                                 .0083 for Rate.
                                 Nper - the number of periods you have to pay.
                                 (For example, if you get a four-year car loan and
                                 make monthly payments, your loan has 4x12 (or
                                 48) periods. You would enter 48 into the formula
                                 for Nper.
Pv - the present value or amount of the loan, - how much the loan is taken out
for.
Fv - the future value of the loan (this will be 0 if you intend to pay the loan off),
Type and the type of payment to be made (this is 0 if your payments begin at the
end of the month you get the loan; 1 if the payments begin immediately when you
get the loan -- 0 is the most common option here).

An exercise using this function will come on another sheet.


Groceries Exercise
                            Using the formulas we have covered above create and
                            complete the Groceries Bill shown left.

                            The formulas we will use are:
                            Sum, Max, Min, and Average.

                            Save with “your name” and Groceries.




                                                                            Page 13
      International Academy Exercise
      Using the formulas we have covered above create and complete the International
      Academy spreadsheet shown below.

      The formulas we will use are:
      Sum, Max, Min, and Average.

      Save with “your name” and Academy.




      Moving Data on the Spreadsheet.
      One of the necessary tools in using a spreadsheet is being able to move data
      around your spreadsheet. The most obvious method is to delete out the data you
      don‟t want in one place and type the new data into the place you want it. However
      that method is long and tedious.

      Here are some more simpler and easier techniques for moving data


 Cut and copy.
      1. If you don‟t already have it open, then open Academy
      2. Mark the Name column including all the names, from A2 to A16.
      3. Click on the Cut button
      4. Move to J2 and click in the cell, to give it the focus.

                                                                               Page 14
     5. Click the Paste button to paste the selection


     Click and Drag.
     1. Mark the Name column from J2 to J16 again.
     2. Move your cursor to the edge, or boundary of the highlighted area.
     3. Click and hold down the left mouse button and drag the cells back into their
        original place.

     Dragging Exercise
     On a new spreadsheet, create the table below anywhere. Using cut and paste, and
     Drag, rearrange all the years in consecutive order from 1995 to 1999.




     Inserting Rows and Columns.
     As well as being able to move cells around the spreadsheet it can be important to
     learn how to add extra cells into an existing table or spreadsheet.

     For this exercise we are going to create a place to move the June column into the
     Personal Expenses 2 table.

     Inserting a new Column
     1. Open the Personal Expenses 2 exercise. Click in the Column G marker to

      highlight the entire G column.
     2. Right click your mouse button and choose Insert.
     3. A new column will be inserted, moving the Total column over one .
     4. Highlight and drag the June Column into the space created in the new G
        column.
     5. Note that the formulas for the Total column have automatically added in the
        new information.


     Inserting a new Row
     1. Open the Personal Expenses 2 exercise.

   2. Insert a row by clicking on the Row 9 marker
                                                                              Page 15
                  3. Right click your mouse button and choose Insert.
                  4. Add in the following information and complete the table.
                  5. Hire Purchase with payments of $25 every month.


                  Deleting a row or Column.
                  1. Click the marker for the Row or column to highlight the entire row or column
                  2. Right click your mouse button and choose Delete.



                  Absolute and Relative Cell references
                  In some situations you will not want the cell reference to change when copying
                  formulas across cells.

                  Below is an example where multiplying the Gross wages by the Tax, gives the
                  Tax Paid. Here if you drag the formula from C7 to H7 the equation will not
                  operate, as the tax cell B4 will also change. To prevent the formula changing the

                cell B4 must be „locked’ into place – to prevent it changing when the formula is
                  dragged.

                  1. Type in the Spreadsheet below onto a new page and save it as „Your name’
                     Personal Income

Use 14pt and                                                                                     Use
    bold                                                                                       AutoSum



Type 25% using
the keyboard %
    (shift 5)




                  2. Click on C7 and in the formula bar type
                     the following: =C6*B4
                  3. Click the Tick and drag the C7 cell to H7 to copy the formula across.
It is important   Note that the formula operates only in C7 but not in any other cell. In the other
that you          cells the B4 in the formula has changed to C4, or D4, or E4 etc all of which are
understand the    empty cells. These are Relative Cell references, as they change relative to the
difference        cell.
between
Relative and
                  To lock the cell reference B4 into the formula type a $ before the cell reference,
Absolute Cell
References
                  so in the C7 formula we will replace B4 with $B$4. This creates an Absolute Cell
                  Reference, a cell reference that will not change.

                  1. In C7 type in the $ before the B and 4 and
                     drag over the old formula to copy to H7.
                  2. Use Auto Sum to total the Tax Paid range.

                                                                                             Page 16
     Personal Income Exercise
     Our client makes candles as extra part time income, and as well as tax he pays
     GST on the candles.
     Using the Personal Income spreadsheet above modify the spreadsheet to add the
     following below.

     You will need to write a simple formula to calculate the Candles Net Income.
     This formula will subtract the Tax and GST from the Gross wages. How will you
     do it? Another formula is needed to achieve the total Net income in row 15.

   Use Auto Sum for the Total Column.




     Some cells in the I column in particular may change to look like #####. This
     means that the numbers are too big for the cell, and they cannot be viewed in the
     cell. To fix this click and hold your left mouse button down on the line between
     the Column letters and drag the line to the right to widen the cell, or double click
     on the cell border line.




                                                                                 Page 17
 Berry Pickers Exercise

 1. Create the spreadsheet below. Both are on one page.


 2. Use a formula to calculate the hours.
 3. Using an Absolute cell reference create a formula in B22 for Fred that
    multiplies the Hourly pay rate by the Hours he worked on Monday (B6).
 4. Copy the formula right across the page to G20.
                                                          5. Copy the range from
                                                             B20 to G20 down the
                                                             page to fill the
                                                             spreadsheet.
                                                          6. Use a formula to
                                                             calculate the Pay
                                                             received by each
                                                             person.
                                                          7. Check your answer is
                                                             the same as below and
                                                             save as “Your Name”
                                                             Berry Pickers




                                                                          Page 18
     Formatting Cells
     There are many presentation techniques that can be used on your spreadsheet to
     make it easy on the eye.


   Some of these are the following, be sure to experiment with the techniques used
     and investigate any others we have not covered in this section.

     Type in the following spreadsheet outlining our clients car expenses and save as
     “Your Name” Car Expenses.




     Centering across Columns
     Instead of having the heading Auto Expenses on the left of the Spreadsheet it can
     be centred across the spreadsheet..

     1. Mark the cells B4 to H4, so that they are highlighted.
     2. Click on the Merge and Centre button. .
     3. Click the Bold button to make bold.

   4. Note that although the heading is in the middle of the selection, the words are
        still in the cell B4, not in any other cell in the heading line. Click on C4 and
        D4, to see what I mean.

     Word colour and Cell colours.
     It is easy to change the colours of the words or the cells.
     1. First highlight the cell you want to change.
     2. Then either click on the colours shown on the buttons, to put
        that colour into the cell, or choose another colour by clicking
        on the small down arrow and choosing a new colour.

     Vertical Adjustment within rows
     We can also stretch out rows and set the text within the row to the Top, Centre or
     bottom, of the Row.

     1. Move your mouse arrow over the line under row 4, as shown left, and click and
        drag down to extend the height of the row.



                                                                                Page 19
                                                  2. Click in row marker 4 to highlight
                                                     the row, and click your Right
                                                     mouse button. From the menu
                                                     click on Format cells.


                                                  3. Click on the Alignment Tab and
                                                     then under the Vertical section
                                                     choose Centre.
                                                  4. Click again on the Alignment tab,
                                                     and then click Centre from the
                                                     selection under Vertical and then
                                                     OK.


     Note that the heading is now centred within the row, and centred across the
     selection of the columns.




   Using the Font window in the same Format cells screen, make the font size of
     the heading 14, and the font Times New Roman.

     Wrapping text within a cell.
     Words that go over the edge of the cell border will still be seen in the table if the
     next cell is empty, as in the Unexpected Expenses heading in our example above.
     But if there is something in the next cell the information will seem to disappear.
     We can however make the words „wrap’ within the cell, that is start another line
     to fit into the cell boundaries. It‟s very useful to know, and here‟s how we do it.

           1. Click on C5 and in the formula bar add the words Vic and click the tick.
              Note that Vic is not shown on the Spreadsheet.

   2. Open the Format Cells Window and again click on the Alignment tab. Click
        on Wrap Text, and click OK.
     3. Using Wrap Text, wrap the words Unexpected Expenses so they are in the
        same cell.
     4. Use the Centre button (left) from the toolbar to centre the text within the cell.




                                                                                  Page 20
     Number Formats.
                                      The numbers themselves can be formatted as
                                      either, general, currency, percentage etc, using
                                      the same Format Cells window.

                                      1. Mark the cells C6 to G6 containing the
                                         numbers. Open the Format Cells window,
                                         click on the Number tab and then click
                                         Currency.
                                      2. Click in the Negative numbers in Red field
                                         to show deficits in red. Note that you can set
                                         the decimal places if wished (however they
                                         are set correctly for currency already)
                                      3. Investigate the other formats available,
                                         Currency, Percentage and General are the
                                         most common ones, and we will use others
                                         later in the manual.


     Deleting Rows and Columns
     To delete an entire row or column click on the Row number or the Column
     letter to highlight the entire row or column. Then with your mouse arrow in the
     shaded area, click your right mouse button to show the menu and click Delete.
     Using the Car Expenses exercise delete out Column A by the following:
     1. Click on the A Column Letter.
     2. Put your arrow into the shaded area and click your right mouse button.
     3. Choose Delete from the menu.


     Car Expenses Exercise
     Combining all we have learnt so far create the spreadsheet below using the Car
     expenses spreadsheet as the basis for your new sheet. This spreadsheet breaks

   down the yearly expenses into monthly and weekly totals so our client can set
     aside a budget for the car. All the new fields can be dragged to save time.

                                                                  In B7 create a
                                                                  formula to divide
                                                                  the Registration by
                                                                  the 12 months of
                                                                  the year.


     In B8 divide B6 by 52 weeks. Drag both across to the G column.
      Set the cells black and the letters white where shown.
      Mark the entire table and use the Border options in the Format Cells window
       to set the double line around the table.
                                                                               Page 21
Sorting
Sorting data on a sheet is very easy to do. Click on the first cell of the column you
want sorted and click on the Sort Ascending, or Sort Descending, buttons on the
toolbar.

Multiple columns can be sorted at once. Highlight the columns you want to sort
and then go: Data, from the Menu, and click on Sort. Choose the fields you want
sorted.


Garage Sale Exercise
                                   1. Create the Garage Sale spreadsheet
                                   2. Widen the column to best fit the items
                                   3. Format the numbers to Currency using
                                      Format Cells.
                                   4. Using formulas do the following:
                                   5. Calculate the total
                                   6. Calculate the cheapest
                                   7. Calculate the most expensive
                                   8. Calculate the number of items.
                                   9. Click on cell A2 containing Ladder. Click on
                                      the Sort Ascending button     to sort.
10.Sort the money in ascending order.
11.Save as “Your Name” Garage sale




                                                                            Page 22
Lookup Function
Imagine you want to type a number or word into your spreadsheet and
immediately have a related number or word available.
Using Excel you can look up a table containing the information you want and call
down another value in that table.

For example:
George needs to know how many days there are in each month so he can calculate
his income as he works out his spreadsheet. To achieve this he has made two
columns containing the months of the year, and how many days in the month.

           Do the following:
           1. In cell A1 type in Jan and drag down until you have the first six
              months.
           2. In cell B1 type in the number of days for each month.
                  3. Now for this function to operate you must sort the months
                     alphabetically so click on cell A1 and click the Sort button
   (The first column must always be sorted). Note that the second column also
               changes to stay the same as the first once they are sorted.
          4. Below the Monthly table, create the spreadsheet shown




5. In B11 we are going to place the Look Up formula that will read the month
   from B10 (Jan), then look up the table (A1:A6) and display the days
   from (B1:B6).
                                                           6. Click in B11 and
                                                              find the Look Up
                                                              Function using the
                                                              Function Button.
                                                         7. Type in the values
                                                             shown in the three
                                                             windows. We are
                                                             going to look up
                                                             B10, read the
                                                             month in that cell,
                                                             then find the
   month in the range A1:A6. The day will be found in the range B1:B6.
8. Click OK if the Formula result = 31, otherwise check your input and correct.


                                                                          Page 23
               9. If you drag the formula from B11 to G11 the values will change as the cell
                  references are Relative. You must make them all Absolute except for B10 .
                  How will you do that?
               10. Create the Daily Income fields as well and save as Look-Up exercise.


     Harry’s Bar Date and Time exercise
               Harry‟s Bar needs a timesheet to record the hours of its casual workers.
               Create the spreadsheet below using the following functions.
               Hour
               Minute
               Now
               All functions are opened with the Function Button.

                                                E2




Format cells B5 to C10 as time.
                                                         Format cell E2 as below




                                                                                          Page 24
        Using Sheets
        In Excel there are a number of sheets, like pages, that you can use at once. By
        clicking on a Sheet Tab you can move between the sheets, and easily rename,
        add, or delete sheets.


        In this exercise we are going to use the sheets to create a multi-sheet expenses
        book.

              1. We need a total of 4 worksheets in our workbook, so to add another
                 sheet to the Workbook Right Click on a Sheet tab and choose Insert,
                 click on Worksheet, and click OK. A new sheet will be added called
                 Sheet 4. (Note that if you have an earlier version of Excel there will
                 already be sufficient sheets visible).

             2. Then we must rename the sheets to be used. Click with your Right
           Mouse button on Sheet1. Click on Rename and call the sheet Vehicle
           Expenses.
        3. Rename Sheet2 to Van 1, Sheet 3 to Van 2, and Sheet 4 to Car 1.

                        4. Reorder the sheets so Vehicle Expenses is first by Clicking
                           on the Vehicle Expenses tab and holding down the Left
                           Mouse button. Drag the Vehicle Expenses tab to the left to
                           move the tab.

        5. On Car 1 sheet, create the spreadsheet below. Use a formula for Total, and
           Cost per km.
        6. Copy the spreadsheet to Van 1 and Van 2 sheets and change the numbers as
           shown.

Car 1                       Van 1                               Van 2




        7. On the Vehicle Expenses sheet below, create the spreadsheet shown.
        8. Click in the cell B9 to make the formula. Type = in the formula bar then click
           on Car 1 sheet and click on D4 and click the tick to complete the formula.
           The formula will look like the one below and the cell will have 100,000 in it.



                                                                                 Page 25
9. Complete the fields copying from the Van and Car sheets back to the main
    sheet.
10. Complete the Total fields with formulas.

                                                  11. Using this system individual
                                                      records can be maintained
                                                      for each vehicle. Change
                                                      some of the numbers for the
                                                      vehicles on their individual
                                                      sheets and note the changes
                                                      to the main sheet.


Save as Sheets exercise


Macro’s: Automating tasks
A macro is a series of commands and functions can be run whenever you need to
perform a task. You record then run the macro to repeat, or "play back," the
commands. Before recording a macro, plan the steps and commands you want the
macro to perform. If you make a mistake when you record the macro the
corrections you make will also be recorded.

         In this exercise we are going to make a simple macro that moves a
         column of numbers across 2 columns and then a second macro to return
         the column back to the original position.

         1. Open the Spreadsheet Macro, or type in the information left on a
            new spreadsheet.
                          2. On the Tools menu, point to Macro, and then click
                             Record.
                          3. In the Macro name box, enter the name Move for the
                             macro.
                          4. To run the macro with a keyboard-shortcut key, enter
                             the letter (m) in the Shortcut key box and complete
                             the description window.
5. Click OK, the macro will begin to record.
6. The small window that appears shows the Stop button and the button that
       changes the macro between absolute and relative cell references. The
       macro will automatically select the same cell reference every time it‟s
       played back regardless of which cell is highlighted when the macro is run
       because it records absolute cell references. If you want a macro to select
       cells in relation to the position of the highlighted cell when you run the
   macro, press the button to record relative cell references.




                                                                          Page 26
             7. Highlight the column C3 to C9 right click your mouse button
                and choose copy.
             8. Right click in E3 and choose Paste Special and click Skip
                blanks. Skip blanks will prevent blank cells from being copied
                over cells with characters in them. This is a good safety tool to
                prevent accidentally erasing your data.
             9. Now that you have 2 columns of data, go back to the C3 to C9
                column and delete out the data
             10.Press the Stop button, you have created your first macro.
11.Return the Column E numbers back to Column C and press CTRL m to run
   your macro.



Extra Exercises
 Run the macro again to move the data to column E, and then create a second
  macro, called Return to return the column back to the original position.
 Using the Add operation under Paste Special, create a macro that adds the
  numbers in column C to a new column in F. Each time you run this macro the
  numbers in F will increase by the numbers in E.


Run a macro from a button
You can easily assign a macro to a button on your worksheet. When you click the
button your macro will run automatically. We will use the macro‟s we have
already created in this exercise.
1. Open the Forms Toolbar by going View / Toolbars / Forms.
2. Click on the Button symbol    and drag a button onto your spreadsheet.
3. In the Assign Macro window click on the macro you want the button to run, in
   this case the Move macro and then click OK.
4. Resizing and moving the button is easiest if you click on the Select Objects
       arrow on the Drawing toolbar before attempting to work with the button.
   This will prevent you accidentally starting the macro.
5. With the Select Objects      button on, double click on the button to change
   the Font, Format and Colour of the text.
6. Create a button for both the Move and the Return Macro‟s and size them
   together in a suitable place.


                  Run a macro from a Toolbar
                  1. It is also easy to attach a macro to a toolbar button where
                     you can easily use it.
                  2. Go: View / toolbar / Customise to open the Customise
                     window
                                                                         Page 27
                      3. Click on the Macro category and drag the custom
                         button onto the toolbar to where you want your button
            to be.
4. 4 Right-click the button, and then click Assign Macro on the shortcut
   menu.
   5. In the Macro name box, enter the name of the macro.




   Once created the button can be edited to meet your needs. Use Change
                    Button Image to select another picture, or more
                    creatively use Edit Button Image to edit the picture on
                    the button.

                     These features are available for any button whenever the
                     Customise window is open.




                                                                      Page 28
Charting
                                One of the most important and enjoyable tools
                                in Excel is the charting tool. There are a
                                myriad variety of charts available, we will
                                practice making a simple chart then use more
                                advanced methods later.
                                1. Open the File Charting from S:Artfiles /
                                   Computer Exercise / Excel.
                                2. The table below will be found under
                                   Exercise 1 (If you don‟t have access to the
                                   table type it in.)
                                3. Highlight the Arrivals and Departures
                                   columns (B3 toC14) including both
                                   headings

                         4.     Click on the Chart button then click on Line,
                                     and click on Next.
                                       5. When you reach Step Two, click
                                          on the Series Tab.
                                       6. Then click your cursor in the
                                             Category (X) axis labels
                                                     window as at left.
                                                    7. Go back to your table
                                                       and then highlight the
                                                       Years column, from
                                                       A4 to A14, without
                                                       the Year heading.
                                                    8. Click Next for Step
                                                       3.    Complete the
                                                       Chart           Title,
                                                       Category, and Value
                                                       fields as shown at the
                                                       left.
                                                    9. In Step 4 have the
                                                       chart on a new sheet.
                                                   10. Double click in the
                                                   middle of your new chart
                                                   to open the Format Plot
                                                   Area Window. Click on
                                                   None for area, to remove
 the background color.



              11.        Click on the Drawing button to open the drawing
 toolbar.
                                                                      Page 29
   12.      Use a text box to make a place in the bottom right of your chart with your
         name in it.

George’s Chocolate Sales Exercise
   Create the Chocolate sales figures in the table below.
   Use the manual if you are unsure of the following skills needed::
   Wrapping text in a cell – for cells A2, H2, A9.
   Vertical Adjustment within rows – for cells B2 to G2 (set the vertical
   adjustment to Top)

   Format all money to currency format. (Number Formats section in Manual)
   Calculate the monthly total by entering a formula into B9 to sum column B. Drag
   this formula to G9.

   The commission paid to George is 15% of the Monthly total. Using Absolute and
   Relative Cell references create a formula to show George‟s Income in cells B13
   to H13.

   George‟s Average daily Gross Income is the amount of income he receives
   divided by the number of days in each month.




   Create a custom number format
   1. Select the cells you want to format.
   2. On the Format menu, click Cells, and then click the Number tab.
   3. In the Category box, click a category, and then select a built-in format that
      resembles the one you want.

                                                                               Page 30
4. In the Category box, click Custom.
5. In the Type box, edit the number format codes to create the format you want.
   Editing a built-in format does not remove it.




                                                                        Page 31

								
To top