Excel Basics: Foundations • Formulas • Graphs

PART 1 BASIC SKILLS
Chapter 1 Getting Started
1.1 Introduction to Excel Spreadsheets
Cell Contents
Entering Data into a Cell
Exercises 1.1
1.2 Two Essential Skills – Highlighting and Copying
Selecting Cells by Highlighting
Copying Cell Contents
Exercises 1.2
1.3 Opening, Saving, Retrieving, and Printing Spreadsheets
Opening a New or Existing Spreadsheet
Saving a Spreadsheet
Printing a Spreadsheet
Exercises 1.3
1.4 Professional Spreadsheet Guidelines
Input and Results Sections
1.5 Common Excel Errors
Common Errors
1.6 Chapter Appendix: The Excel Development System
Using Excel’s Options
Using the Excel Ribbon Tabs Toolbar
Appendix Exercises
Chapter 2 Formulas and Formatting
2.1 Formulas
From Expressions to Formulas
Graphically Selecting Cells
Copying Formulas
Expression Construction Rules
Exercises 2.1
2.2 Introduction to Excel Functions
Highlighting Selected Arguments
Exercises 2.2
2.3 Formatting
Exercises 2.3
2.4 Cash-Flow and Cash-Balance Spreadsheets
A Cash-Balance Spreadsheet
Exercises 2.4
2.5 Absolute Addresses
Case 1: Magic Numbers as Input Values
Case 2: Running Sub-Totals
Exercises 2.5
2.6 Common Excel Errors
2.7 Chapter Appendix: Working with Rows and Columns
Adjusting Column and Row Widths
Inserting and Deleting Columns and Rows
Chapter 3 Functions and Conditional Formatting
3.1 Date, Time, and Text Functions
Text and Character Functions
Exercises 3.1
3.2 Statistical Functions
Standard Deviations
Exercises 3.2
3.3 Financial Functions
The PMT( ) function
Repayment Schedules: Amortization
Exercises 3.3
3.4 Information (IS) Functions
3.5 Conditional Formatting
Creating a Simple Conditional Formatting Rule
Creating a User-Entered Conditional Value
Exercises 3.5
3.6 Common Excel Errors
3.7 Chapter Appendix: Transferring Data Between Sheets
Chapter 4 Selection
4.1 Relational Expressions
Logical Functions
Exercises 4.1
4.2 The IF( ) Function
Selecting a Text Value
Selecting a Numeric Value
Selecting a Calculation
Nested IF( ) Functions
Logical Conditions
Exercises 4.2
4.3 The VLOOKUP( ) Function
Exact Match
Give It a Try
Approximate (Closest) Match
Exercises 4.3
4.4 The HLOOKUP( ) Function
Give It a Try
Exercises 4.4
4.5 Common Excel Errors
4.6 Chapter Appendix: Testing and Debugging
PART 2 PRESENTATIONS
Chapter 5 Graphical Presentations
5.1 Introduction
Exercises 5.1
5.2 Creating Column and Bar Charts
Changing a Column Chart’s and Bar Chart’s Elements and Appearance
Changing Chart Types
Exercises 5.2
5.3 Creating Pie Charts
Changing a Pie Chart’s Elements and Appearance
Changing Chart Types
Exercises 5.3
5.4 Creating Line Graphs
Changing a Line Graph’s Chart Elements and Appearance
Changing Chart Types
Exercises 5.4
5.5 Trend Lines and Scatter Diagrams
Creating a Scatter Diagram
Changing a Scatter Diagram’s Elements and Appearance
Adding a Trend Line
R2 and Selecting an Appropriate Trend Line
Exercises 5.5
5.6 Common Excel Errors
5.7 Chapter Appendix: Creating Histograms
Procedure to Produce a Histogram in all Excel Versions
Chapter 6 PivotTables
6.1 Introduction
Basic PivotTable Elements
Exercises 6.1
6.2 Creating a Basic PivotTable
Additional Row and Summary Column Examples
Single Row Field and Single Value Field
Multiple Row Fields and Single Value Fields
Single Row Fields and Multiple Value Fields
Multiple Row Fields and Multiple Value Fields
Exercises 6.2
6.3 Adding Column Fields
Additional Column Field Examples
A Single Set of ROW and COLUMN Fields
Two Sets of Row Fields and a Single Set of Column Fields
Exercises 6.3
6.4 Common Excel Errors
6.5 Chapter Appendix: PivotTable Design Criteria
PART 3 INTERMEDIATE SKILLS
Chapter 7 Sorting and Searching
7.1 Records as Rows
Exercises 7.1
7.2 Sorting Spreadsheet Rows
Multi-Level Sorting
Exercises 7.2
7.3 Creating Group Subtotals
Nested Subtotals
Creating Nested Subtotals
Exercises 7.3
7.4 Searching (Filtering) for Selected Records
More Complex Searches
OR condition
Multiple ANDs on the same Criteria Range Row
Multiple AND Conditions on the same Criteria Range Row
Criteria employing both AND and OR conditions
Exercises 7.4
7.5 Database Functions
Exercises 7.5
7.6 Common Excel Errors
7.7 Chapter Appendix: Spreadsheets as Databases
Chapter 8 Intermediate and Advanced Techniques
8.1 Creating Drop-Down Lists
Exercises 8.1
8.2 Correcting Rounding Errors
8.3 Creating De-Duping Keys for Removing
Duplicate Records
8.4 Array Functions - The SUMPRODUCT( ) Function
Exercises 8.4
8.5 User-Entered Conditional Formatting Formulas
Exercises 8.5
8.6 Chapter Appendix: Restricting Cell Access
Appendix A Character Code Storage
Appendix B Installing the Analysis ToolPak
Index