Charts Exercise by aZQK8Zp

VIEWS: 11 PAGES: 30

									Excel 2007
Function                   Tab or           Group             Button or Dialog Box Launcher   Page
                           Office Button

AutoFill                                                                                      4

Calendars                  Office Button    New               Templates                       16

Center a Title                                                                                5

Charts                     Insert           Charts            Chart Options                   17

Date Format                Home                                                               7

Drop-Down Box              Data             Data Tools        Data Validation                 26

File Save/Open Location    Office Button    Excel Options     Save, Browse for U: Drive

Filters                    Home             Styles            Format as Table                 9

Formulas                   Formula Bar                                                        14

Freeze Panes               View             Window            Freeze Pane button              13

Gridlines                  Page Layout      Sheet                                             12

Headers/Footers            Page Layout Option                 Header Area                     13

Insert or Delete Rows& Columns                                                                4

Mouse Pointer Options & Right-click                                                           4

Number Format              Home             Number                                            6

Print                      Home                                                               11

Print Headings             Page Layout                                                        12

Protect Elements           Review           After unlocking cells                             23

Save for others            Office Button    Save, Save As                                     2

Sheets                                                                                        24

Sort                       Home             Editing           Sort                            21

Subtotals                  Data             Outline           Subtotal                        22

Tabs, Ribbons Overview                                                                        2

Templates                  Office Button    New Option        Calendars, etc.                 16

Text Format                Home             Font                                              5

Widen Column                                                                                  8

Word Merge                                                                                    28

Wrap Text                  Home Tab         Alignment         Wrap Text       Button          7


Office 2007 Excel                                                                             Page 1
     An Excel document is called a workbook. The grid or screen where you enter data is called the worksheet. A
     new workbook displays multiple sheets that are named Sheet1, Sheet2, etc. at the bottom of the screen. You
     can rename the sheets at the bottom by clicking with the right mouse button and selecting Rename. A
     worksheet consists of a grid of columns (up/down, named by letters of the alphabet) and rows (across, named
     by numbers). The rectangle where a column and row intersect is called a cell. The name of the cell is the
     column letter and the row number. The zoom button for viewing the worksheet (spreadsheet) is in the
     right/bottom corner of the window.




     Tabs and Ribbons                                                                                   Help

              Office Button
                                                                                       Tabs

                                                                                                         Ribbons

                                                                                                         Groups
Active Cell                      Edit Bar
                                                           Dialog Box Launcher (Office 2003)

                   Rows          Columns




                                                               Quick Access Toolbar




                                                                You can add more
                                                                command by
                                                                selecting the option
                                                                button to the right
                                                                of the toolbar and
                                                                clicking beside the
                                                                commands that you
                                                                want




       Ribbons       Context Oriented – a ribbon changes according to the Word feature selected


     Office 2007 Excel                                                                                      Page 2
Example: If you click on a chart that you have created, an option for a Chart Ribbon appears at the top and the
following Ribbon appears with the Chart Tools option




Major Tab Ribbons




Saving Your File for Others
If you are sharing your files with someone who used Excel 2003, you must save as a type that they can open.
File, Save As and save the file down to Excel 97-2003.
                                                                  PDF - Exercise
                                                                  1. Office Button
                                                                  2. New
                                                                  3. Installed Templates
                                                                  4. Sales Report
                                                                  5. Create
                                                                  6. Office Button
                                                                  7. Save As
                                                                  8. PDF file




View, Zoom                                                   Bottom/right of the screen

 Mini-Toolbar                                          Change the Default Directory to Your U: Drive
                                                       Click the Office Button and Excel Options
When you right-
click in a cell or a
series of                                              Select Save on the left of the window and type U:\ in
highlighted cells                                      the Default File Location box
you get a menu
and a Mini-Toolbar
of options




                                                       Click OK



Office 2007 Excel                                                                                         Page 3
Mouse Pointer




                    Select and Entire                                                         Select A Cell – Use
                    Column – Use for                                                          this for highlighting
                    formatting an                                                             more than 1 cell
                                          Select an Entire Row – Use
                    entire column
                                         for formatting an entire row

                      Place in the                           When you                                 Place
                      square of                              drag and                                 between
                      highlighted cell                       then let go,                             column
                      and drag for                           the cells fill                           headings
                      Autofill                               in with                                  and double-
                                                             more of the                              click to
                                                             same data                                widen
Auto Fill
Numbering, Dates, anything with numbers,              Same day, next month for consecutive months
make 2 entries and highlight before auto fill          Highlight column, right-click column letter heading,
 Type a 1 in cell A!                                    format cells, format for Date, select the format you
 Type a 2 in cell B1                                    wish to use to enter your dates
 Drag to highlight both cells
 Notice the small square in the right corner of
   the cursor


                                                         Type two dates and highlight both cells
                                                         Place cursor in bottom/right corner to get a Plus cursor
                                                          and drag down
   Drag that square to cell A12 and release the
    mouse
   The numbers will fill in through 12.

Copy a Formula from one column to the next




                                    Type the formula in a cell, enter, place your cursor on the auto-fill square
of that cell and drag across the columns

Insert Rows and Columns
Column - Right-click on the heading of a column and        Row - Right-click on the heading of a row and select
select Insert                                              Insert




                                                                                        A new row is inserted
                    A new column is inserted before the    before the selected column
selected column
Office 2007 Excel                                                                                           Page 4
Center a Title
                                                                           1. Type title in A1
                                                                           2. Click in A1 (fat cross) and drag
                                                                              to the right to highlight A1
                                                                              thru D1
                                                                           3. Right-click in A1
                                                                           4. Select Format Cells
                                                                           5. Click the Alignment Tab
                                                                           6. In the Horizontal box select
                                                                              Center Across Selection

Exercise For Data Entry and Formatting Cells

                                                           NEVER type a comma or dollar
                                                           sign when you type numbers
                                                           into a cell.

                                                           Format the column for commas
                                                           and dollar signs.

Formatting Cells
Use the right mouse button for a menu – Highlight the cell, entire row or entire column and tap the right-mouse
button while hovering over the selection, select Format Cells

                                           Highlight multiple cells, columns or rows to format all that is
                                           highlighted or to widen more than 1 column at a time




Text - Wrap Text Home Tab, alignment, Wrap Text Button
    1. Select the cell (cells by highlighting the entire row, column or drag for selected cells)
    2. Click the Home Tab and select the options in the Font and Alignment




        Group
Dates
Excel knows that you are entering a date when you place a forward slash between the



month/day/year


Office 2007 Excel                                                                                         Page 5
Numbers

Never type a dollar sign, comma, percent sign, etc. when typing data
Use a period only there are parts of an increment (cents in a dollar format)

1. Select the cell, column or row
2. Click the Dialog Box Launch in the Number Group




Decimal Places

    Under the Number Tab select Number in the Category list




        Select the number of decimal places you want (in this example I leave it at 2)

Commas in Numbers

3. For commas place a check in the Use 1000 Separator box




Formatting Selections and Examples

Two Decimal Places

Currency

Accounting

Percentage

Office 2007 Excel                                                                        Page 6
Format Example:

Data Entered




Data Formatted




1. Dates were entered with a back slash and the numbers were entered as pure numbers. A period was used
   in C3 to denote cents in the dollar amount

2. Row 1 was highlighted, on the Home Tab, blue was selected as the color and bold




   text

3. Cells B1 and C1 were selected and aligned right




4. Column C was selected, the Dialog Box Launcher was clicked in the Number Group, the Number Tab was
   selected with 2 decimal places and commas




Office 2007 Excel                                                                                  Page 7
Widen a Column

                       To widen a column for the exact width required, place the pointer on the line between
                       the columns and double-click

                       To widen a column for a desired width, place the pointer on the line between the
                       columns and drag the line to the right or left

If you get characters in your column that look like the following your column is not wide enough. Double-click
between the column headings




Easy Formatting with Automatic Filters

1. Highlight all data
2. On the Home Tab, Styles Group select Format as Table




3. Select a style
4. Click OK at the format window




    Note: If the column headings are cut off, highlight the cells and on the Home button align Center




Office 2007 Excel                                                                                         Page 8
Filters




If you wish to know all customers who purchased on 7/16/2009

1. Click the Filter Button at the top of the Date column
2. The following window appears                          3. Take the check out of Select All and select July 16
                                                            Click OK




4. Only those customer records appear:




5. To view all records, click the Filter Button and click Select All, OK




Office 2007 Excel                                                                                         Page 9
   Conditional Formatting
1. Highlight Specific Data for Reports
2. Highlight the data in the spreadsheet and on the Home Tab select Conditional Formatting




Customers buying less than $1,000

Highlight Cells Rules, Less Than, type 1000.00, pick the highlight color you want, click OK




Results




Clear Rules – Select Conditional Formatting, select Clear Rules




Office 2007 Excel                                                                             Page 10
Print

Visual Aids

Add the Print Preview button to the Quick Access Toolbar




Another feature that will assist in planning the printing is the Page Layout Option at the bottom/right corner of
the screen.




You can see what will print on the page and easily type a header for your spreadsheet




Print Area
1. Set a print area
        a. On the worksheet, select the cells that you want to define as the print area.
        b. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print
           Area. Note The print area that you set is saved when you save the workbook.
2. Add cells to an existing print area
        a. On the worksheet, select the cells that you want to add to the existing print area.
        b. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Add to Print
           Area.
3. Clear a print area
        a. Click anywhere on the worksheet for which you want to clear the print area.
        b. On the Page Layout tab, in the Page Setup group, click Clear Print Area.




Office 2007 Excel                                                                                         Page 11
Page Layout Tab

You can use the commands on the ribbon for margins, orientation, etc. or you can click the Dialog Box Launcher
if you are used to using the 2003 commands.




Print Gridlines

The default for gridlines is to see them while working but it is not the default for printing. On the Page Layout
Tab, Sheet Options Group, place a check beside Print under Gridlines (see illustration above in red circle)

Print Column Headings on Every Page

1. On the Page Layout Tab, click the Print Titles icon
2. Click the option button to the right of the dialog box for Rows to repeat at top:




3. Select the row header for the row you wish to repeat


4. Enter
5. Click OK
6. The commands is now in the dialog box



Office 2007 Excel                                                                                          Page 12
Headers and Footers

Select Page Layout Option at the bottom/right corner of the screen.




Click inside the header area and a Toolbar Ribbon appears at the top of your screen. Insert the commands that
you wish to display in your header or footer.




Page Orientation, Margins




In the Page Layout Option (See instructions above) you see immediately printing changes as you change to
Landscape or change your margins.

Freeze Panes to View Column/Row Titles as You Work

You must be in Normal mode


To freeze the top horizontal pane, select the row below where you want the split to appear. To freeze the left
vertical pane, select the column to the right of where you want the split to appear.

Under the View Tab, Windows Group, select the Freeze Panes option button




Office 2007 Excel                                                                                       Page 13
Formulas, Equation, Calculations - Never type a dollar sign, comma, percent sign, etc. when typing data.
Use a period only there are parts of an increment (cents in a dollar format).

Each formula begins with an = (equal sign)
   To add the sum of cells B6 through B10, you can enter either of these formulas:
   (a)                          and Enter
    (b)                           and Enter The period means the first cell thru the last cell.

+                   Add                                       =a3+a4+a5+a6+a7+a8
SUM( )              Grouping Addition                         =sum(a3.a8)
-                   Subtract                                  =a3-a4
*                   Multiply                                  =a3*a4
/                   Divide                                    =a3/a4
AVERAGE( )          Average of the cell range                 =average (a3.a8)
COUNT( )            How many in the cell range                =count (a3.a8)
MIN( )              Smallest amount in the cell range         =min (a3.a8)
MAX( )              Largest amount in the cell range          =max (a3.a8)
                    Format cells to Percentage                =a3/a4 changed to percent, Highlight cells,
Percentage
                                                              Home Tab, Number Group

AutoSum
 Click in the cell below a series of numbers with no empty cells dividing them, click the Formulas Tab,
click the AutoSum options button, make your selection and press enter.




Formula Bar and Operators
                         Every formula begins with an equal sign.
Notice the difference:
When I type 3+3 and enter the result in the cell is still 3+3; when I type =3+3 and enter the result is 6.
                         Erase Enter




As I type the =, SUM appears and options to erase what I have type or enter appear as illustrated above.



                 - = subtract                   / is divide                   * is multiply
Office 2007 Excel                                                                                           Page 14
Combining Operations Parenthesis always comes first

Exercise:                                        Mr. Brown's and Mrs. Wilson's classes sold M&Ms.
                                                 Each class could keep the top 3 student amounts
                                                 for their classroom. This was not only a fundraiser
                                                 for the school but an exercise in cooperation.
                                                 They were to add the money for each of their
                                                 totals and divide the money by 2 to calculate the
                                                 amount of money that each class would received.
                                                 How much was that?
    1. Type the text in Column A
    2. Enlarge Column A to fit the text in that column
    3. Format Cells B1, A7, A13, A15 Bold Text
    4. Format the cells in Column B as a Number with 2 decimal places and type the amounts for each student
       in Column B
    5. In Cell B7 type the formula for Mr. Brown's class total using Cell Names
    6. In Cell B13 type the formula for Mrs. Wilson's total using Cell Names
    7. In Cell B15 type the formula for the money received by each class when divided by 2



Display formulas: Press CTRL + ` (grave accent – key on left of keyboard beneath the esc key)




Office 2007 Excel                                                                                      Page 15
Templates

Click the Office Button and select New …. many templates are made available that are installed and online


              Select the option for Installed Templates, Personal Monthly Budget and click Create

This is a great example of how formatting and formulas are used.        Formatting Example Below

                                                   1.                                                   Select the
                                                      cell E7 and see that only clean numbers were enter and
                                                      the cell is formatted for currency.
                                                   2. Notice that portions of the spreadsheet were highlighted
                                                      and formatted to give the unique appearance and
                                                      automatic filters as explained on page 11.
                                                   3. Click on the cell J58 to see how parenthesis is used for
                                                      adding many cells with the + sign

Calendars – Select 2008 calendar
When you find the one you want, select it and click the Download button




                                    One sheet for the entire            Each month on a different sheet in the
                                    Workbook                                        Workbook




Calculate Age of Students

1. Format Column C for Number with 2 decimal places

2. Type the child’s birthday in Column A

3. Use the Control+semi-colon for B for the current date

4. Type the formula to calculate age in Column C




Office 2007 Excel                                                                                        Page 16
Charts




                                                  Chart Dialog Box Launcher




Exercise

1. Click the Office Button, New, Installed Templates, Sales Report and click the Create button




2. Click in cell A1 and drag the mouse to highlight all cells from B1 through F14




3. Select the Insert Tab, Charts Group, Column


Office 2007 Excel                                                                                Page 17
4. Select the first 2-D Column Chart and your chart appears




   Move and Modify the Chart – Use the Chart Tools Tab
5. Select the Chart Tools Tab




6. Select Move Chart
7. Click New Sheet and name it Alice Mutton, click OK




8. Your chart is moved to a sheet of its own entitled Alice Mutton


9. Select Chart Tools again at the top of the screen
10. Chart Layout Group can add information




                                        Add a chart title More layouts
11. I can change chart types by selecting the icon in the Type Group




Office 2007 Excel                                                        Page 18
12. Change the Scale $4,500 to $500 (left column of chart)




13. Right-click on the money values

14. Select Format Axis




15. Change Minimum, Maximum and Major Unit




Office 2007 Excel                                            Page 19
Result:

                                                                    Click Chart Title and
                                                                    type the title of your
                                                                    chart.




Data Table – exact values under graph
bars




                                                                    1.   Create the graph
                                                                    2.   Chart Tools
                                                                    3.   Layout Tab
                                                                    4.   Labels Group
                                                                    5.   Data Table Button




Copy the chart and paste into Word: Select the graph, Home Tab, Copy Button, Open Word, Edit ,Paste

Office 2007 Excel                                                                                     Page 20
Sort
1. If you are not already in the Exercise Spreadsheet, Click the Office Button, New, Installed Templates, Sales
    Report and click the Create button




2. Drag and highlight all data from cell A1 to F278




3. Click the Sort icon on the Home Tab in the Editing Group




4. Select the Custom Sort




5. Sort by Product and click Add Level




6. Select Then by: Customer




7. Click OK
8. It places the products in alphabetical order and inside the products the customers in alphabetical order.




Office 2007 Excel                                                                                        Page 21
Subtotals

1. If you are not already in the Exercise Spreadsheet, Click the Office Button, New, Installed Templates, Sales
   Report and click the Create button




2. Drag and highlight all data from cell A1 to F278




3. Sort the data following the instructions on the preceding page in the way you wish to find your subtotals.
   We will keep Products as the Sorting Group

4. Select the Data Tab, Outline Group, Subtotal Icon




5. Always keep the same group in the first box as the group for your sort and select if you want the sum or
   count, etc.




                               Click OK
6. Notice that you now have the totals for each product




Office 2007 Excel                                                                                        Page 22
Protect Workbook Elements

By default, when you protect a worksheet, all the cells on the worksheet are locked, and users cannot make any
changes to a locked cell. If you want them to fill out certain cells and lock the rest of the spreadsheet:
Highlight the cells in which the users can enter data and unlock them. For example:




In this worksheet I selected B4, right-clicked, selected Format Cells, the Protection Tab, and took the check out
of Locked and clicked Ok




I repeated this for cells E4, A13-27, B13-27, C13-27, E13-27 and G13-27

On the Review Tab, click Protect Sheet and set your conditions, click OK and save your document




Office 2007 Excel                                                                                          Page 23
Working with Sheets Your Excel file is called a Workbook that contains multiple sheets for that workbook.
The sheet tabs appear at the bottom of the window. To insert an additional sheet, click the insert sheet tab:




Rename a sheet

 Right-click on the sheet tab at the bottom of the spreadsheet
 Left-click on Rename
 Type the name that you want for the sheet
 Enter
Example:




Group Sheets:
You can enter the same information on every sheet at one time by grouping the sheets. This helps if you have
the same students for each sheet and want to type all of their names in all of the sheets at one time or you want
to set the same formatting and formulas for each sheet (each month). Once you have entered the information
and formatting that you want for every sheet, ungroup the sheets and enter the data that is unique to each
sheet.
 Always leave the last tab ungrouped – do this by clicking the new sheet tab to insert one sheet more than
    you need
 Left-click to select the first sheet
 Hold down the Shift key and left-click to select the last sheet tab and all sheets in between those 2 sheets
 The grouped sheets all turn white
       Click                                                        Shift+click




Cancel the Group Mode:
Click the non-selected sheet tab

Link a Cell in One Worksheet to Another Worksheet – You would use this if you want a total from one sheet to
carry over as the beginning total for the next sheet
 Click the cell with the information to automatically enter into a cell in a different sheet.
 Click Edit on the menu
 Click Copy
 Locate the Sheet and the Cell where the data is to be linked
 Click Edit on the menu
 Click Paste Special
 Click Paste Link

Office 2007 Excel                                                                                        Page 24
Create a Summary Sheet with Multiple Sheets




Example:




Summary of Groceries for the year
1. If you grouped your sheets to set up the items and formulas, groceries will always be in Cell C4. Include your
    summary sheet when grouping and setting up the format
2. In the Summary Sheet, click Cell C4
3. Type = (This begins a formula)
4. Click the September tab
5. Click Cell 4
6. Type +
7. Click the August tab
8. Click Cell C4
9. Type +
10. Click the July tab
11. Click Cell C4
12. Repeat this pattern for every month
13. After you have clicked C4 in the last sheet press Enter
14. You return to your Summary sheet and there is a running total spent on groceries for the last three months.
15. Repeat this process for all other items in your summary sheet

Exercise:
1. Open a new spreadsheet with 4 sheets at the bottom


2. In Sheet1- Cell A1 type 4, Sheet2- A1 type 8, Sheet3- A1 type 5
3. Rename Sheet4 to Summary
4. In the Summary Sheet, Cell A1 type the equal sign =
5. In Sheet3 click once in A1 to select it and click the plus sign +
    In Sheet 2 click A1 and type the plus sign
6. In Sheet1 click A1 and Enter

7. You return to your summary sheet with the total




Office 2007 Excel                                                                                        Page 25
    Create a Drop-down List

                                             To make data entry easier, or to limit entries to certain items that
                                             you define, you can create a drop-down list of valid entries that is
                                             compiled from cells elsewhere in the workbook. When you create a
                                             drop-down list for a cell, it displays an arrow in that cell. To enter
                                             information in that cell, click the arrow, and then click the entry
                                             that you want.


Create a list of valid entries for the drop-down list
1. Type the entries in a single column or row without blank cells.
2. You may want to sort your data before the next steps
3. Highlight the entries
4. Click the option button for the name box




5. Type a name for your range of cells (in this example Schools is the name of the range)

Create your drop-down list in a cell
1. Select the cell where you want the drop-down list




2. On the Data bat, in the Data Tools group, click Data Validation




3. In the Data Validation box, click the Settings tab
4. In the Allow Box, click List




Office 2007 Excel                                                                                         Page 26
5. Click the Option Button to select your Source




                                                         Option Button

6. A small box appears in your spreadsheet that wants you to name the cells for the list

7. Drag to Highlight the cells for the list




    Notice that when you highlight the range of cells that you named School, that appears in the box with an
    equal sign

8. Press the Enter key to select what is in the Validation box, your named range appears in the Source Box




9. Click OK

10. Your drop-down box appear in the cell you selected in the beginning of this exercise




Office 2007 Excel                                                                                       Page 27
   Example of Mail Merge with Word




Mail Merge – Letters

1. Type your list of names and addresses or any information that changes with a record in Excel




2. Type the letter or document that you wish to send to the list in Word


Office 2007 Excel                                                                                 Page 28
3. Click the Mailings Tab, Start Mail Merge Option Button, Letters




4. Click the Select Recipients Option button and Use Existing List




5. Locate the Excel file and click Open

6. Click OK at this window




7. Place your cursor where you want your first field name to go (the heading of your first column in your
   spreadsheet) For this example it would be after the word Dear

8. Click the Insert Merge Field Option Button and select the first field name First Name




Office 2007 Excel                                                                                       Page 29
9. Repeat for the other field names




10. Click the Finish & Merge Option Button, Edit Individual Documents




11. Click All, OK




Office 2007 Excel                                                       Page 30

								
To top