Basic, Intermediate and Advance Excel Course OverView

Section 1

Introduction to Excel

Be an Excel genius with the help of our basic to advance Excel courses in as little as 24 hours! This course outlines a complete guide from the fundamentals of Excel to very powerful complex formulas and functions. Practice our each and every example to master yourself in Excel by watching our interactive videos and using our notes as a reference guide!

Section 2

Excel Transition Phase

What is the difference between 2007, 2010 and 2013 Excel? What interface, ribbons and functionalities are added in different versions of Excel?

Section 3

Interface and Ribbon

What functionalities Excel offers in various tabs: Insert Tab; Formula Tab; Data Tab; Purpose of different Formatting areas within Excel.

Section 4

Use of Excel

What is Excel used for; How to use simple Math functions; Excel file saving techniques; Different formats of saving an Excel file and why do we need that?

Section 5

Formatting Techniques

Art of Formatting Techniques: Format Painter, Font Size, Bold, Italic, Underline, Fill Color, Font Colour, Left, Right and Centre Alighnment, Wrap Text, Merge & Centre, Currency Sign, Numbers, Percentage, Control decimal points; Advance Formatting techniques: General, Number, Currency, Accounting, Date, Percentage, Custom Formatting and few short cuts.

Section 6

Excel Workbook Introduction

Difference between Worksheet and Workbooks. How new Excel file appears; Purpose of Excel tabs; How to create new worksheet; Change worksheet name; Apply different formatting.

Section 7

Important Shortcut Keys

So many important Excel shortcut keys! Purpose of those shortcuts keys; Different types of shortcut keys using CNTRL, ALT, CNTRL + SHIFT, ALT + SHIFT, CNTRL + SHIFT + ENTER; CNTRL + SHIFT + Numbers and so on.

Section 8

Printing Techniques

Printing file correctly is a skill; Learn some new shortcut keys; print previews; repeat titles on each page; page setup; how to print large data sets!

Section 9

Introduction to Excel Formulas

Introduction to some Basic formulas; Learn how to use formulas in Excel; Various different ways of using AUTOSUM function; Learn how and where to use LEFT and RIGHT function; Calculate the length of text using LEN function.

Section 10

Cell Reference

The most important module in Basic Excel course: Learn absolute cell reference; How rows and columns behaves using cell references; Purpose of Dollar sign ($) in a formula; a new F4 shortcut key!

Section 11

Customization

How to change permanent and default Excel settings for Fonts, Colours, Numbers of Worksheets, Ribbons and much more.

Section 12

Workbook Protection

What is the purpose of protecting worksheets, workbook? How do we protect worksheets and Workbook with and without the password? Learn the importance! How to share workbooks in a working environment?

Section 13

Linking Data to Different Sources

How do we link data in Excel to different data source? Learn how to update external links; How to parse data in Excel; How to convert .txt files into Excel format and much more.

Section 14

Risk Assessment Techniques

Some basic principles upon which Excel crashes can be avoided; Monitor and Reduce file size; Learn Excel trouble shooting and some other Risk assessment techniques!

Section 15

Text Functions

Define TEXT functions and their usage? Get introduced to CLEAN() and TRIM() functions. Relativity between LOGICAL conditions and EXACT() function in a case sensitive manner. Thorough understanding of AMBPERCENT[&] to join columns together with DOLLAR() function.

Section 16

Statistical Functions

Multiple ways to use AVERAGE() and COUNT() function in Excel. Use COUNTIF() function on a single criteria and pull values using IF() within SUMIF() functions, combine it with ARRAY formula to make it powerful. When to use AVERAGEIF() function.

Section 17

Logical Functions

Learn the power of Logical Statement that evaluates to TRUE and FALSE using AND() and OR() functions. Use IF() function to calculate the cumulative totals, define different value sets, hardcode a massage in double quotes.

Section 18

Date and Time Functions

Five different techniques of using DATE and TIME functions; Learn YEAR(), MONTH(), DAY() and TEXT() functions to achieve desired results.

Section 19

Math and Trigonometry Functions

Use SUMIF() function and define CELL Reference within the example; Learn the new SUMPRODUCT() function that make tasks simpler.

Section 20

Find and Lookup Functions

Learn few of most important and frequently used Excel functions. Different LOOKUP() techniques using Approximate and Exact match; Two different techniques of using TRANSPOSE() function [Array Formula]; Pull values from various worksheets using simple INDIRECT() function; Combine HLOOKUP() with SUMPRODUCT in Array Form to pull values from a single and multiple ROWS.

Section 21

Array Formulas

Learn principles or ARRAY{} formula; How to connect various Columns and Rows using AMBPERCENT[&]; Learn the art of CNTRL + SHIFT + ENTER in an ARRAY context; Get the desired results through ARRAY Formula!

Section 22

Database Features

Make your data dynamic using TABLE feature; Learn how to use TEXT to STRING functionality and combine it with CONCATENATE() function.

Section 23

Validations Techniques

How to create DROPDOWN and LIST in Excel; What are CONTROL BUTTONS, CHECK BOX for; Learn some other data validation techniques.

Section 24

Conditional Formatting

Learn the art of CONDITIONAL FORMATTING TECHNIQUES using 9 various examples.

Section 25

Macros

Automate frequent used tasks by recording MACRO; Learn MACRO file saving technique; Activate Developer Ribbons; Add and assign buttons to MACRO.

Section 26

Pivot Tables

Make large dataset interactive and create PIVOT TABLES; Learn how to define DATASOURCE; What are major Pivot Table Fields; How to make interactive reports using PIVOT TABLE; Learn STYLING techniques; Introducing TWO more new Ribbon TABs.

Section 27

Pivot Charts

What is PIVOT CHARTs? How to display data interactively using Pivot Charts; Link Chart Titles with the PIVOT TABLE, Formatting technique and much more.

Section 28

Charts and Graphs

How to create charts in Excel; Learn the fundamentals of Charts; What is a series?; What are axis?; Learn different types of charts in Excel.

Section 29

Whatif Scenarios

Take you knowledge beyond next level using WHAT-IF Analysis; Create various Mortgage Payment scenario by creating interactive Mortgage Calculator using PMT() function and DATA TABLE functionality.

Section 30

Logical Functions
Learn the power of Logical Statements that evaluates to TRUE, FALSE with the help of multiple examples using Nested IF Statements combined with the power of INDEX(), COUNTIFS(), ROWS(), ROW(), SMALL() functions; the use of AND(), OR() functions stand alone and within IF() that makes the formula very powerful and yield amazing results based on various multiple criteria.

Section 31

Text Functions
Extend your understanding of Logical Condition and compare various strings using Text functions. Get the understanding of case sensitive scenario using EXACT() function or use (==) comparison operator to make exact comparison.

Section 32

Maths and Trigonometry Functions
Specify up to 127 conditions to use the power of SUMIFS() based on multiple columns and rows; Use -- signs within SUMPRODUCT() or create Logic Conditions with [=] operator; Customize criteria in desire formats using TEXT() function.

Section 33

Find and Lookup Functions
Learn the most used functions in Excel with superpower capabilities - Use INDEX() in Reference or Array form; VLOOKUP() to join multiple columns using AMBPERCENT[&] and drop downs; TRANSPOSE() Array to flip the data or alternative ways of achieving same results using INDEX(), COLUMNS(), ROWS(); Call in advance Name Range functionality using HLOOKUP() without giving Table Range.

Section 34

Statistical Functions
Multiple criteria? COUNTIFS() and AVERAGEIFS() solves this problem even if dataset is ambiguous, learn how to take help of Wildcard [*].

Section 35

Array Formulas
Perform multiple calculations on one or more of the items in an ARRAY{} and manipulate data with INDIRECT(), LEN(), MID(), ISNUMBER(), ROW(); Combine these functionalities together and apply methodological calculations within to make the formula even more powerful and get the desired results.

Section 36

Introducing Formulas to Condition Formatting
Learn the art of CONDITIONAL FORMATTING with the help of VLOOKUP(), AND(), OR(), COUNTIF(), LOGICAL CONDITION, Data Validation and flag OH Budget to Actual variance; highlight ODD, EVEN Rows; ROW, COLUMN and INTERSECTING Value; On the basis of a single or multiple criteria; Duplicate records; TRUE entire Row; TRUE entire Column and even more with our 10 set of examples!

Section 37

Importing and Exporting Data
Ready to import your database based on SAP, Access, SQL, etc.? Not a problem anymore. Step by step guide to make the learning easy; understand the database relationship; define them; perform customized user based reports and flush out unwanted data using the powerful Pivot Tables; Present them using Pivot Charts and more.

Section 38

Forms Controls
Use automated FORM CONTROLS tools to make your life easier; Learn the difference between LABELS, CHECK BOX, SPIN BUTTON, OPTION BUTTON, GROUP BOX and so on.

Section 39

Dynamic Tables
Manage and analyze a group of related data easier with the help of TABLES; Use Dynamic Tables to extract data into various TABS using Nested IF(), INDEX(), SMALL(),ROW(), ROWS(), ARRAY{}.

Section 40

Data Validations
Define RESTRICTIONS on what data can or should be entered in a cell using DATA VALIDATION techniques; Learn how to create DROP DOWN LIST; Restrict cell input using WARNINGS; How to use formulas within VLOOKUP(); Create Names from SELECTION, advance Name Range, CIRCLES using INVALID DATA option.

Section 41

Advance Macros
Automate frequently used task by creating ADVANCE MACRO; Create LOGICAL CONDITIONS using AND(); Invoke ADVANCE FILTERS; Record MACROS and create database and store it on a different location when multiple criteria are met.

Section 42

VBA Interaction
Learn the Excel VBA interaction; VBA coding format; How to write basic VBA program; Sample basic and complex VBA examples: Message Box; Using formula within the VBA codes: Learn how to move data from one worksheet to another worksheet: create Buttons: Saving VBA files: Run VBA macros.

Section 43

Pivot Tables
Make large dataset interactive using PIVOT TABLES; Acquire the GROUPING Technique; Manipulate Database to achieve desire results with or without the help of FORMULAS; Combine them with interactive PIVOT CHARTS to make the decision making process effective.

Section 44

Power Pivots
Amazing ad-in feature available in Excel 2010 & 2013 - Perform powerful Data Analysis and create Data Models like never before; Pull data from DAX sources; Combine it with Pivot Tables and Customized Reports. 

Section 45

Conditional Formatting To Reports
Extend your CONDITIONAL FORMATTING knowledge and apply it to various REPORTS such as Inventory Aging; Write Off; Balance Sheet Scorecard, etc.; Combine it with EDATE(), TODAY() functions to become champion.

Section 46

Charts and Graphs
How to create effective charts and graphs using advance techniques; Use of advance formatting tools; Create advance dynamic and interactive charts; Waterfall charts; Use sample techniques to create effective charts in Excel.

Section 47

Conditional Formatting To Charts
Further extend your CONDITIONAL FORMATTING knowledge and this time we will apply to charts; show Negatives in RED and Positives in GREEN using INVERTS options; or Formulas in 2007.

Section 48

Sparklines
Use interactive SPARKLINES to do TREND ANALYSIS; Use small charts to reveal patterns in large datasets in a concise and highly visual way; Analyze different types of SPARKLINES - LINES, COLUMNS, WIN/LOSS.

Section 49

Whatif Analysis
Take your knowledge beyond next level using whatif and create scenario analysis; Use scenario manager to create multiple scenario; Create P&L, Balance sheet based on demands; Create various Forecast models; define goals and much more!
© Copyright 2016 | All Rights Reserved.