This 3-hour Excel workshop covers three intermediate to advanced features that bring automation into your spreadsheets: formulas and functions, lookup functions and macros.
You'll start with the top 10 functions every user should know, including SUM, AVERAGE, COUNT, IF and its variants, date functions and text functions for cleaning imported data. Next come lookup functions, which beat searching for data by hand: VLOOKUP and its arguments, approximate matches, lookups across workbooks, handling #N/A with IFNA and IFERROR, HLOOKUP, and why INDEX and MATCH is often the better choice.
Finally, you'll create a macro with the built-in recorder, save a macro-enabled workbook, run it and make simple edits in VBA. No prior macro or VBA experience is needed.
WHY SHOULD YOU ATTEND?
A handful of functions, lookups and macros can save hours of manual work and make your spreadsheets perform better, whether you run a small business, work in an office or use Excel casually.
This workshop suits business owners, executives, managers, accountants and CPAs, financial consultants, auditors, bookkeepers, IT, HR and marketing staff, and anyone who works with large amounts of data in Excel.
AREA COVERED
Formulas and functions
- Statistical functions including SUM, AVERAGE, MAX, MIN and COUNT
- IF and its counterparts COUNTIF, SUMIF and AVERAGEIF
- Calculating dates with TODAY and NOW
- Text functions for imported data, including LEFT, RIGHT, LEN, TRIM and CONCATENATE
Lookup functions
- The arguments in the VLOOKUP function
- Why absolute references matter in lookup functions
- Approximate matches with VLOOKUP
- Simplifying multiple-field lookups with concatenation
- Looking up data from another workbook
- How VLOOKUP stops at the first match in a list
- Why VLOOKUP sometimes returns #N/A
- The IFNA function (Excel 2013 and later) and IFERROR for handling missing matches
- Horizontal matches with HLOOKUP
- Why INDEX and MATCH often beats VLOOKUP or HLOOKUP
- Finding an item's position with MATCH
Macros
- Workbook macros vs. personal macros
- Making macros available to all your workbooks
- Creating a macro with the macro recorder
- Saving a file as a macro-enabled workbook
- Running a macro
- Viewing the recorded macro in VBA
- Editing the macro
LEARNING OBJECTIVES
- Use the top statistical, logical, date and text functions in Excel
- Build VLOOKUP formulas with exact and approximate matches
- Troubleshoot #N/A results with IFNA and IFERROR
- Use HLOOKUP, INDEX and MATCH for more flexible lookups
- Record, save and run macros in a macro-enabled workbook
- View and edit recorded macros in VBA
WHO WILL BENEFIT?
- Business owners
- CEO's/CFO's/CTO's
- Managers
- Accountants
- CPA's
- Financial consultants
- IT professionals
- Auditors
- Human Resource personnel
- Bookkeepers
- Marketers
- Anybody with large amounts of Data
- Anybody who uses Microsoft Excel on a regular basis, and wants to be more efficient and productive
A handful of functions, lookups and macros can save hours of manual work and make your spreadsheets perform better, whether you run a small business, work in an office or use Excel casually.
This workshop suits business owners, executives, managers, accountants and CPAs, financial consultants, auditors, bookkeepers, IT, HR and marketing staff, and anyone who works with large amounts of data in Excel.
Formulas and functions
- Statistical functions including SUM, AVERAGE, MAX, MIN and COUNT
- IF and its counterparts COUNTIF, SUMIF and AVERAGEIF
- Calculating dates with TODAY and NOW
- Text functions for imported data, including LEFT, RIGHT, LEN, TRIM and CONCATENATE
Lookup functions
- The arguments in the VLOOKUP function
- Why absolute references matter in lookup functions
- Approximate matches with VLOOKUP
- Simplifying multiple-field lookups with concatenation
- Looking up data from another workbook
- How VLOOKUP stops at the first match in a list
- Why VLOOKUP sometimes returns #N/A
- The IFNA function (Excel 2013 and later) and IFERROR for handling missing matches
- Horizontal matches with HLOOKUP
- Why INDEX and MATCH often beats VLOOKUP or HLOOKUP
- Finding an item's position with MATCH
Macros
- Workbook macros vs. personal macros
- Making macros available to all your workbooks
- Creating a macro with the macro recorder
- Saving a file as a macro-enabled workbook
- Running a macro
- Viewing the recorded macro in VBA
- Editing the macro
- Use the top statistical, logical, date and text functions in Excel
- Build VLOOKUP formulas with exact and approximate matches
- Troubleshoot #N/A results with IFNA and IFERROR
- Use HLOOKUP, INDEX and MATCH for more flexible lookups
- Record, save and run macros in a macro-enabled workbook
- View and edit recorded macros in VBA
- Business owners
- CEO's/CFO's/CTO's
- Managers
- Accountants
- CPA's
- Financial consultants
- IT professionals
- Auditors
- Human Resource personnel
- Bookkeepers
- Marketers
- Anybody with large amounts of Data
- Anybody who uses Microsoft Excel on a regular basis, and wants to be more efficient and productive
