Lesson · 15 min

Intelligent Services: Data Types & Analyze Data

Use Excel's connected data types and its automated analysis, and learn where both need checking.

By the end of this lesson you can

Convert text into a linked data type and pull fields from it
Use Analyze Data to generate charts and summaries from a table
Explain what a linked data type depends on and when it breaks
Judge when an automated suggestion should be trusted

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

Cells that know what they contain

On the job

You are preparing a facilities report at Lakeside Medical Associates.

You have a column of city names for the practice's three locations, and you need population and state for each. You could look all six values up by hand, or Excel can attach them to the cells and keep them there.

Your task: Use linked data types where they save real work, and understand what they depend on.

A linked data type turns a cell's text into a record with fields behind it. Select a column of city names, choose Data > Geography, and Excel matches each to a place. A small icon appears in the cell, and clicking it opens a card of fields — population, area, leader, and so on. You pull a field into a neighboring cell with the dot operator: =A2.Population.

Type	Attaches	Reached from
Geography	Cities, states, countries, regions	Data tab > Geography
Stocks	Companies, funds, and currencies	Data tab > Stocks

The two data types you are most likely to use

Microsoft 365 & Office 2024Linked data types and Analyze Data require a Microsoft 365 subscription and an internet connection. They are not in Excel 2019, and they are not in the perpetual-license Office 2024 either — these are subscription services, not local features. On a machine without them, the Data tab simply will not show Geography and Stocks.

A linked data type is a live connection to an external service, which has three consequences. The values change when the source updates, so a figure in a report you saved last month may not be the figure someone sees today. The cells go stale or error without a connection. And ambiguous names get matched wrongly — a city name that exists in four states will be matched to one of them, and Excel does not ask which. Always check what it matched before you rely on it.

Analyze Data, and what it is actually doing

Analyze Data — previously called Ideas — examines a table and proposes charts, patterns, and summaries. Select any cell in a well-formed table, click Analyze Data on the Home tab, and a pane offers visualizations you can insert with one click. You can also type a question in plain language, such as 'total cost by category'.

It works well on clean data and poorly on messy data, which makes it a reasonable test of your table. If Analyze Data cannot find anything useful, the usual cause is the layout rules from Module 5: merged cells, blank rows inside the range, more than one header row, or a column mixing text and numbers.

Analyze Data finds correlations, and a correlation is not a cause. It may report that supply spending rises with appointment volume, which is unsurprising, or that two unrelated columns move together, which is coincidence. It has no knowledge of your practice and cannot tell the difference. Treat every suggestion as a question to investigate, never as a finding to report.

Worked example

Using an automated suggestion responsibly

Analyze Data reports that costs in one supply category rose sharply in Q3.

1
Check the underlying numbers before repeating the claim.
The tool read your table. If the table has a data problem — a duplicated row, a decimal error, a category typo splitting one thing into two — the pattern is real in the data and false in the world.
2
Ask whether anything changed that would explain it.
A vendor contract ending, a price increase, a new service line. A rise with a known cause is a fact; a rise with no known cause is a question for someone who would know.
3
Look at what the chart is not showing.
A category can rise in dollars while falling as a share of spending, and a chart of one category alone will not tell you that. Ask what the denominator is.
4
Report it as a question rather than a conclusion.
'Category X is up 31% in Q3 — I have checked the data and it looks correct, and I don't know why' is genuinely useful. Presenting it as a finding invites a decision built on an unverified number.

Result: A verified observation handed to a decision-maker as a question, not an unchecked chart presented as analysis.

Automated analysis is good at spotting patterns and has no idea what they mean. Verify the data, look for the cause, and report honestly what you do not know.

Check your understanding

Analyze Data returns no useful suggestions for your table. What is the most likely cause?

Challenge

Apply what you've learned in this lesson.

These features need a Microsoft 365 account. If your machine does not have them, do the written parts — the judgment matters more than the clicking.

Enter four city names, convert them to the Geography data type, and pull population and state into adjacent columns using the dot operator.
Find a city name that exists in more than one state. Convert it and record which one Excel matched it to, and how you would have known if it were wrong.
Run Analyze Data on a clean table and note the three suggestions it makes. Then break the table deliberately — merge two header cells — and run it again. Record the difference.
For one suggestion it produced, write three sentences: what it claims, what you would verify before repeating it, and what you still would not know.

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

← Dynamic Arrays & XLOOKUP Icons, Drawing Tools & Modern Collaboration →