• Practice
  • About 16 min
  • 5 practice questions

Spreadsheets with AI help: formulas, clean data, charts and testing by hand

Get AI help to write and explain SUM, AVERAGE, IF, COUNTIF and lookup formulas, understand pivot tables, clean messy data and choose charts - and test every formula on rows you can work out by hand.

If something looks wrong, please tell us.

By the end of this lesson you can

  • Ask an AI tool to write or explain a spreadsheet formula and describe your data so it can.
  • Test any formula on a few rows you can calculate by hand before trusting it on the whole sheet.
  • Describe what a pivot table does, clean common data problems and choose a suitable chart.

Spreadsheets run a large share of office work: attendance, stock registers, fee collections, marks lists, budgets. Computer-awareness papers also ask about common functions. AI tools can write a formula from a plain-language request and explain one you do not understand. They can also write a formula that looks right and is wrong. The skill that protects you is old and simple: test the formula on rows you can check by hand.

Getting a formula you can trust

  1. Describe your data Say where the columns and rows are.
  2. Let AI write the formula Or explain one you do not understand.
  3. Work out rows by hand Two or three rows you can calculate yourself.
  4. Compare the answers If the sheet and your hand answer differ, the formula is wrong until proved otherwise.

Describe your data clearly

A formula depends on where the data sits. Tell the tool the columns and rows: 'Names are in A2 to A6, marks in B2 to B6, row 1 has headings.' Then say what you want: 'Count how many students scored 40 or more.' Formulas below use the common style; small details such as separators can differ between spreadsheet programs, and not every program offers every function.

Common functions
FunctionWhat it doesExample
SUMAdds a range=SUM(B2:B6)
AVERAGEArithmetic mean of a range=AVERAGE(B2:B6)
IFReturns one value if a condition is true, another if false=IF(B2>=40,"Pass","Fail")
COUNTIFCounts cells meeting one condition=COUNTIF(B2:B6,">=40")
VLOOKUPFinds a value in the first column of a table and returns a value from another column=VLOOKUP("Ravi",A2:B6,2,FALSE)
XLOOKUPNewer lookup: search one range, return from another=XLOOKUP("Ravi",A2:A6,B2:B6)

Worked example 1: a marks list checked by hand

A sheet has five students: Asha 72, Ravi 45, Imran 88, Neha 30, Kiran 65, with names in A2:A6 and marks in B2:B6. Before trusting any formula, work out the answers yourself:

  1. Total: 72 + 45 + 88 + 30 + 65 = 300. =SUM(B2:B6) must show 300.
  2. Average: 300 ÷ 5 = 60. =AVERAGE(B2:B6) must show 60.
  3. Pass mark 40: Asha, Ravi, Imran and Kiran pass; Neha fails. =COUNTIF(B2:B6,">=40") must show 4.
  4. =IF(B5>=40,"Pass","Fail") on Neha's row must show Fail, because 30 is less than 40.
  5. =VLOOKUP("Ravi",A2:B6,2,FALSE) must return 45: it finds Ravi in the first column and returns the 2nd column. FALSE asks for an exact match.

Now suppose an AI tool had suggested =AVERAGE(B2:B5). The sheet shows 58.75, because it averages only four rows: 235 ÷ 4 = 58.75. Your hand answer of 60 exposes the missing row at once. The same test catches a COUNTIF written with ">40" instead of ">=40" (a student with exactly 40 would be left out) and a VLOOKUP without FALSE, which may do an approximate match and return a wrong row when the names are not sorted.

Before you trust a formula, work out two or three rows by hand. If the sheet and your hand answer differ, the formula is wrong until proved otherwise.

Copying formulas and references

When a formula is copied down a column, cell references shift. That is useful for =IF(B2>=40,...) becoming =IF(B3>=40,...). It is a problem when every row should refer to one fixed cell, such as a pass mark typed in E1. Writing $E$1 keeps that reference fixed. Ask the AI to explain which references should be fixed, then copy the formula and check a row near the bottom as well as the top.

Pivot tables as a concept

A pivot table summarises a long list by groups without any formulas. From a register of 500 expense entries with columns Department, Month and Amount, a pivot table can show the total amount for each department, or each department by month. Think of it as 'group by this, then add up that'. An AI tool can tell you which field to group by and which to total; you check one group's total against a quick manual sum.

Worked example 2: cleaning data

A fee register typed by several people has problems: 'Ravi ' with a trailing space, the same receipt entered twice, dates written as 03/04 and 4-Mar, and amounts stored as text. Lookups and counts fail on such data. Ask the AI how to fix each problem: TRIM removes extra spaces, a remove-duplicates step or a COUNTIF test finds repeats, one consistent date format is chosen, and text numbers are converted to numbers. Always work on a copy and keep the original sheet unchanged. Afterwards, count the rows: if duplicates were removed, the drop in rows should equal the number of duplicates you found.

Cleaning a fee register

  • Extra spaces TRIM removes them.
  • The same receipt entered twice A remove-duplicates step or a COUNTIF test finds repeats.
  • Dates written in different ways Choose one consistent date format.
  • Amounts stored as text Convert them to numbers.
Work on a copy, and count the rows before and after.

Charts

Choosing a chart
PurposeUsual chart
Compare categories, such as offices or subjectsColumn or bar chart
Show change over timeLine chart
Show parts of one whole, with few partsPie chart
See whether two measures move togetherScatter chart

Check every chart: a clear title, labelled axes with units, and an axis that does not start at a misleading value. A chart drawn from a wrong range is wrong however good it looks.

Key takeaways: describe where your data is; let AI write or explain the formula; then test it on rows you can calculate by hand. Watch for missing rows, > versus >=, exact-match lookups and fixed references. Clean data on a copy and count rows before and after. Choose the chart that fits the question, and label it.

Check your understanding

  1. 1. Marks in B2:B6 are 72, 45, 88, 30 and 65. What should =AVERAGE(B2:B6) show?

    1. 58.75
    2. 65
    3. 60
    4. 300
    Show answer

    Answer: C. Sum = 72 + 45 + 88 + 30 + 65 = 300; 300 ÷ 5 = 60. 300 is the SUM, and 58.75 is what you get by averaging only the first four rows (235 ÷ 4).

  2. 2. With the same marks, how many students does =COUNTIF(B2:B6,">=40") count?

    1. 3
    2. 4
    3. 5
    4. 2
    Show answer

    Answer: B. 72, 45, 88 and 65 are 40 or more; only 30 is below. The count is 4.

  3. 3. An AI suggests =VLOOKUP("Ravi",A2:B6,2) for an unsorted list of names. What is the risk?

    1. Without FALSE it may do an approximate match and return a wrong row
    2. VLOOKUP cannot find text values
    3. The 2 makes it return the second row instead of the second column
    4. It will add the marks instead of finding them
    Show answer

    Answer: A. If the last argument is left out, VLOOKUP may use approximate matching, which is meant for sorted data. FALSE forces an exact match. The 2 is the column number to return.

  4. 4. A formula refers to a pass mark in cell E1 and is copied down 200 rows. How should E1 be written so every row uses it?

    1. E1
    2. E$
    3. #E1
    4. $E$1
    Show answer

    Answer: D. The dollar signs fix both column and row, so the reference stays on E1 when the formula is copied. A plain E1 would shift to E2, E3 and so on.

  5. 5. Which chart best shows how monthly applications at an office changed over a year?

    1. Pie chart
    2. Line chart
    3. Scatter chart with no time axis
    4. A single number in one cell
    Show answer

    Answer: B. A line chart shows change over time. A pie chart shows parts of one whole and hides the order of months.

Privacy · Terms · Refunds · Delivery