ITKokka
Lesson 7 · Note 13

Creating a Chart

📌 The Short Note

Download

📖 Explanation

Creating a Chart in a Spreadsheet

A spreadsheet chart presents worksheet data visually. Instead of comparing numbers only in rows and columns, a chart displays the information using bars, lines, slices, or plotted points. This makes data easier to understand, compare, and communicate.

Charts can be created from values entered directly into a worksheet or from values calculated using formulas. This note explains the basic steps for creating a chart, adding a chart title and axis labels, and creating an XY (Scatter) chart using calculated values.

Steps to Create a Chart

Creating a chart usually involves selecting the data, inserting a chart, choosing a suitable chart type, and completing the chart with useful labels and formatting.

Step 1: Select a Data Range

A data range is a group of cells containing the information that will be used to create a chart.

Before inserting a chart:

  1. Enter the data into the worksheet in an organised table.
  2. Include suitable headings for the columns or rows.
  3. Select the cells containing the data, including the headings.

Including headings helps spreadsheet software identify the names of the data series and categories in the chart.

Example: Monthly Sales Data
MonthSales (Rs.)
Jan4,000
Feb6,000
Mar5,000
Apr7,000
May6,000

For this example, select the range containing both columns and all the headings and values. In the worksheet shown in the note, the selected range is A1:B6.

Step 2: Insert a Chart

After selecting the data range:

  1. Go to the Insert tab on the spreadsheet ribbon.
  2. Find the chart options.
  3. Click the chart type you want to insert, or open the chart menu to view more choices.

The Insert tab provides different chart types, such as Column, Bar, Line, Pie, and Scatter.

Step 3: Select the Chart Type

Choose a chart type that communicates the data clearly. The best choice depends on what you want the reader to understand.

Common choices include:

  • Column: compares categories using vertical bars.
  • Bar: compares categories using horizontal bars.
  • Line: shows changes and trends over time.
  • Pie: shows how categories contribute to a whole.
  • XY (Scatter): shows the relationship between two numerical variables.

Some spreadsheet applications also provide different chart styles, such as 2-D and 3-D versions. Choose a style that keeps the information clear and readable.

Step 4: Complete the Chart

After selecting the chart type, the chart is inserted into the worksheet. You can then improve it by adding or editing:

  • A chart title.
  • Horizontal and vertical axis titles.
  • A legend, when needed.
  • Data labels, when useful.
  • Other formatting options.

A completed chart should make the meaning of the data clear without unnecessary decoration.

Chart Titles and Axis Labels

A chart title and axis labels help readers understand what the chart represents.

Chart Title

A chart title describes the subject of the chart. It should briefly explain what information is being presented.

For example, Monthly Sales of a Shop is a suitable title for a chart showing a shop's sales over several months.

Axis Labels

An axis is a reference line on a chart. Many charts use two axes:

  • Horizontal axis (X-axis): usually displays categories or the independent variable.
  • Vertical axis (Y-axis): usually displays values or the dependent variable.

An axis title explains what the values or categories on an axis represent.

Example: Monthly Sales Chart

For a chart showing monthly sales:

  • Chart title: Monthly Sales of a Shop
  • Horizontal axis title: Month
  • Vertical axis title: Sales (Rs.)

These labels tell the reader that the chart compares sales amounts across different months.

Adding Chart Titles and Axis Titles

The note shows the following procedure using the Chart Elements (+) button:

  1. Click the chart to select it.
  2. Click the Chart Elements (+) button beside the chart.
  3. Select Chart Title and Axis Titles.
  4. Click each title placeholder and type the required text.

The Chart Elements menu can also provide options such as a legend, data labels, and gridlines. Select only the elements that help explain the chart.

Creating a Chart from a Formula

A chart does not have to use only values typed directly into cells. It can also use values calculated by spreadsheet formulas.

The example in the note creates a chart for the mathematical relationship:

[ Y = X^2 ]

The worksheet stores the X-values in one column and calculates the corresponding Y-values in another column.

Step 1: Enter the X-values

Enter the input values into a worksheet column. In the example, the X-values are 1, 2, 3, 4, and 5.

Step 2: Write a Formula to Calculate Y

Enter a formula that calculates the square of each X-value.

If the X-value is stored in cell A2, the formula in B2 is:

=A2^2

The ^ operator is used for exponentiation. Therefore, A2^2 means the value in cell A2 raised to the power of 2.

The results are:

XY = X²
11
24
39
416
525

Step 3: Copy the Formula

Use the Fill Handle to copy the formula to the cells below.

When the formula is copied down, the relative cell reference changes to match the new row:

CellFormulaResult
B2=A2^21
B3=A3^24
B4=A4^29
B5=A5^216
B6=A6^225

The spreadsheet calculates each result using the X-value in the same row.

Step 4: Insert an XY (Scatter) Chart

To plot the relationship between X and Y:

  1. Select both the X-values and the calculated Y-values, including the relevant headings if available.
  2. Go to the Insert tab.
  3. Choose an XY (Scatter) chart.
  4. Select a suitable scatter chart style.

The scatter chart plots each pair of values as a point. The X-value determines the point's horizontal position, and the Y-value determines its vertical position.

For the equation (Y = X^2), the plotted points are (1, 1), (2, 4), (3, 9), (4, 16), and (5, 25).

The points show an increasing relationship: as X increases, Y also increases, and the increase in Y becomes larger for each equal step in X.

Why Use an XY (Scatter) Chart for Formula Results?

An XY scatter chart is appropriate when both variables are numerical and each observation is a pair of values.

In the formula example:

  • X is the input value.
  • Y is the calculated result.
  • Each plotted point represents one X-Y pair.

This differs from a chart that uses category labels such as month names. A scatter chart uses numerical positions on both axes, making it suitable for showing the relationship between two numerical variables.

Exam-Focused Points

Key Idea: To create a chart, select the data range (including headings), use the Insert tab, choose a suitable chart type, and complete the chart with titles and labels.

Key Idea: A chart title describes the chart, while axis titles explain what the horizontal (X) and vertical (Y) axes represent.

Key Idea: An XY (Scatter) chart is used to plot pairs of numerical values and show the relationship between two numerical variables.

Students should be able to describe the chart-creation steps in the correct order, identify the purpose of chart titles and axis labels, and explain how formula results can be plotted in a scatter chart.

Summary

A spreadsheet chart is created by selecting a data range, inserting a chart, choosing a suitable chart type, and adding titles or labels to make the information clear. Charts can use values entered directly into cells or values calculated with formulas.

For formula-based charts, enter the input values, calculate the results with a formula, copy the formula where necessary, and select both sets of values to create the chart. An XY (Scatter) chart is useful for displaying the relationship between the two numerical variables.

Share:
← Why Use Charts?