This Morning S Exercise Will Attempt to Demonstrate How Excel Users Can

This Morning S Exercise Will Attempt to Demonstrate How Excel Users Can

/ Using Excel with Instructional Reports for Beginners

This morning’s exercise will attempt to demonstrate how Excel users can:

  1. Sort their data
  2. Invoke conditional formatting (traffic-lighting)
  3. Insert formulas for average, minimum and maximum values.

SORTING

The SORT function allows you to arrange your data using one or more variables.

  1. Highlight the range to be sorted. This can be accomplished several ways. Highlight the entire range, highlight the rows (including the header), or highlight the entire sheet.
  2. Select DATA tab on the toolbar.
  1. Select SORT on the toolbar.
  2. A box will appear where you will determine the variables by which you will sort your data. The box has several options available. If sorting on more than one variable, you will use the “add level” button.
  1. Select TEST_NAME from the pull-down menu in the first box.
  2. Select the order for your data. Depending on the type of variable you select, you will have lowest to highest value, highest to lowest value, A-Z, or Z-A.
  3. To add a second criteria to the sort order, select the “add level” button. Select your next variable to use (such as TEACHER/SECTION).
  4. After selecting your sort order, select the “OK” button.

(Breakout a single test here)

AVERAGE, MIN, and MAX

When working with formulas, you must be aware if your data has any missing values. For our example, we will assume there are no missing values.

Let’s start with “Average”.

  1. Click on the cell immediately after the final SCALSCOR.
  2. Select the FORMULAS tab on the toolbar.
  1. Click on the triangle below the “Auto Sum” option.
  2. Select “Average”. The entire column of scores to be used in calculating the average should be highlighted. If not, you will need to manually select the range of scores to be used.
  1. Press enter to complete the formula. Your mean score for that column should appear.

For MIN, MAX, or other statistics including counts, follow the same process above.

CONDITIONAL FORMATTING

Conditional formatting allows you to format your data in various ways such as stop-lighting or color-coding based on the value in the cell.

  1. Highlight the area to be formatted.
  2. Select the HOME tab at the top of the toolbar (if not already selected).
  3. Select “Conditional Formatting”.
  4. Select New Rule.
  5. Select a format style. For this exercise, select “Icon Sets”.
  6. Select an icon set to use. For this exercise, select the traffic lights.
  7. Set the values and the type for the various lights you are using. For this exercise, all data used will be numbers. For the overall score, scores within the range of 500-600 will be green, 400-499 will be yellow, and under 400 will be red. The settings will appear like this:

  1. Press OK and then OK. Your results should look something like this.
  1. Repeat these steps for the three reporting categories using your own thresholds. Remember, reporting categories range from 0 to 50. As a general rule, 30 is a passing score for reporting categories but can be more or less.

Freezing Panes

Freezing panes allows users to keep headers or the first column visible as your scroll through a large spreadsheet.

To freeze the header row, go to the row immediate after the last row to freeze and select VIEW from the menu bar.

Next, select the FREEZE PANE button. Select the FREEZE TOP ROW option.

To freeze the left column, select a cell after the first column and select VIEW, FREEZE PANE, and FREEZE FIRST COLUMN.

To unfreeze all panes, select VIEW, FREEZE PANE, and UNFREEZE PANES.

Top View