The #1 Resource for NYC Tech

   
  Sign up / Sign in  
 
    
 
Sponsored Event
Saturday, Apr 26 (09:00 AM) @ Venue
 
     

     

     

 
Details
Permalink:   http://gary.to/uqt612q
 
Cost:  $850
 
URL:  Click here for Event Website
 
Location: 
Venue, 241 Centre St, 6th Fl, New York
 
 
 
Description
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 *
 
Weekly Email Newsletter
Sign up for our awesome NYC Tech Events weekly email newsletter! :)  
 
 
- FEATURED IN -
 
© 2014 GarysGuide   About   Terms    Press   Feedback