|
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
|
Opening text files, CSV files, etc.
Exporting to text files (Earl’s utility)
|
|
5
|
|
Real time access to a database
|
MS Query, SQL, ODBC, OLAP
XL as a database
Utility to use ODBC to access closed XL files
|
|
6
|
|
Real time web queries
|
Examples: Stock tracking, optionally and if
practical: USPS zip code lookup, FedEx/UPS package tracking, search
Google
Utility for faster updates than XL default
frequency
|
|
7
|
Transformation into Information
|
Native functions and tools
|
|
|
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
|
Filter, Sort, PivotTables, MS Query, relational
lookups with XL worksheets!
|
|
|
|
Introduction to OLAP
|
Address Excel Services?
|
|
15
|
|
Optimization & set matching
|
Payment-Invoice matching; Goal Seek, Solver;
Risk (scenario) management;
|
|
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
|
Information Presentation
|
Worksheet as the UI
|
Treating a worksheet as a form
|
|
18
|
|
Emailing XL files
|
Utility to strip formulas, email range / sheet
in variety of formats
|
|
19
|
|
Charts
|
Creating; exporting to PP; dynamic charts
|
|
20
|
|
Custom Reporting
|
Customer specific tables, charts; using a
PivotTable as a stepping-stone
|
|
21
|
|
|
|
|
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
|
