Beginners to Expert Excel and Excel VBA 38 Hours Mega Course

BY
Udemy

Develop an understanding of the basic as well as advanced tools and functionalities of Microsoft Excel and Excel VBA including tables, charts, and more.

Mode

Online

Fees

₹ 3999

Quick Facts

particular details
Medium of instructions English
Mode of learning Self study
Mode of Delivery Video and Text Based

Course overview

Beginners to Expert Excel and Excel VBA 38 Hours Mega Course online certification is created by Mach 42 Productions - Excel Consulting Solutions and presented by Udemy which is aimed at applicants who want to learn how to understand, write, use, and audit formulas in Microsoft Excel, as well as coverage of Excel text functions, math operators, and advanced functions such as vlookup. From inception, the Beginners to Expert Excel and Excel VBA 38 Hours Mega Course online classes by Udemy seek to teach a comprehensive grasp of tables and charts, including bar charts, line charts, waterfall charts, and funnel charts.

Beginners to Expert Excel and Excel VBA 38 Hours Mega Course online training is divided into 11 sections that span 38 hours and are accompanied by 28 articles and 227 downloadable learning resources. It starts with the fundamentals of MS Excel and VBA and progresses to topics such as creating waterfall charts, automating excel charts, spreadsheet protections, and much more. This course also includes an assignment that assesses the applicants' knowledge and understanding of MS EXcel's basic functionality for activities such as data analysis and data validation.

The highlights

  • Certificate of completion
  • Self-paced course
  • 38 hours of pre-recorded video content
  • 28 articles
  • 227 downloadable resources
  • Assignments

Program offerings

  • Online course
  • Learning resources. 30-day money-back guarantee
  • Unlimited access
  • Accessible on mobile devices and tv

Course and certificate fees

Fees information
₹ 3,999
certificate availability

Yes

certificate providing authority

Udemy

What you will learn

After completing the Beginners to Expert Excel and Excel VBA 38 Hours Mega Course online training, applicants will gain a deeper understanding of both the fundamental and advanced concepts of Microsoft Excel and VBA. Applicants will learn about Excel's numerous tools and capabilities, including cells, charts, and pivot tables. Applicants will learn how to utilize Excel to perform data analysis and data validation. Additionally, candidates will learn about Vlookup, condition formatting, and condition testing, as well as several Excel security tools for protecting spreadsheet reports.

The syllabus

Introduction to course!

  • Introduction to the Excel course
  • Get your personalised training program
  • How to access the course discussion forum
  • Handy List of Excel Shortcuts

Excel worksheet operations- the basics

  • A very quick overview of an Excel workbook
  • Excel worksheet operations- the basics
  • Sorting Excel worksheet tabs
  • The Excel ribbon tab
  • Customising the Quick Access Toolbar and Excel Ribbon
  • Your Excel exercises for the Excel ribbon
  • Changing the look of Excel- Office Themes
  • Resizing Excel columns
  • Hiding and Unhiding Excel Columns
  • Hiding and Unhiding Excel Rows
  • Hiding and Unhiding Non Sequential Excel Rows and Columns
  • How to zoom in and out on an Excel spreadsheet
  • Splitting a worksheet view in Excel
  • Using Freeze Panes part 1
  • Using Freeze Panes part 2
  • Filling in data using Auto Fill
  • Excel Auto Fill exercises
  • The Excel skill of spotting errors
  • A quick note about Auto Fill in Excel
  • Using Flash Fill in Excel to fill in data
  • Flash Fill exercises
  • Printing pages in Excel properly
  • Using Excel's status bar
  • How to use Excel's Spell Checker Properly
  • Good Excel Spreadsheet Practice
  • Student Handout: Excel Essentials 'How to' Sheet Summary of Section

How to use copy, cut and paste features in Excel

  • An overview to Copy, Cutting and Pasting Excel data
  • Examples of Copy/Cut/Paste
  • More Worked Examples
  • Yours Tasks for this Section
  • How to cut, copy and paste rows and columns
  • How to copy only visible cells in Excel

Excel Comments- How to use them

  • Adding comments to Excel worksheets
  • Quiz on Comments in Excel
  • Deleting Excel comments all at the same time
  • Changing the colour of Excel comments
  • Resize comments in Excel
  • How to use Excel comments properly

Sorting data in Excel

  • Overview of sort
  • Sorting data by numbers
  • Sorting data by cell background colour
  • Sorting data by font colour
  • Using more than one rule to sort data
  • Conclusion to Sorting in Excel section
  • Your Sorting Tasks
  • How to sort in reverse order with data that has no logical order

Finding and Replacing Data in Excel

  • Finding and replacing data in Excel
  • Finding and replacing data for selected columns
  • Quiz on finding and replacing data

Text Formatting in Excel

  • Applying formatting to text
  • Applying formatting to text part 2

Applying a format quickly to other cells

  • Applying a format quickly to other cells
  • Quiz on Format Painter

Tables and Excel filtering- the basics

  • Making a table
  • Using filters in tables, removing duplicate data and resetting a table
  • Your turn! Preparing a table for a report
  • How to copy a table from MS Excel to MS Word

Data Validation in under 20 minutes

  • Data Validation Part 1
  • Data Validation Part 2

Formulas Part 1- Understanding syntax and basic maths

  • Formulas- An Introduction
  • Formula Syntax Breakdown
  • Formulas syntax quiz
  • Back to basics overview
  • Learning to add in Excel
  • Excel adding quiz
  • Multiplying in Excel
  • Excel multiplication quiz
  • Learning to subtract in Excel
  • Excel subtraction quiz
  • Dividing in Excel
  • Excel division quiz

Formulas Part 2- Understanding more formula syntax and formula references

  • Brackets and references- Quick intro
  • Using brackets() in Excel
  • Excel brackets quiz
  • Understanding absolute and fixed references
  • Best way for editing Excel absolute and relative references
  • Excel references quiz
  • Excellent key to use when writing Excel formulas
  • How the F-lock key looks like on a typical keyboard

Formulas Part 3- Learn how to write formulas

  • Formulas Part 1
  • Formulas Part 2
  • Using Excel's Auto Fill for formulas
  • Formulas Worked Example 1
  • Formulas Worked Example 2
  • Formulas Worked Example 3
  • Formulas Worked Example 4
  • Formulas Further Examples 4.1
  • Formulas Worked Example 5
  • Formulas - Your tasks for this section

Formulas Part 4- If then else statements

  • Introduction to if then else statements
  • Logical operators
  • If then else statements Worked Example 2
  • If then else statements Worked Example 3
  • Your tasks for this section

Formulas Part 5- Multiple conditions testing in Excel

  • The Excel AND operator
  • The Excel OR operator
  • Your tasks
  • Using ifs statements

Formulas Part 6- How do I know which formula to use?

  • Formula Helper Part 1
  • Formula Helper Part 2

Formulas Part 7- Excel Formula Error Messages

  • Excel Error Messages- What do they mean?
  • Excel error messages quiz

Formulas Part 8- How to audit any formula

  • Audit that formula!
  • Auditing formula quiz

Formulas Part 9- Useful text functions to know

  • Getting the length of a string
  • Strings length quiz
  • Finding text within a string
  • Finding text quiz
  • Finding text within a string part 2
  • Searching text quiz
  • Extracting text from a string
  • Extracting text quiz
  • Getting rid of excess spaces
  • Trimming text quiz
  • Joining text from different Excel cells
  • Joining text quiz
  • Worked Example- How do you separate first and last names?
  • Separating first and last names quiz
  • How to extract initials from a full name in Excel

Formulas Part 10- Data analysis Excel functions to know

  • How to sum a range of values based on one or more criteria
  • Sum if quiz
  • How to retrieve a value based on a lookup of another value
  • A vlookup() key concept that every Excel user must know
  • Vlookup() and column sorting
  • Vlookup() exercises
  • Isna() task
  • Vlookup() with Dynamic Columns
  • Excel jobs demand for vlookup() as an employable skill
  • Vlookup quiz
  • The Isblank() function- Determining if a cell is blank or not
  • Isblank() - Two examples of its typical use
  • Using dropdown lists and vlookup to create user friendly lookups
  • Building in advanced error checking into our friendly vlookup()
  • Using Index() and Match() functions to lookup data
  • Index/Match exercises
  • How to use HLookup()
  • How to calculate working days
  • Adding working days to a date!
  • Working days quiz
  • Using the Excel Aggregate function()
  • How to add Excel comments to formulas within the actual formula
  • Two quick tips for entering formulas
  • How to make formulas easier to type and read by inserting breaks
  • Calculating Results Direct in the Formula Bar in Excel

Formulas Part 11- Advanced Vlookup()

  • Advanced Vlookup()- Using the 'true' parameter to reduce the calculation time
  • Evolving our Vlookup()

Charts- The basics part 1

  • What is a chart?
  • How to quickly make a bar chart
  • Adjusting a bar chart
  • Presenting a bar chart
  • Presenting a bar chart part 2- Worked Examples
  • Test on charts

Charts- The basics part 2

  • Making a Clustered Column Excel Chart
  • How to make a 100% Stacked Bar Chart in Excel
  • How to make a Pie Chart in Excel
  • How to create 'before' and 'after' effect Excel charts
  • Before and After charts quiz
  • Why 3D charts are bad
  • 3D Charts quiz
  • Making sparkline Excel charts
  • Sparklines quiz
  • Making text based charts
  • Text based charts quiz
  • Variations of text based charts
  • Scales and charts- why they matter

Charts Intermediate level Part 1- Colour changing and thermometer charts

  • How to make dynamic colour changing bar charts
  • How to make a thermometer chart in Excel Part 1
  • How to make a thermometer chart in Excel Part 2
  • How to make thermometer chart in Excel Part 3

Charts Intermediate level Part 2- Handy chart techniques,funnel and gantt charts

  • Sorting Excel Chart Bars Automatically- Overview
  • Sorting Excel Chart Bars- Method 1
  • Sorting Excel Chart Bars- Method 2
  • Sorting Excel Chart Bars- Solving Equal Rank
  • Making an Excel Funnel Chart in Excel 2016
  • How to make a Gantt chart in Excel

Charts Intermediate Level Part 3- How to make Excel waterfall charts

  • How to make a waterfall chart in Excel 2016- Overview
  • How to make the monthly sales Excel chart
  • Waterfall basics quiz
  • How to make a personal expense and income Excel chart
  • How to make a monthly Excel business expense/income chart
  • Making an Excel waterfall chart with 3 totals

Charts Intermediate Level Part 4- How to make a gauge chart in Excel

  • How to make a gauge chart in Excel

Charts Advanced level- Creating an Excel Timeline Chart

  • How to make an Excel timeline chart part 1
  • How to make an Excel timeline chart part 2

Charts Advanced level- Creating a dynamic chart

  • Our dynamic chart !
  • Named ranges
  • The min function
  • The max function
  • Min() and Max() quiz
  • The offset function part 1
  • The offset function part 2
  • Spinners
  • The & symbol
  • The & Symbol quiz

Charts Advanced level- Creating a dynamic chart part 2

  • Building our dynamic chart part 1
  • Building our dynamic chart part 2
  • Building our dynamic chart part 3

Charts Advanced level- Creating a dynamic chart part 3

  • Overview of our Excel annotated automated sales chart
  • How to annotate our Excel sales chart
  • How to turn on/off our Excel chart annotations dynamically

Conditional Formatting

  • Conditional Formatting part 1
  • Conditional Formatting Part 2
  • Highlighting the top ten performers in an Excel table- Part 1
  • Highlighting the top ten performers in an Excel table- Part 2
  • Conditional Formatting Tasks
  • How to highlight alternate rows in Excel
  • How to highlight alternate columns in Excel
  • How to highlight even numbered rows and columns
  • Using conditional formatting to highlight percentage change

Pivot Tables

  • PivotTables - An overview to our section and our first case study
  • PivotTables Preparation - Cleaning the data
  • PivotTables overview quiz
  • PivotTables - Constructing our Table Part 1
  • PivotTables- Constructing our Table Part 2
  • PivotTables- Quick note before the next lecture
  • PivotTables- Building our table using the Classic Layout / View
  • Showing Values as Percentages in PivotTables
  • PivotTable Slicers and Timelines - An introduction via another case study
  • Grouping , Sorting and Adding Slicers to PivotTables
  • Adding Excel Timelines to PivotTables
  • Linking an Excel Timeline to more than one PivotTable
  • Adding an Excel Slicer to more than one PivotTable
  • Conditional formatting for PivotTables
  • PivotTables- Another worked example and your task
  • PivotTable task walkthrough
  • Replacing blank values in PivotTables

Excel Reporting Skills- Essential Tips for Excel Reports

  • How to do bullet points and new lines in the same cell
  • An alternative way to do bullet points
  • Handy special characters to use in Excel Reports
  • How to capitalise/de-captalise your text- quickly
  • How to make text properly capitalised
  • Proper and Upper/Lower quiz
  • How to add a watermark in Excel
  • Watermark quiz
  • How to customise numbers to display as currency in Excel
  • Format quiz
  • Formatting numbers as percentages
  • Generating random numbers in Excel
  • How to extract the name from an email address using Excel functions
  • How to find merged cells in Excel quickly
  • How to show a percentage for the amount of work done
  • How to check if an Excel cell has a formula in it
  • How to generate random dates between two specified dates in Excel
  • How to get a random list in Excel
  • How to work out an average excluding zero in Excel
  • How to work out the average dynamically based on the last 5 days sales
  • How to calculate the weighted average in Excel
  • How to work out the maximum value based on selected criteria in Excel
  • How to calculate the minimum value based on selected criteria in Excel
  • How to get dates as days in Excel
  • How to add shapes in Excel
  • How to add Hierarchy diagrams in Excel
  • How to add Cycle flow diagrams in Excel
  • How to add a Funnel diagram in Excel
  • How to quickly locate cells which have formulas in Excel
  • How to get a weekday from a date using Excel functions
  • How to determine the fiscal month from a date in Excel
  • How to return a formula as text in Excel 2013/Excel 2016
  • Returning formulas as strings quiz
  • How to increment numbers as part of text in Excel
  • How to search by colour in Excel
  • Searching Excel by other ways
  • How to bookmark Excel spreadsheets quickly for easy reference
  • Joining formulas and text: An In-depth look
  • Printing Excel reports- and making them look professional
  • Resizing comments in Excel using VBA Part 1
  • Resizing comments in Excel using VBA Part 2
  • Resizing comments in Excel using VBA Part 3
  • Using Excel Charts to Quickly Check Data for Outliers
  • How to display the last time a workbook was edited
  • Using vlookups() to calculate money owed by clients
  • How to work out an average excluding zero
  • How to Assign Equal Rank to Two or More Items Without Jumping Rank

Excel Security- Protect your workbooks

  • Protecting your worksheet
  • Passwording your worksheet quiz
  • Adding a password to a workbook
  • Quick note on unpasswording lecture
  • How to unpassword your Excel workbook
  • Quiz on workbooks
  • How to protect the structure of a workbook
  • Hiding Excel sheets from plain sight
  • Quiz on hiding sheets
  • Choosing a good password
  • Use Excel 2013 onwards

Making Excel run faster

  • Setting a higher priority for Excel in Windows
  • Higher priority Excel quiz
  • Understanding how to use the calculation feature in Excel
  • Keeping the Excel window in focus
  • Summary of ways to turbo charge Excel

How to stop one Excel workbook from crashing another

  • The problem- One Excel workbook crashing another- live example
  • A very simple explanation of how Excel loads workbooks
  • The solution- How to stop one Excel workbook from crashing another

Introduction to VBA

  • Excel VBA Introduction
  • Excel VBA Handouts

Setting up Excel for VBA use

  • Adding the Developer tab in MS Excel and how to run the example VBA codes
  • Exploring the VBA editor

The Basics of VBA

  • How to run the VBA code from our Excel course
  • Variables and data types part 1
  • Variables and data types part 2
  • Variables and data types test
  • VBA Functions
  • Functions assignment
  • Subroutines Part 1
  • Subroutines Part 2
  • Subroutines knowledge test
  • Operators in VBA
  • Using if then else statements to make decisions in VBA
  • Using for loops

VBA Object Model Explained

  • VBA Object Model Part 1
  • Give an example of a method for a 'Car' Object and a Property too
  • VBA Object Model Part 2
  • Short test for our coding example
  • VBA Object Model Part 3
  • VBA Object Model Part 4
  • Quick test on R1C1 notation attributes
  • VBA Object Model Part 5
  • VBA Object Model Part 6
  • VBA Object Model Part 7

Debugging Code in VBA

  • Debugging Code in VBA Part 1
  • Debugging Code in VBA Part 2

Building 'Real Life' Macros

  • Project 1 - Making a macro to clean data
  • Using the Object Browser in VBA
  • Project 2 - Making a Macro to Sort Excel Worksheets Alphabetically Part 1
  • Project 2 - Making a Macro to Sort Excel Worksheets Alphabetically Part 2
  • Project 3 - A VBA Macro to Colour Cells which contain Formulas
  • Project 4 - Copying a worksheet from one open workbook to another open workbook
  • Project 5 - Getting the last used row of data automatically in Excel using VBA
  • For Each Loops- How to use them
  • How to Exit a For loop Early
  • Project 6 - Writing an Excel VBA function to get a total based on user criteria
  • How to Make Your VBA Code More Robust
  • How to Increase Your Excel VBA Coding Productivity Part 1
  • How to Increase Your Excel VBA Coding Productivity Part 2
  • How to Make a Reverse For Next Loop Part 1
  • How to Make a Reverse For Next Loop Part 2
  • How to use a Do Until Loop
  • Project 7 - How to Delete Rows of Data in VBA
  • Project 7.1 - Deleting rows of data with more than one criteria
  • Project 8 - How to copy data from one Excel worksheet to another
  • Evolving our macro using Application CutCopyMode
  • Project 9 - Exporting an Excel worksheet as a PDF

Excel Career Tips- Use your skills to do better

  • How to write MS Excel skills on your CV/Resume
  • Different jobs that use Excel and the CV/Resume areas you can focus on
  • Excel career tips for Graduates with little or no work experience
  • Power words to use on your CV/Resume for Excel examples
  • Optimising your Excel CV/Resume for online jobs
  • Excel Careers lectures wrap up

General IT Career Tips

  • Overview of section
  • Get your business card sorted
  • Activities/tasks for business cards
  • Its good to give
  • Company reports
  • Activities/tasks for Company Reports lecture
  • Take on any IT project
  • Activities/tasks for IT projects lecture
  • Experiment with new technologies
  • Activities/tasks for 'Experiment with new technologies'
  • Learn how to talk the 'talk'
  • Activities/tasks for Learn how to talk the 'talk' lecture
  • Joining peer societies
  • Start a blog/Join forums
  • Activities/tasks for blog/forums
  • IT Career checklist

The end!

  • Wrap up lecture to our Excel course

Trending Courses

Popular Courses

Popular Platforms

Learn more about the Courses

Download the Careers360 App on your Android phone

Regular exam updates, QnA, Predictors, College Applications & E-books now on your Mobile

Careers360 App
150M+ Students
30,000+ Colleges
500+ Exams
1500+ E-books