Data Visualization using Advanced Charting Techniques in Excel


Course introduction

Numbers, in itself and of themselves tell only half the story. When faced with ploughing through a morass of numbers or reading a column graph, most people opt for the latter. Communicating numbers, analysis and trend through the use of charts is a very effective (and proven) method. This course explores chart creation techniques that go beyond the regular Microsoft Excel chart offerings.

Course Benefits

Upon completion of this workshop, participants will be able to;

  • This course equips experienced Microsoft Excel users with a practical understanding of advanced data plotting and charting techniques
  • Examines the various formatting and trending methods, and explores ways to maximize data display with charts
  • Learn to create formula-based charts, establish target values, and display meaningful charts
  • Learn to identify useful Excel Chart for any need and scenario and focus on how to properly design each type of chart for effective communication

Target Audience

Experienced Microsoft Excel users, including project leaders, data analysts, and other officers who have a vested interest and need in plotting data and charts at the industrial, corporate, and project levels.

Course Outline

Module 1: Exploring Chart Types & Options

  • Overview of Various Standard Chart Types
  • Components of a Chart
  • Managing Data Source
  • Plotting Non-Contiguous Data Ranges
  • Improving the Formatting Defaults
  • Applying Chart Layouts, Styles & Colours
  • Working with Chart Labels
  • Modifying Chart Background
  • Formatting Chart Elements
  • Formatting Axis
  • Managing Chart Templates (Custom Charts)
Module 2: Enhanced Column, Bar & Lines Charts
  • Working with Dual Category Axis
  • Using Multiple Category Labels
  • Creating Column Line Combination Chart with Secondary Axis
  • Using Date Based Category Chart Axis
  • Adding Average Lines
  • Working with Positive & Negative Axis Labels
  • Displaying Total/Labelling Last Data Point
  • Creating Pictographs
  • Waterfall Charts
  • Funnel/Pyramid Charts
Module 3: XY Scatter Charts & Trend Lines
  • Scatter vs. Line
  • When to Use a Scatter Chart
  • When to Use a Line Chart
  • Creating an XY Scatter Chart
  • Colourization of XY Scatter Points
  • Adding Vertical Line
  • Adding Horizontal Line
  • Dot Plot Visualisation
  • Trend Lines
  • Adding a Linear Trendline
  • Specifying Number of Periods for Forecast
  • Creating Drop Lines
  • Drop Lines in Line & Area Charts
  • Drop Lines if Scatter Charts
  • Filling Area Below/Between XY Series
Module 4: Interactive Charts
  • Creating Dynamic Charts
  • Creating Scroll Bars
  • Creating Conditional Charts
  • Creating a Column or Line Conditional Chart
  • Special Format for Minimum & Maximum
  • Line Chart with Background Bands
  • Plotting Survey Data (Dual Convergent Scales)
  • Thermometer Charts (Target Columns)
Module 5: Pie Charts & Pictographs
  • Pie of Pie
  • Gauge Chart Visualizations
  • Creating Pictographs
  • Around the Clock Plotting
Module 6: Data Analysis with Charts
  • Forecasting using Moving Averages
  • Calculating a Moving Average
  • Charting a Moving Average
  • Refining Moving Average
  • Histograms
  • Creating Histogram using the Analysis ToolPak
  • Creating Comparative Histogram
  • Trend Charts and Missing Data
Module 7: Useful Workarounds
  • Using Third-Party Add-Ins
  • Labelling Data Points Directly
  • Constructing Small Multiples

Available Course Sessions

Please click here to stay updated on upcoming sessions.

Trainer Profile

Microsoft Certified Trainer

All of our Associate Trainers offer our high level of service therefore are subject to regular peer assessments and interview prior to joining our Institute. They must all be qualified and have relevant experience to an agreed level and are subjected to continual audit and evaluation. Strict internal assessment is implemented as an integral part of our quality control mechanism.