Advanced Excel Formulas for Project Management

Ways of Working

 

Description

This course will give you the key Excel Formula shortcuts, tips, tricks & techniques you need to improve your Project management calculations work and reporting efficiency.

 

This session will cover:

  • Shortcut tips, tricks & techniques with formulas & nested calculations
  • Using conditional formulas to summarise activities / project expenditure data
  • Linking project data in different sheets / files using lookup formulas
  • Date calculations in Excel - accommodating weekends, public holidays & custom exclusion dates for duration calculations

 

Requirements:

Must have a computer with a Windows or Mac based desktop installed version of Excel. The Excel version required is MS Excel 2016 or higher including Excel for Office 365

 

Event Agenda

8:00am-8:05am: Chapter Announcements

 

8:05am – 9:05am:

  • Creating efficient formulas using the box method – shortcuts and benefits
  • Formula summarization techniques in Excel using sumif calculations
  • Formula range selection shortcut & shortcut for getting back to top of range in calculations
  • Knowing when to use absolute & relative references in calculations & shortcut to cycle through referencing in calculations
  • Exercise in creating data summarization using formulas
  • Proofing the calculation’s accuracy – nonformula techniques

 

9:06am – 10:25am:

  • Joining data from multiple columns – concatenation techniques for descriptive fields needed in reports
  • Date calculations in Excel network days & the international variant
  • Calculation of project / activity duration adjusting for weekends, public holidays & custom exclusion dates

 

10:26am-10:40am: Break

 

10:41am-11:00am:

  • Date calculations in Excel cont’d

 

11:01am - 12:00pm:

  • Linking project data in different sheets / files using lookup formulas – vlookup, xlookup
  • Using data tables with lookup formulas to automate joining data from different sources
  • Eliminate space errors in lookups using TRIM
  • Shortcuts specific to Vlookup formula
  • Doing matrix lookup using xlookup
  • Final Q&A

 

PDU's:

Cancellation policy:

All cancellations are subject to a minimum $20 fee if done during the early bird period. Cancellations after the early bird or event starts are subject to full charge to ensure the Chapter does not incur excessive expenses. All refunds will be issued after two credit card-billing cycles. If you have any questions regarding the event, please send inquiries to profdev@pmisfbac.org.

 

 

 

About the Speaker

 

Areef-Ali.jpeg

Areef Ali

Areef Ali is a Microsoft certified trainer, business productivity consultant and entrepreneur. He has over twenty-seven (27) years of diverse work experience. He is a member of ICATT, ACCA & serves on the Georgetown Chamber of Commerce & Industry Trade & Investment Committee.

 

He worked as an auditor with a big four accounting firm and then served as a management reporting professional in the Energy sector. He was subsequently appointed the Chief Accountant and then the Corporate Finance Head at a leading financial institution. In June 2006, Areef started his own training and consulting business.

 

AAABS does training and completes projects for numerous organizations, both private and public sector in MS Excel work productivity techniques, Financial / business modeling, Power BI reporting, Management reporting automation, Audit & Fraud automation techniques and Dashboard development.

 

He is a Microsoft certified trainer at the Expert level in Excel and is also a certified Power BI consultant / trainer. He has trained with and hosted public seminars with many world-renowned experts including Bill Jelen - Mr. Excel & professional bodies such as the ACFE.

 

In April 2017, Areef launched the “AA Success Centre” - a corporate retreat, meeting, training and events rental Centre. Areef’s aim and passion is to inspire individuals and organizations to find success.

 

 

It is no longer possible to register for this event

It is no longer possible to register for this event

Information

Type of category: Virtual Course/Training

Type of activity: Ways of Working, Business Acumen

Date: April 1st, 2023

Hour: 8:00AM to 12:00PM

Registration close date: March 31st, 2023 at 11:59PM

# of PDUs: 4

Price

Members: $115.00

Non members and Guests: $125.00

Location

Virtual event