Data used in the demostrations

• Census County Business Patterns Complete US files and NAICS descriptions for 2008 and 2012
• Real estate listing from Redfin.

Formulae and features shown: average, averageif, concatenate, Conditional formatting, countif, Data Analysis regression tool, Filter, if, lookup, median, paste special, Pivot tables, Scatter chart, Sort, sum, sumif, Remove duplicates, Text to columns

• Collect data on Redfin
  • Select a market at the bootom of the front page.
  • Adjust the map or restrict the search settings so that fewer than 500 listings are selected.
  • Switch to table view and download a csv file.
• Import into Excel
  • Open the csv file in Excel
    OR
  • Open the csv file in notepad and copy into Excel
  • Use text-to-columns in the Data tab.
• Display and filters
  • Resize columns.
  • Show only listing from a town or only 1000-1200 sq ft using filters (Data > Filter).
  • Delete columns.
  • Sort using pull down tabs.
  • Turn off filter.
  • Sort using Sort menu (Data > Sort).
  • Format numbers (Home > Numbers) or (Right-click > Format cells)
  • Conditional formatting (Home > Conditonal formatting)
  • New column: proper street name
• Calculations
  • Calculate sq meters.
  • Create and label new tab
  • Sum prices.
  • Sumif, countif, averageif (syntax: =sumif(B1:B499, "=Hayward", E1:E499)
  • Show $ sign for copying.
• Preparing output
  • Paste special value
  • Paste special tranpose
  • Add html tags and use concatenate
• Graphs
  • Bar chart of averages
  • Scatter plots of floor area and price
  • Add regression line
• Pivot tables
  • Create and label new tab
  • Create PivotTable (Insert>PivotTable)
• Download County Business Patterns data
• Import 2008 and 2012 data
  • Use text-to-columns
  • Filter for lfo="-" (not catagorized by legal form)
  • Copy the filtered results into new sheets
• Import NAICS descriptions
• Compare employment totals
  • Use the lookup function to find the employment total of the other sheet (syntax: =lookup(naics code in description, column of naics code in data file, column of employment in the data file)
  • Calculate growth rates.