Microsoft Excel - 300 Module Masterclass!

This course will help you to turn Excel into a personal business intelligence tool: learn creative interactive charts, super dashboards and formulas

COURSE HIGHLIGHTS

• Over 15 hours of lecture content across 300 modules!
• Exercises and quizzes available to monitor progress
• Covering a range of topics from printing on Excel to complex formulas
• Learn button functions & how to use for presentations
• Learn to use Excel for business & professional purposes
• Covers 5 topics: Excel Hacks/Tips, Advanced Charts, Power Pivot, Dashboards, and Macros

ENROL TO EARN YOUR CERTIFICATE

• Enrol and become certified in a new skill
• Share your new skill with employers, friends and colleagues
• Build a porfolio of course projects

More Info

Ever wanted the ground to swallow you up when your boss asks, ‘You know how to use Microsoft Excel, right?’ - Stay above ground and never fear, a solution is here!

Computing expert, Andreas Exadaktylos, will help you to turn Excel into a personal business intelligence tool to create interactive charts, super dashboards, both easy & complex formulas and even how to print from partial & full workbooks. In addition to these fundamental skills, you will learn to format, use for data analysis and manage worksheets using real world examples and step-by-step instructions.

For use in all types of industries, do not pass up the chance to learn this key programme today. Broken down into small tasks, you will learn from an instructor with over 16 years experience in the field. Complete exercises and quizzes as you go to be clued up on your individual progress.

By the course conclusion, there is no doubt that you will be able to answer the opening question confidently and proclaim yourself to be an Excel-lent user!

Curriculum

My Top Ten (10) Hacks and Tips

• My Top Ten (10) Hacks and Tips - Downloadable Materials
• Say hi and tell your fellow students why you're taking this course
• Share your new course with your friends!
• TIP #1 - Navigate between Worksheets Quickly
• TIP #2 - How to Prevent Someone from Unhiding a Worksheet – Very Hidden Property
• TIP #3 - Blocking a User from Interacting Outside a Specific Range
• TIP #4 - Entering Data to Multiple Worksheets at a Time
• TIP #5 - Learning Different Techniques for Navigating within a Given Worksheet
• TIP #6 - Entering Data Quickly and Efficiently
• TIP #7 - How to Use FlashFill to Fill Values in a Range Rapidly
• TIP #8 - Changing Values without Formulas
• TIP #9 - How to Generate Random Decimal Numbers between 0 and 1
• TIP #10 - Creating a Chart with Keyboard Shortcuts

Excel Interface

• TIP #11 - Customizing the Quick Access Toolbar
• TIP #12 - Customizing the Ribbon
• TIP #13 - Using Keyboard to Access Ribbon
• TIP #14 - Undo, Redo and Repeat

Workbooks and Worksheets

• Workbooks and Worksheets Downloadble materials
• TIP #15 - Navigate between Workbooks Quickly
• TIP #16 - Zooming In and Out Quickly
• TIP #17 - Applying Document Themes in a Workbook
• TIP #18 - Copying and Moving Worksheets in Various Ways
• TIP #19 - The Best Techniques for Inserting, Deleting & Hiding Columns and Rows
• TIP #20 - How to Print a 2-page Worksheet on 1 page
• TIP #21 - Hiding Scrollbars in a Worksheet
• TIP #22 - How to Set the Number of Worksheets
• TIP #23 - Using Status Bar to Get a Quick Summary of your Data
• TIP #24 - How to Hide the Status Bar
• TIP #25 - How to Display a Print Preview using Keyboard Shortcuts
• TIP #26 - How to Freeze Titles and Split Screens
• TIP #27 - Saving Excel as a Read-only Webpage File
• TIP #28 - How to Analyze an Active Workbook using the Inquire Add-in
• TIP #29 - Getting Data from a PDF File

Formatting

• Formtting Downloadable Materials
• TIP #30 - Solving the Problem with Leading Zero
• TIP #31 - How to Add Text to a Number
• TIP #32 - How to Enter a Line Break into One of Your Cell’s Data
• TIP #33 - How to Show Hours Greater than 24
• TIP #34 - Using Wrap Text and Shrink Cell Tools to Have the Size of the Cells
• TIP #35 - Creating a Conditional Format based on a value
• TIP #36 - Identifying and Fixing Error inside of an Excel Formula
• TIP #37 - Converting Formulas to Values Easily
• TIP #38 - How to Add Comments Inside Formulas
• TIP #39 - How to Highlight Formula Cells using Conditional Formatting
• TIP #40 - How to Color Rows using Conditional Formatting - Alternate Row Shading
• TIP #41 - How to Format Numbers using Keystroke Shortcuts
• TIP #42 - How to Modify or Create a Cell Style
• TIP #43 - Duplicate Records and How to Get Rid of These
• TIP #44 - Creating Bulleted List
• TIP #45 - Format Painter Tips
• TIP #46 - How to Insert a Watermark
• TIP #47 - Making Your Comments Stand Out
• TIP #48 - How to Compare Two Lists of Items and Identify Differences between the

Organising and Working with Data

• Organising and Working with Data Downloadable Materials
• TIP #49 - How to Select Data using Various Keystroke Shortcuts
• TIP #50 - Copying and Moving Data with Various Ways
• TIP #51 - Using Fill Handle to Enter Sequences of Values
• TIP #52 - The Paste Special Feature
• TIP #53 - Using Quick Analysis Tool to find Information about Your Data
• TIP #54 - How to Set a Specific Print Area
• TIP #55 - How to Create a Hyperlink to Other Worksheets
• TIP #56 - How to Create and Save Custom Views of a Worksheet
• TIP #57 - Presenting Information in a More Compact Way, using Group Tool
• TIP #58 - Applying Goal Seek Tool to Find a Target
• TIP #59 - Using Pick from Drop-down List and Enter Data Quickly
• TIP #60 - Forecasting Scenarios with Scenario Manager
• TIP #61 - Using AutoCorrect to Enter Data Fast
• TIP #62 - Creating a Custom List
• TIP #63 - How to Create a Data Entry Form
• TIP #64 - Eliminating Duplicate Values from a Data List
• TIP #65 - Monitoring the Value in a Worksheet using Watch Window Tool
• TIP #66 - Filling non-adjacent Cells with the Current Entry
• TIP #67 - Highlighting Blank Cells and Sorting Columns with Them
• TIP #68 - Making the Rows Columns and the Columns Rows

Numbers, Dates and Formulas

• Numbers, Dates and Formulas Downloadable Materials
• TIP #69 - Filling Random Values into Every Cell in a Selection
• TIP #70 - How to use COUNTIF and Data Validation to Prevent Duplications
• TIP #71 - How to Enter a Series of Dates using Auto Fill Options
• TIP #72 - The AutoSum Magic
• TIP #73 - How to Enter the Current Date or Time
• TIP #74 - Finding Related Formulas Easy
• TIP #75 - How to Calculate the Number of Days between Two Date values
• TIP #76 - How to Look up Data without using VLOOKUP Function
• TIP #77 - Using the new-in-Excel-2016 Function PDURATION to Calculate Time
• TIP #78 - How to Round Values with Various Ways
• TIP #79 - Calculating Running Totals
• TIP #80 - Using Named Ranges in Formulas
• TIP #81 - Displaying the Formula Text
• TIP #82 - Defining a Name that Refers to a Constant
• TIP #83 - Displaying All Worksheet Formulas in a Second
• TIP #84 - Using TRIM Function to Get Rid of Unwanted Spaces
• TIP #85 - How to Calculate a Person's Age
• TIP #86 - How to Convert Values between Measurement Systems
• TIP #87 - Locking and Hiding Formula Cells

Tables and Pivot Tables

• Tables and Pivot Tables Downloadable Materials
• TIP #88 - Create a Table using Keyboard Shortcuts
• TIP #89 - Adding a Unique Identification Number in Your Table
• TIP #90 - Formatting Data as Table with Table Styles
• TIP #91 - Using the Table Reference to Create a Formula
• TIP #92 - Create a Recommended PivotTable Quickly
• TIP #93 - How to Create Separate Worksheets from PivotTable using Filters Area

Charts

• Charts Downloadable Materials
• TIP #94 - Let Excel Decide Which Chart is Ideal for Your Data
• TIP #95 - How to Move or Resize a Chart using Special Keys
• TIP #96 - Create Mini Charts using Sparklines
• TIP #97 - Linking Chart Titles with Cell Content
• TIP #98 - How to Make all the Charts Exactly the Same Size
• TIP #99 - How to Deal with Missing Data for a Chart
• TIP #100 - Creating Simple Column and Pie Charts

Creating, Resizing and Modifying Charts

• Creating, Resizing and Modifying Charts Downloadbale Materials
• Select Data and Create a Chart with various ways
• How to select the right Chart for your Data?
• Choosing a Chart Type, Chart Templates and Live Preview
• Change the Layout or Style of a Chart

Formatting & Customising Charts

• Formatting & Customising Charts
• Getting to Know the Elements and Types of a Chart
• Quick Layouts and learn to modify all the elements of a chart
• Chart Titles and how to link them with a cell
• Learn Chart Axes in depth and add a Secondary Axis
• Displaying Data - Custom Axis Labels and Gridlines

Updating & Printing Charts

• Updating & Printing Charts
• Updating and adding new data in Chart with keyboard and mouse
• Printing a chart and the page break feature

Working With Text, Pictures, Shapes and Effects

• Working With Text, Pictures, Shapes and Effects Downloadbale Materials
• Use Pictures in Chart and how to create a Pictograph
• Using Shapes and Effects to make prettier Charts
• Using Text and Wordart fancy Styles in a Chart

Basic Chart Types

• Basic Chart Types Downloadable Materials
• COLUMN CHART ~ the most widely used chart type
• BAR CHART ~ and the main difference with Column Chart
• LINE CHART ~ show me the trend!
• PIE CHART~ show proportions to a whole
• DOUGHNUT CHART ~ a pie chart displaying more than one data series
• AREA CHART ~ useful for time periods
• SCATTER (XY) CHART ~ ideal for scientific, statistical or engineering data
• BUBBLE CHART ~ a combination of Scatter and Area Chart
• RADAR (OR SPIDER) & SURFACE CHART
• STOCK CHART ~ ideal for stocks or temperatures
• COMBO CHART ~ a combination of different chart types

Master Excel Charts

• Master Excel Charts Downloadable Materials
• GANTT CHART ~ for project management applications
• SPEEDOMETER - GAUGE CHART ~ learn how to create and use them in Dashboards
• BULLET CHART ~ a variation of a thermometer chart
• PARETO CHART ~ the 80-20 chart - for business analysis
• WATERFALL - BRIDGE CHART ~ Use them and make awesome sales analysis
• THERMOMETER CHART ~ Ideal for Measuring Performance
• SPARKLINE CHARTS & GRAPHS
• CLUSTERED STACKED COLUMN CHART
• LINE CHART WITH KPI BANDS
• HISTOGRAM ~ with Frequency Distribution
• POPULATION PYRAMID CHART
• BI-DIRECTIONAL BAR CHART - Ideal for comparing data
• TORNADO CHART - Ideal for Showing Financial Analysis

Get Started With Power Pivot and Power View

• Power Pivot Downloadbale Materials
• What are Excel Power Pivot and Power View add-ins?
• Install the PowerPivot and Power BI add-ins
• Install the PowerPivot and Power View add-in
• The Powerpivot Interface (Excel 2016)
• How to import data from PowerPivot Tab
• How to link data from PowerPivot tab
• Importing Access Data & Create Pivot Table
• The diagram view and the relations between data

Calculated Fields - Columns & Dax Formula Language

• Calculated Fields - Columns & Dax Formula Language Downloadable Materials
• Introduction to DAX Formula Language
• Calculated fields
• Calculated Columns & using Date Functions in PowerPivot
• Create calculated fields using DAX formula language
• Create filtered calculations - Using COUNTX & FILTER Functions

KPI, Key Metrics and Design Your Dashboard > PART:1

• KPI, Key Metrics and Design Your Dashboard > PART:1 Downloadable Materials
• Dashboards: Design principles and 10 golden rules
• What are KPI’s and how to create them?
• Design your Dashboard with link buttons
• Add key metrics and create our first Powerpivot pivot table

Design Your Dashboard > PART:2

• Design Your Dashboard > PART:2 Downloadable Materials
• Create pivot charts
• Complete the dashboard using slicers and timeline

Create an Amazing Sales Map With Power View

• Create an Amazing Sales Map With Power View Downloadable Materials
• Preconditions and features of Power View
• Using Matrices, Cards and Tiles to visualize Power View Data
• How to create an amazing sales map and other useful charts

Completing Your Dashboard

• Completing Your Dashboard Downloadable Materials
• Completing Dashboards with Pivot Charts

Analysing a Dashboard With Functions

• Analysing a Dashboard With Functions Downloadable Materials
• Dashboards: Design principles and 10 golden rules
• VLOOKUP Function and Data Validation Case Study
• HLOOKUP function
• SUMPRODUCT Function

Beginners: Introduction to Charts

• Introduction to Charts Dowwnloadable Materials
• Select Data and Create a Chart with various ways
• How to select the right Chart for your Data?
• Three ways to resize a Chart and move it to any location
• Choosing a Chart Type, Chart Templates and Live Preview
• Change the Layout or Style of a Chart
• Updating and adding new data in Chart with keyboard and mouse
• Add new Data to an existing Chart, from different worksheets
• Linking charts with Word and Powerpoint
• Printing a chart and the page break feature

Beginners: Basic Build-in Chart Types

• Basic Build-in Chart Types Downloadable Materials
• COLUMN CHART ~ the most widely used chart type
• BAR CHART ~ and the main difference with Column Chart
• LINE CHART ~ show me the trend!
• PIE CHART~ show proportions to a whole
• DOUGHNUT CHART ~ a pie chart displaying more than one data series
• AREA CHART ~ useful for time periods
• SCATTER (XY) CHART ~ ideal for scientific, statistical or engineering data
• BUBBLE CHART ~ a combination of Scatter and Area Chart
• STOCK CHART ~ ideal for stocks or temperatures
• RADAR (OR SPIDER) & SURFACE CHART
• COMBO CHART ~ a combination of different chart types

Formatting & Customising Charts

• Formatting & Customising Charts Downloadable Materials
• Getting to Know the Elements of a Chart
• Quick Layouts and learn to modify all the elements of a chart
• Chart Titles and how to link them with a cell
• How to use dynamic labels, titles and make your chart interactive
• Learn Chart Axes in depth and add a Secondary Axis
• Displaying Data - Custom Axis Labels and Gridlines
• How to use Statistics with Line Chart - choose the best trendline
• How to link data with the graph and how to fill the gaps of a chart

Graphics for Charts & Dashboards

• Graphics for Charts & Dashboards Downloadable Materials
• Use Pictures in Chart and how to create a Pictograph
• Unlinking a Chart and saving it as a picture - Static charts
• Excel’s Camera Tool and how we can use it
• Using Shapes and Effects to make prettier Charts
• Using Text and Wordart fancy Styles in a Chart

Dashboards Components: Sparklines - Mini Charts

• Dashboards Components: Sparklines - Mini Charts Downloadable Materials
• What are Sparklines?
• In-cell charting techniques using REPT function
• Create Data Bars with Conditional Formatting

Advanced Charts & Dashboard Components

• Advanced Charts & Dashboard Components Downloadable Materials
• POPULATION PYRAMID CHART
• BI-DIRECTIONAL BAR CHART - Ideal for comparing data
• TORNADO CHART - Ideal for Showing Financial Analysis
• CLUSTERED STACKED COLUMN CHART
• LINE CHART WITH KPI BANDS
• GANTT CHART ~ for project management applications
• SPEEDOMETER - GAUGE CHART ~ learn how to create and use them in Dashboards
• PARETO CHART ~ the 80-20 chart - for business analysis
• WATERFALL - BRIDGE CHART ~ Use them and make awesome sales analysis
• BULLET CHART ~ a variation of a thermometer chart
• THERMOMETER CHART ~ Ideal for Measuring Performance
• HISTOGRAM ~ with Frequency Distribution

Interactive Charts & Dashboards Controls

• Interactive Charts & Dashboards Controls Downloadable Materials
• Dynamic Chart with Tables
• Dynamic Chart with Data Filters
• Dynamic Chart with OFFSET Function
• Dynamic Chart with INDEX function & Combo Box (Form Control)
• Dynamic Chart with Scroll Bar (Form Control)
• Dynamic Chart with Check Box (Form Control)
• Dynamic Chart with List Box (Form Control)

Analysing a Dashboard With Pivot Tables & Charts

• Analysing a Dashboard With Pivot Tables & Charts Downloadable Materials
• The anatomy of a Pivot Table
• How to create a Pivot Chart using different methods
• Modifying Pivot Chart and how to use Slicers to filter data
• Changing the format of a Pivot Chart and the role of field buttons
• CASE STUDY ~ Analyze Facebook Ad Reports with Excel Pivot Tables and Charts

Dashboards: Putting it All Together

• Dashboards: Putting it All Together Downloadable Materials
• Create a Dashboard using Sparklines
• Create an Excel Traffic Lights Sales Dashboard - Part:1
• Create an Excel Traffic Lights Sales Dashboard - Part:2
• Create an Excel Traffic Lights Sales Dashboard - Part:3
• Create an Excel Traffic Lights Sales Dashboard - Final Part
• Create a Dashboard using Pivot Tables & Charts - part: 1
• Create a Dashboard using Pivot Tables & Charts - part: 2
• Create a Dashboard using Pivot Tables & Charts - part: 3

The Basics of Macros

• The Basics of Macros Downloadable Materials
• Let’s get started by answering the most common questions
• What are macros and how we can use them?
• Why macros are huge time-savers? See examples
• Macros and Security
• Ways of Recording a Macro
• Run a Macro using the Ribbon
• Run a Macro using the Quick Access Toolbar
• Run a Macro using Buttons or Graphics

Enter the world of VBA

• Enter the world of VBA Downloadablle Materials
• Enter the world of Excel VBA
• Dealing with VBA Errors
• Macro Recorder vs VBA
• The VBA Environment
• VBA Editor Tips

Get in-depth with Macros

• Get in-depth with Macros Downloadable Materials
• The Anatomy of Macros
• Make a macro available all the time
• Organising your Personal Macro Workbook
• Recording your first Macro
• Case Study : Use navigation buttons to change Chart Type

How to create your First Piece of Code

• How to create your First Piece of Code Downloadable Materials
• Hello World!
• The core of VBA: Workbook, Worksheet and Range
• With …End With : how this statement can save you time
• Dim and Set statements
• How to read from a cell
• Range and Cells properties
• Variables

Supercharge Macros and VBA with Loops

• Supercharge Macros and VBA with Loops
• Using IF..ELSE Statements with Macros
• Using Relative or Absolute Reference
• Using FOR…NEXT Statements with Macros
• Using DO…UNTIL Statements with Macros
• Case Study: Prompting a user to select a worksheet and run a macro
• Combining Macro Recording and VBA Statements
• How to join 2 different Macros
• Tips for running your Macros fast

Ready-to-use Excel Macros

• Ready-to-use Excel Macros Downloadable Materials
• Saving a Workbook before Closing
• Closing All Workbooks at Once

Quiz

• Part 1
• Part 2
• Part 3
• Part 4
• Part 5
• Part 6
• Part 7
• Part 8
• Part 9
• Part 10
• Part 11
• Part 12
• Part 13
• Part 14
• Part 15
• Part 16
• Part 17
• Part 18
• Part 19