Datasets and Tables

• Large data has become easily accessible.
• Excel is the first level tool to work with large data.
• We will discuss another tool (ACCESS) later.
• Excel gives you the ability to work with these larger data sets.
• Data:
  • I got this data from here. (It is just data for a class)
  • The data is here
  • Download and open this file.
    • You will notice that everything is in one column
    • 
    • If this were really a CSV (comma separated value) file, we would be fine, but it is delimited (or separated) by ;
    • Go to the Data tab
      • Select column A
      • Select the Text to Columns button.
      • 
      • This will bring up a dialog
      • 
      • Since your data is delimited, or marked with a character, select Delimited and Next
      • This will bring up a new dialog
      • 
      • Select the semicolon.
      • The dialog will update to show how the data will be split.
      • 
      • The next screen allows you to place data, but we don't need that, so just click Finish
      • You should see something like this
      • 
    • Cleaning up the data a little.
      • Highlight A1:P1 and make it bold
      • Delete row 2.
      • As you scroll through the data, the headers disappear.
      • Reduce column widths to the amount needed.
      • Format rating to two decimal places.
      • 
    • The Freeze Panes button on the VIEW tab will help here.
      • Click in cell B2
      • Click on Freeze Panes
      • 
      • You can now move around and the headers will always be in row 1
      • And the type of cereal will be in column A.
    • You can also split into different windows.
    • You can also hide/unhide columns and rows.
• Tables
  • Tables is a structured range that contains related data organized in such a way as to facilitate data management and analysis.
  • We have a table
    • Usually column headings
    • No blank rows or columns in the table.
    • Each row needs a unique data value, the name in our case.
    • Put a blank row and blank column around the edges of the table.
  • We can create a table by
    • Clicking anywhere in the table
    • Click INSERT tab Table button
      • This will bring up a dialog box to allow you to select the table, but it is probably right.
      • 
    • Notice the filtering arrows
      • You can sort or filter quickly.
      • Numbers and text
    • You can insert columns and rows.
    • If you add a computation column it will automatically be inserted in all fields.
    • You can find and remove duplicates.
    • You can apply various styles.
    • You can apply
• The last section discusses conditional formatting.
  • On the home tab, Conditional formatting.
• You can also return it to a range.
  • Table design tab Convert To Range