Unit 2: Charts and Graphs in Excel

1. Creating Charts and Graphs in Excel

Charts are visual representations of your data. Using the right chart type is essential for communicating your findings clearly.

General Steps to Create Any Chart:

1. Select Data: Select the cells containing the data you want to plot. Include the headers (labels) for your data.
2. Insert Chart: Go to the Insert tab on the Ribbon. In the Charts group, you will see icons for all the major chart types.
3. Choose Chart Type: Click the icon for the chart you want (e.g., Column or Bar Chart, Line Chart).
4. Refine: Once the chart appears, you can use the Chart Design and Format tabs (which appear when the chart is selected) to customize it.

2. Bar Charts

Also known as Column Charts (if vertical). They are used to compare frequencies or values across different categories.

• Best For: Categorical data (e.g., 'Sales by Region', 'Number of Students by Major').
• How to:
  1. Select your categories (e.g., 'Region') and values (e.g., 'Sales').
  2. Go to Insert > Charts > Insert Column or Bar Chart.
  3. Choose a 2-D Column (vertical) or 2-D Bar (horizontal) chart.
• Key Feature: There are gaps between the bars to show that the categories are distinct.

3. Line Graphs

Line graphs are used to show trends over time or another continuous sequence.

• Best For: Time-series data (e.g., 'Stock Price over 30 Days', 'Monthly Rainfall', 'Website Visits per Day').
• How to:
  1. Select your time data (e.g., 'Date') and value data (e.g., 'Stock Price').
  2. Go to Insert > Charts > Insert Line or Area Chart.
  3. Choose a 2-D Line with Markers for clarity.
• Key Feature: The line connects the data points, showing the progression and rate of change.

4. Pie Charts

Pie charts are used to show the proportions of categories as part of a whole (100%).

• Best For: Showing "market share" or percentage breakdown (e.g., 'Budget Allocation by Department', 'Survey Responses').
• How to:
  1. Select your categories and values.
  2. Go to Insert > Charts > Insert Pie or Doughnut Chart.
  3. Choose a 2-D Pie.
• Key Feature: All "slices" add up to 100%.
• Exam Tip: Pie charts are bad for comparing more than 5-6 categories, as it becomes hard to read. A bar chart is often a better choice.

5. Scatter Plots

Scatter plots are used to visualize the relationship between two quantitative variables. This is the standard chart for correlation and regression analysis.

• Best For: Bivariate data (e.g., 'Height vs. Weight', 'Advertising Spend vs. Sales', 'Study Hours vs. Exam Score').
• How to:
  1. Select your two columns of quantitative data (your 'x' and 'y' variables).
  2. Go to Insert > Charts > Insert Scatter (X, Y) or Bubble Chart.
  3. Choose the first Scatter option.
• Key Feature: It shows patterns. If points trend upwards, it's a positive correlation. If downwards, it's negative.

6. Customizing Charts and Adding Visual Elements

A basic chart is rarely ready for a final report. It must be customized.

When your chart is selected, click the "+" icon (Chart Elements) on the top-right to add/remove:

• Chart Title: Always add a clear, descriptive title.
• Axis Titles: Essential. Label your X-axis and Y-axis, including units (e.g., "Sales (in $ '000s)").
• Data Labels: Adds the exact value to each bar, point, or slice. Can make the chart look cluttered.
• Legend: Necessary if you are plotting multiple data series (e.g., 'Sales 2024' and 'Sales 2025' on the same line graph).
• Gridlines: Can help in reading values, but can also be removed for a cleaner look.

Other Formatting:

• Changing Colors: Use the Chart Design tab or right-click a data series (e.g., the bars) and select Fill.
• Changing Font: Select the text (like the title or axis label) and use the Home tab.

7. Formatting and Labeling Axes

This is one of the most important parts of chart customization.

1. Add Axis Titles: Click Chart Elements ("+") > Axis Titles. Click the new text boxes on the chart to type your labels.
2. Format the Axis Scale:
   • Right-click the axis you want to change (e.g., the numbers on the Y-axis).
   • Select Format Axis...
   • A Format Axis pane will open. Under Axis Options, you can set:
     • Bounds (Min/Max): Change the start and end values of the axis (e.g., start at 0 instead of -10).
     • Units (Major/Minor): Change the spacing of the gridlines and labels.
     • Number: Change the number format (e.g., to Currency, Percentage, or add/remove decimal places).

8. Adding Trendlines

A trendline (or line of best fit) is a line superimposed on a chart to show the general pattern or direction of the data. It is most often used on Scatter Plots and Line Graphs.

1. Create your chart (e.g., a Scatter Plot).
2. Select the chart.
3. Click the Chart Elements ("+") icon.
4. Check Trendline.
5. For more options, click the small arrow next to Trendline and select More Options...
6. In the Format Trendline pane, you can select the type of fit:
   • Linear: A straight line (for linear regression).
   • Polynomial: A curved line (for polynomial regression).
   • Exponential: For exponential growth.
7. (Most Important) Check the boxes for "Display Equation on chart" and "Display R-squared value on chart". This is the core of regression analysis (covered in Units 4 & 5).