Automating Reports with

possehastyΜηχανική

5 Νοε 2013 (πριν από 3 χρόνια και 9 μήνες)

73 εμφανίσεις

Automating Reports with


Advanced Excel Tricks


Presented by:

Mark Baker


Assoc. Registrar

Whitworth University

NWDUG 2012

2012

NWDUG

Automating Reports

Want to increase your efficiency?

2012

NWDUG

Automating Reports

Two Common Scenarios

1) Repetitive formatting steps

2) Recreating calculations & charts

2012

NWDUG

Automating Reports

Repetitive Formatting Steps

Open text
file

Separate
columns

Fit column
widths

Format
header row

Conditional
formatting

More…

2012

NWDUG

Automating Reports

Recreating Calculations & Charts

Data from Query

Pivot Table and Chart based on Data

2012

NWDUG

Automating Reports

The Solution

1) Recorded Macros

2)
Referenced Data Sheets

2012

NWDUG

Automating Reports

Recorded Macros

Then we’ll record one

Enroll_Sched Macro

Let’s watch it

2012

NWDUG

Automating Reports

Info about Macros


Record in Personal.xlsx


Every action is recorded


Can be edited later


Must unhide Personal.xlsx to edit


Powerful…be careful

2012

NWDUG

Automating Reports

Referenced Data Sheets

Then we’ll create one

Pivot Chart.xlsx

Let’s see it

2012

NWDUG

Automating Reports

Info about Referenced Data Sheets


Create data & working workbooks


Reference other workbook (or sheet)


Example formula
=
'
C:
\
NWDUG
\
Pivot
Chart
\
[Pivot Chart Data.xlsx]
Sheet1
'!
A2


Include extra rows


Name datasheet consistently


Add calculations, formatting, pivot tables, charts


Data will refresh, pivot will not


2012

NWDUG

Automating Reports

Putting It All Together


Utilization Report

Using macros and data sheets together!


Utilization Report Example

2012

NWDUG

Automating Reports

Questions?

Automating Reports with Advanced Excel Tricks