| |
|
|
| |
EVENT DETAILS |
Please RSVP this class at http://nycdatascience.com/course/excel-intermediate-level/
Sign up for the newsletter for free Data Science learning material and upcoming classes at http://nycdatascience.com/
Sign up for NYC Open Data Meetup for free workshops twice/week!
http://www.meetup.com/NYC-Open-Data/
Date: April 26th, May 3rd, 10th, 17th, 24th (5 Saturdays)
Time: 9:00am - 1:00pm
Instructor: Mary DiPerna, Instructor from NYU
Venue (one of the following locations, we will confirm ASAP):
241 Centre Street, 6th floor, New York, NY (SOHO)
500 7th Ave, 17th floor, New York, NY (close to Times Square)
This five week, intermediate level, course goes from multi-sheet/file formulas to creating a looping macros that transform volumes of information with a single click.
Project Demo Day and Certificates
From the rudimentary building blocks of programming basics, to data manipulation and use of advanced drawing packages, the course ends with a demonstration of a project of your choice on Project Demo Day. On Demo Day you will access and analyze real data, utilizing the tools and skill sets taught to you throughout the course. After the successful completion of the course, you will qualify for one of three certificates: Extraordinary Standing pass, Honorable Graduation pass, and Active Participation pass. Certificates are awarded according to your understanding, skill, and participation.
Syllabus
4 hours: Multi-sheet/File Operations
How to work with complex Excel workbooks
Set up and format multiple sheets
3D formulas
Formulas with external references
Advanced Copy and Paste
Absolute formulas
Range Names
2 hours: Data Referencing and Transformation
Use lookup functions to pull specific data
Exact and inexact match variations - Hlookup, Vlookup, Choose, Match
Logical and information functions - If, IsErr, IsNA, IsBlanks
String manipulation functions - Find, Replace, Substitute, Left, Right, Mid
2 hours: Data Visualization
Create charts
Change legends, labels, titles, axes, and datapoint formating
Overlay chart
Data segmentation chart
Scatter chart with trend line
Adding data to charts
6 hours: Data Summarization
Maximize efficiency when using Excel as a database
Conditional Formats
Sort, Subtotal, Outline, Filtering
Dget, Dsum, Dcount
Pivot tables and summary sheets
Data validation
Combine If and Array formulas
Slivers
2 hours: Data Analysis
Single and dual variable sensitivity analysis
Finding targets by varying inputs
What-If analysis
Autofill
Goal Seek
Scenario Manager
4 hours: Data Automation
Record a macro to automate repreated actions
Macro Recorder
Absolute vs Relative Record
Use Visual Basic - enhance macros, using the debugger, decision making and looping commands
If we finish the class early, we will cover selected topics based on your need.
Time-related functions:
Use time related functions to calculate accurate work availably and monthly calculations, use time-value functions to determine current value of even and uneven streams of payment as well as internal rates of return. Case studies and exercises: Create formulas to accurately calculate workdays within a time period. Use time-value formulas to analyze the costs of various projects based on streams of payments and assumed interest rates.
Additional Shortcut Tools:
Explore additional features for quickly formatting and changing lists and providing multiple reporting views in a single worksheet, use tools to trace errors and track dependencies. Case studies and exercises: Convert an existing list to a table and add formulas and formats and de-dupe the list. Work with a multi-year spreadsheet and create monthly, quarterly and annual reports. Work with an existing file and trace formulas to identify possible errors.
Additional VBA:
Use techniques to interact with the user, learn about error trapping, learn additional looping commands as well as how to build structured macros with arguments that change the effect of the routine. Case studies and exercises: Add routine to allow the user to select specific files and build error routines to trap when incorrect files are selected. Create a looping routine that can be called in different situations using varying arguments to change the result of the sub-routine.
* If, IsErr, IsNA and IsBlank logical functions
* Find/Replace/Substitute /Left/Right/Mid string manipulation functions
Data Visualization: 2 hours
Abstract: create and modify Excel charts and learn when to use which type of visualization, learn to change legends, data points labels, titles, axis and data point formatting, as well as add data to existing charts .
Case studies and exercises: Create an overlay chart and data segmentation chart and add data to existing charts.
* Overlay chart
* Data segmentation chart
* Scatter chart with trend line
* Data Point
* Data Series
* Scale, Format, Alignment
* add data
Data Summarization: 6 hours
Abstract: maximize efficiency when using Excel as a database (Lists), highlight distribution, sort, filter, and subtotal, build data validation for more consistent data, create time-saving pivot tables and summary sheets that dynamically summarize subsets of information.
Case studies and exercises: Work with volumes of data and create data validation and median formulas that easily summarize different groupings within the data list. Create three different types of summaries using subtotals, functions and Pivot Tables.
* Conditional Formats
* Sort, Subtotal, Outline
* Filtering
* Data Validation
* Dget, Dsum, Dcount
* Combine If and Array formulas
* Pivot Tables
* Slicers
Data Analysis: 2 hours
Abstract: how to perform single and dual variable sensitivity analysis, find a specific target by varying input and perform What-if analysis and store multiple situations in a single file.
Case studies and exercises: Using an existing data analysis sheet, create single and dual variable sensitivity analyses. Use the scenario manager to maintain Best, Worst and Likely scenarios in a single file.
* One/Two variable Data Tables
* Autofill
* Goal Seek
* Scenario Manager
Data Automation: 4 hours
Abstract: record a macro to automate repeated actions, introduce Visual Basic to enhance recorded macros and broaden their functionality, practice using the Visual Basic debugger to step through and debug macros, incorporate decision making and looping commands that allow the macro to repeat.
Case studies and exercises: Record a simple macro and use VB debugger. Adjust the recorded macro to make it more flexible. Create a looping macro to transform volumes of information with a single click.
* Macro Recorder
* Absolute/Relative record
* Debugger
* Selection.Offset * Activecell * If * *Do While* MsgBox *
(If we finish the class early, we will cover selected topics based on your need)
Time-related functions: Abstract: use time related functions to calculate accurate work availably and monthly calculations, use time-value functions to determine current value of even and uneven streams of payment as well as internal rates of return. Case studies and exercises: Create formulas to accurately calculate workdays within a time period. Use time-value formulas to analyze the costs of various projects based on streams of payments and assumed interest rates.
* Edate * EoMonth * Workday * Weekday *
PMT * RATE * NPER
* PV * FV * NPV * XNPV
* IRR * XIRR *
Additional Shortcut Tools: Abstract: explore additional features for quickly formatting and changing lists and providing multiple reporting views in a single worksheet, use tools to trace errors and track dependencies.
Case studies and exercises: Convert an existing list to a table and add formulas and formats and de-dupe the list. Work with a multi-year spreadsheet and create monthly, quarterly and annual reports. Work with an existing file and trace formulas to identify possible errors.
* Tables
* Table Styles
* de-dupe a list
* Custom Views
* Advanced Printing options
* Select Special
* Trace precedents/dependents
* Circular References
Additional VBA:
Abstract: use techniques to interact with the user, learn about error trapping, learn additional looping commands as well as how to build structured macros with arguments that change the effect of the routine.
Case studies and exercises: Add routine to allow the user to select specific files and build error routines to trap when incorrect files are selected. Create a looping routine that can be called in different situations using varying arguments to change the result of the sub-routine. * On Error * Goto * Application.Dialogs * Option Explicit * Declare * For Next * Arguments *
|
|
|
|
|
|
