Turn an Excel report into clean, publishable tabular data

To prepare Excel tabular data for a public page, start by deciding what each row represents. Then give every field its own column and every column a clear header. The result should make sense without merged headings, color-coded meanings or explanatory notes inside the data.

This walkthrough turns a small monthly report into a dataset readers can filter by park and month. The figures are fictional.

Choose the row structure before formatting

An Excel worksheet can contain tabular data without being an Excel Table. Tabular data describes the arrangement of records and fields; an Excel Table is a feature applied to a range that helps you sort, filter and calculate within it. Microsoft’s worksheet organization guidance describes how a range can be converted to a Table.

Suppose your report looks like this:

park_id	park_name	2026-01	2026-02	2026-03
001	North Park	120	135	150
002	South Park	80	90	100

This is already tabular: each row represents a park. But the months are spread across headers. For a page where readers should filter by month, a better structure is one park-month observation per row:

park_id	park_name	month	visits
001	North Park	2026-01	120
001	North Park	2026-02	135
001	North Park	2026-03	150
002	South Park	2026-01	80
002	South Park	2026-02	90
002	South Park	2026-03	100

Neither layout is universally correct. Keep the first for a compact side-by-side report; use the second when month should be a filterable field or new months should become additional records.

1. Clean the source range

Work in a copy of the workbook. Before reshaping it:

• Use one header row with distinct, descriptive names.
• Remove merged headings and repeat any group label needed to identify each record.
• Remove blank separator rows and columns inside the range.
• Keep totals, titles and source notes outside the records.
• Inspect hidden rows and columns rather than assuming they are irrelevant.

Microsoft recommends keeping similar items in the same column, avoiding internal blank rows and columns, and displaying hidden data before making changes. It also recommends formatting header cells as Text before typing labels—do this for the sample’s month headers to preserve their exact wording. Worksheet organization guidelines.

For this example, format the park_id cells as Text before entering 001 and 002. Identifiers are labels, not quantities. Excel can remove leading zeros and has a maximum numeric precision of 15 significant digits; changing the format afterward does not recover digits already lost. Microsoft’s identifier-preservation guidance.

2. Convert the range to an Excel Table

For the sample above, place the headers and records in A1:E3.

1. Select a cell within the data.
2. Choose Home > Format as Table and select a style.
3. Confirm that the range is A1:E3.
4. Select My table has headers, then OK.

These are Microsoft’s documented table-creation steps. A table style makes the range easier to work with, but it does not fix mixed data types, duplicate records or an unsuitable row structure.

3. Unpivot the month columns

In Excel with Power Query’s From Table/Range command available:

1. Select a cell in the Excel Table.
2. Choose Data > Get & Transform Data > From Table/Range to open Power Query Editor. Importing an Excel Table.
3. Inspect the automatic Changed Type step, if present. Power Query detects types for Excel sources by default. If this step converted park_id to a number, remove that step and set park_id to Text before continuing. Check that 001 and 002 are intact; adding a text conversion after a numeric conversion will not restore the zeros. Use the type icon beside a column heading to set its type. Power Query data types.
4. Select the three month columns: 2026-01, 2026-02 and 2026-03.
5. Choose Transform > Unpivot Columns. This moves the selected headers and their corresponding values into attribute and value columns. Microsoft’s unpivot instructions.
6. Rename Attribute to month and Value to visits.
7. Check the column types: park_id, park_name and month should be Text for this example; visits should be Whole Number.
8. Choose Home > Close & Load to return the result to Excel. Power Query loads to a new worksheet by default, leaving the source worksheet unchanged. Import and load behavior.

For this complete sample, the expected output is six records and four columns. With real data, check missing-value records explicitly rather than relying only on a row-count calculation.

4. Check the result and prepare the public file

Validate the transformed data against the source:

Check	Expected result for this sample
Records	6
Unique park-month combinations	6
North Park total	405
South Park total	270
Overall visits	675
Park IDs	001 and 002, with zeros intact

Document what readers need to interpret it: one row means one park-month; visits is a count of visits, not necessarily unique visitors; month uses YYYY-MM. For real data, add the source, coverage period, update date and missing-value rules.

Create a separate, reviewed publication file containing only the intended records and columns. If the working workbook contains formulas, preserve it separately and use reviewed values in the release copy. Do not upload personal, confidential or otherwise sensitive information.

TablePage accepts CSV, TSV, XLSX and XLS files and generates a public dataset page with a filterable table. After uploading the reviewed file, check the published headers, identifiers, month labels and totals before sharing its link. For a detailed file-level checklist, follow the CSV export workflow.