1 / 17

What will be covered

What will be covered. Based on our older “Excel for Data Management” class but freshened up a bit since everything moved in 2007. Importing and exporting, data manipulation, graphing, tips and tricks

kim
Download Presentation

What will be covered

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. What will be covered • Based on our older “Excel for Data Management” class but freshened up a bit since everything moved in 2007. • Importing and exporting, data manipulation, graphing, tips and tricks • We’ll use a couple of datasets for illustration and then leave time for more specific questions at the end.

  2. If there’s enough time: • Array Formulas • Merging Excel Files • Probably shouldn’t do this in Excel

  3. When and Why Excel • It’s a spreadsheet • Great for calculations and formulas • Okay for Data Management • But that’s what most people use it for • Should be using Access • Everybody has Excel (including Macs) • That’s how we got the Data • Ubiquitous file format widely imported and exported • We’ve been using it your whole life, right? • Most of us actually only scratch the surface.

  4. Ask Questions • Ask questions Today! • Lots of resources available • StatLab Consultants • Sherlock Campbell • Themba Flowers • Internet Searches. • Microsoft Live Search might actually be better for Office questions than Google

  5. Review of Basics • Office 2007 – where the ? • Everything moved and looks different. • That glowing circle in the upper left corner • New and improved! • More rows (not 65536 anymore) • You have to resave to new data format • Note the new endings .xlsx and .xlsm • Not backwards compatible • More levels of sorting and filtering • Better charting • Differences between Mac and PC • VBA (Big Difference) • Sharepoint

  6. Importing And Exporting • File/Open • “All Files” • Text to Column • For all text files OR • For existing excel data • Let’s try it now: open a text file http://statlab.stat.yale.edu/help/workshops/introData/dataintro • StatTransfer • Important for Exporting: Define VariableTypes • Embedding in Word • Keeps data linked so if you change data you don’t have to re-import to document

  7. Tips and tricks • String manipulation (Trim, Left, Right, Substitute) • See Movies.xlsx for example • Easy Dummy variables • Copy and paste as value/Re-Sort • Conditional formatting andData Validation • Quick way to determine whether your data looks good

  8. Text Manipulation • If cell A1 = “abcde ” • TRIM(A7)=abcde • gets rid of extra spaces • RIGHT (A7,2)=de • and LEFT, MID • SUBSTITUTE (A7, “c”, “o”)=abode • Comparing Cells (especially in Macros): • If cell A1 = “12345” (a string, not a #) • VALUE(A1)=12345

  9. Working With Data Sets • Most common question: • I need unique or some subset (Sorting and Filtering) • Defining Data Ranges • Name your Sheets • Name your data ranges • Use the Table • database functions

  10. Getting a feel for the Data • COUNT, COUNTA, COUNTBLANKS • COUNTIF (array, value) • Counts values that meet criteria • SUMIF (array, value, sum range) • Sums observations that meet criteria

  11. Data Analysis • Charts • Insert/Chart • Many Basic Statistical Functions • STDEV, CORREL, etc • Data Analysis Tools • Tools/Data Analysis (If not installed, Tools/Addins/Data Analysis Toolpack) • But not Excel is not the best program • Much better in Excel2007?

  12. Pivot Tables/Charts • Easy snapshot views of your data. • Somehow takes up much less memory and time than the equivalent functions to do the same thing (indexing?) • Great for transforming time series data into tables • Be aware: Default calculation is “COUNT” and you probably want “SUM”

  13. Example for Today • Census Data • Let’s go to StatCathttp://ssrs.yale.edu/statcat/ • Type in ‘Excel’ • Download “Language Spoken at Home for the Population 5 Years and Over”

  14. Writing Your Own Macros • Be careful: You can’t UNDO running a macro. • Use the record function to get the idea and then customize for your needs. • Basic structure is Object Oriented • Object.property() • The built-in Editor will show options • Supports If, then, else, for loops , while loops

  15. Advanced Graphing • Trend lines • Changing the data to series • Removing/Hiding lines • Error Bars • Pivot Charts for Large Data • Graphs for publishing • Black and white

  16. There’s even More Advanced Stuff • Database connections • Forms

  17. Other Resources • Excel Hacks (O’Reilly) • Orbis.yale.edu • We have access to several online resources • YouTube. Really.

More Related