Charts in spreadsheets and their elements

Column, bar, line and pie charts; chart area, plot area, title, axes, legend, data series and labels; a sector’s share p = x / S · 100% — with solutions of DİM’s “pie chart on formula cells” and “revenue chart” tasks.

  • Grade 9
  • 22 min
  • 12 questions
  • Lesson 9 of 59

In this lesson you will learn

  • Choose the right kind of chart for the data and build it in Excel
  • Recognise and name the elements of a chart (chart and plot area, title, axes, legend, data series, labels)
  • Find a sector’s percentage and angle in a pie chart, and the total of all values from one sector
  • Find an unknown cell from a chart built on formula cells, and pick the correct revenue chart

Each of DİM’s four entrance exams of 2025–2026 had one task on the topic Elektron cədvəldə diaqramlar və onların elementləri (charts in spreadsheets and their elements). They come in two kinds. In the first, a pie chart is built on cells calculated by formulas, and you must find which number to enter in a cell so that the chart looks as shown. In the second, two charts are given — one of quantities, one of prices — and you choose the chart that shows the revenue correctly. Both rest on one formula: a sector’s share = value / total. First, the kinds of charts and their elements.

Charts and their kinds

ChartDefinition

A graphical picture of the numeric data in a table. A chart is linked to its data: as soon as a number or a formula result in a cell changes, the chart changes too.

TypeExcelWhen to choose itExample
ColumnColumncomparing categoriespupils in each class
BarBarlong category names; the bars are horizontalpopulation of countries
LineLinechange over timetemperature during a week
PiePieparts of a whole (percentages); one data series onlyhow a family budget is split
  1. Select the data

    Together with the headers, for example A1:C5. The headers become axis labels and legend entries.

  2. Choose the type

    In Insert › Charts: Column, Bar, Line, Pie. Alt+F1 inserts a default chart on the same sheet, F11 creates it on a separate chart sheet.

  3. Add elements

    Chart Design › Add Chart Element: title, axis titles, legend, data labels, gridlines.

  4. Change it if needed

    Change Chart Type changes the type, Switch Row/Column swaps the roles of rows and columns.

A chart helps to “read” the numbers of a table, but it has to be read correctly. In column and bar charts a value is shown by the height of a column (the length of a bar) and read on the scale; in a line chart the main message is the slope of the line: when it goes up, the value grows. A pie chart does not show absolute values at all — only shares. So be careful when comparing two pie charts: a 50% sector may mean 20 items in one chart and 200 in another.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

The elements of a chart

01020304028A36B18C30DBook salesBooks soldShop20252026123456789
The elements of a column chart. The numbers are explained in the list below.
  1. Chart area — the frame around the whole chart; all other elements lie inside it.
  2. Plot area — the area bounded by the axes, where the data are drawn.
  3. Chart title.
  4. Value axis — the vertical axis with its scale and axis title.
  5. Category axis — the horizontal axis; here the shops A, B, C, D.
  6. Legend — shows which colour belongs to which data series: 2025 and 2026.
  7. Data series — the columns of one colour: the sales of 2026.
  8. Data labels — the value written on top of a column.
  9. Gridlines — horizontal lines that help to read the values.

Data seriesDefinition

Values taken from one row or column of a table and drawn in one colour. Column, bar and line charts can show several series; a pie chart shows only one: each sector is one value of the series, there are no axes, and the labels usually show percentages.

Pie charts: the share of a sector

p = x / S · 100%α = x / S · 360°p = x / S · 100%α = x / S · 360°

Where

x
the value of the cell behind the sector
S
the sum of all values in the range
p
the sector’s share in per cent
α
the central angle of the sector

The percentages of all sectors add up to 100%, their angles to 360°. When one value changes, S changes too, so every percentage changes.

Example 1. Percentages and angles

Problem

The range B1:B3 holds 45, 30 and 75. A pie chart is built on it. Find each sector’s percentage and angle.

Show solutionHide solution

Solution

S = 45 + 30 + 75 = 150.
45 / 150 = 0.3 → 30%, angle 0.3 · 360° = 108°.
30 / 150 = 0.2 → 20%, angle 72°.
75 / 150 = 0.5 → 50%, angle 180°.
Check: 30 + 20 + 50 = 100%, 108 + 72 + 180 = 360°.

Example 2. From one sector to the total

Problem

In a pie chart the sector of the number 18 is 15%. 1) What is the total of all values? 2) Which number belongs to the 40% sector? 3) What is the angle of the 15% sector?

Show solutionHide solution

Solution

1) 18 is 15% of the total: S = 18 / 0.15 = 120.
2) 0.4 · 120 = 48.
3) 0.15 · 360° = 54°.

DİM-type tasks

In DİM’s chart task the chart is usually built not on plain numbers but on cells calculated by formulas. So the task joins two topics: first you copy the formula and find the value of each cell (as in the lesson on addresses), then you use the percentages of the pie chart. The algorithm below works for all tasks of this type.

  1. Copy the formulas

    Write the formula of every cell of the charted range; parts with $ do not change.

  2. Compute the known values

    Mark the unknown cell with x: for example, D2 = 3x + 2.

  3. Match percentages to cells

    The ratio of the known values must equal the ratio of their percentages.

  4. Find the total, then x

    S = x / p · 100 from a known sector; the unknown cell = S − the sum of the known cells. Finally check that the percentages add up to 100%.

ABCD
1742=A1*B1+C$1
23?5
3927
Example 3: the formula in D1 is copied to D2 and D3; the value of B2 is unknown.

Example 3. A pie chart on formula cells (DİM type)

Problem

In the fragment above, the formula in D1 has been copied to D2 and D3. The pie chart built on D1:D3 has sectors of 30%, 50% and 20%. Which number is in B2?
A) 12 B) 15 C) 16 D) 48 E) 50

Show solutionHide solution

Solution

1) Copy the formulas (copying down changes only the row, and C$1 is locked):
D1: =A1*B1+C$1 = 7 · 4 + 2 = 30;
D2: =A2*B2+C$1 = 3 · B2 + 2;
D3: =A3*B3+C$1 = 9 · 2 + 2 = 20.
2) D1 : D3 = 30 : 20 = 3 : 2, which matches 30% : 20%. So D1 is 30%, D3 is 20% and D2 is 50%.
3) 30 is 30% of the total: S = 100, D2 = 50.
4) 3 · B2 + 2 = 50 → B2 = 16.
Answer: C) 16. B (15) comes from copying C$1 as if it were relative (D2 = 3 · B2 + 5), E (50) from confusing D2 with B2.

Interactive

Loading simulation…
Checking the answer of Example 3: D1 = 30, D2 = 50, D3 = 20 (data made up for the task).

Example 4. From a known sector to an unknown cell (coded answer)

Problem

E1 holds =2*(C1+D1), copied to E2:E4. C1:C4 hold 5, 8, x, 4 and D1:D4 hold 7, 4, 3, 2. In the pie chart built on E1:E4, the sector of E4 is 10%. What is x? What percentage is the sector of E1?

Show solutionHide solution

Solution

E1 = 2 · (5 + 7) = 24; E2 = 2 · (8 + 4) = 24; E3 = 2 · (x + 3); E4 = 2 · (4 + 2) = 12.
12 is 10% of the total: S = 120.
24 + 24 + 2 · (x + 3) + 12 = 120 → 2 · (x + 3) = 60 → x + 3 = 30 → x = 27.
Share of E1: 24 / 120 = 0.2 → 20%.
Answer: 27; 20%.

Interactive

Loading simulation…
The first chart of Example 5 (data made up for the task).

Interactive

Loading simulation…
The second chart of Example 5.

Example 5. From two charts to a revenue chart (DİM type)

Problem

A shop sold 60 items of products A, B, C and D in total. The pie chart shows how the quantity is split (in per cent), the column chart shows the price of one item (above). Which column chart shows the total revenue per product correctly? (Column heights are given in the order A, B, C, D.)
A) 120, 120, 90, 72
B) 8, 5, 10, 6
C) 200, 200, 150, 120
D) 15, 24, 9, 12
E) 23, 29, 19, 18

Show solutionHide solution

Solution

1) Turn the percentages into quantities: A — 60 · 0.25 = 15, B — 60 · 0.4 = 24, C — 60 · 0.15 = 9, D — 60 · 0.2 = 12.
2) Revenue = quantity · price: A — 15 · 8 = 120, B — 24 · 5 = 120, C — 9 · 10 = 90, D — 12 · 6 = 72.
Answer: A. B shows the prices and D the quantities; C multiplies the percentages by the prices (the same shape, but the wrong scale), and E adds quantity and price.

Example 6. Choosing the type of chart

Problem

Which kind of chart suits best?
1) The air temperature in Baku during a week.
2) The shares of a family’s monthly expenses.
3) Comparing the numbers of pupils in five schools.
4) The population of ten countries with long names.

Show solutionHide solution

Solution

1) Line — change over time.
2) Pie — parts of a whole.
3) Column — comparing categories.
4) Bar — long names fit easily next to horizontal bars.

Interactive

Loading simulation…
Percentages by formula: =D1/SUM($D$1:$D$3)*100 — exactly what a pie chart calculates.

This lesson builds on “Spreadsheets: addresses, ranges and formulas” and “Spreadsheet functions”: in DİM’s chart tasks you usually copy a formula first and then compute a percentage. Professional chart work in Excel (changing chart types, PivotCharts) is in the Excel course lesson “Charts and PivotTables”.

Remember

  • Column and bar charts compare categories, a line chart shows change over time, a pie chart shows parts of a whole.
  • Elements: chart area, plot area, title, value and category axes, legend, data series, data labels, gridlines.
  • A pie chart shows one data series only; a sector’s share is p = x / S · 100%, its angle α = x / S · 360°.
  • A chart is linked to its data: if it is built on formula cells, copy the formulas and compute their values first, then find the total from a known sector.
  • In a revenue task turn percentages into quantities first (quantity = total · p), then multiply by the price; multiplying percentages by prices gives the wrong scale.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

12 questions. Every correct answer earns XP.

Question 1 / 12

Question 1 / 12. Which chart suits best for showing the parts of a whole (percentages)?