Table of Contents

1 Preliminary Notes

2 Further Formulas and Functions

2.1 Errors in a worksheet
2.2 Text category
2.3 Date & Time category
2.4 Add-Ins
2.5 Math & Trig category
2.6 Statistical category
2.7 Logical category
2.8 Lookup & Reference category
2.9 Nested functions
2.10 Arrays
2.11 Exercise
2.12 Working with a lookup function
2.13 Protecting cells
2.14 Unprotecting documents
2.15 Exercise 1
2.16 Exercise 2
2.17 Custom number formats
2.18 Conditional formatting
2.19 Index function
2.20 Exercise

3 Working with Data Lists

3.1 General information for creating a list
3.2 Complex sorting using a dialog box

4 Working with Data Validation

4.1 Setting data validation criteria
4.2 Checking data validation
4.3 Applying data validation to other cells

5 Goal Seek

6 Consolidate

7 Scenario Manager

7.1 Task
7.2 Working with estimates
7.3 Opening the Scenario Manager
7.4 Creating a scenario report
7.5 Deleting a scenario
7.6 Exercise

8 Solver

8.1 An application example of the Solver
8.2 Exercises

9 PivotTable

9.1 What is a PivotTable?
9.2 Creating a data list
9.3 PivotTable Tools
9.4 Creating the PivotTable report
9.5 Advanced PivotTable features
9.6 Changing the PivotTable
9.7 Rearranging rows and columns
9.8 Hiding contents
9.9 Displaying extreme values
9.10 Exercise
9.11 PowerPivot

10 Outlining Excel Data

10.1 Sample spreadsheet
10.2 Showing and hiding cell ranges
10.3 Removing the outline
10.4 Customizing levels and ranges
10.5 Exercise

11 Subtotals

11.1 Exercise

12 Charts

12.1 Car suspension
12.2 Exercises
12.3 Break-even analysis
12.4 Sparklines

13 Macros – Task Automation

13.1 Recording a macro
13.2 Running a macro
13.3 Opening a workbook with macros
13.4 Exercise

14 Creating a Custom Function

14.1 Procedures
14.2 Custom function features
14.3 The custom function: gross amount
14.4 Using the custom function
14.5 Exercise

15 Data Import and Export

15.1 Using the clipboard to exchange data
15.2 OLE and DDE
15.3 Object Linking and Embedding – OLE
15.4 Dynamic Data Exchange – DDE
15.5 Exercise
15.6 Importing graphics
15.7 Editing imported graphics
15.8 Picture Tools
15.9 Preparing a document for sharing

16 Templates

16.1 The benefits of a template
16.2 Creating a template
16.3 Saving the template
16.4 Using the template for a new workbook
16.5 Exercise

17 Forms

17.1 Validation and cell protection
17.2 Controls
17.3 Other controls (calendar)
17.4 Format
17.5 Print
17.6 Exercise: scroll bar

18 Microsoft Office 2010 Web Apps

19 Performing Settings in Excel

19.1 Options
19.2 Customizing the Quick Access Toolbar

20 Appendix: Solutions

21 Subject Index