Excel Session 3: Introduction to Data Visualization - Pie Charts and Line Graphs

Review of Previous Excel Sessions

  • Week 2 Recap: The session began by highlighting the concepts covered in the previous week, which included:     - Cell References:         - Absolute Cell References: Utilizing the dollar sign ($) to fix a specific column, row, or individual cell within a formula.         - Relative Cell References: References that change automatically when a formula is copied to another cell.     - Boolean Operators: The use of AND and OR logic.     - Conditional Functions:         - SUMIF         - COUNTIF         - AVERAGEIF         - The standard IF function.     - Nested Functions: Combining multiple functions within a single Excel formula.

Introduction to Excel Graphs

  • Purpose of Graphs: Graphs are critical tools for data visualization in organizations. Managers use them to:     - Present summaries of data (similar to tables).     - Make patterns and relationships within the data visible and easily interpretable.

  • Types of Graphs Covered (Session Overview):     - Pie Chart: Visualizing components of a whole.     - Line Graph: Showing trends/changes over time.     - Column Graph: Comparison of discrete categories (Vertical).     - Bar Graph: Comparison of discrete categories (Horizontal).

  • Data Fundamentals: To develop a graph, Excel requires numerical data. Without numbers, the software cannot depict the hidden insights within the data set.

Understanding X and Y Axes

  • Terminology and Dimensions: The session focuses on 2D (two-dimensional) graphics involving the X-axis and Y-axis, rather than 3D (Z-axis) graphics.

  • The X-Axis (Independent Variable):     - Definition: Represents values that are independent and exist regardless of secondary factors.     - Examples: ID numbers, student numbers, passport numbers, or membership numbers.     - Persistence: These values remain constant for an individual regardless of their age, location, or the courses they take.     - Transcript Example: A student's ID number will stay the same even if they change universities or take multiple courses.

  • The Y-Axis (Dependent Variable):     - Definition: Represents data that cannot exist in isolation; it depends on the independent variable.     - Examples: Marks or assessment scores. Marks cannot exist unless they are attached to a specific student.     - Analogy: A vacation trip cannot be complete without people attached to it; thus, the people are independent, and the trip details are dependent.

Pie Charts: Procedures and Properties

  • Definition and Use Case: A pie chart is used for a single set of data. It provides a "360-degree look" at the data, allowing the user to see which part or slice is larger than the others.

  • Method 1: The "Old School" Manual Selection (Option 1):     1. Select the X-axis values/Independent variables (e.g., student numbers in cells A4A4 to A9A9).     2. Hold down the Control Key (CTRL): Located at the bottom left of the keyboard.     3. While holding CTRL, select the Y-axis values/Dependent variables (e.g., Test 1 marks in cells B4B4 to B9B9).     4. Release the mouse and the CTRL key.     5. Navigate to the Insert Tab at the top of the Excel ribbon.     6. Locating the Charts Group: Find the pie chart icon and select 2D Pie.

  • Interpreting the Pie Chart:     - The bigger the slice, the higher the value.     - Example from Transcript: A yellow slice represented student number DT5115 and k, who performed the highest in Test 1.

  • Pie Chart Customization:     - Chart Title: Change by double-clicking the title area (e.g., "Test 1 Mark Distribution").     - Data Labels: Click the Plus Sign (+) (Chart Elements) and select "Data Labels" to display the exact numerical values on each slice.     - Hover Feature: Moving the cursor over a slice reveals the value and the percentage it represents (e.g., "3, which is 5% of the pie chart").     - Legends: Pie charts use color coding to link student numbers (X-axis) to slices (Y-axis).

Line Graphs: Procedures and Properties

  • Definition and Use Case: Line graphs are used to show change over time or continuous trends in data. While a pie chart compares different students at one point (Test 1), a line graph is better for assessing a single student's performance across multiple assessments.

  • Method 2: Selecting Specific Contiguous Information (Option 2):     1. Click and hold on the first value (e.g., cell A4A4).     2. Drag the mouse across to include all relevant assessments (e.g., to cell F4F4).     3. Excel defaults the first selected value as the X-axis value.     4. Go to the Insert Tab -> Chart Group -> Insert Line or Area Chart -> 2D Line.

  • Line Graph Customization:     - Axis Labels: Necessary to explain the horizontal and vertical values.         - X-axis (Horizontal): In this example, labeled as "Student Assessments".         - Y-axis (Vertical): In this example, labeled as "Marks".     - Plus Sign (+) Elements:         - Add Axis Titles.         - Add Data Labels to make the line points self-explanatory.         - Add a Legend if necessary to clarify what the line represents.     - Chart Title: Double-click to edit (e.g., "Student Marks Review").

Comparative Summary: Pie Chart vs. Line Graph

  • Dimensional Perspective:     - Pie Chart: Offers a 360-degree proportional view. It does not require X or Y axis labels because the slices represent the whole.     - Line Graph: Offers a 90-degree look at the data trends across a grid.

  • Data Requirements:     - Both can use a single set of data, but line graphs are superior for showing the progression of a single subject (one student) over time, whereas pie charts are superior for comparing multiple subjects (many students) at a single point in time.