Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Business Friendly Excel Automation (Formula + Macro/VBA)

Name: Business Friendly Excel Automation (Formula + Macro/VBA)
Rating: 4.3 (30 reviews)

Why Wait? Just Automate! - Start Learning Excel Automation with Our Easy to Follow Course

Created byMichael Joon Seng Goh, PhD (Eng)

Last updated 4/2021

English

English [Auto],

What you'll learn

Excel Formula
Excel Macro
Programming Logic
Process Automation
Report Automation
Excel Consolidation
Smart Excel workbooks for business
Smart Excel workbooks for operations
Smart Excel workbooks for finance
SAP automation with Excel
Email automation with Excel

Course content

2 sections • 18 lectures • 1h 55m total length

Overview3:07
This lecture series will cover my pick of top formulas that helped me automate the majority of business processes I have encountered. Most formulas are taught with relatable business applications to help you hit the ground running.
Remember to download the course material (Excel files) provided to follow along with the video.

The following videos will go over:
Basics: Mathematical Operators, If/Else, And/Or, List, Iferror/ifblank/ifnumber
String Manipulation: Concatenation, cutting strings
Sumif: Sumif and Sumifs
Index & Match combination
Offset
Sumproduct
Indirect Reference
Basics11:42
1. Basics: Mathematical Operators, If/Else, And/Or, List, Iferror/ifblank/ifnumber
This video will cover the basics of Excel formulas and rudimentary logic formulas as well as how to make dropdown list.
Feel free to skip ahead to the next video if you are already familiar with all the formulas used here.
=if()
=and()
=or()
=iferror()
=ifblank()
=ifnumber()
String Manipulation7:52
2. String Manipulation: Concatenation, cutting strings into segments
This video covers methods used to combine multiple strings/text as well as methods used to extract parts of a string such as separating a name into first and last names
If & Ifs (Sumif, Countif, Sumifs, Countifs)9:14
3. If & ifs: Sumif, Countif, Sumifs, Countifs
Counting and/or calculating the sum of a list of values based on a certain criteria.
For example, finding the total sales of a selected sales person.
Index Match7:24
4. Index & Match combination
A more flexible and powerful version of lookup/vlookup/hlookup.
Simultaneously find data in two tables based on its row header and column header
Offset5:01
5. Offset
Working together with Index & Match, Offset allows you to handle complicated but well structured tables.
Here it is applied to a table with two row headers (Branch & Product) and two column headers (Quarters & Months)
Sumproduct2:43
6. Sumproduct
Can be used sum the results from multiplying columns.
Used here as a 2D sumif or sumif where the criteria range is multiple columns
Indirect Referencing6:38
7. Indirect Referencing
Programmatically get data from other sheets. Set up reference to one sheet and easily replicate to as many other sheets. Also more stable than cell linking.

Overview2:16
This lecture series will cover my pick of top Excel Macros and/or functions that helped me automate the majority of business processes I have encountered. Most Macros are taught with relatable business applications and some Macros are provided as easily reusable templates.
Remember to download the course material (Excel files) provided to follow along with the video.

The following videos will go over:
Basics: Hello World, get and set range/cells
Range Management
Copy & Paste
For Loops
If Else Logic
Importing Data from other workbooks (Excel files)*
Sending Email*
SAP Automation using Excel Macro*

*Easily Reusable Templates
Before You Get Started - Setting Up the Developer Tab0:34
How to enable the "Developer" tab/ribbon in Excel for easy access to Visual Basic Console and running your Macros
Basics9:32
1. Basics
This video will cover the obligatory "Hello World" as well as the basics of Macro/VBA programming.
We will look at the .range and .cells method to get values, set values and set formulas.
Range Management4:08
2. Range Management
This video covers how to get the number of rows and columns used in a worksheet.
This video also shows how to get the number of rows for a specified column and number of columns in a specified row.
Copy & Paste6:07
3. Copy & Paste
This video covers how to copy and paste a range using the general copy paste as well as "paste special" methods such as pasting values and pasting formats.
This video also covers fill down and fill right to replicate a formula or value similar to how you would drag a cell across a range.
For Loops8:20
4. For Loops
This video covers how to use for loops with the example demonstrating a nested for loop (a loop in a loop) to perform scenario analysis varying two variables.
If Else Logic7:33
5. If Else Logic
This video covers how to use "If Else" logic in Excel Macro/VBA
Import Data7:00
6. Importing Data from other workbooks (Excel files)
This video demonstrates how to programmatically import a specific sheet from a different workbook (file) as well as provides a template for you to build your own data importer.
Sending Email6:42
7. Sending Email
This video shows how the provided email sending macro is used to programmatically send emails via Outlook to a list of recipients with content specific to each recipient.
The video also explains how to adapt and modify the macro for your own use.
Automating SAP with Excel VBA9:10
8. SAP Automation using Excel Macro
This video shows how to automate SAP processes using SAP's script recorder and the provided macro template.

Requirements

Basic operation of Microsoft Excel
Access to Microsoft Excel 2013 and above

Description

This lecture series will cover my pick of top Excel Formulas, Macros and/or functions that helped me automate the majority of business processes I have encountered such as consolidating data from multiple workbooks, sending emails with data from Excel, data cleanup, handling of complicated data structure, SAP automation, etc.

The two series is taught assuming basic Excel operation knowledge suitable for business users with little to no programming background.

Most of the materials are taught with relatable business applications and some Macros are provided as easily reusable templates to help you get started in automating your own business processes.

The course is separated into two video series, one for Formulas and another for Macro / VBA. Each series has 7 or 8 bite size videos which get progressively more difficult and may require learnings from a previous video.

Two versions of Excel .xlsx and .xlsm workbooks are also provided; one for you to follow along the video and attempt the Formulas/Macros for yourself and one with the solutions along with detailed commentary to help you understand how to use each Formula/Macro taught.

Video Series 1, Excel Formulas:

1. Basics: Mathematical Operators, If / Else, And/Or, List, Iferror / ifblank / ifnumber

2. String Manipulation: Concatenation, cutting strings

3. Sumif: Sumif and Sumifs

4. Index & Match combination

5. Offset

6. Sumproduct

7. Indirect Reference

Video Series 2, Excel Macros:

1. Basics: Hello World, Get and Set Range / Cells

2. Range Management

3. Copy & Paste

4. For Loops

5. If Else Logic

6. Importing Data from other workbooks (Excel files)*

7. Sending Email*

8. SAP Automation using Excel Macro*

*With Easily Reusable Templates

Who this course is for:

Business users looking to improve productivity
Business users wanting to learn how to automate their existing and future Excel workbooks
Business, Operations and Finance users who want to automate tedious manual Excel work
People who want to start using Robotic Process Automation (RPA) but want start small with existing infrastructure

Business Friendly Excel Automation (Formula + Macro/VBA)

What you'll learn

Explore related topics

Course content

Excel Formulas8 lectures • 54min

Excel Macro or Visual Basic for Application10 lectures • 1hr 1min

Requirements

Description

Who this course is for: