Spreadsheets: Choosing, Creating and Formatting Charts

“A picture is worth a thousand numbers.” A chart lets a reader see patterns and comparisons at a glance. In this post you will learn to choose the right type of chart, create it in Excel, and format it with titles, labels and a legend.

Build on what you learned in Forms 1–3

These Lower School ICT posts cover the basics of this topic:

In this post:

  • Types of chart and when to use each
  • Creating a chart step by step
  • Parts of a chart
  • Formatting and changing a chart
  • Common mistakes

Choosing the right chart

ChartBest forExample
Column / bar chartComparing amounts across categoriesRainfall in each Caribbean island; marks of each student
Line graphShowing change over time (trends)Temperature each month; sales over a year
Pie chartShowing parts of a whole (percentages), one data series onlyHow a family budget is divided; votes per candidate
Scatter (XY) chartRelationship between two sets of numbersHours studied vs exam mark

Creating a chart

AB
1MonthVisitors (thousands)
2Jan42
3Feb58
4Mar47
5Apr35

Practice data – type this into a new worksheet.

In Excel:

  1. Select the data including the headings (A1:B5). To select columns that are not next to each other, hold Ctrl.
  2. Insert → choose a chart type in the Charts group (e.g. Clustered Column), or Insert → Recommended Charts.
  3. The chart appears on the worksheet. Drag it to position it; drag the corners to resize.
  4. To put the chart on its own sheet: Chart Design → Move Chart → New sheet.

Video: Excel: Charts – https://www.youtube.com/watch?v=_Wu7jYTr1Pk

Parts of a chart

  • Chart title – says what the chart shows.
  • Axis titles – label the x-axis (categories, e.g. Month) and y-axis (values, e.g. Visitors in thousands).
  • Legend – explains the colours when there is more than one data series.
  • Data labels – show the actual value or percentage on each bar or slice.
  • Data series – the set of values being plotted.
  • Gridlines – help read values.

Formatting and changing a chart

  • Click the chart, then the + (Chart Elements) button to add or remove the title, axis titles, legend and data labels.
  • Chart Design → Change Chart Type to switch, e.g., from column to line.
  • Chart Design → Select Data to change the range or add a series.
  • Switch Row/Column swaps what is on the x-axis and in the legend.
  • Double-click any element to format its colour, font or number format.

Google Sheets: Insert → Chart, then use the Chart editor (Setup and Customize). LibreOffice Calc: Insert → Chart opens a step-by-step wizard.

Common mistakes

  • Using a pie chart for data that does not make up a whole, or with too many slices.
  • Forgetting the chart title or axis titles (marks are often lost here).
  • Selecting a total row along with the data, which distorts the chart.
  • Using a line graph for categories that are not in time order.

Try it: Choose the chart

Choose the most suitable chart for each set of data.

Monthly rainfall in Barbados over one year, to show the trend
The percentage of a school’s students in each house
Comparing the population of five Caribbean islands
Whether taller students tend to have bigger shoe sizes

Related

Related: Excel Charts, Pivot Tables Intro (recorded class)

Paper 1 practice (multiple choice)

Choose the best answer for each question. Try them all before you check.

1. Which chart is BEST for showing how a whole is divided into parts?
(A) Line graph
(B) Pie chart
(C) Scatter chart
(D) Column chart

2. The part of a chart that explains what each colour represents is the
(A) title
(B) legend
(C) axis
(D) gridline

3. To show a trend in temperature over twelve months, the best chart is a
(A) pie chart
(B) line graph
(C) bar chart of categories
(D) table

4. Before inserting a chart you should first
(A) save the file
(B) select the data, including headings
(C) sort the data descending
(D) freeze panes

5. Values shown directly on the bars of a chart are called
(A) data labels
(B) legends
(C) axis titles
(D) gridlines

Show answers

1. B
2. B
3. B
4. B
5. A

Paper 2 practice (structured question)

A Form 5 class recorded the number of tourists (in thousands) arriving in Tobago each month for one year, and how the tourists travelled (air or sea).

(a) Recommend a suitable chart for showing the monthly trend and give a reason. (2 marks)

(b) Name a chart for showing the proportion who came by air and by sea, and give a reason. (2 marks)

(c) State TWO chart elements that should be added to make the chart in (a) easy to understand. (2 marks)

(d) Outline the steps to create the chart in (a) on a separate sheet. (2 marks)

Total: 8 marks

Show answers

(a) Line graph (1) – shows change over time/trends clearly (1).

(b) Pie chart (1) – shows parts of a whole/percentages (1).

(c) Any two (1 each):
• chart title
• axis titles (Month, Tourists in thousands)
• legend
• data labels

(d) Select the month and tourist data with headings and Insert → Line chart (1); Chart Design → Move Chart → New sheet (1).

Next: Databases: Primary, Candidate, Secondary and Foreign Keys, Data Types and Relationships

Syllabus link: This post is relevant to the CSEC® Information Technology Syllabus (CXC 30/G/SYLL 17), Section 5: Spreadsheets, Specific Objective 8 (“perform charting operations”).

Attributions to media used in this post:

No third-party images are used in this post. Embedded videos are credited on YouTube to their channels.

Updates

2026/10/16

– Lists and algorithms laid out line by line.

© 2026 Vedesh Kungebeharry. All rights reserved.

Spreadsheets: Number Formats, Sorting, Filtering and Pivot Tables

A spreadsheet full of numbers only becomes useful when the data is easy to read and organised. In this post you will learn to manipulate data: apply number formats (currency, percentage, decimal places, dates), sort records, filter to show only the records you need, and summarise data with a pivot table.

Build on what you learned in Forms 1–3

These Lower School ICT posts cover the basics of this topic:

In this post:

  • Number formats
  • Sorting (single and multi-level)
  • Filtering (AutoFilter and advanced filter)
  • Pivot tables and pivot charts – a quick guide

Number formats

A number format changes how a value looks, not the value itself. Select the cells, then Home → Number group, or right-click → Format Cells → Number.

FormatExampleUse
General1250.5Default
Number (2 decimal places)1250.50Measurements, averages
Currency / Accounting$1,250.50Money (choose the $ symbol for your country)
Percentage12.5%Rates, marks (0.125 shown as 12.5%)
Date16/10/2026 or 16-Oct-26Dates – stored as numbers so they can be sorted and subtracted
Text00123Phone numbers, ID numbers with leading zeros

Use the Increase/Decrease Decimal buttons to change decimal places. Remember: the cell still holds the full value – 3.14159 shown as 3.14 is still 3.14159 in calculations.

Video: Excel: Understanding Number Formats – https://www.youtube.com/watch?v=fjyOG7Ls7BA

Sorting

Sort a list (Excel):

  1. Click any cell in the list (make sure there are no blank rows or columns inside it).
  2. Data → Sort. Tick “My data has headers”.
  3. Sort by: choose the column (e.g. Surname); Order: A to Z (ascending) or Z to A (descending).
  4. For a multi-level sort, click Add Level – e.g. sort by Form, then by Surname within each Form.
  5. Click OK. Whole rows move together, so records stay intact.

Warning: if you select only one column and sort it, Excel may ask to expand the selection – always choose Expand the selection, or the data in other columns will no longer match.

Video: Excel: Sorting Data – https://www.youtube.com/watch?v=Ep5q1cUhQas

Filtering

  • AutoFilter: click in the list → Data → Filter. Drop-down arrows appear in the headings. Choose values to show (e.g. only Form 4A), or use Number Filters (e.g. Greater Than 75) and Text Filters (e.g. Begins With “S”). Other rows are hidden, not deleted.
  • Clear a filter: Data → Clear. Turn filters off: click Filter again.
  • Advanced Filter: type the criteria in a separate criteria range (copy of the headings plus conditions below), then Data → Advanced. You can copy the results to another location.

Video: Excel: Filtering Data – https://www.youtube.com/watch?v=_OdsZR_rL1U

Pivot tables and pivot charts

A pivot table summarises a large list – for example total sales by month and by product – by dragging fields into Rows, Columns, Values and Filters. A pivot chart is a chart based on the pivot table.

Create a pivot table:

  1. Click in the list → Insert → PivotTable → New Worksheet → OK.
  2. Drag a field (e.g. Product) to Rows, another (e.g. Month) to Columns, and a number field (e.g. Sales) to Values (it will Sum by default; change to Average or Count with Value Field Settings).
  3. Insert → PivotChart to chart it.

Video: Excel: Intro to PivotTables – https://www.youtube.com/watch?v=N4K3xjM76kI

For worked examples see A pivot table’s intended use and Using a pivot table to make sense of unknown data.

Google Sheets: Format → Number; Data → Sort range / Create a filter; Insert → Pivot table.
LibreOffice Calc:
Format → Cells; Data → Sort / AutoFilter; Insert → Pivot Table.

Try it: Format, sort, filter or pivot?

Choose the best feature for each task.

Show 0.15 as 15%
List students from highest to lowest mark
Show only the students in Form 5B
Find total sales for each product in each month from 2,000 sales records
Show 1500 as $1,500.00

Related

Related: Pivot tables

Related: Excel Charts, Pivot Tables Intro (recorded class)

Paper 1 practice (multiple choice)

Choose the best answer for each question. Try them all before you check.

1. Which feature displays only the records that meet a condition, hiding the others?
(A) Sort
(B) Filter
(C) Freeze Panes
(D) AutoSum

2. Arranging records from Z to A is a sort in
(A) ascending order
(B) descending order
(C) alphabetical filter
(D) numerical format

3. A cell contains 0.08. Formatted as a percentage it displays
(A) 0.08%
(B) 8%
(C) 80%
(D) 800%

4. Which tool is BEST for summarising total sales by region and month?
(A) Pivot table
(B) Freeze Panes
(C) Find and Replace
(D) Text format

5. Changing the number format of 3.456 to show 3.46
(A) changes the stored value to 3.46
(B) only changes how it is displayed
(C) deletes the decimals
(D) rounds it in every formula

Show answers

1. B
2. B
3. B
4. A
5. B

Paper 2 practice (structured question)

A sports club keeps a spreadsheet of 120 members with columns: Member ID, Name, Age, Sport, Fees Paid. The fees appear as 350 instead of $350.00.

(a) Name the number format that should be applied to the Fees Paid column. (2 marks)

(b) Describe how to sort the members by Sport and then by Name within each sport. (2 marks)

(c) Explain how a filter could be used to show only members who play football and are under 16. (2 marks)

(d) The treasurer wants the total fees for each sport. Name the most suitable tool and state ONE advantage. (2 marks)

Total: 8 marks

Show answers

(a) Currency/Accounting format (1) with 2 decimal places (1).

(b) Data → Sort, sort by Sport (1); Add Level, then by Name, both A to Z (1).

(c) Turn on AutoFilter (Data → Filter) (1); choose “Football” in the Sport column and Number Filter “Less Than 16” in the Age column (1).

(d) A pivot table (1); advantage: it summarises large amounts of data quickly/automatically and can be rearranged easily (1). (Accept SUMIF with a suitable advantage.)

Next: Spreadsheets: Choosing, Creating and Formatting Charts

Syllabus link: This post is relevant to the CSEC® Information Technology Syllabus (CXC 30/G/SYLL 17), Section 5: Spreadsheets, Specific Objective 7 (“manipulate data in a spreadsheet”).

Attributions to media used in this post:

No third-party images are used in this post. Embedded videos are credited on YouTube to their channels.

Updates

2026/10/16

– Lists and algorithms laid out line by line.

© 2026 Vedesh Kungebeharry. All rights reserved.

Spreadsheets: Manipulating Rows and Columns – Insert, Delete, Hide, Resize and Freeze

As your spreadsheet grows you will need to add a forgotten row, remove an empty column, widen a column that shows ####, hide working columns, or keep the headings visible while you scroll. This post shows you how to manipulate columns and rows.

Build on what you learned in Forms 1–3

These Lower School ICT posts cover the basics of this topic:

In this post:

  • Selecting rows and columns
  • Inserting and deleting
  • Changing width and height (and why you see ####)
  • Hiding and unhiding
  • Freezing panes
  • Moving rows and columns

Selecting rows and columns

  • Click the column letter (e.g. C) to select the whole column; click the row number (e.g. 5) to select the whole row.
  • Drag across headings, or Shift+click, to select several next to each other; Ctrl+click for ones that are apart.

Inserting and deleting

Insert a row or column (Excel):

  1. Right-click the row number below where you want the new row (or the column letter to the right of where you want a new column).
  2. Choose Insert. (Or Home → Insert → Insert Sheet Rows / Columns.)
  3. A blank row appears above (or a column to the left). Formulas that refer to cells that moved are updated automatically.

Delete a row or column:

  1. Right-click the row number or column letter.
  2. Choose Delete. (Pressing the Delete key only clears the contents – it does not remove the row.)
  3. Check formulas: if a formula used a deleted cell it will show #REF!.

Changing column width and row height

  • Drag the boundary to the right of the column letter (or below the row number).
  • Double-click the boundary to AutoFit to the longest entry.
  • Home → Format → Column Width / Row Height to type an exact size.
  • #### in a cell means the column is too narrow to show the number or date – widen it.
  • Wrap Text (Home tab) lets long labels go on to several lines in a cell.

Video: Excel: Modifying Columns, Rows, and Cells – https://www.youtube.com/watch?v=9s0OdXiuqL0

Hiding and unhiding

  • Right-click a row number or column letter → Hide. The data is still there and formulas still use it – it is just not shown (or printed).
  • To unhide, select the rows/columns on both sides of the hidden ones → right-click → Unhide.
  • Useful for hiding working columns or confidential data before printing.

Freezing panes

Freeze Panes keeps headings visible while you scroll through a long list.

In Excel:

  1. To keep row 1 visible: View → Freeze Panes → Freeze Top Row.
  2. To keep column A visible: View → Freeze Panes → Freeze First Column.
  3. To freeze both rows and columns: click the cell just below and to the right of what should stay (e.g. B2), then View → Freeze Panes → Freeze Panes.
  4. Unfreeze with View → Freeze Panes → Unfreeze Panes.

Video: Excel: Freeze Panes and View Options – https://www.youtube.com/watch?v=zuhsUNBeNHw

Moving rows and columns

Select the row or column, Cut (Ctrl+X), right-click the heading where it should go and choose Insert Cut Cells. Or hold Shift and drag the border of the selection.

Google Sheets: right-click headings for Insert/Delete/Hide; View → Freeze.
LibreOffice Calc:
right-click headings; View → Freeze Cells.

Try it: Which operation?

Choose the operation for each task.

A column shows ##### instead of a date
Keep the headings in row 1 visible while scrolling to row 300
Add a new student between rows 6 and 7
Stop a column of confidential salaries from printing, without losing the data
Remove an empty column C completely

Related

Related: Excel Skills Tutorial Part 1 (recorded class)

Paper 1 practice (multiple choice)

Choose the best answer for each question. Try them all before you check.

1. A cell displays ##### . The most likely reason is that
(A) the formula is wrong
(B) the column is too narrow
(C) the cell is protected
(D) the data was deleted

2. To keep the column headings visible while scrolling down, you would use
(A) Hide
(B) Freeze Panes
(C) Wrap Text
(D) Merge

3. When a row is inserted above row 5, the old row 5 becomes
(A) row 4
(B) row 5
(C) row 6
(D) it is deleted

4. Hiding a column
(A) deletes its data
(B) removes it from formulas
(C) stops it being displayed, but the data remains
(D) sorts it

5. After deleting a row that a formula refers to, the formula may show
(A) ####
(B) #REF!
(C) #VALUE!
(D) 0

Show answers

1. B
2. B
3. C
4. C
5. B

Paper 2 practice (structured question)

A form teacher keeps a spreadsheet of 35 students. Row 1 has headings (Name, Test 1, Test 2, Average, Parent Phone). When she scrolls down, the headings disappear. A new student has joined, and the Average column shows #####.

(a) State the feature that would keep the headings visible while she scrolls. (2 marks)

(b) Describe how she would insert a row for the new student between rows 10 and 11. (2 marks)

(c) Explain why the Average column shows ##### and how to fix it. (2 marks)

(d) She wants to print the list without the Parent Phone column but keep the data:

(i) name the feature (1 mark)

(ii) state ONE reason it is better than deleting the column (1 mark)

Total: 8 marks

Show answers

(a) Freeze Panes (1) – Freeze Top Row / freeze row 1 (1).

(b) Right-click row number 11 (1) and choose Insert, which adds a blank row above it (1).

(c) The column is too narrow to display the numbers (1); widen it by dragging/double-clicking the column boundary (AutoFit) (1).

(d)

(i) Hide the column (1 mark)

(ii) The data is kept and can be unhidden later/formulas that use it still work (1 mark)

Next: Spreadsheets: Number Formats, Sorting, Filtering and Pivot Tables

Syllabus link: This post is relevant to the CSEC® Information Technology Syllabus (CXC 30/G/SYLL 17), Section 5: Spreadsheets, Specific Objective 6 (“manipulate columns and rows”).

Attributions to media used in this post:

No third-party images are used in this post. Embedded videos are credited on YouTube to their channels.

Updates

2026/10/16

– Lists and algorithms laid out line by line.

© 2026 Vedesh Kungebeharry. All rights reserved.

Spreadsheets: Copying Formulas with Relative, Absolute and Mixed Cell References and Named Ranges

One of the biggest time-savers in a spreadsheet is writing a formula once and copying it down a whole column. But copy the wrong kind of formula and every answer is wrong. In this post you will learn how relative, absolute and mixed cell references behave when you replicate (copy) formulae, and how named ranges make formulas easier to read. Steps are for Microsoft Excel, with Google Sheets and LibreOffice Calc notes.

Build on what you learned in Forms 1–3

These Lower School ICT posts cover the basics of this topic:

In this post:

  • Replicating formulae with the fill handle
  • Relative references (A1)
  • Absolute references ($A$1)
  • Mixed references ($A1 and A$1)
  • Named ranges

Replicating a formula

In Excel:

  1. Type the formula in the first cell, e.g. =B2*C2 in D2, and press Enter.
  2. Click D2 and point at the small square in the bottom-right corner (the fill handle) until the pointer becomes a thin black +.
  3. Drag down to the last row (or double-click the fill handle to fill down automatically).
  4. Alternatively: copy (Ctrl+C), select the target cells, paste (Ctrl+V); or select the range and press Ctrl+D (fill down).

Relative references

A normal reference such as B2 is relative: when the formula is copied, the reference changes relative to its new position. Copy =B2*C2 from D2 down to D3 and it becomes =B3*C3. This is what you want for most row-by-row calculations.

ABCD
1ItemPrice ($)QtyTotal
2Roti253=B2*C2 → 75
3Doubles65=B3*C3 → 30
4Sorrel drink152=B4*C4 → 30

The formula in D2 was copied down; Excel changed the row numbers automatically.

Absolute references

Sometimes every formula must use the same cell – for example a VAT rate or exchange rate stored in one place. Put a $ before the column letter and row number to make it absolute: $F$1. It does not change when copied. Press F4 after typing a reference to add the $ signs.

ABCF
1ItemPrice ($)VATVAT rate0.125
2Roti25=B2*$F$1 → 3.13
3Doubles6=B3*$F$1 → 0.75
4Sorrel drink15=B4*$F$1 → 1.88

B changes as it is copied (relative); $F$1 stays fixed (absolute). Without the $ signs, the copy in C3 would use F2, which is empty, giving 0.

Video: Excel: Relative and Absolute Cell References – https://www.youtube.com/watch?v=iDg9s7BJ2m4

Mixed references

  • $A1 – the column is fixed, the row changes.
  • A$1 – the row is fixed, the column changes.
  • Press F4 repeatedly to cycle: A1 → $A$1 → A$1 → $A1 → A1.
  • Useful for tables like a multiplication grid where one value is in a fixed row and the other in a fixed column.

Named ranges

A named range gives a cell or range a meaningful name, such as VATRate or Prices. Formulas become easier to read – =B2*VATRate – and a name always refers to the same cells, so it behaves like an absolute reference when copied.

To create a name in Excel:

  1. Select the cell or range (e.g. F1).
  2. Click in the Name Box (left of the formula bar), type a name with no spaces (e.g. VATRate) and press Enter. Or use Formulas → Define Name.
  3. Use it in formulas: =B2*VATRate, =SUM(Prices).
  4. Manage names with Formulas → Name Manager.

Video: How to Create Named Ranges in Excel – https://www.youtube.com/watch?v=pnSXJC5HJ4c

Google Sheets: $ and F4 work the same; Data → Named ranges.
LibreOffice Calc:
F4 (Shift+F4 in some versions) toggles references; Sheet → Named Ranges and Expressions → Define.

Try it: What does it become?

Each formula is in row 2 and is copied down ONE row to row 3. Choose what it becomes.

=B2*C2
=B2*$F$1
=$B$2+C2
=B$2*C2
=SUM(B2:D2)

Related

Related: Practical Exercise – Range names, Advanced Filters, Linking to other sheets

Related: Advanced formulae – Spreadsheets

Paper 1 practice (multiple choice)

Choose the best answer for each question. Try them all before you check.

1. Which of the following is an absolute cell reference?
(A) B5
(B) $B5
(C) B$5
(D) $B$5

2. The formula =A2*B2 in C2 is copied to C5. It becomes
(A) =A2*B2
(B) =A5*B5
(C) =A3*B3
(D) =$A$5*$B$5

3. Which key toggles a reference between relative and absolute in Excel?
(A) F2
(B) F4
(C) F7
(D) F9

4. The formula =B4*$H$2 is copied from row 4 to row 6. It becomes
(A) =B6*$H$4
(B) =B6*$H$2
(C) =B4*$H$2
(D) =B6*H4

5. A meaningful name given to a cell or group of cells is called a
(A) label
(B) named range
(C) function
(D) field

Show answers

1. D
2. B
3. B
4. B
5. B

Paper 2 practice (structured question)

A small store uses a spreadsheet to calculate the price of goods in US dollars. Prices in TT$ are in B2:B20 and the exchange rate (TT$ per US$1) is in cell E1.

(a) Write a formula for C2 that converts the price in B2 to US dollars and can be copied down to C20. (2 marks)

(b) Explain why an absolute reference is needed in your formula. (2 marks)

(c) The formula =B2/E1 was copied from C2 to C3:

(i) state what it becomes (1 mark)

(ii) explain why the answer is wrong (1 mark)

(d) Describe how a named range could be used for the exchange rate, and give ONE advantage. (2 marks)

Total: 8 marks

Show answers

(a) =B2/$E$1 (1 mark for dividing B2 by E1, 1 mark for the absolute reference $E$1).

(b) The exchange rate is in one fixed cell (1); without the $ signs the reference would change to E2, E3… when copied, which are empty/wrong (1).

(c)

(i) =B3/E2 (1 mark)

(ii) E2 is empty/not the exchange rate, so the result is an error or wrong (1 mark)

(d) Select E1 and type a name such as Rate in the Name Box, then use =B2/Rate (1); advantage: easier to read/understand, or stays fixed when copied (1).

Next: Spreadsheets: Manipulating Rows and Columns – Insert, Delete, Hide, Resize and Freeze

Syllabus link: This post is relevant to the CSEC® Information Technology Syllabus (CXC 30/G/SYLL 17), Section 5: Spreadsheets, Specific Objective 5 (“replicate (copy) formulae into other cells”).

Attributions to media used in this post:

No third-party images are used in this post. Embedded videos are credited on YouTube to their channels.

Updates

2026/10/16

– Lists and algorithms laid out line by line.

© 2026 Vedesh Kungebeharry. All rights reserved.

Advanced formulae – Spreadsheets

In this tutorial, we seek to use the basic arithmetic operations addition subtraction multiplication and division to accomplish complex tasks.

Usually when using a computer system to represent addition subtraction multiplication and division we use the following symbols respectively:

Addition

The plus sign : +

Hold shift and press the plus sign
Subtraction

The hyphen: –

Hyphen Key
Multiplication

The asterisk : *

Asterisk key
Division

The front slash: /

Front slash key.

Tutorial:

Click the link below to download the file. Do not open the documents using Google Sheets, rather download the documents and open it with your desktop application of Microsoft Excel:

https://drive.google.com/file/d/1lubYKdD70AAx_9eD2BeoBb8FbjKcd9Wq/

This tutorial demonstrates how a spreadsheet can be used to perform a complex calculation. The complex calculation chosen is the quadratic formula.

Note that you do not need to have an intimate understanding of the quadratic formula. All you will need to do is to break down the quadratic formula into its sub parts, and represent those parts using functions and formulas in your spreadsheet software.

This Excel document contains two worksheets. Use the first worksheet to fill in formulas and functions which can be used to calculate the items shown in the labels.

How to fill the sheet:

A copy of the sheet is shown below:

Advanced formulae tutorial

Start filling in the correct formula for each level shown in the sheet. For example, you would start with the label negative B an insert a formula in the cell next to the label which would calculate the volume negative B. In this case you would enter =-1*C10 Into cell location H3.

Continue following all the way down for b squared, 4ac …. All the way until you have calculated root 1 and root 2.

The second sheet contains notes and solutions to the first worksheet.

Homework

Complete the challenge listed in the sheet above. Ensure that you do not use any pre-calculated values.

Video Tutorial

Step-by-step tutorial (follows the video)

Follow these steps alongside the video above to fill in the Tutorial worksheet and find both roots of the quadratic equation. Approximate video times are shown in brackets.

Before you start: when you open the downloaded file in Excel, a yellow PROTECTED VIEW bar may appear at the top. Click Enable Editing, or Excel will not let you type in the sheet. (3:57)

  1. Open the file link above in your browser and click the Download button (the download arrow at the top right of the Google Drive preview). Do not open it with Google Sheets; open the downloaded file in your desktop copy of Microsoft Excel. (1:02)
  2. Look over the Tutorial worksheet. The coefficients of the quadratic equation are stored in B10 (a = 6), C10 (b = -5) and D10 (c = 1), so the equation is 6x² − 5x + 1 = 0. The labels in column G break the quadratic formula into its sub parts, and each answer goes in the cell next to its label in column H. (1:27)
  3. (Optional check.) Find the expected answers using Wolfram Alpha (wolframalpha.com): type 6x^2-5x+1=0 and press Enter. The solutions are x = 1/3 (0.333…) and x = 1/2 (0.5). These are the roots your spreadsheet should produce. (2:29)
  4. Negative B: click cell H3 and enter =-1*C10 (multiplying by -1 gives the negative of a number). Result: 5. (4:01)
  5. b squared: in H4 enter =C10*C10 (b squared is b times b). Result: 25. (4:25)
  6. 4ac: in H5 enter =4*B10*D10. Result: 24. (4:58)
  7. 2a (denominator): in H6 enter =2*B10. Result: 12. (5:21)
  8. b squared -4ac: in H7 subtract the 4ac cell from the b squared cell: =H4-H5. Result: 1. (5:47)
  9. Square root of b squared -4ac: this needs a function. In H8 start typing =sq, choose SQRT from the list, and complete the formula as =SQRT(H7). Result: 1. (6:16)
  10. Root 1 – Numerator (positive square root, −b + √(b² − 4ac)): in H11 enter =H3+H8. Result: 6. (6:46)
  11. Root1: the root is the numerator divided by the denominator. In H12 enter =H11/H6 (the front slash / divides). Result: 0.5. (7:13)
  12. Root 2 – Numerator (negative square root, −b − √(b² − 4ac)): in H15 enter =H3-H8. Result: 4. (7:32)
  13. Root2: in H16 enter =H15/H6. Result: 0.333333333. Both roots (0.5 and 0.333…) match the Wolfram Alpha answers. (8:01)
  14. Test the sheet with another equation: x² − x − 1 = 0. Wolfram Alpha gives roots of about 1.618 and −0.618. In Excel, change the coefficients to B10 = 1, C10 = -1 and D10 = -1. Your formulas recalculate automatically: H12 (Root1) = 1.618033989 and H16 (Root2) = -0.618033989, the same values as Wolfram Alpha. (8:32)
  15. Press Ctrl + ~ (the tilde key) to show the formulas in the cells instead of the results, so you can check your work. Most of the work is plain arithmetic; the only function used is SQRT. Press Ctrl + ~ again to return to normal view. (10:19)
  16. Challenge / homework: in the green cells I12 (Root 1) and I16 (Root 2), write a single formula for each root that uses only the coefficient cells B10, C10 and D10, with no pre-calculated values. Use brackets to control the order of operations. Try it yourself before looking at the Solution sheet. (10:48)
  17. After you have attempted the challenge, open the Solution worksheet. It shows that there are different ways to get the same answers, e.g. using predefined functions: b squared as =POWER(C10,2) and 4ac as =PRODUCT(4,B10,D10). Clicking the fx button next to the formula bar opens the Function Arguments box, which explains each parameter (for POWER: Number and Power). The single-formula answers to the challenge are also on this sheet. (11:21)

Updates

2026/10/04

Added a step-by-step tutorial under the video.

2021/11/08

-Fixed a spelling error

-Improved formatting

-Added Tutorial Video

© 2020  Vedesh Kungebeharry. All rights reserved. 

Formulas and Functions – Part 2 (Video)

This series of videos is a continuation of the post, Formulas and Functions – Part 1 (Class Video)

Use this file and follow along with the videos, pausing the video when you need to accomplish a task:

It 5 videos covering the following functions: Max, Min, Count, Countif, Rank, and Vlookup.

A 6th function, PMT was left for your research and homework.

See the videos below, and follow the instructions for homework based on the pmt function:

Max and Min

Count

Countif

Rank

Vlookup

PMT

Homework – Research/examine the PMT function.

Explain :

  1. The functions’ parameters.
  2. The purpose of the function.

We will be discussing this function at our next class.

© 2020  Vedesh Kungebeharry. All rights reserved. 

Spreadsheets: Introduction, definition & terminology

See the video below which introduces spreadsheet software

Terminology

Spreadsheet – an electronic document in which data is arranged in the rows and columns of a grid and can be manipulated and used in calculations.


Oxford English Dictionary via google. (https://www.google.com/search?q=define+spreadsheet&oq=define+spreadsheet&aqs=chrome..69i57j0l7.4183j1j7&sourceid=chrome&ie=UTF-8)

A Spreadsheet consists as a grid of many cells.

Cell – A single location in a spreadsheet that can store data. It is the intersection of a row and column

row – A horizontal arrangement of cells , usually denoted by a number

column – a vertical arrangement of cells ,usually denoted by a letter, cell,

value – the data that is stored in a cell.

formula – a representation for a calculation stored within a cell. The formula itself is not displayed, rather, the results of the calculation is displayed in the cell.

range – a group of cells; can be a group of horizontal cells, a group of vertical cells, a block of cells, or even many different blocks of cells.

worksheet – a single spreadsheet in a workbook.

workbook – a file , consisting of many worksheets (individual spreadsheets). A workbook is a file which is comprised of 1 or more worksheets (spreadsheets)

(last updated Monday 11th January 2021)

© 2020  Vedesh Kungebeharry. All rights reserved.