1. Login
2. Double-click Microsoft Excel icon

  If there is no icon:

A. Click on Start in the lower left hand corner
B. Click on Programs
C. Click on Microsoft Office
D. Click on Microsoft Excel

After you enter statistical data, Microsoft Excel can be used to generate meaningful charts to enhance your information.   There are two types of charts:

Embedded Chart:  A chart object that is placed on a worksheet and saved with that worksheet when the workbook is saved. Embedded charts are beneficial when you want to view or print a chart with its source data or with other information in a worksheet.

Chart sheet:  A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart separately from worksheet data or a PivotTable report.  It is saved when the workbook is saved.

When information is entered, it is best to do so without any blank rows or columns in the data to be included in the chart.

Using Excel's Automatic Chart Option (back to top)

1.  Enter the data

2.  Select the data to be included in the chart (as is shown above).   If the chart information is not together in one block, you may select individual cells by holding down the CTRL key as you click (or drag) over additional cells.  Normally totals are not included with individual amounts. If the chart is desired to show just totals, use the CTRL key to select separate cells.

3.  Press the F11 key

This will create a standard style chart on a separate sheet in the workbook.

4.  To make modifications to the chart, click on Chart in the top Menu bar, then click on Chart Options.  Complete the information as directed in the dialogue box.  Although some changes are easy to make, it may be more helpful to start over with different data selected  or use the Chart Wizard as discussed below.

Using the Chart Wizard  (back to top)

1.  Enter the data

2.  Select the data to be included in the chart.   If the chart information is not together in one block, you may select individual cells by holding down the CTRL key as you click (or drag) over additional cells.  Normally totals are not included with individual amounts. If the chart is desired to show just totals, use the CTRL key to select separate cells.

3.  Click on the Chart Wizard tool in the Standard Toolbar (Optional Method:  Click on Insert, then click on Chart)

4.  Chose from the options displayed on the first of the four stages of the Chart Wizard or leave as displayed by default.  As different options are selected, the sample at the right displays a sample of the results.  You may click on Cancel at any point to start over or Finish to accept all defaults offered by the Chart Wizard.

5.  To proceed to the next step of the Chart Wizard, click on Next.

6.  Continue with the remaining options displayed in the Chart Wizard until finished.

Comments about working with Charts  (back to top)

• Resizing a Chart:  If you elect to place the chart as an object in a sheet (step 4 of the Chart Wizard), it is inserted as an object with small boxes around the edge called "handles".  The chart may be sized (made larger or smaller) by using the mouse to point to any handle, then press and hold the left mouse button down while he mouse is dragged to the desired size.  A chart on a separate sheet (such as is made with the F11 key) is not resizeable.
   
• Moving a Chart:  A chart placed as an object in a sheet may be repositioned by pointing anywhere in the chart, then press and hold down the left mouse button as you drag the chart to the desired position.
   
• Printing a Chart:  A chart on a separate sheet in Excel will print on a separate sheet of paper.  If a chart is placed as an object in a sheet, it may be printed as part of the sheet or separately.  If it is selected (click on the chart to display the handles), only the chart will print.  If it is not selected, the entire sheet will print -- both the data and the chart.
   
• Deleting a Chart:  To delete a chart placed as an object in a sheet, click on it to select it, then press Delete.  To delete a chart in a separate sheet, delete the sheet (right click on the sheet name and left click on Delete).

Editing an Excel Chart  (back to top)

Although some changes are easy to make, it is often easier to start over with different data selected or to use the Chart Wizard.  Different options for editing a chart include the following.

• The easiest way to edit a chart is to double click on the area of the chart where changes are to be made.  This will display a dialogue box to make changes specific to the area selected.   
   
• Use the Chart tool bar. 
  
  If it is not displayed on the screen:
      Click on View
      Wait until you see Toolbars (or click on the double arrows at the bottom of the list to expand the display)
      Point to Toolbars
      Click on Chart
  
  The Tools on the Chart Tool Bar:
  1.  Chart Objects:  The drop down arrow at the end of the first tool allows the selection of the chart object to be affected.
  2.  Format:  Clicking on the second tool will display a dialogue box for whichever Chart Object is selected in the first box.  Therefore it will change as the chart object is changed.
  3.  Chart Type:  The drop down next to the third tool will allow the chart to be changed to different types of charts.
  4.  Legend:  The fourth tool is a toggle that will turn off (or on) the display of the legend.  Click it a second time to return it to the original state.
  5.  Data Table:  The fifth tool is a toggle what will turn on (of off) the display of the data used to build the chart.
  6.  By Row/By Column:  The sixth and seventh tools allows the display of the chart to be changed from showing information by row or column
  7.  Angle Tools:  The last two tools may be used to angle the display of the value or category axis (the words/numbers displayed on the bottom or left of the chart).  The tools are not available when a different part of the chart is selected in the first tool.
   
• Use the Chart Menu Option
  
  When a chart is selected, or a chart sheet is displayed, a Chart Menu Option is added to the Menus displayed at the top of the screen.
  
  Click on Chart. 
  The first four choices in the menu are the four steps included in the Chart Wizard
  Add Data:  Allows the original data selected for the chart to be altered

Working with Excel's Database Features:  (back to top)

After you enter data, Microsoft Excel can be used to sort and filter your information.  When information is entered, do not use any blank rows or columns within the data.  When data is entered without blank rows or columns, it allows Excel to recognize the location of the data. 

Sorting Data (back to top)

 The easiest database feature is to sort. 

    1.  Click in the top cell of the column to be sorted.

DO NOT SELECT THE COLUMN unless you only want to sort the data in that one column!  When a column is selected, Excel only sorts the selected column; it will not sort the data in the other columns in the same row with it. 

    2.  Click on one of the two sort buttons in the Standard Toolbar.  Data may be sorted in ascending or descending order.

A more advanced sort may be performed from the Data menu option across the top of the screen.  Using the Menu, you may sort up to three columns as well as define if there is a header row that should be excluded from the sort.

Filtering Data (back to top)

Filters are used to selectively determine what rows are displayed by criteria defined for a specific column.  Excel has a simplified filtering option (AutoFilter) and a more powerful, complex filtering option (Advanced Filter).  Rows that do not fit the filter criteria are hidden from view (not deleted).

Auto Filter

    1.  Click in any cell within the data (to define for Excel the area to be considered)
    2.  Click on Data
    3.  Point to Filter
    4.  Click on Auto Filter

Grey drop-down arrows will be added to the end of each cell in the first row of the data.  The values for each column are determined by the entries in that column of each row. 

• Click on a specific entry in the drop-down list to display only the rows with the selected entry.  Notice the arrow is shown as blue to determine that there is a filter applied for that column.
• If desired, click on the drop-down arrow in other columns to further refine the filter
• When desired, click on the drop-down arrow where a filter has been applied and click on All to remove the filter for that column

In addition to selecting a specific entry in the drop-down list, you may also:

• Click on Top 10 to display the selected number of top (or lowest) values in a list.  A  dialogue box will be displayed to select whether to display the top or lowest values, how many rows to display and whether to display the value of the item or a percentage.
• Custom allows you to build your own filter values based on what is equal to, not equal to, greater or less than your selection.

To remove the autofilter arrows, repeat the same steps used to display them as listed above.

Advanced Filter

An advanced filter may be used when:

• the autofilter does not provide enough flexibility
• the filter information will be saved and reused
• the filtered information is to be copied to another location

    1.  A portion of the spreadsheet must be prepared as the Criteria Range:
            A.  Select the header row of the data (the column titles)
            B.  Copy the selected row to the clipboard (the quickest way to do this is to hold down the CTRL key and press C) 
            C.  Move to an empty portion of the spreadsheet with at least one empty row or column after the data
            D.  Paste the clipboard contents into the first cell of the criteria range (the quickest way to do this is to hold down the CTRL key and press V)
 

 2.  Enter the criteria in the blank row under the row just copied.  Criteria is the information used for filtering.  The example below shows criteria requesting to show all individuals that sold more than five items on Monday AND more than 15 total items.  Information may be entered in more than one column to be considered in the filtering process. 

If information is entered in more than one column on the SAME row, it creates an AND condition (all criteria must be met).  

The top two rows at the right shows a criteria range with an AND condition.  The resulting display is shown below it.  Because both criteria were entered on the SAME line, the filter shows information where there were more than 5 items on Monday AND more than 15 total items.

If information is entered in more than one column on separate rows (without a blank row between), it creates an OR condition (only one criteria must be met to be included in the filter). 

The top two rows at the left shows a criteria range with an OR condition.  The resulting display is shown below it.  Because the criteria were entered on DIFFERENT lines, the filter shows any items where there were more than 5 items on Monday as well as those with more than 15 total items.

    3.  When you have finished preparing the criteria range, click in any cell within the data (to define for Excel the area to be considered)
    4.  Click on Data
    5.  Point to Filter
    6.  Click on Advanced Filter
    7.  Click on the desired action (to filter in place or copy to a different location)
    8.  Click on the red arrow at the end of the line for List Range (if the range is not already shown)
    9.  Select the cells that contain the original list (if the range is not already shown)
    10.  Click on the red arrow at the end of the range definition to return to the Advanced Filter dialogue box (if the range is not already shown)
    11. Repeat steps 7 through 9 for the Criteria Range and the Copy to Range (if the filter is to be copied to another location)
    12. Click in the box at the lower right if only unique rows are to be displayed
    13. Click on OK to filter the list
            Note:  If a previous filter exists in the Copy to location, it is best to delete that information before proceeding with an additional filter.

Forms (back to top)

Any form can be automatically generated by Excel to enter information into an Excel database.  A form can be helpful by:

• preventing entry in a cell that contains a formula cell
• automatically copying cells that contains a formula to new rows
• displaying a warning before removing deleting an entire row
• providing criteria entry options to locate rows
• moving between rows
• quickly positioning correctly for the entry of a new row

    1.  Click in any cell within the data (to define for Excel the area to be considered)
    2.  Click on Data
    3.  Click on Form

Follow the prompts on the screen to utilize the form feature.

Subtotals (back to top)

Before using the subtotal feature, it is important that the list be sorted by the column used to define the subtotal category.  In the example to the right, the list is sorted by the "Class" column since the subtotals will be the number of hours worked by the individuals in the three different classes.

    1.  Click in any cell within the data (to define for Excel the area to be considered)
    2.  Click on Data
    3.  Click on Subtotals

        A.  At each change in:  Use the drop down arrow at the end of the first line to determine which column will be used to create the subtotal categories.
        B.  Use function:  Use the drop down arrow to define what type of calculation should be performed for each subtotal category.
        C.  Add subtotal to:  Click in the box in front of each column that is to be calculated
        D.  Replace current subtotals:  Click in the box if there are current automatically generated subtotals that are to be removed.
        E.  Page break between groups:  Click in the box if each group is to be on a separate page.
        F.  Summary below data:  Click in the box if the summary information is below the data (without the box, the data would be placed on lines above the data.
        G.  Remove All:  Click on this button if subtotals are to be removed

    4.  Click OK to insert subtotals

Note the bars that are displayed to the left of the subtotal sheet.  This gives flexibility of the detail of the lines displayed with the subtotals.  Click on the minus or the plus signs to expand or collapse the categories.

For example, click on the minus sign at the bottom of the first bar to remove the display of the individual line items and show only the grand total.  To return to the full display, click on the plus symbol that is displayed.

To remove the individual line detail for only one item (such as the Junior Class), click the minus sign to the left of the Junior Total in the second bar.  To return to the full display, click on the plus symbol that is displayed.

    5.  To remove subtotals:
        A.  Click on Data
        B.  Click on Subtotals
        C.  Click on Remove All at the bottom of the Dialogue Box 

Validation (back to top)

Validation allows the user to set criteria for the type of entry that is accepted in a cell or range of cells such as numeric, a date, etc.  For example, if an individual is not allowed to work more than 40 hours in a week, the validation might prohibit an entry larger than 40.

    1.  Click in a cell where validation is to be defined
    2.  Click on Data
    3.  Click on Validation

The three tabs on the validation dialogue box allow for the definition of the entry, an input message to be displayed to help the data entry process, and an error alert to be displayed if the data input does not meet the criteria defined.

Settings

The settings tab (displayed when the dialogue box is opened the first time) is used to set the validation criteria.  Use the drop down arrows at the end of each line to select the desired criteria.

Input Message

The second tab is to display a message to the anyone would be entering the data. 

There is an option for a title (the top line that is shown bolded) and the message (the remaining lines that would be displayed when the cell is selected.

Error Alert

The third screen provides an option to display a message if an entry is made that does not conform to the validation criteria.  There are three styles to choose from:

Stop:  Will not allow an entry that does not conform to the validation criteria.  If an incorrect entry is entered, the user must retry with a different entry or cancel to return to the previous entry.

Warning:  Will warn that the entry does not conform to the validation criteria, however will allow such an entry to be accepted.

Information:  Will notify that the entry does not conform to the validation criteria, however will allow such an entry to be accepted.

The contents in the Title box of the Error Alert dialogue box will determine the contents displayed in the blue title bar of the error alert. 

The text in the Error Message of the Error Alert dialogue box will determine the contents displayed next to the symbol in the error alert box.

Pivot Tables (back to top)

Although confusing at first, Pivot Tables are easy to create and powerful objects for displaying and manipulating data. 

    1.  Click in any cell within the data (to define for Excel the area to be considered)
    2.  Click on Data
    3.  Click on PivotTable and Pivot Chart Report. . .
    4.  Click Next to accept the default information (determined if you were within the list before starting the process)
    5.  Click Next to accept the list range (determined if you were within the list before starting the process)
    6.  Click Finish to place the PivotTable in a new worksheet
    7.  Drag the fields to the proper places.  The field placement will determine the outcome of the Pivot Table.  This may take some practice to achieve the desired results.  It is fairly easy to modify, so experiment with the options. 
    8.  Any grey area of the PivotTable may be modified by double clicking on the shaded area.