Microsoft Excel - 300 Module Masterclass!

Course

Online

Save 80%
£ 19 VAT inc.

Description

  • Type

    Course

  • Methodology

    Online

  • Start date

    Different dates available

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 creative 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 TYPE_IDs 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!

Facilities

Location

Start date

Online

Start date

Different dates availableEnrolment now open

About this course

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

Anyone

None

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Subjects

  • Excel
  • Access
  • Microsoft Excel
  • Design
  • Sales
  • Graphics
  • Printing
  • Materials
  • Microsoft excel training
  • MS Excel
  • Sales Training

Course programme

My Top Ten (10) Hacks and Tips
My Top Ten (10) Hacks and Tips - Downloadable Materials
Say hi to your fellow students!
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_ID, 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 TYPE_IDs 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 TYPE_IDs
Basic Chart TYPE_IDs Downloadable Materials
COLUMN CHART ~ the most widely used chart TYPE_ID
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 TYPE_IDs

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_ID, 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 TYPE_IDs
Basic Build-in Chart TYPE_IDs Downloadable Materials
COLUMN CHART ~ the most widely used chart TYPE_ID
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 TYPE_IDs

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_ID

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

Microsoft Excel - 300 Module Masterclass!

£ 19 VAT inc.