Automating Reports with Advanced Excel Tricks

1. Automating Reports with Advanced Excel Tricks Presented by: Mark Baker – Assoc. Registrar Whitworth University NWDUG 2012

2. Automating Reports Want to increase your efficiency? 2012 NWDUG

3. 1) Repetitive formatting steps Automating Reports 2) Recreating calculations & charts Two Common Scenarios 2012 NWDUG

4. Automating Reports Repetitive Formatting Steps 2012 NWDUG

5. Data from Query Pivot Table and Chart based on Data Automating Reports Recreating Calculations & Charts 2012 NWDUG

6. 1) Recorded Macros Automating Reports 2) Referenced Data Sheets The Solution 2012 NWDUG

7. Automating Reports Enroll_Sched Macro Let’s watch it Recorded Macros Then we’ll record one 2012 NWDUG

8. Record in Personal.xlsx • Every action is recorded • Can be edited later • Must unhide Personal.xlsx to edit • Powerful…be careful Automating Reports Info about Macros 2012 NWDUG

9. Automating Reports Pivot Chart.xlsx Let’s see it Referenced Data Sheets Then we’ll create one 2012 NWDUG

10. 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 Automating Reports Info about Referenced Data Sheets 2012 NWDUG

11. Automating Reports Using macros and data sheets together! Utilization Report Example Putting It All Together – Utilization Report 2012 NWDUG

12. Automating Reports Questions? Automating Reports with Advanced Excel Tricks 2012 NWDUG