Turn spreadsheet analysis into a chart readers can check

Suppose you have monthly library-branch totals and want to publish a comparison. Which branch lends the most items? Which records the most loans relative to visits? Those are different questions—and the same spreadsheet can produce different rankings.

Data analytics determines what to calculate and what the result means. Data visualization makes that result easier to compare. A useful public page connects both: a chart highlights the pattern, while a table preserves the numbers and definitions needed to check it.

1. Define the question and the row

Start with one sentence: “Compare loans per 100 recorded visits across branches in the same month.”

Define each row as one branch in one calendar month. Keep the underlying counts alongside the calculated metric.

The following dataset is fictional and illustrates the workflow; it is not evidence about real libraries.

month	branch	visits	loans	loans_per_100_visits
2026-09	North	1200	1800	150
2026-09	Central	600	1080	180
2026-09	South	900	1260	140

For this example, define visits as recorded in-person entries and loans as items checked out at the branch during the month, excluding renewals and online borrowing.

In a real release, check the source definitions rather than assuming them. If one branch includes renewals and another excludes them, a consistent-looking column still contains incomparable measurements.

Use one header row, consistent column types and no merged cells or subtotal rows inside the observations. GOV.UK’s tabular-data guidance recommends a CSV header row and metadata describing provenance, column titles and data types.

2. Calculate a metric that answers the question

Calculate:

loans_per_100_visits = loans / visits × 100

North has the largest loan count: 1,800. Central has the highest loans per 100 visits: 180. Neither ranking is inherently better; each answers a different question.

This metric is not the percentage of visitors who borrowed. One visit can involve several loans, and these aggregate counts do not link individual borrowers to visits. A value above 100 is therefore possible.

Check the calculation before making a chart:

• Row identity: each branch–month combination appears once.
• Count validity: visits and loans are numeric, nonnegative counts.
• Missing values: an unknown count is not silently replaced with zero.
• Zero denominator: if visits are zero, leave the ratio unavailable and explain why.
• Totals: source counts reconcile with the reviewed export.

For an overall ratio, divide the summed loans by the summed visits:

4140 / 2700 × 100 = 153.3 loans per 100 visits

Do not average the three branch ratios to describe the combined activity. Their simple average is 156.7, which gives a 600-visit branch the same weight as a 1,200-visit branch. Keep full precision in calculations and round only for presentation.

For an Excel-specific worked calculation, see data analysis in Excel.

3. Choose the chart after choosing the metric

For this question, use a horizontal bar chart of loans per 100 visits, ordered Central, North, South. Label the metric explicitly; “Branch performance” would imply a broader judgment than the data supports.

Start the numerical axis at zero. The Government Analysis Function’s chart guidance recommends bar charts for rankings and explains that a nonzero baseline distorts their relative proportions.

Change the chart when the question changes:

Reader question	Useful starting point
Which branch has the most loans?	Bar chart of loan counts
How has a branch’s ratio changed over several months?	Line chart in chronological order
Do branches with more visits also record more loans?	Scatter plot of visits against loans
What are the exact counts and ratios?	Table

A chart does not establish why a difference exists. Central’s higher ratio could prompt further investigation; it does not prove better service or greater visitor satisfaction.

4. Publish the numbers with their interpretation

Prepare a reviewed, data-only release file. Use aggregate data cleared for public sharing—never upload borrower names, contact details, account identifiers or other sensitive information.

As of October 2026, TablePage describes support for CSV, TSV, XLSX and XLS uploads that generate public dataset pages with charts and a filterable table, viewable without signup. Upload the reviewed release, then inspect the public result rather than assuming automatic charts answer your chosen question.

Check that the published values, row count, date labels and metric match your release file. Include these details in the accompanying publication text:

• Coverage: branches and reporting month.
• Definitions: what visits and loans include or exclude.
• Method: loans divided by visits, multiplied by 100.
• Source: the specific original dataset, its version or retrieval date, and reuse terms.
• Limit: the ratio measures recorded activity, not individual borrowing behavior.

For the fictional example, the chart description could read: “Central records 180 loans per 100 visits, compared with North’s 150 and South’s 140. North records the largest total number of loans.”

Keep that explanation available as text alongside the table. W3C’s guidance on complex images recommends descriptions that communicate relevant values, relationships and trends. Readers should be able to understand the comparison—and check its arithmetic—without relying on the chart alone.