You are on the Home/Publications & Training/Business Data Management page
Google
Web This Site

Business Data Management with MS Excel – Table of Contents

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

 

Excel 2010, Excel 2007, data analysis, transform data to information

Comments