Datasets and Tables

• Today we will start looking at using excel to process larger datasets.
  • I will be using this file
  • It contains nearly 16000 records.
  • This represents historical employment data for selected regions of the country.
  • It is from this page.
  • It is an example of many different types of data available on line.
  • Each row represents a data point.
• The first section of chapter 4 presents tools for large data sets.
  • F5 is the goto cell function
  • On the View tab in the Window group
    • Freeze Panes is a useful tool.
    • The split window is nice too.
  • They spend some time discussing printing larger files, but I don't think this is a good idea. Learn to use the tools
• The second section introduces data tables.
  • This is a really useful feature of excel for handling large data.
    They introduce some rules for creating a table.
    • There should be a name at the top of each column.
    • None of these names should be identical
    • All data in a column is the same
    • No blank rows or columns
    • All data in a row is related to a single item.
    • Only one data item per cell.
    • In our data, row 4 needs to be deleted.
    • To create a table
      • click anywhere in the table.
      • On the Insert tab, select Table from the Tables workgroup.
      • Make sure that the headers box is clicked appropriately.
      • Make sure it has selected the right range of data.
    • Note that there is a Table Tools Design tab.
    • And you can go back to a regular workbook with Convert To Range in the Tools workgroup of this tab.
    • Adding rows and columns.
      • Go to the bottom and add a row.
      • Go the the right, add a column (at least a computation)
  • The strength of tables is revealed in section 3, Table Manipulation
    • Sorting a single field by the dropdown box.
    • Multiple fields via the Sort function on the Data tab.
    • Filtering data is easy as well.
  • Section 4 shows how to add a total row.
  • Section 5 shows conditional cell formatting
    • Selecting a large range:
      • Select the starting cell
      • F5 and select ending cell (but don't press ok)
      • Press Shift and click on OK
    • Conditional formatting on the Home tab.