1 / 20

Spreadsheets and Non-Spatial Databases

Spreadsheets and Non-Spatial Databases. Unit 4: Module 15, Lecture 2- Advanced Microsoft Excel. Advanced Microsoft Excel. Beyond the Basics Copying and Pasting Formulas Absolute Cell Addresses Trendlines Statistical Analysis Pivot Tables and Charts Helpful Hints File Conversions.

boris-bush
Download Presentation

Spreadsheets and Non-Spatial Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 °Fconversion: • Formula F=C*1.8+32 • 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 $B$15 and $B$16. 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/icelake/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

More Related