Microsoft Excel Notes

1. MicrosoftExcelNotes Part 1 Introduction to Microsoft Excel: What is a Spreadsheet?

2. Objectives (1 of 2) • Describe what a spreadsheet is and potential applications • Distinguish between a formula and a constant • Open, save, print a workbook; insert and delete rows and columns • Distinguish between a pull-down menu, shortcut menu and toolbar

3. Objectives (2 of 2) • Describe the three-dimensional nature of a workbook; distinguish between a workbook and a worksheet • Print worksheet with values or formulas • Use Page Setup to print worksheet and preview before printing

4. Overview • Introduction to Spreadsheets and Excel • Show wide diversity of spreadsheet applications • Fundamentals of spreadsheets using Excel • Worksheet recalculates automatically after changes

5. Spreadsheet Basics • Spreadsheet is a computerized ledger • Divided into Rows and Columns • Cell References • Constants--entries that do not change • Formulas--combination of constants and functions

6. Excel Basics • Common user interface of all Office applications • Worksheet is an Excel spreadsheet • Workbook contains one or more worksheets • Toolbars--Standard and Formatting • File menu--Save, Open and Print commands

7. Modifying the Worksheet • Insert and Delete Commands for both rows and columns • Page Setup Commands--Portrait versus Landscape, Margins, Header/Footer, and Sheet Tabs

8. Excel Features and Commands • Active cell • Formula bar • Shortcut menu • Status bar • Toolbars • ScreenTips • Edit Delete versus Edit Clear • Incompatible File Type from Excel • Save as Command

9. MicrosoftExcelNotes Part 2 Gaining Proficiency: Copying, Formatting, and Isolating Assumptions

10. Objectives • Explain importance of isolating assumptions • Define, select and deselect cell ranges • Copy and move cells; differentiate between relative, absolute and mixed addresses • Format a worksheet • Change column widths • Describe steps to create a financial forecast worksheet

11. Overview • Basic commands to create a worksheet • Use cell ranges and commands to build a worksheet • Isolate assumptions to easily evaluate alternatives • Improve appearance of worksheets • Accuracy is critical for spreadsheets

12. Getting around the Worksheet • A rectangular group of cells is a range • Copy duplicates contents of a cell from a source range to a destination range • Three types of addresses: absolute, relative and mixed • Move transfer contents of a cell

13. Formatting • Column widths • Row Heights • Numeric Format • Alignment • Fonts • Borders, Patterns, and Shading

14. General Number Currency Accounting Date Time Percentage Fraction Scientific Text Special Custom Numeric Formats

15. A Financial Forecast • Always isolate assumptions and initial conditions • Creating a financial forecast is a common task • Be aware that by isolating that is separating initial conditions you spreadsheet will be more valuable when changes occur

16. MicrosoftExcelNotes Part 3 Graphs and Charts: Delivering a Message

17. Objectives (1 of 2) • Know advantages and disadvantages of different chart types • Distinguish between an embedded chart and one in a separate chart sheet • Use the ChartWizard • Use arrows and text to enhance a chart

18. Objectives (2 of 2) • Differentiate between data series specified in rows versus ones in columns • Understand how charts can be accurate statistically yet misleading conceptually • Create a compound document consisting of word processing, worksheet and a chart

19. Overview • Business graphics one of most exciting Windows applications • Determine message of chart • Create charts using Chart Wizard • Plot multiple data sets on a single chart • Dynamically link a chart to a memo

20. Chart Types • Always remember to keep it simple • Pie and Exploded pie charts, effective for displaying proportional relationships • Column chart used for number display • Bar charts show numbers horizontally

21. Creating a Chart • Embedding in a worksheet or separate chart sheet • Use the Chart Wizard a four step process • Step one choosing a chart • Step two review data series • Step three final touches • Step four where to store • Enhance with Drawing toolbar or Chart toolbar

22. Multiple Data Series • What message do you want chart to display • Rows versus columns • If data series are in rows the Chart Wizard will use first row for X axis and use first column for legend text • If data series are in columns the Chart Wizard will use first column for X axis and use first row for legend text

23. Object Linking and Embedding • Primary advantage of Windows is to create a compound document • Embedded object is stored in the compound document • Linked object is stored in its own file and used with a compound document • OLE pronounced “Oh-lay”

24. Additional Chart Information • Excel has 14 standard chart types on the Chart Wizard • Line used to display time-related information • Combination uses two or more charts • Use Accurate Labels • Don’t add Dissimilar Quantities

25. MicrosoftExcelNotes Part 4 Spreadsheets in Decision Making: What If?

26. Objectives (1 of 2) • Use spreadsheets in decision making; use Goal Seek and Scenario Manager • Use PMT function • Use Paste Function • Use fill handle and AutoFill capability • Use pointing to create a formula

27. Objectives (2 of 2) • Use Average, Max, Min, and Count functions in a worksheet • Use the If function to implement a decision and explain how the Vlookup function is used • Print and view large spreadsheets

28. Overview • Spreadsheets are a tool for decision making • Use financial and statistical functions • Find desired end results with the Goal Seek command • Choose between solutions with Scenario Manager • Use relative and absolute cell references

29. Excel Features • Relative versus Absolute addressing in a worksheet • Using the fill handle to copy • Pointing to cell address for formulas or functions is more accurate • Using the Paste Function and the Formula Palette

30. Using Functions • Statistical Functions: MAX, MIN, AVERAGE, COUNT, and COUNTA • Use functions over arithmetic expressions • IF function enhances decision making • VLOOKUP(vertical lookup) Function and its use

31. Managing a Large Worksheet • Scrolling shows specific rows and columns • Freezing Panes keeps headings in sight • AutoFill capability enter series into adjacent cells • Scenario Manager enables evaluation of multiple conditions

32. MicrosoftExcelNotes Part 5 List and Data Management: Converting Data to Information

33. Objectives1 of 2 • Create a list • Add, edit and delete records in an existing list • Distinguish between data and information • Describe the TODAY function and use date arithmetic • Use the Sort command

34. Objectives2 of 2 • Use the database functions, DSUM, DAVERAGE, DMAX, DMIN, and DCOUNT • Use AutoFilter and Advanced Filter • Use the Subtotals command • Use a pivot table

35. Overview • Maintain data in a list • Fundamentals of list management • Display selected records • Sort the list • Use database functions, criteria range, and arithmetic • Excel or Access can be used for database management

36. List and Data Management • Data management is based on lists in Excel • Database concepts--record, fields, and primary keys • Need valid input to produce valid output--Garbage In Garbage Out (GIGO) • Editing the list through Insert Row and Columns command and Edit Delete

37. Important Commands • Data Form Command provides easy way to add, edit and delete records • Sort command arranges lists according to value in fields • Date Arithmetic is a powerful tool for formulas

38. Data Versus Information • Data is simply facts • Information is data arranged for a specific use • Decisions in an organization are based on information • Data commands, functions and reports provide information

39. Filter Commands • AutoFilter is a subset of records which meet a set of criteria • Advanced Filter allows for complex criterion and storing records in a separate worksheet area • Criteria range specifies the values to search for in records

40. Criteria Range • Must contain at least two rows--field names and a second row of values • Same row entries imply an AND condition • Values entered in different rows meet the OR condition • Empty rows return all records

41. Criteria Rangecontinued • Relational operators can be used to find a designated range • Upper and Lower Boundaries can be established • Equal and unequal signs select empty and nonempty records

42. Database Functions • Parallels statistical functions • DSUM • DAVERAGE • DMAX • DMIN • DCOUNT

43. Subtotals and Pivot Tables • Subtotals command in the Data menu computes subtotals based on data groups • Pivot tables extends the capability of database functions by presenting the data in summary form • Use PivotTable Wizard

44. MicrosoftExcelNotes Part 6 Consolidating Data: 3D Workbooks and File Linking

45. Objectives1 of 2 • Distinguish between a cell reference, worksheet reference, and a 3D reference; us appropriate references to consolidate data • Select and group multiple worksheets to enter common formulas • Explain the advantages of functions over formula

46. Objectives2 of 2 • Properly organize and document a workbook • Copy and Paste to another workbook • Distinguish between a source versus dependent workbook; create external references to link workbooks

47. Overview • Combine data from several sources into a summary report • Reconcile summary totals with detail totals • Workbook contains 3 branch office worksheets and 1 summary worksheet • OR workbooks for each branch office and summary workbook

48. 3D Workbook • Electronic equivalent of a 3 ring binder • Contains Worksheet tabs • Scrolling buttons allow easy movement amongst worksheets • Window menu allows for tiling, cascading options for multiple workbooks or worksheets

49. Worksheet References • Allows you to reference cells in other worksheets • Requires using the name of the worksheet before the cell range • Exclamation point separates worksheet and cell reference

50. 3-D References • Range that spans two or more worksheets in a workbook • Can be used in a Summary sheet • Requires worksheet names be separated using a colon and exclamation point to separate worksheet name from cell reference