Data Visualization using Advanced Charting Techniques in ExcelIT Short Courses

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.

Key Takeaways

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

Who Should Attend

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.

Programme 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

Methodologies

A good foundation in Excel is essential for this course. Participants must have been using Excel regularly and have no difficulty creating formulas and cell formats. Having attended an advanced course is advantageous.

A combination of lectures, explicit teaching, practical demonstrations and peer teaching is applied to this course.

Experiential learning approach is adopted where the learners will be required to consider the experience as a learning process joined with critical reflection which further qualifies for this theory to be applied for this course because, at each segment, the learners are introduced to some form of experience after which they are asked to reflect on these experiences before sharing and putting ideas into practice.