Microsoft Excel Notes Part 1 Introduction to Microsoft

Microsoft Excel Notes Part 1 Introduction to Microsoft Excel: What is a Spreadsheet? MS Excel Notes 1

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 MS Excel Notes 2

Objectives (2 of 2) • Describe 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 MS Excel Notes 3

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

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 MS Excel Notes 5

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 MS Excel Notes 6

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

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

Microsoft Excel Notes Part 2 Gaining Proficiency: Copying, Formatting, and Isolating Assumptions MS Excel Notes 9

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 MS Excel Notes 10

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 MS Excel Notes 11

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 MS Excel Notes 12

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

Numeric Formats General Number Currency Accounting Date Time MS Excel Notes Percentage Fraction Scientific Text Special Custom 14

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 MS Excel Notes 15

Microsoft Excel Notes Part 3 Graphs and Charts: Delivering a Message MS Excel Notes 16

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 Chart. Wizard • Use arrows and text to enhance a chart MS Excel Notes 17

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 MS Excel Notes 18

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 MS Excel Notes 19

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 MS Excel Notes 20

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 MS Excel Notes 21

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 MS Excel Notes 22

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” MS Excel Notes 23

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 MS Excel Notes 24

Microsoft Excel Notes Part 4 Spreadsheets in Decision Making: What If? MS Excel Notes 25

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 Auto. Fill capability • Use pointing to create a formula MS Excel Notes 26

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 MS Excel Notes 27

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 MS Excel Notes 28

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

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 MS Excel Notes 30

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

Microsoft Excel Notes Part 5 List and Data Management: Converting Data to Information MS Excel Notes 32

Objectives 1 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 MS Excel Notes 33

Objectives 2 of 2 • Use the database functions, DSUM, DAVERAGE, DMAX, DMIN, and DCOUNT • Use Auto. Filter and Advanced Filter • Use the Subtotals command • Use a pivot table MS Excel Notes 34

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 MS Excel Notes 35

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 Edit Delete MS Excel Notes 36

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 formulas MS Excel Notes 37

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 MS Excel Notes 38

Filter Commands • Auto. Filter 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 MS Excel Notes 39

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 MS Excel Notes 40

Criteria Range continued • 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 MS Excel Notes 41

Database Functions • • • MS Excel Notes Parallels statistical functions DSUM DAVERAGE DMAX DMIN DCOUNT 42

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 Pivot. Table Wizard MS Excel Notes 43

Microsoft Excel Notes Part 6 Consolidating Data: 3 D Workbooks and File Linking MS Excel Notes 44

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

Objectives 2 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 MS Excel Notes 46

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 MS Excel Notes 47

3 D 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 MS Excel Notes 48

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 MS Excel Notes 49

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 MS Excel Notes 50

Documenting the Workbook • Helpful to document a workbook with a documentation worksheet • Contains vital descriptive information making it easier to read for all • Formatting ability will improve appearance MS Excel Notes 51

Linking Workbooks • Retain information in separate workbooks • Linking uses external references • Dependent workbook requires external data from source workbooks MS Excel Notes 52

Microsoft Excel Notes Part 7 Automating Repetitive Tasks: Macros and Visual Basic MS Excel Notes 53

Objectives 1 of 2 • Define a macro • Record and run a macro, view and edit a simple macro • Use the Input. Box statement • Use a keyboard shortcut, and/or custom toolbar to execute a macro MS Excel Notes 54

Objectives 2 of 2 • Describe function of Personal Macro workbook • Use the Step Into command to execute a macro one statement at a time • Use Copy and Paste to duplicate an existing macro • Use Visual Basic IF and DO statements to make decisions MS Excel Notes 55

Overview • Use macros to avoid repetitious tasks • Macro is a set of instructions for Excel • Macro instructions are written in the Visual Basic Programming Language • Use the macro recorder to create macros • Create more powerful macros MS Excel Notes 56

Introduction to Macros • Macro recorder stores Excel commands • Macros are written in Visual Basic • Macros can be displayed with Visual Basic Editor • Use Project Explorer to locate macro modules • Statements appear in Code window MS Excel Notes 57

Relative vs Absolute Cell Addresses • Make sure to specify cell references • Absolute is constant; relative changes • Visual Basic uses Offset to indicate space from active cell MS Excel Notes 58

Loops and Decision Making • Including IF and Do statements allows for testing • If statement tests a condition and provides a positive and negative • Do statement repeats a block of statements until a condition becomes true MS Excel Notes 59