Spreadsheets and Non-Spatial Databases

1
Spreadsheets and Non-Spatial Databases

• Unit 4 Module 15, Lecture 2- Advanced Microsoft
  Excel

2
Advanced Microsoft Excel

• Beyond the Basics
• Copying and Pasting Formulas
• Absolute Cell Addresses
• Trendlines
• Statistical Analysis
• Pivot Tables and Charts
• Helpful Hints
• File Conversions

3
Advanced Microsoft Excel

• Copying and Pasting Formulas
• When entering the same formula multiple times
  use
• Autofill
• Drag the fill handle (black box at the bottom of
  highlighted cells) over the cells to be filled
• Fill right
• Highlight cells to be filled
• Ctrl r
• Fill down
• Highlight cells to be filled
• Ctrl d

• Fill Handle

4
Advanced Microsoft Excel

• Paste Special
• When copying a formula choose to paste only the
• Formula
• Value
• Format
• Etc.
• For example if copying a formula to a new table
  or spreadsheet and only the value of the formula
  is to be displayed choose paste special and
  highlight values.

5
Advanced Microsoft Excel

• Absolute Cell Addresses
• When copying formulas Excel shifts the reference
  cell to a relative reference in the next column
  or row.
• Example when using the fill right command the
  relative reference shifts one column to the
  right.
• If the formula is to refer back to the same cell
  each time it must use an absolute cell address.

Relative reference
6
Advanced Microsoft Excel

• C to F conversion
• Formula FC1.832
• Cell B15 contains 1.8 and Cell B16 contains 32.
  In order to refer to these as an absolute
  reference they must be written in the formula as
  B15 and B16. In this manner when copying and
  pasting or filling the formula it will always
  refer to these two cells.
• Shortcut F4 will toggle between relative and
  absolute references

7
Advanced Microsoft Excel

• Trendlines
• Independent (Depth) vs. Dependent variable (pH)
• Graph data using the XY (Scatter) Chart type.
• Once graph is finished go to the chart menu and
  select add trendline.
• Right click on trendline to format
• Display the R2, equation, etc.
• This trendline shows that pH is negatively
  correlated with depth. This means that as depth
  increases pH decreases or vice versa.

Trendline
8
Advanced Microsoft Excel

• Statistical Analysis
• Most Excel packages do not have the capability to
  perform advanced statistical analysis without an
  add-in.
• Under the tools menu select add-ins.
• Select the Analysis Tool Pack and Analysis Tool
  Pack (VBA).
• It may be necessary to insert the Microsoft
  Office installation CD.

9
Advanced Microsoft Excel

• To perform a a statistical test
• Under the Tools Menu select Data Analysis.
• Choose which statistical operation to perform.
• For Example If a trendline is not sufficient.
• Select the regression option.
• Input Y range and X range.
• Click OK.
• Summary Output will display in a new worksheet.
• Note the R2 here is the same as that displayed
  on the trendline graph.

R2
10
Advanced Microsoft Excel

• Pivot Tables
• Organize and summarize large amounts of data
  quickly.
• Add or Remove data
• Rearrange the layout
• View a subset of data
• Calculate overall or by subset
• Sum
• Average
• Count
• Standard Deviation

11
Advanced Microsoft Excel

• Creating a Pivot Table
• Start with a table of data
• Clear column headings
• No blanks
• Select cell anywhere in table
• Go to the Data menu
• Select Pivot Table and Pivot Chart Report
• Follow Steps of the Pivot table and Pivot Chart
  Wizard.

12
Advanced Microsoft Excel

• Pivot Table Design
• Step 3 of the Pivot Table and Chart Wizard
• Select Finish to organize the table on the
  spreadsheet.
• Choose the Layout option to organize the table in
  the chart wizard.
• Drag field buttons to the labeled areas on the
  pivot table diagram

Pivot Table Diagram
Field buttons
13
Advanced Microsoft Excel

• Pivot Table Design
• Data totals are automatically calculated as sums
• Change this by right clicking on the sum of temp,
  etc. in the data column.
• Select Field Settings
• Choose from list
• Sum, Average, Max, Min, etc.

14
Advanced Microsoft Excel

• Pivot Table Options
• Easily add or remove data
• Return to wizard
• Select Layout Option
• Rearrange Fields
• Use the Pivot Table Field list to rearrange on
  the worksheet
• View a subset of the data
• Click on arrows
• List drops down
• Check items to display

15
Advanced Microsoft Excel

• Pivot Tables
• Create many tables from same pivot table
• Select data to be shown
• Copy table
• Paste Special
• Values
• Creating a pivot table is a trial and error
  process.
• Practice moving things around to become familiar
• Of course look to Microsoft for help!

16
Advanced Microsoft Excel

• Pivot Table Charts
• Simply click on the chart wizard icon in the
  Pivot Table toolbox.
• Automatically creates chart
• Same rules apply
• Change layout
• Display only certain information
• Etc.

17
Advanced Microsoft Excel

• Pivot Table Example
• Ice Lake, MN
• 9/5/2004-9/11/2004
• Available at http//www.waterontheweb.org/data/i
  celake/realtime/weekly.html
• Preparing Excel Table
• Change the date field to two columns time and
  date.
• Delete Spaces in data table
• Make column heading into one row
• Use wrap text option under format cell
  alignment tab

18
Advanced Microsoft Excel

• Example Ice Lake, MN
• Use the layout option to design the pivot table
• Drag Date and Time Fields to the Page Field
• Drag Depth Field to the row Field
• Drag the rest of the Field Buttons to the Data
  Field
• Click OK and Finish
• Result
• Data organized by
• Date and time
• Depth
• Can calculate average, etc. much quicker than
  entering formulas.

19
Advanced Microsoft Excel

• Other Helpful Features
• Autofilter
• Search for blanks, non-blanks, or other data in a
  table
• Select any cell in table
• Go to Data Menu
• Filter Auto Filter
• Click on arrows to select from drop down list
• Transpose
• When data is in columns and it needs to be in
  rows
• Paste special-Transpose

20
Advanced Microsoft Excel

• Transferring files between programs
• Many file formats
• Microsoft Excel .XLS
• Quattro Pro .WQ1, .WB1, .WB2
• Lotus 1-2-3 WKS, .WK1, .WK3, .WKE
• .DBF, .CSV, .TXT, and many others.
• From Excel
• Save As
• Choose from Save as Type
• To Excel
• Right click on file
• Open With Choose Program

• Save as Type