1

Introduction

Focus of book: Business data organization, data acquisition, transformation into information, information presentation

2

Data Organization

Common Techniques and weaknesses; Principles of good data organization; basic concepts of relational database design

3

Acquire, view, (limited) export of data

Data entry by hand

Relation between data storage and data input/display;

Utility for transforming between computer/user-friendly layout

Improving the user interface, reducing errors – validation, templates, forms;

Utility for automated creation of data entry form

4

Data from flat files

Real time access to a database

Real time web queries

Opening text files, CSV files, etc.

Exporting to text files (Earl’s utility)

MS Query, SQL, ODBC, OLAP

XL as a database

Utility to use ODBC to access closed XL files

Examples: Stock tracking, optionally and if practical: USPS zip code lookup, FedEx/UPS package tracking, search Google

Utility for faster updates than the Excel default frequency

5

Transformation into Information

Native functions and tools

Sort

6

Slice and Dice – I

Autofilter and Advanced Filter

7

Slice and Dice - II

PivotTables, Slicers

8

The power of names and formatting a formula

Named ranges, named formulas

Value to self-documentation

Look forward to dynamic charts

9

Array formulas

Types; uses; regression analysis

13

Slice and Dice - III

MS Query, relational lookups with XL worksheets!

15

Slice and Dice – IV

PowerPivot and > 1,000,000 records

10

UDFs

Introduction to UDF

Capabilities and limits and interaction with XL’s calculation chain

11

Advanced concept– UDF as an array formula

UDF as an array formula – an underused power technique

12

Text handling :Regular Expressions

Value of regular expression

Convert flat file reports into relational data tables; Text cleaning; Name handling

14

Advanced Concept – Macro support for XL-native capability

Macros to fine-tune database access, enhance PivotTable, PivotChart features

16

Advanced Concept – Regression analysis

17

Optimization & set matching

Payment-Invoice matching; Goal Seek, Solver; Risk (scenario) management;

18

Information Presentation

Worksheet as the UI

Treating a worksheet as a form

19

Emailing XL files

Utility to strip formulas, email range / sheet in variety of formats

20

Charts

Creating; exporting to PP; dynamic charts

21

Custom Reporting

Customer specific tables, charts; using a PivotTable as a stepping-stone

22

Analysis Templates

Apply a custom analysis (Report) template to multiple data sets

23

Chart presentation styles

Dashboard, sparklines, stacked charts, drilldown charts

24

Advanced Concept – Charting “outside the box”

Worksheet as a chart; chart formatting; Some important examples; Worksheet as chart table

25

Advanced Concept – cross-program support

Live update of PP slide with XL data; use Office Web Table/Chart