🚨 Time is Running Out: Reserve Your Spot in the Lucky Draw & Claim Rewards! START NOW
watching now
80 Students

Description

What Will You Learn?

  • How to use Microsoft Excel on an advanced level
  • How to use the most commonly used formulas in the office
  • The basic and advanced concepts of Excel
  • How to create pivottable and pivotchart in Excel
  • Solid skills in analyzing raw business data, and much - MUCH - more!

Requirements

  • This course is recorded on excel 2019 but all previous versions 2007, 2010, 2013, 2016 and office 365 will work on this course
  • No previous experience with Excel is needed, as you may start learning from the very basics
  • Experienced users can skip the introductory parts and choose the advanced topics to study and practice
  • NFT Certificate
  • 406 Lessons
  • Beginner
  • English
  • 4.5 Rating
  • +100 XP

Share Course on Social media

Curriculum

Course consist of total 23h 7min of content, in total.

Section 1: Data Analysis in PivotTables (Creating Revenue Dashboard)
33:33
Download Resource Files
Adding and Formatting Bar Chart and Creating Customer Revenue PivotTable
04:15
Creating Dashboard Part 1
03:57
Creating Dashboard (Adding Text Box) Part 2
03:48
Creating Dashboard (Adding Text Box) Part 3
03:17
Creating Dashboard (Adding Text Box) Part 4
03:18
Creating Connections between PivotTables and Slicers
03:24
Section 2: Sparklines (Line,Column,Win or Loss) and Data Bars
20:59
Sparklines (Line,Column,Win or Loss)
13:27
Data Bars
07:32
Section 3: Waterfall Chart,Funnel Chart and Map Chart
09:56
Waterfall Chart,Funnel Chart and Map Chart
09:56
Section 4: People Graph,Sunburst Chart,Treemap and Bubble Charts
14:37
People Graph,Sunburst Chart,Treemap and Bubble Charts
14:37
Section 5: Stocks and Geography,Progress Chart
24:11
Stocks and Geography,Progress Chart Part 1
11:05
Stocks and Geography,Progress Chart Part 2
13:06
Section 6: Analyzing Sales Data in Pivot Tables and Pivot Charts
34:38
Adding Data in Excel Table
02:50
Adding and Formatting First Chart in Pivot Table
04:02
Adding and Formatting Second Chart in Pivot Table
03:27
Adding and Formatting Third Chart in Pivot Table
04:09
Adding and Formatting Fourth Chart in Pivot Table
03:17
Adding and Formatting Fifth Chart in Pivot Table
03:29
Adding and Formatting Sixth Chart in Pivot Table
03:04
Adding Chart in Excel Dashboard
03:03
Designing Dashboard Layout and Extracting Pivot Tables Data in Text Box
03:24
Inserting Slicers in the Dashboard for Analyzing Sales Data
03:53
Section 7: Creating Excel Tables, Pivot Tables and Pivot Charts
46:07
Freezing Header or Row
01:31
Converting Data in an Excel Table
02:01
Adding Records in an Excel Table
04:09
Renaming and Finding Records in an Excel Table
02:47
Editing and Deleting Records in an Excel Table
02:26
Remove Duplicates
02:26
Sorting Date Column in an Excel Table
01:43
Sorting Multiple Columns
02:05
Filtering Data in an Excel Worksheet and Deleting an Excel Table
03:15
Filtering Data by Creating Criteria
02:57
Filtering Data in an Excel Table by Using Slicer
02:16
Splitting the Worksheet into Panes
02:34
Calculating Records in an Excel Table
02:54
Inserting Subtotals
03:07
Creating and Adding Data in a Pivot Table
03:51
Filtering Data in a Pivot Table
03:13
Adding Pivot Chart in a Pivot Table
02:14
Home Work
00:38
Creating Excel Tables, Pivot Tables and Pivot Charts Quiz
Section 8: Data Validation and Inserting Comments
17:53
Specifying a Validation Rule by Using List
04:34
Show an Input Message on an Active Cell
04:10
Displaying Error Alert Message
04:01
Circle Invalid Data
01:08
Inserting Comments in a Worksheet
03:18
Home Work
00:42
Data Validation and Inserting Comments Quiz
Section 9: Analyzing Data in the Table
24:21
Introducing and Converting a Range to a Table
03:03
Sorting a Table using Different Methods
03:40
Filtering a Table using Different Methods
05:48
Analyzing Basic Table Operations
05:33
Analyzing Table Data using COUNTIF,SUMIF and AVERAGEIF Functions
06:17
Section 10: Analyzing Data with PivotTables
16:20
Introducing PivotTables
02:00
Creating PivotTable
04:49
Inserting Subtotals In the PivotTable
03:39
Summarizes Value Field by Changing Functions
02:11
Adding PivotChart and Slicer in the PivotTable
03:41
Section 11: Analyzing Data with Business Intelligence (Power Queries and Trendlines)
20:46
Introducing Power Query and Upload CSV File
03:48
Retrieving Power Query Data into an Excel Table and Editing a Query Data
05:03
Adding a New Column and Grouping Values in a Power Query Data
06:40
Analyzing Revenue Data by Adding Trendlines in the Chart
05:15
Section 12: Analyzing Data with Business Intelligence (Forecast Sheet)
06:04
Analyzing Revenue Data by Creating a Forecast Sheet
06:04
Section 13: Analyzing Data with Business Intelligence (Power Pivot,Data Model & Power Map)
37:21
Introducing Database and Upload Access Database File
04:27
Creating the Data Model
04:26
Transferring Data in the Power Pivot
03:36
Exploring the Data Model in the Diagram View
02:01
Managing Table Relationships in the Power Pivot
04:11
Creating a PivotTable from the Data Model
05:38
Adding a Timeline Slicer in the PivotTable
02:48
Drilling down into the Data in the PivotTable
03:46
Visualizing Stores Sale Data with Power Map
06:28
Section 14: Extracting Data from the Website(Power Query,Power Pivot,Pivot Table)
17:26
Importing Population and Area Data from the Website and Creating Queries
05:11
Extracting Population and Area Data into PivotTable Edit Delete Expand Move
06:20
Merging Two Queries into One Query
05:55
Section 15: Loading Website Data into Power Map(Power Query,Power Map)
06:51
Loading Website Data into Power Map
06:51
Section 16: Extracting Data from the Website(Power Query,Pivot Table)
16:36
Extracting and Transforming User Rating Data from the Website
07:52
Combining Queries
08:44
Section 17: Extracting Football Team Data from the Website(Power Query,Pivot Table)
06:51
Extracting and Transforming Data from the Website
06:51
Section 18: Analyzing Business Data by using What-If Analysis Tools(Goal Seek,Data Table)
31:21
Cost-Volume-Profit(CVP) Chart Analysis
02:54
Find the Break-Even Point by Using Goal Seek
04:09
Generate $30000 Net Income of Company by using Goal Seek Tool
01:13
Creating One-Variable Data Table to Examine the Impact of Changing Sales Volume
04:41
Completing the One-Variable Data Table
02:41
Creating the CVP Chart of the Data Table
02:59
Viewing the Impact of Changing the Sales Price in the Data Table
02:45
Creating the Two-Variable Data Table
05:35
Create a Chart of the Two Variable Data Table
04:24
Section 19: Analyzing Business Data by using What-If Analysis Tools(Scenario Manager)
23:02
Define Names for the Income Statement Values
02:34
Adding the Scenarios in the Scenario Manager
07:18
View the Impact of the Reduced Operations Scenario
04:12
Creating the Scenario Summary Report
02:37
Creating the Scenario PivotTable Report
06:21
Section 20: Analyzing Business Data with Solver
21:36
Understand Solver with Simple Example
05:52
Introducing Product Mix and Find an Optimal Solution with Solver Part 1
04:53
Introducing Product Mix and Find an Optimal Solution with Solver Part 2
06:10
Creating a Solver Answer Report
02:23
Saving and Loading Solver Model
02:18
Section 21: Introduction to Excel
23:29
Course Files
00:41
Download Practices Files
Starting With Excel
01:43
Excel Tabs, Groups and Commands
02:26
Excel Rows and Columns
03:23
Entering Text and Numbers in a Worksheet
04:06
Changing the Size of Columns and Rows
01:31
Wrap Text Inside the Cell
00:44
Edit Text Within a Cell
00:34
Merging and Formatting Cells in a Worksheet
01:53
Formatting Header in a Worksheet
01:09
Adding Image in a Worksheet
01:55
Formatting Date in Excel Worksheet
01:24
Working of Undo & Redo Commands in an Excel Worksheet
01:07
Home Work
00:41
Introduction to Excel Quiz
Section 22: Calculations With Basic Formulas
23:13
Introduction to Arithmetic Operators
01:45
Calculations with Arithmetic Operators
07:12
Copy and Paste the Formula
01:56
Auto Complete Formula
01:40
Inserting and Deleting Columns and Rows
02:03
Hide and Unhide Columns and Rows
01:26
Moving a Cell or Range by Drag and Drop Method
01:28
Moving a Cell or Range by Cut and Paste Method
00:41
Relative and Absolute Cell References
04:07
Home Work
00:55
Calculations With Basic Formulas Quiz
Section 23: Calculations with Simple Excel Functions
22:05
Introduction to Function Syntax
02:17
Calculating Total Sales by Using SUM Function
02:46
Calculating Total Sales Quantities by Using SUM Function
01:12
Count Price Items by Using COUNT Function
01:28
Count Food Items by Using COUNTA Function
01:38
Calculating Average Sales by Using AVERAGE Function
02:14
Calculating Maximum Sales Value of Customer by Using MAX Function
01:31
Calculating Minimum Sales Value of Customer by Using MIN Function
01:28
Calculating Total Price of Products by Using PRODUCT Function
02:02
Calculating Sales Data by Using Auto FUNCTION Button
01:42
Calculating a Tax Rate on Total Sales
03:03
Home Work
00:44
Calculations with Simple Excel Functions Quiz
Section 24: Formatting a Worksheet
32:49
Merging Two or More Cells into One Cell
02:05
Alignment of Cell Text
02:09
Splitting Text into Multiple Lines
02:08
Rotating a Cell Text or Numbers
01:31
Formatting Background Color of Text and Numbers
03:11
Formatting Font Size and Font Color
02:18
Adding Border into Cells
01:40
Formatting Cell Styles
02:11
Find and Replace Text in a Worksheet
03:07
Calculating Students Enrollment and Difference Between two Columns Values
04:22
Calculating Percentage Difference Between two Columns Values
02:45
Conditionally Formatting Cells in a Worksheet
02:54
Conditionally Formatting Cells with a Top and Bottom Rules
01:51
Home Work
00:37
Formatting a Worksheet Quiz
Section 25: Saving and Printing a Workbook
15:50
Saving a Workbook
01:08
Worksheet Views Page Orientation and Scaling
03:37
Setting Margins in a Print Page
01:24
Adding Header and Footer in a Print Page
03:42
Print Preview and Selecting Print Area
03:05
Setting Scaling and Margins in a Print Page(Document 1)
02:54
Saving and Printing a Workbook Quiz
Section 26: Project Calculations with Operators and Functions
25:05
Calculating the Area of Field and Plots
03:32
Calculating the Grown and Empty Area of Vegetables Field
01:58
Calculating the Percentage of Grown and Empty area in Vegetables Field
02:18
Calculating Cost of Sales and Weightages of Vegetables
04:08
Calculating Profit and Percentage of Profit from Vegetables
03:41
Calculating Revenue by Using Sum, Max, Min, Average and Median Functions
04:53
Introduction to NESTED Function
01:38
Nesting the SUM and MEDIAN Functions within ROUND Function
01:53
Home Work
01:04
Project Calculations with Operators and Functions Quiz
Section 27: IF and VLOOKUP Functions
23:29
Introduction to IF Function and Apply Weather Condition on IF Function
04:10
Apply Different Conditions on IF Function
08:07
Introduction to VLOOKUP Function
03:30
Apply VLOOKUP Function on Data Table
02:02
Retrieving Values from Data Table by Using VLOOKUP Function
02:27
Errors in VLOOKUP Function
02:28
Home Work
00:45
IF and VLOOKUP Functions Quiz
Section 28: Adding Chart in an Excel worksheet
15:20
Embed Column Chart in a Worksheet
02:42
Analyzing Data with a Column Chart
05:21
Analyzing Data with a Pie Chart
03:47
Recommended Charts
02:53
Home Work
00:37
Analyzing Data with a Chart Quiz
Section 29: Date and Time Functions
42:36
NOW and TIME Functions
03:40
HOUR, MINUTE and SECOND Functions
03:27
TODAY, DATE AND DAYS Functions
03:57
YEAR, MONTH and DAY Functions
04:23
WEEKDAY Function
02:38
WEEKNUM Function
01:47
EDATE Function
02:32
EOMONTH Function
02:58
Determining Person's Birthday
02:03
Calculating Age of Person
02:13
Formatting Date in a Cell
01:24
DATEVALUE Function
02:13
DAYS360 Function
02:02
ISOWEEKNUM Function
01:38
NETWORKDAYS Function
01:22
TIMEVALUE Function
01:58
YEARFRAC Function
02:21
Date and Time Functions Quiz
Section 30: Logical Functions in an Excel
1:01:02
Calculating the Age of Employees and Length of Service
02:45
Analyzing Data by Using IF Function
04:16
Determining Birthday of Employees
02:24
Specifying Day of Birthdate by Nesting DAY Function Within IF Function
03:06
Determining Employee Eligibility for Promotion by Using AND Function
04:14
Nesting the AND Function Within IF Function
04:24
Determining Employee Eligibility for Promotion by Using OR Function
03:49
Nesting the OR Function Within IF Function
04:23
IFERROR Function
03:00
IFNA Function
05:32
FALSE Function
02:48
IFS Function
04:42
NOT Function
03:24
SWITCH Function
04:20
TRUE Function
03:00
XOR Function
02:37
Home Work
00:32
Logical Functions in an Excel Quiz
COLUMN Function
01:46
Section 31: Lookup and Reference Functions in an Excel
49:19
Extracting Value from Data Table by Using VLOOKUP Function
06:13
Extracting Value from Data Table by Using HLOOKUP Function
05:05
Calculating Discount and Total Price
02:02
Calculating Tax Rate on Total Price Value
01:09
Extracting Value by Using CHOOSE Function
03:59
Nesting the CHOOSE Function Within Sum Function
04:17
MATCH Function
02:58
Extracting Value by Using INDEX Function
03:20
Nesting the MATCH Function Within an INDEX Function
03:24
Nesting the VLOOKUP Function Within an IFERROR Function
04:29
COLUMN Function
01:46
LOOKUP Function
06:33
TRANSPOSE Function
03:41
Home Work
00:23
Section 32: Adding Line and Combination Charts in an Excel
07:46
Adding and Formatting Line Chart
03:39
Adding and Formatting Combination Chart
04:07
Section 33: Text Functions in an Excel
1:15:10
BHATTEXT Function
01:51
CHAR Function
02:18
CLEAN Function
01:30
CODE Function
01:17
CONCAT Function
02:25
DOLLAR Function
02:19
EXACT Function
02:29
FIND Function
03:40
FIXED Function
02:46
LEFT Function
02:44
LEN Function
01:55
LOWER Function
02:01
MID Function
04:05
NUMBERVALUE Function
02:50
PROPER Function
01:19
REPLACE Function
04:26
REPT Function
03:59
RIGHT Function
02:54
SEARCH Function
03:52
SUBSTITUTE Function
03:55
T Function
01:41
TEXT Function
04:09
TEXTJOIN Function
03:34
TRIM Function
01:48
UPPER Function
02:14
UNICHAR Function
01:17
UNICODE Function
01:42
UPPER Function
02:14
VALUE Function
01:56
Section 34: Math and Trigonometry Functions in an Excel
1:02:12
SUM Function
05:44
MOD Function
01:39
POWER Function
02:57
SUBTOTAL Function
06:18
SUMIF Function
07:19
SUMIFS Function
08:09
SUMPRODUCT Function
04:20
ROUND Function
05:38
ROUNDDOWN Function
02:18
ROUNDUP Function
02:07
CEILING.MATH Function
03:21
FLOOR.MATH Function
02:59
EVEN Function
01:50
ODD Function
01:45
INT Function
02:25
TRUNC Function
03:23
Section 35: Statistical Functions in an Excel
1:04:08
AVERAGE Function
01:50
AVERAGEIF Function
08:32
AVERAGEIFS Function
09:46
COUNT and COUNTA Function
04:46
COUNTBLANK Function
02:52
COUNTIF Function
07:16
COUNTIFS Function
08:09
LARGE Function
04:28
SMALL Function
03:51
MAX and MIN Function
02:56
MEDIAN Function
03:02
MODE.MULT Function
03:40
MODE.SNGL Function
03:00
Section 36: Engineering Functions in an Excel
24:51
BIN2DEC Function
02:54
BIN2HEX Function
03:20
BIN2OCT Function
03:06
COMPLEX Function
03:46
CONVERT Function
05:08
DEC2BIN Function
02:49
DEC2HEX Function
01:56
DEC2OCT Function
01:52
Section 37: Information Functions in an Excel
1:04:14
CELL Function
06:57
ERROR.TYPE Function
01:45
INFO Function
03:07
ISBLANK Function
04:34
ISERR Function
01:51
ISERROR Function
03:56
ISEVEN Function
02:54
ISFORMULA Function
02:48
ISLOGICAL Function
03:03
ISNA Function
02:55
ISNONTEXT Function
02:55
ISNUMBER Function
02:35
ISODD Function
03:00
ISREF Function
03:01
ISTEXT Function
03:01
N Function
03:18
NA Function
03:20
SHEET Function
03:00
SHEETS Function
02:23
TYPE Function
03:51
Section 38: Database Functions in an Excel
14:42
DAVERAGE Function
04:17
DSUM Function
02:33
DMAX and DMIN Functions
03:54
DCOUNTA Function
03:58
Section 39: Financial Functions:Borrow Money to Bank
1:22:09
Calculating a Quarterly Payment of Loan by using the PMT Function
07:15
Calculating a Future Value of Loan by using the FV Function
05:46
Calculating a Payment Period of Loan by using the NPER Function
04:11
Calculating a Present Value of Loan by using the PV Function
04:57
Calculating an Interest Rate of Loan by using the RATE Function
04:23
Positive Cash Flow and Negative Cash Flow
03:19
Calculating the Value of Inflation by Using FV Function
03:20
Calculating a Monthly Car Payment
04:20
Calculating the Interest Payment by using IPMT Function
05:39
Calculating the Principal Payment by using PPMT Function
03:26
Calculating Remaining Principal Amount and Total Payment
04:09
Completing the Amortization Schedule of Loan
06:25
Plotting Interest and Principal Payments
03:23
Calculating Cumulative Interest Payments by Using CUMIPMT Function
05:12
Calculating Cumulative Principal Payments by Using CUMPRINC Function
05:31
Calculating Remaining Principal,Total Interest and Total Principal Payments
06:04
Calculating Total Loan Amount
04:49
Section 40: Financial Functions:Invest Money in the Bank Saving Account
57:15
Calculating the Compound Interest
05:27
Calculating Nominal and Effective Interest Rates Using NOMINAL&EFFECT Functions
06:11
Calculating the Future Value of an Investment by Monthly and Initial Deposits
06:36
Calculating the Interest Rate of an Investment
06:25
Calculating the Total Number of Deposits of an Investment
06:51
Calculating the Initial Deposit or Present Value of an investment
05:18
Calculating the Value of Deposit Per Month
04:38
Calculating the Variable Interest Rates by using FVSCHEDULE Function
05:09
Amortization Schedule of an Investment 1
05:28
Amortization Schedule of an Investment 2
05:12
Section 41: Financial Functioms:Build Future Income Statement of Company
59:35
Introduction to Income Statement Worksheet
02:15
Linear and Growth Trend Values
02:09
Projecting Future Value of Sales or Revenue by using Linear Trend Option
05:02
Projecting Future Value of Sales or Revenue by Inserting a Growth Trend
04:59
Calculating the Cost of Research and Development
03:32
Calculating the Cost of Sales and Marketing
03:36
Calculating the Gross Profit
03:27
Calculating the Expenses of Company Part 1
03:21
Calculating the Expenses of Company Part 2
04:19
Calculating the Expenses of Company Part 3
04:29
Calculating the Total General Expenses and Initial Earnings
03:52
Adding Depreciation of an Asset in the Income Statement
03:09
Calculating Operating Profit in the Income Statement
02:32
Adding Interest Expenses in the Income Statement
04:00
Calculating Profit Without Adding Tax
01:55
Adding Taxes in the Income Statement
03:58
Calculating Final Profit of the Company in the Income Statement
03:00
Section 42: Financial Functions:Calculate Depreciation of an Asset of Company
39:10
Straight Line Depreciation of an Asset Part 1
03:22
Straight Line Depreciation of an Asset Part 2
04:04
Straight Line Depreciation of an Asset Part 3
03:07
Declining Balance Depreciation of an Asset Part 1
03:58
Declining Balance Depreciation of an Asset Part 2
05:15
Calculating Depreciation of an asset by Using SYD Function
04:57
Calculating Depreciation of an asset by Using DDB Function
04:01
Calculating Depreciation of an asset by Using VDB Function
05:28
Calculating Depreciation Value of a Car
02:37
Selecting Method of Depreciation to Calculate Depreciation of an Asset
02:21
Section 43: Financial Functions:Invest Money in Company
32:40
Calculating Yearly Interest Rate of an Investment by Using Rate Function Part 1
02:40
Calculating Yearly Interest Rate of an Investment by Using Rate Function Part 2
03:32
Calculating Payback Period of an Investment Part 1
04:30
Calculating Payback Period of an Investment Part 2
02:49
Calculating Payback Period of an Investment Part 3
02:44
Calculate Present Value of an Invetment by Using NPV Function Part 1
03:14
Calculate Present Value of an Invetment by Using NPV Function Part 2
02:26
Calculate Net Present Value of an Invetment
02:09
Calculate Internal Rate of Return of an Invetment by Using IRR Function
02:04
Calculate Net Present Value & Internal Rate of Return using XNPV & XIRR Functons
03:34
Calculate Equivalent Value of Interest Rate by Using RRI Function
02:58
Section 44: Basic Financial Examples
39:07
Calculating the Remaining Amount with 30% Rate
03:30
Calculating the Remaining Amount with 15% and 40% Rate
04:25
Calculating the Percentage of Remaining Amount
04:30
Calculating the Percentage of Savings
04:10
Finding the Value of Actual Amount
04:57
Calculating the Value of Expense,Save and Total Amount
05:23
Calculating the Remaining Profit
04:04
Calculating the Values of Profit and Percentage of Profit
03:25
Calculating the Values of Loss and Percentage of Loss
04:43

About the Instructor

I have completed master degree in science (applied physics) but now I am teaching Microsoft Excel and Microsoft Power BI online and hundreds of students have enrolled in my courses. I love teaching online as well as I am teaching data analysis and data visualization.

See All Instructor Courses

BitDegree platform reviews