IS107 module 6

• Data range


  • Organized into columns and rows representing fields and records
  • First row contains field names (header row)
• It's excellent practice to provide a data definition table for a data range
• Data definition table


  Lists the fields included with each record, the type of data stored in each field, and a short description of each field

  
• Dividing the Workbook Window into Panes

  1. Excel can split the workbook window in up to four sections called panes

  2. Each pane offers a separate view into the worksheet
• Freezing Panes


  • When you freeze a pane, its contents are always visible though you cannot scroll within it
  • You can freeze the panes located to the top and left of a selected cell, allowing scrolling within the lower-right pane
• Highlighting Duplicate Values


  Use conditional formatting to locate a duplicate record by highlighting duplicate values within a selected range
• Removing Duplicate Records


  1. Click any cell in the data range

  2. On the Data tab, in the Data Tools group, click the Remove Duplicates button

  3. Select the check boxes for the fields that you want to check for duplicates

  4. Click OK to remove records containing duplicates of all of the selected fields
• Sorting by a Single Field

  • Ascending order arranges text alphabetically from A to Z, numbers from smallest to largest, and dates from oldest to newest
  • Descending order arranges text in reverse alphabetical order from Z to A, numbers from largest to smallest, and dates from newest to oldest
• Sorting by Multiple Fields

  • The first sort field is called the primary sort field
  • The second sort is called the secondary sort field
• Sorting with a Custom List

  • A custom list indicates sequence to order data
  • Two predefined custom sort lists: day-of-the-week and month-of-the-year
  • Can create a custom list to sort records in a sequence you define

  
• Creating a Subtotal Row

  Add subtotals, which are summary functions that are applied to a part of a data range
• Subtotal Outline View


  The outline tool lets you control the level of detail displayed in the worksheet
• Finding and Selecting Multiple Cells

  Find & Select can locate cells that match a specified criterion, such as by last name, base salary, or date of hire
• Finding Cells by Type

  Find & Select can locate cells based on criteria other than cell value, such as whether the cell contains a formula, constant, blank, or conditional formatting rule
• Filtering Based on One Field

  Filtering data hides the rows whose values do not match the search criteria. Those rows are not removed from the worksheet.
• Filtering Based on Multiple Columns

  You can filter a data range based on criteria from multiple fields. Each additional filter reduces the number of records displayed.
• Excel Tables


  • Support sorting and filtering tools built into the table itself
  • Support table styles to format different features of the table, including banded rows and columns
  • Support automatic insertion of a totals row containing summary statistics for each field, which update as records are inserted or deleted
  • Support calculated values that use field names rather than cell references
• Converting a Range to a Table

  Excel tables have features not available with data ranges
• Additional filter


  Reduces the number of records displayed since a record has to fulfill all filter criteria to be selected
• Using filters from multiple fields


  Select the filter button from other column headers in the data range
• Excel tables

  • Support several features that are not available with data ranges, including:
  • Sorting and filtering tools built into the table itself
  • Table styles to format different features of the table, including banded rows and columns
  • Automatic insertion of a totals row containing summary statistics for each field, which update as records are inserted or deleted
  • Calculated values that use field names rather than cell references to make formulas easier to write and understand
  • Named references to different parts of the table structure, including table columns, total rows, and header rows
• Structural elements of an Excel table


  • Header row
  • Total row
  • First column
  • Last column
  • Banded rows
  • Banded columns
  • Filter buttons
• Table styles


  Can be applied to different parts of the table, but do not override the formatting applied to individual cells
• Total row


  Calculates summary statistics, including the average, sum, minimum, and maximum of select fields within the table
• Adding and deleting records in an Excel table

  The table adjusts to the new contents, and the format applied to the banded rows updates to accommodate the new data set size. The calculations in the Total row reflect the new data.
• Calculated field


  A field that contains a formula referencing other fields in the table, which updates automatically as other field values change
• Structural references


  References to fields and elements in an Excel table, enclosed within square brackets [ ]. The @ symbol refers to the current record or row within the table.
• Slicer


  • A way to filter a PivotTable, containing a button for each unique value in a field
• Creating and formatting a slicer

  Can create a slicer for any field in the PivotTable Fields pane, and format the slicer and its buttons
• Dashboard


  A page or screen providing informative visuals of data, key performance indicators, and statistics, with interactive tools to help users explore data
• SUBTOTAL function


  Used in creating dashboards
• Dynamic charts


  • Charts based on Excel tables that update automatically as the source data is filtered, making them ideal for dashboards
• VLOOKUP function


  Works well with Excel tables as lookup tables, automatically including additional rows as you add more records to the lookup table, and restricting the lookup table to only those rows that match filter criteria