1 / 24

Microsoft Excel – Advanced Topics

Microsoft Excel – Advanced Topics. April 17, 2008 Abby Wiertzema wiertzem@oswego.edu http://www.oswego.edu/~wiertzem. Topics. Protecting your data Excel Tricks Customizing your workspace Macros Chart Wizard Link data on multiple worksheets Pivot Tables Importing data.

Download Presentation

Microsoft Excel – Advanced Topics

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. Microsoft Excel – Advanced Topics April 17, 2008 Abby Wiertzema wiertzem@oswego.edu http://www.oswego.edu/~wiertzem

  2. Topics • Protecting your data • Excel Tricks • Customizing your workspace • Macros • Chart Wizard • Link data on multiple worksheets • Pivot Tables • Importing data

  3. Protecting your Data • Multiple copies of different media & locations • Local hard drive • Server hard drive • http://www.oswego.edu/admin • Diskette, CD, flash drive • Autosave – set a period of time you are willing to re-do your work • Save before attempting something new and unsure; undo may not work (macros, some sorts, pivot tables)

  4. Trick 1 – Different SAVE Options • Save– for the first time and to save open file • Save As– Saves the open file with different name/format • Text format - .csv, .dif, .prn, .slk, .txt • Database format - .dbf, wbx (Quattro Pro), .wks (MS Works) • Lotus 1-2-3 format - .wk1, .wk3, .wk4 • Previous Excel format - .xls • Web ready format - .htm, .html • Save As Web Page • Save Workplace– saves display information, not data • Autosave – Use Tools, Options, Save tab; not a replacement for regular saves

  5. Trick 2 – Pick from a List • When you want to select from values already entered in a column of cells, right-click on the cell and select “Pick from Drop-down List”

  6. Trick 3 – Insert Break in a Cell • To start a new line of text at a specific point in a cell, click where you want to break the line, and then press ALT+ENTER.

  7. Trick 4 – Hide Rows or Columns • Highlight rows or columns to be hidden • For Rows, select Format, Row, Hide • For Columns, select Format, Column, Hide • Select different ranges by holding CTRL • Hidden rows show ‘missing numbers’ • Hidden columns show ‘missing letters’ • Easiest way to Unhide is to highlight entire spreadsheet, then select Format, Row, Unhide or Format, Column, Unhide

  8. Trick 5 – Named Ranges • Allow you to select a range of cells and to refer to them by a name rather than the range, i.e. Depts rather than A2:A4 • Can be used in formulas & data validation • How: • Type the values in a column • Highlight all values • In the Names field, type a unique name • Press Enter. • Find them under Insert, Name, Define

  9. Trick 6 – Data Validation • Use this when you want to define data in a cell • For specific values from a list: • On Sheet2, create a list of values that are valid • Highlight and name the range • On Sheet1, click on a cell, select Data, Validation • On Settings tab, change Allow to a “List”. Type “=range-name” in Source. It is case-sensitive. • Copy down to multiple columns. • Other validations – number minimum/maximum, length of characters entered, date interval • Can have Input Message or Error Message • http://office.microsoft.com/en-us/excel/HA010346571033.aspx

  10. Exercise • Open both workbooks. Arrange as desired. Save As Workplace. Close & reopen. • Save a workbook as a web page. Open to see how it looks. • Practice hiding & unhiding rows & columns. • On Trick5-Validation tab, enter several School names. Name the range “Schools”. Then use the range name to validate the School value in cells D23 through D27.

  11. Customizing your workspace (1 of 3) • Tools, Customize – allows you to change how the toolbars look and what is displayed • Toolbars tab – decide which toolbars to display • Commands tab – decide which icons appear • Options tab – show full menus, show Screentips • To move icons on a toolbar • Select Tools, Customize, then go to the toolbar and drag-and-drop

  12. Customizing your workspace (2 of 3) • Tools, Options • View tab – display settings • General tab – Recently used file list, Sheets in new workbook, Standard font (name and font size), Default file location (where documents are saved), User name • Custom Lists • Save tab – Autosave time interval and location • Security – Password to open, Remove personal information

  13. Customizing your workspace (3 of 3) • Exercise – Add a Save As button and change its picture • Open Tools, Customize, Commands tab, <Rearrange Tabs> button • Click on ToolBar • From File category, add Save As & move below Save • Click on Modify Selection or right-click icon • Select Test only (in Menus) • Select Change Button Image; change to diskette • Select Edit Button Image; make changes • Close

  14. Macros (1 of 2) • Practice the steps to create the macro • Go to Tools, Macro, Record New Macro • Give it a name and a Shortcut key • Then record the steps to be included • When complete, click on the Stop button • Test the macro using shortcut • It is possible to EDIT the macro • Once a macro is run, you cannot undo the results

  15. Macros (2 of 2) • Exercise: Create a macro and a toolbar icon for it • Create a macro that will transpose a row of cells to a column • Using the Tools, Customize dialog box, add a new icon to the Formatting toolbar • Associate the icon to the macro • Change the picture on the icon • Test the icon

  16. Chart Wizard • Select the range of data • Use Insert, Chart or the icon for Chart Wizard • Select chart type and follow instructions on remaining wizard windows. • Modify the chart afterwards by manual changes or by using the Chart menu • Exercise – Add sales data for 2 more years. Change chart colors. Change the legend. Use the Chart menu to make other changes.

  17. Link data on multiple worksheets "=SUM('c:\Documents and Settings\wiertzem\ My Documents\[Budget.xls]Annual'!a1:a6)" Exercise – Use the example in Excel_Adv_IncomeStmt.xls in the Mar tab, add values for Income (Sales, Donations, Other) in column G. Use a formula to create the YTD values in column H.

  18. Pivot Tables (1 of 4) • An interactive view of your data • Think of it as a kaleidoscope • Why to use a pivot table: • You have large amounts of data to analyze and summarize • You need to find relationships in the data • You need to find data trends • You anticipate frequent requests for changes • You need to create subtotals that frequently change • You need to make charts of the data.

  19. Pivot tables (2 of 4) • Components: • The Data area – numbers being summarized • The Page area – fields used for filtering • The Column area • The Row area

  20. Pivot Tables (3 of 4) • Cautions: • Make sure your data is in tabular layout. • The first row is labels for column headings • Use unique headings • Do not use row labels • Each row is one individual data element • Eliminate gaps and empty cells • Apply appropriate formatting to the cells

  21. Pivot tables (4 of 4) • Use the Pivot Table Wizard • Select the range of data • Determine where to place the pivot table • Drag the row, column, and data labels • Changes to the data • Use the exclamation symbol (Refresh Data) • Only when the range of data has not changed

  22. Importing data • The easiest file type to import is tab-delimited text. • Open Excel, then Open the text file. • The wizard will walk you through the process. • Any field that contains a leading zero must be marked as “text” to avoid Excel dropping the leading zero. This includes Social Security numbers and Zip Codes. • Download Importing.txt. Import into Excel. Be careful of zip codes and SSNs.

  23. Internet Resources – Free Excel Templates Financial • http://www.swlearning.com/finance/moyer/excel.html • http://www.exinfm.com/free_spreadsheets.html • http://www.dotxls.com/ Educational • http://www.sabine.k12.la.us/class/excel_resources.htm Business • http://www.mhhe.com/business/opsci/aczel05/student/excel.mhtml Scientific • http://www.crystalball.com/resources/rec_templates.html Microsoft’s Office home page, Templates tab: http://office.microsoft.com/en-us/templates/FX100595491033.aspx?pid=CL100632981033

  24. Pivot Table Internet Resources • http://office.microsoft.com/training/training.aspx?AssetID=RC010136191033&pid=CR061831141033 • http://biostat.mc.vanderbilt.edu/twiki/pub/Main/TheresaScott/PvtTblsNChrts.Macros.pdf • http://www.ozgrid.com/Excel/PivotTables/ExCreatePiv1.htm • http://www.cpearson.com/excel/pivots.htm Go to Help, Microsoft Excel Help or use the F1 key

More Related