Harness the Power of Excel 2007 Part 2: Data Analysis

1. Harness the Power of Excel 2007 Part 2:Data Analysis Lynn Mann July 24, 2008 For audio call Toll Free 1-888-886-3951 and use PIN/code 114310

2. Housekeeping • Maximize your CCC Confer window. • Mute your phone (*6) if you have visitors or noise in your office. • Please note phone audio may be in presenter-only mode. • Ask questions over the phone when the presenter prompts. • Ask questions throughout presentation via the chat window. • Turn on or off Closed Captioning by clicking on the icon. • Save the presentation or chat by clicking on the icon.

3. Agenda Manage your data with built-in functions, or create your own formulas Use data from multiple sheets in your analyses Summarize data with PivotTables Create informative charts and embed them into reports and presentations Reduce repetitive work using Macros

4. Built-In Functions There are about 340 built-in functions categorized in the function library by type of function • Financial ▫ Logical • Date & Time ▫ Lookup & Reference • Math & Trig ▫ Text • Database • More Functions ◦ Statistical ◦ Engineering ◦ Cube ◦ Information

5. Common Built-In Functions • Financial – FV, PMT • Logical – AND, IF • Date & Time – DATE, NOW • Lookup & Reference – HYPERLINK, VLOOKUP • Math & Trig – SUM, PRODUCT • Text – CONCATENATE, PROPER • Statistical – AVERAGE, STDEV • Engineering – CONVERT

6. Creating Functions • + Addition • - Subtraction • * Multiplication • / Division • Combination of built-in and/or user formulas

7. User Defined Function • Programming is required

8. Analysis of Data on Multiple Sheets • 3-D Referencing within a workbook • Used in formula, syntax is =‘WorksheetRange’!CellRange • Ex: ‘Quarter1’!B7 or ‘Quarter1:Quarter4’!H2:H5 • Grouping Sheets • Click the first sheet to group, hold Shift (for adjacent) then click on the last sheet you want to group or Ctrl (for nonadjacent) click on each of the sheets you want to add to the group. • Hyperlinking within a workbook • On the Insert tab, in the Links group, click Hyperlink. • Select Place in this Document in the Link to: menu • Type the cell and select the sheet of the linked location • Type the Text to Display, click OK.

9. PivotTables A PivotTable is an interactive table that will allow us to group and summarize data quickly and easily. • Click in the table or select the range of data • On the Insert tab, click the PivotTable button • Click the Select a table or range option button and verify the reference in the Table/Range box • Choose either New Worksheet or Existing Worksheet and select a starting cell • Click OK • Select the fields you want to add to the PivotTable by clicking on the check boxes or drag fields to the box in the layout section

10. Charts Charts allow us a visual representation of a set of data showing trends or relationships of data that are more difficult to see by looking at numbers. • Select the data source with the range of data you want to chart • Click Insert tab, in the Charts group click a chart type, and select a chart subtype in the Chart Gallery. • On the Chart Tools Design tab, in the Location group, click Move Chart button to place the chart on it’s own worksheet

11. Selecting a Chart Type Each chart type will display data a specific way therefore it’s important to know which chart will display your information in a meaningful way.

12. Types of Charts

13. Linking Charts One of the many benefits of using Microsoft Office suite is that the applications work nicely with one another. You can link an Excel chart from one workbook into PowerPoint presentation, Word document or another Excel workbook. Upon any changes in the source the chart will be updated.

14. Macros A macro is a series of stored commands that can be run whenever you need to perform the recorded task. If you have macros in your workbook you will need to save your workbook as Excel Macro-Enabled Workbook .xlmx To add Developer tab to your Ribbon, click on Office button, Excel Options, on the Popular menu check Show Developer tab in the Ribbon, click OK.

15. Recording/Running Macros Recording: • Click the Developer tab, in the Code group, (or on the View tab), click RecordMacro button. • Enter a name for the Macro, and specify the location to store the macro. • Specify a shortcut key, if you’d like. • Enter a description of the macro. • Click OK to begin recording. • Perform the tasks you want to automate. • Click Stop Recording button. Running: • Press the shortcut key you assigned the macro OR • Click the Developer tab, in the Code group, click Macro button. • Select the macro from the list of macros, and click Run button.

16. Editing Macros • Programming is required

17. Deleting Macros • Do one of the following: • Open the workbook that contains the macro that you want to delete. • If the macro that you want to delete is stored in the personal macro workbook (Personal.xlsb), and this workbook is hidden, do the following to unhide the workbook: • On the View tab, in the Window group, click Unhide. • Under Unhide workbooks, click PERSONAL, and then click OK. • On the Developer tab, in the Code group, (or on the View tab), click Macros. • In the Macros in list, select the workbook that contains the macro that you want to delete. For example, click This Workbook. • In the Macro name box, click the name of the macro that you want to delete. • Click Delete.

18. Q&A Lynn Mann lmann@msjc.edu 951-487-3524

19. Evaluation Survey Link http://tinyurl.com/5qrxg2

20. Thanks for Attending For upcoming desktop seminars and links to recently archived seminars, check the @ONE Web site at: http://www.cccone.org/seminars/index.php