| |
| |
| |
| Introduction | |
| |
| |
| What is Excel? | |
| |
| |
| Why Are We Here? | |
| |
| |
| A Decision-Making Tool | |
| |
| |
| An Overview of Excel | |
| |
| |
| Ribbons, Tabs, and Icons | |
| |
| |
| The File Menu | |
| |
| |
| Excel 97-2003 File Format | |
| |
| |
| Right Click and Control Keys | |
| |
| |
| Settings and Status Bar | |
| |
| |
| Excel Help | |
| |
| |
| |
| Excel Basics | |
| |
| |
| Data Management | |
| |
| |
| Data Entry | |
| |
| |
| Auto Fill | |
| |
| |
| Adjusting Columns and Rows | |
| |
| |
| Hiding Columns and Rows | |
| |
| |
| Copy and Paste | |
| |
| |
| Sorting Data (Single Level) | |
| |
| |
| Sorting Data (Multiple Levels) | |
| |
| |
| Formatting | |
| |
| |
| Data Formats | |
| |
| |
| Data Alignment | |
| |
| |
| Number Formats | |
| |
| |
| Borders (Line Formats) | |
| |
| |
| Cell Colors (Fill Color) | |
| |
| |
| Editing | |
| |
| |
| Editing Data in a Cell | |
| |
| |
| Moving Data | |
| |
| |
| Deleting Columns and Rows | |
| |
| |
| Deleting Worksheets | |
| |
| |
| Inserting Columns and Rows | |
| |
| |
| Inserting, Moving, and Renaming Worksheet Tabs | |
| |
| |
| Printing | |
| |
| |
| Page Setup | |
| |
| |
| Printing a Worksheet | |
| |
| |
| |
| Calculating Data: Formulas and Functions | |
| |
| |
| Formulas | |
| |
| |
| Basic Formulas | |
| |
| |
| Copy and Pasting Formulas (Relative Reference and Paste Special) | |
| |
| |
| Complex Formulas | |
| |
| |
| Auditing Formulas | |
| |
| |
| Basic Functions | |
| |
| |
| Basic Statistical Functions | |
| |
| |
| AutoSum | |
| |
| |
| Absolute References (Turning Off Relative References) | |
| |
| |
| Financial Functions | |
| |
| |
| The Future Value Function | |
| |
| |
| The Payment Function | |
| |
| |
| The Function Library | |
| |
| |
| |
| Evaluating Data | |
| |
| |
| Logical Functions (The IF Function) | |
| |
| |
| IF Function | |
| |
| |
| Nested IF Function | |
| |
| |
| Logical Functions (AND OR) | |
| |
| |
| AND Function | |
| |
| |
| OR Function | |
| |
| |
| Combining AND, OR, and IF Functions | |
| |
| |
| Lookup Functions | |
| |
| |
| VLookup Function | |
| |
| |
| HLookup Function | |
| |
| |
| |
| Presenting Information with Charts | |
| |
| |
| Creating Charts | |
| |
| |
| Column Charts (Data Comparisons) | |
| |
| |
| Stacked Column Charts (Percent to Total Over Time) | |
| |
| |
| Line Charts (Trends Over Time) | |
| |
| |
| Pie Charts (Percent to Total) | |
| |
| |
| Formatting Charts | |
| |
| |
| Titles and Legends | |
| |
| |
| Plot Area and Data Series | |
| |
| |
| X- and Y-Axes Labels | |
| |
| |
| Adding Annotations and Objects | |
| |
| |
| Advanced Chart Options | |
| |
| |
| Defining the X- and Y-Axes Manually | |
| |
| |
| The Scatter Plot Chart | |
| |
| |
| Pasting Charts into PowerPoint and Word | |
| |
| |
| |
| Applying Core Competency Skills: Financial Planning and Accounting | |
| |
| |
| Linking Data | |
| |
| |
| Linking Worksheets and Workbooks | |
| |
| |
| Updating Workbook Links | |
| |
| |
| SUMIF Function | |
| |
| |
| Financial Planning | |
| |
| |
| Financial Assumptions | |
| |
| |
| Freeze Panes | |
| |
| |
| Financial Data | |
| |
| |
| Cash Analysis (The NPV Function) | |
| |
| |
| Financial Accounting | |
| |
| |
| T-Accounts | |
| |
| |
| Journals | |
| |
| |
| Ledgers | |
| |
| |
| Trial Balance and Work Sheets | |
| |
| |
| Statements | |
| |
| |
| |
| Date, Text and Error Functions | |
| |
| |
| Date Functions | |
| |
| |
| Today | |
| |
| |
| Year | |
| |
| |
| Month | |
| |
| |
| Date and Day | |
| |
| |
| Text Functions | |
| |
| |
| Concatenate | |
| |
| |
| Search, Left, Len and Mid | |
| |
| |
| IS Functions | |
| |
| |
| Is Number and Is Text | |
| |
| |
| Is Error | |
| |
| |
| |
| Managing Large Volumes of Data | |
| |
| |
| PivotTables | |
| |
| |
| Creating A New PivotTable | |
| |
| |
| Adding Fields and Changing Field Settings | |
| |
| |
| Applying Filters to Fields | |
| |
| |
| Adding and Removing Report Totals | |
| |
| |
| Adding Formulas (Calculated Fields) | |
| |
| |
| Sorting Data | |
| |
| |
| PivotCharts | |
| |
| |
| Tools for Selecting and Summarizing Data | |
| |
| |
| Filters | |
| |
| |
| Subtotals | |
| |
| |
| Adding the Developer Tab | |
| |
| |
| List Boxes | |
| |
| |
| |
| Scenarios and Advanced Statistics | |
| |
| |
| Scenario Tools | |
| |
| |
| Scroll Bar | |
| |
| |
| Conditional Formatting | |
| |
| |
| Data Tables | |
| |
| |
| Goal Seek | |
| |
| |
| Scenario Manager | |
| |
| |
| Data Analysis Tools | |
| |
| |
| Add-Ins | |
| |
| |
| Descriptive Statistics | |
| |
| |
| Histograms | |
| |
| |
| Regression | |
| |
| |
| Solver | |
| |
| |
| Defining Constraints | |
| |
| |
| Defining Solver Parameters | |
| |
| |
| |
| Advanced Excel: Importing External Data | |
| |
| |
| Importing Text Files | |
| |
| |
| Importing Delimited Text Files | |
| |
| |
| Importing Fixed Width Text Files | |
| |
| |
| Importing Data from a Database | |
| |
| |
| Database Queries | |
| |
| |
| Editing a Database Query | |
| |
| |
| Advanced Database Queries (Microsoft Query) | |
| |
| |
| Importing Data from the Internet | |
| |
| |
| Web Queries | |
| |
| |
| Appendix Macros and VBA | |
| |
| |
| Macros | |
| |
| |
| Recording a Macro | |
| |
| |
| Action Buttons | |
| |
| |
| Editing Action Buttons | |
| |
| |
| VBA (Visual Basic for Applications) | |
| |
| |
| The Visual Basic Editor | |
| |
| |
| Adding a Procedure | |
| |
| |
| Defining Variables | |
| |
| |
| Referencing and Assigning Values in Cell Locations | |
| |
| |
| For Next Loops | |
| |
| |
| If Statements | |
| |
| |
| Running a Program | |
| |
| |
| Initiating VBA Code through User Events | |
