Excel for All: Beginner to Experts

All round use of Excel Explained with examples
3.7 (8 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
190 students enrolled
$25
Take This Course
  • Lectures 84
  • Contents Video: 8.5 hours
    Other: 29 mins
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2014 English

Course Description

Microsoft Excel is by far the most widely used and useful software there is! It is the most basic tool that is used when dealing with data - data in any format.

This simple yet sophisticated tool has a use for everyone, from keeping a simple tab your daily expenses, to analyzing and presenting a complex profit and loss statement, from keeping a tab on attendance of students to analyzing and presenting data on world hunger!

Excel when used intelligently makes for one of the most powerful yet simple reporting engine. Such is the power of Excel. But despite this, Microsoft Excel in most cases is not used to its full potential even though it one of the most easiest skill to pick up.

With this course, aimed at beginners, intermediate and Expert users we begin by introducing the basic functionality of this software and then work upwards, along the way covering very simple tips that will profoundly increase your speed and efficiency while using Excel.

At a high level the course is structured in the below format.

- Data Cleaning

- Data Processing

- Data Summarizing (As A Template)

- Data Presentation (As A Dashboard)

- Automation of the above process using VBA Macros

The course comprises of videos, key points /summary of each video is available as downloadable material. We suggest that you give a quick run through the downloadable material before you begin watching any video. This is will allow you to understand what it is that you will be learning in the attached video. The short videos take you through each concept step-by-step and allow you to practice while studying.

This course as indicated is one for those just beginning with Excel and for those who need to master their Excel skills.

What are the requirements?

  • MS Excel 2007 or later

What am I going to get from this course?

  • Master Excel
  • Learn with Logics
  • Automate your work in Excel
  • Understand how Excel works
  • Get the work out of Excel
  • VBA
  • Macros
  • Learn to delegate work to Excel and stay at ease

What is the target audience?

  • Anybody From Beginner from Expert
  • Anybody who wants to understand rather learn

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Intro
Intro Lecture
Preview
01:03
Section 2: MS Excel: Offload your Work
What you will learn
Preview
01:03
20:53

MS Excel: Offload your work: Agenda

In this video we are will be finalizing the steps to reach our agenda.

The video starts with the screen overview and also explains some essential starting techniques.

Please download the attached reading material.

Section 3: Data Cleaning
How you can clean Data
00:33
07:28

Data Cleaning: Customize a list

This Video explains about how the Excel's Fill handle can be used to drag and drop and create several lists out of it without typing it.

We can also see if a customized list can be created and incorporated in excel in the similar way.

06:03

Data Cleaning: Format Numbers

This video is one of the important video in the series:

We are learning the different types on number formatting in Excel here like:

Data formats, Currency formats, etc.

Please download the Employees Data and start working with us

09:44

Data Cleaning: Coloring cells the smarter way

Have you been coloring cells the historical way? Here is a video that can help us save tremendous amount of time in coloring and highlighting the insightful data. and also helps us save time from redoing it again.

Download the Data below and start working together.

Please download the attached material for your reference.

06:22

Data Cleaning: Going beyond a simple paste

In Excel, when we copy, there are too many features that gets copied , example - Value of the cell, boder, color, font color, formula, etc.

But at the time of pasting we might want to paste only one required feature.

The video explains how can we use the option "Paste Special" in excel to do this.

Please download the attached material.

05:14

Data Cleaning: Going beyond a simple Find

This video takes us beyond a very popular concept of "CTRL + F"

Use a blank sheet to work with is, no specific data needed.

Please download the attached material.

Using Shortcuts
05:05
Section 4: Organizing Data in Excel
How to organize data in Excel
00:51
12:08

Organizing data in Excel: Sorting and Filtering

This video will explain about the best practices from data arrangement. and Talks about the difference in sorting and filtering as well. Do look for quick shortcuts to the same here.

Download the Employee data given below to start off with the video.

06:23

Organizing data in Excel: Deciding what others Enter

We all know the problems and discrepancies in entering of the data by different people. the best way is to restrict them to type the way we need it. Data validation talks about the same in this video.

Please download the file and then continue with the video.

05:07

Organizing data in Excel: Creating Subtotals

This video is based on the concept of Subtotal, where we derive calculations on the data and present them within the data itself. (below the data mostly). very useful when we need to segment the data and take printouts.

Please download the attached material.

09:30

Organizing data in Excel: Consolidating Tables

Summary from a summary.

This is exactly what a Consolidate tool does.

If you have two cross tabs displaying similar information, and you need them to be clubbed together, do switch on the video once you download the given data, and we can start together then?

Please download the attached material.

Section 5: Writing Smart Functions in Excel
How Functions can help you
00:37
10:28

writing smart Functions in Excel: Tricks

This video is to explain the symbols important to work with functions, and help you self suffice yourself to get any new function by yourself.

this shall be treated as the foundation of all the functions, based on which every function in excel works, and will help us understand any new functions by ourselves -easily.

04:55

writing smart Functions in Excel:Arguments

This video is a continuation of the last video and explains some trial usage of functions. some sample functions will also be displayed in this video.

00:01 how to identify optional Arguments and usage.
01:38 Absolute/ Fixed cell references (using $ Symbols)
03:20 to make dynamic(smarter) functions
03:43

writing smart Functions in Excel: Creating Templates from Summaries

This Video explains how do we create a summary of the data and then how do we convert it into a self learning template.

a self learning template is a kind of report which is generated one and automatically adopts itself on any changes made on the data

00:35 Creating Summary
01:40 Formula of summary to template conversion

Please download the Data for working with this video. you can continue with the same data for the next videos, untill we complete counting.

10:01

writing smart Functions in Excel: Count Functions

We start touching upon functions from this video. carrying an idea of learning various aspects of a function and be able to crack functions concept as a whole.

we are hereby starting with some counting functions used in summarizing data sets.

00:20 Logic comes from the historical ways of doing things (strange but true)
02:40 Thinking about the name of the functions
03:15 learning to Explore functions
04:25 CountA function ( difference between CountA and Count Function)
05:55 Popularly made mistakes while selecting a range in Excel
07:14 Correcting  the mistake by selecting a bigger range
15:48

writing smart Functions in Excel: Countif Functions

This video explains the practical usage of the functions in a correct manner and filling arguments step by step.

02:10 COUNTIF Function to conditionally calculate the summary of the data based on values in the columns.
04:00 how to name ranges and whats the usage of the same in functions
04:50 naming multiple columns at a time using their headings as names
06:45 using COUNTIFS function to work with multiple conditions at a time.
08:25 Creating charts to present the template
11:15 Chart alignment trick
11:50 Connecting Excel charts to Powerpoint dynamically
15:55 using Wildcard characters(*/?) to avoid the data cleaning.
12:53

writing smart Functions in Excel: Text functions

This video talks about some random data cleaning text functions. you don't need any specific data sets to be downloaded, just open up a blank sheet and start working on it with the video.

The presentation theory Material is under revamp and shall update it asap.

Section 6: Segmenting Data in Excel
How to segment your Data
00:27
08:55

Segmenting Data in Excel: IF Functions

Before we can think of a summary, we need to divide the data into meaningful segments by using some logical functions.

basically to create categorical variables in the data.

Download the data to start with the video and replicate as when i explain.

07:01

Segmenting Data in Excel: Nested IF, AND and OR Functions

This video is a continuation of the logical functions itself where the advanced logical function is used.

Nested Ifs
Section 7: Why create Reports the tedious way
10 pages

Why create reports the tedious way: Presentation on Pivots

This lecture introduces Pivot Table concept in excel to us, the next 4 videos are the implementations of this information. get the basic understanding from this video and continue to the next lecture to see the video.

06:32

Why create reports the tedious way: Introduction to Pivots

See how a Pivot table makes our life simpler by generating awesome reports in no time.

Download the data and lets start.

07:29

Why create reports the tedious way: Generating Pivot Reports

This video is a continuation of the previous video and talks further about generating reports. some more problems have been picked up in the video on report generation.

02:30

Why create reports the tedious way: Slicing the Report

In this video we specifically going to focus on data segmentation and slicing of the reports based on different criterias.

06:30

Why create reports the tedious way: Pivots on Dates

Lecture video explains the use of Group option in Pivot tables to make the report generation based on the time factor user friendly.

Section 8: Showcasing your data with Excel
Using Dashboards to make Data look good
13 pages
How to Create a Dashboard
00:35
06:25

Showcasing your data with Excel: Ways to Present

Starting from this lecture , we talk about presenting the information that we have.

the current video talks about some trial and error approaches that we follow.

10:12

Showcasing your data with Excel: Creating a template

A self learning template is a kind of information that keeps getting updated by itself and also accommodates the output based on changes in the requirement.

10:36

Showcasing your data with Excel: Making Templates Smarter

Using index and Match functions , you will notice how the output becomes smarter and smarter.

the standard step to making anything smarter is to:

identify constants and replacing them by variables
10:20

Showcasing your data with Excel: Creating Dashboard from Templates

In this video we are discussing how to create controllable charts.

10:43

Showcasing your data with Excel: using Controls

This video cements the concepts we have learnt and based on it creates a dynamic and well managed information dashboard which can be controlled from a single point.

Section 9: Test Case Study
Revenue Report
6 pages
Section 10: Vocabulary
Introduction to VBA Programming
03:52
OOPS
04:15
Hierarchy as a vocabulary of the language
02:59
Communication among objects
03:24
Examples to justify the communication techniques
03:33
Introducing: Intangible Hierarchy
04:11
Section 11: Grammar
Sentence Creation: Connections in Excel
02:09
Rules of Writing the sentenses in VBA
04:30
Section 12: Introducing the Platform to write and Automate
Introducing the Platform: VBA Window
05:48
Section 13: Small Exercises to connect our learning so far
Applying Functions in VBA
11:12
Basic Error Debugging & Recording
00:05
Applying Methods in VBA
04:13
Various ways of executing a macro
09:36
Section 14: Case Study: Real Implementation
Explaining the case study for hands on learning
02:09
Understanding the IF Statements
11:23
Converting Constants to variables
04:19
Understanding Loops: FOR-NEXT
05:49
Understanding Loops: DO-LOOP
10:24
Recording a Macro: Handholding / On job training to macros
07:22
Trailing multiple macros one after another
06:36
Section 15: Functions and Events Can also be customized
Events to self execute the Macros
10:19
Creating your own functions in Excel
10:20
Section 16: Why Data Visualization
Snapshot of the Course
01:23
Dynamic Charts through checkboxes
01:04
Creating Dynamic Charts
15:57
Section 17: Data Visualization using CheckBoxes
Trailer: Battery Charts
00:50
Creating: Battery Charts
06:34
Creating: Applying motion to the battery Charts
10:17
Section 18: Speedometer charts
Trailer: Speedometer Charts
00:40
Creating: Speedometer Charts
10:18
Creating: Moving Speedometer Charts
07:21
Section 19: Introduction to Excel Apps using Userforms
01:30

Introduction to Excel Apps using userforms: Why userforms

In what ways are the userforms being used and in what ways can we use them

Please download supplementary material

01:49

Introduction to Excel Apps using userforms: Snapshot of the course

This lecture, gives a gist of all the topics covered in the course and intended outcome of the course.

Please download supplementary material

Section 20: Step By Step Approach to creating an App in Excel
01:11

Step by step approach to creating an App in Excel: Creating an Empty userform

In this lecture, we talk about how we insert an empty userform in VBA

Please download supplementary material

07:10

Step by step approach to creating userforms: Creating Controls

Here we talk about Controls like - Buttons, Text box, checkbox, radio button, dropdown, etc

08:18

Step by step approach to creating userforms: Connecting Text Box and Combo Box

Directly connecting the text box and combobox to excel and test if the data reaches in Excel on pressing the Submit Button

05:53

Step by step approach to creating userforms: Adding values in the combo box

A combo box when created will be an empty combo box, in this lecture we will see how can we put the values in the combo box

04:40

Step by step approach to creating userforms: Connecting Radio buttons to Excel data

Radio buttons are the ones with "Only One" to be selected, so how to structure the conditional statements to read it through Excel.

07:31

Step by step approach to creating userforms: Connecting check boxes to excel data

Check boxes are the ones where multiple selections can be made, thus we write different conditional statements for each.

09:34

Step by step approach to creating userforms: Making the excel connection intelligent & dynamic

using variables in place of constants and making the connection more dynamic and usable

Testing the form
06:43
Section 21: Getting your App to interact with the user

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

ATI - Analytics Training Institute, Committed to creating a difference

We are an Analytics firm committed to developing intellectual property that will help individuals and their organisation take smarter decisions every day. ATI, the education arm of Redwood Associates has helped 200 companies and over 15000 individuals speak the language of DATA

The founder Gautam Munshi has nearly two decades of high performance analytics experience. His strong belief that anyone can become an analyst has led him to build a team of 12 - a group of math geeks, techies, musicians, comedians, beer enthusiasts, agriculturists, geneticists, teachers and bankers,who have the gumption that they can make a difference and truly believe that analytics can influence and make a huge impact on a day-day basis. It is this diverse lot that brings Analytics to the mind space of every individual. You can view their moments in the lime light here and follow them on Facebook

Ready to start learning?
Take This Course