Excel: Structured Data & Useful Formulas

Why Clean Data Wins

Excel is the most abused program in any office. People use it as a document, a database, a presentation, a calculator, and a to-do list. Often all at once, in the same file. That's why half the spreadsheets in any organisation are fighting the person trying to use them.

Excel rewards structure ruthlessly. Give it structured data, you get superpowers. Give it a mess, you get a mess back.

This session isn't about advanced Excel. It's about the handful of habits that turn a messy sheet into a sheet formulas, filters, charts and (later, in the Working with AI strand) Copilot can work with. Once the foundation is right, everything else gets easier.

Good Sheets vs Bad Sheets

Most of what makes a spreadsheet usable comes down to its shape. A "good" sheet has one row per record, one column per attribute, no merged cells, no blank rows in the middle, no titles taking up the top ten rows.

The test. Can you sort and filter the data without breaking anything? If yes, the shape is probably good. If the whole sheet falls apart the moment you sort by one column, the shape is wrong.

Tables, Not Just Ranges

Excel has a feature called Tables that's one of the most under-used things in the product. Select your data, Insert, Table (or Ctrl+T), and Excel converts your range into a proper Table. Suddenly you get:

• Filter dropdowns on every column, automatically.
• Auto-expand. Add a row at the bottom, it's in the Table. Add a column to the right, it's in the Table.
• Formulas that use column names, like =SUM(Sales[Amount]) instead of =SUM(C2:C847). Easier to read, and it adjusts when the Table grows.
• Banded rows and a cleaner look, for free.
• A name so you can refer to the whole table in formulas across sheets.

Named Ranges

Similar idea, different use case. A named range is a name you give to a specific cell or range of cells. Then you can refer to it by name instead of by cell reference.

Select a cell or range, click in the Name Box (top-left, where the cell reference shows), type a name, Enter. Done. Now you can use that name in any formula: =A2*TaxRate is friendlier than =A2*Sheet2!$B$4.

Good for: tax rates, conversion factors, thresholds, anything you might change later and have referenced in 20 places. Change the named range's value, every formula updates.

The Everyday Formulas

You don't need to know 200 Excel functions. You need maybe 10, cold. These are the ones.

SUM, AVERAGE, COUNT, COUNTA, MIN, MAX

The basics. SUM adds, AVERAGE averages, COUNT counts numbers, COUNTA counts non-empty cells, MIN and MAX find the smallest and largest.

=SUM(Sales[Amount])
=AVERAGE(Ages)
=COUNTA(Sales[Customer])

IF

Returns one thing if a condition is true, another if not. The building block of conditional logic.

=IF(B2>1000, "Review", "OK")

Reads as: if B2 is greater than 1000, say "Review", otherwise say "OK".

SUMIFS, COUNTIFS, AVERAGEIFS

Sum / count / average, but only the rows that match one or more conditions. The workhorses of any reporting sheet.

=SUMIFS(Sales[Amount], Sales[Region], "NT")
=COUNTIFS(Sales[Region], "NT", Sales[Month], "April")

Reads as: sum the Amount column, only where Region is "NT".

TEXTJOIN, CONCAT

Glue multiple pieces of text together. TEXTJOIN is the modern version, with a separator.

=TEXTJOIN(", ", TRUE, A2:A10)

Joins everything in A2 to A10 with a comma between, ignoring blanks.

LEFT, RIGHT, MID, LEN, TRIM

For pulling bits out of text. LEFT takes the first N characters, RIGHT the last N, MID a chunk in the middle, LEN gives the length, TRIM strips extra spaces.

=LEFT(A2, 3)
=TRIM(A2)

TODAY, NOW, DATEDIF

Date arithmetic. TODAY() gives today's date. DATEDIF calculates the difference between two dates in years, months, or days.

=TODAY()
=DATEDIF(StartDate, TODAY(), "d")

Second one gives the number of days between StartDate and today.

IFERROR

Wrap any formula in IFERROR to return a clean fallback if it fails. Stops #N/A and #DIV/0 showing all over the sheet.

=IFERROR(A2/B2, "")

Those are the ones. Combine them and you can do 80 percent of what anyone ever needs in Excel. The one worth its own section is XLOOKUP.

XLOOKUP, the One to Learn

If you've ever been scared of VLOOKUP, good news, it's been retired by Microsoft's own newer function, XLOOKUP. Same idea, better in every way.

What it does. Looks up a value in one column, returns a related value from another column. "Find this customer's ID, give me their suburb."

XLOOKUP syntax

Three required arguments. Two optional.

=XLOOKUP(what_to_find, where_to_look, what_to_return)

Concrete example: =XLOOKUP("NT-100", Customers[ID], Customers[Suburb])

Find "NT-100" in the Customers table's ID column, return the Suburb from the same row. Clean. Readable. Works left to right, right to left, up, or down. Returns "#N/A" if not found, or whatever you tell it to with the optional fourth argument:

XLOOKUP with a fallback

=XLOOKUP("NT-100", Customers[ID], Customers[Suburb], "Not found")

Conditional Formatting and Filters

Two features that turn a wall of numbers into something your eye can actually scan.

Filters. If you're using a Table (see above), filters are already on. Otherwise, select your data and Home, Sort & Filter, Filter. The arrow in each column header lets you filter, sort, and search. The most-used feature in any reporting sheet.

Conditional formatting. Home, Conditional Formatting. Highlight cells above a threshold, colour-scale a column from low to high, put data bars in cells to show relative size. Three uses that pay for themselves:

• Highlight cells greater than, less than, or between given values. "Any amount over $10,000 in red."
• Top/bottom rules. "Highlight the top 10 percent."
• Colour scales. For a column of numbers, green at the bottom, red at the top (or vice versa). Immediate visual sense of the distribution.

Charts That Communicate

Excel will let you make any chart you want. Most of them you shouldn't. Four chart types do about 95 percent of real work:

• Bar or column, for comparing amounts across categories. "Visits per region."
• Line, for something changing over time. "Monthly visits over the year."
• Stacked bar or column, for breaking a total into parts. "Visits by region, with each region split by program."
• Scatter, for relationships between two numbers. Less common, but the right one when you need it.

3D, pie with 14 slices, doughnut, radar, and "stock" charts, almost always wrong. Stick to the boring ones.

Rules that make charts clearer:

• One idea per chart. If you're comparing three things, don't also add a fourth dimension with colour.
• Label axes. "Amount ($)" not just "Amount".
• Start y-axis at zero for bar charts. Zoomed-in axes exaggerate differences misleadingly.
• Title says the point, not the subject. "Visits up 30 percent in Q2" tells the story. "Visits by quarter" describes the data.

A Sample Dataset to Work From

To make the ideas in this session concrete, here's a small sample dataset, a fictional regional program register. A realistic shape, small enough to read, structured the way a "good" sheet should be.

What this dataset demonstrates:

• One row per record (one program). One column per attribute.
• Dates in ISO format (YYYY-MM-DD) so they sort correctly and Excel treats them as real dates.
• Numbers as numbers, not text.
• A clear status column with a limited set of values (Active, Completed, Planning), which makes filtering and counting easy.
• Regional names drawn from NT context, so examples later in the course can use realistic data.

When we come back to Excel in the Copilot in Word, Excel & PowerPoint session, this is roughly the shape of sheet Copilot can actually do useful work on. Structured data in, useful answers out.