Why cleaning comes before charting
A pivot table does not decide whether a row is a subtotal, a typo, or a missing value—it simply summarizes what it receives. That is why a chart can look polished and still be wrong. A short preparation pass prevents the most common problems: missing categories, incorrect sums, duplicate records, and dates that appear in the wrong order.
1. Keep one header row
Put field names in the first row and data beneath them. Remove report titles, blank rows above the table, and merged cells. Every header should be specific and unique: Order Date, Region, Category, and Sales make better pivot fields than Column 1 or two columns both named Total.
2. Use one record per row
Each row should describe one transaction, observation, or event at a consistent level of detail. Do not mix individual orders with monthly summaries in the same source range. If you include both, the pivot will count the summary and the underlying transactions, inflating the result.
3. Remove totals and subtotals
Rows labeled Total, Grand Total, or Subtotal belong in a report, not in the source data. Because they already contain a calculation, including them causes double counting when you use Sum. Filter them out before upload and let the pivot create totals from the raw records.
4. Make numeric values truly numeric
Amounts that look like numbers may actually be text because of currency symbols, spaces, commas, or an imported apostrophe. Test a few cells: they should align and calculate as numbers in your spreadsheet software. Keep the currency label in the header or format, not embedded inconsistently in the value.
5. Standardize categories
A pivot treats West, west, and West as different labels. Decide on one spelling, case, and naming convention. This is especially important for product names, teams, regions, and status fields. Sort a column alphabetically to spot near-duplicates quickly.
6. Parse dates before grouping
Dates stored as text do not reliably sort or group by month and year. Use one unambiguous format such as YYYY-MM-DD where possible. Check for mixed formats, blank dates, and values that were interpreted as text; otherwise a line chart can place time periods in a misleading order.
7. Decide how to handle blanks
A blank category is not always an error, but it must have a meaning. Fill a known value, label it Unassigned, or filter it out deliberately. For blank numeric values, determine whether the right interpretation is zero, unknown, or a record that should be excluded. Do not silently turn unknown values into zero.
8. Check for duplicates at the right level
Duplicate rows are only wrong when the same event was imported twice. Before removing anything, identify the fields that make a record unique—for example, order ID plus line item. Then compare matching rows. Repeated customer names or products may be valid; repeated transaction IDs may not be.
What to do next
Once the table is tidy, choose a visual based on the question you need to answer. A category comparison, a trend, and a part-to-whole view each need a different chart.