Cleaning data and building a first pivot table

The most common Excel task in a data role is not a formula. It is taking a messy export and producing a clean summary. This lesson walks through the sequence once; after that it is habit.

Clean first

1. Freeze the header row (View > Freeze Panes) so you always know what each column holds.
2. Remove duplicates (Data > Remove Duplicates), choosing the columns that define a duplicate, usually an ID.
3. Fix numbers stored as text: select the column, use Data > Text to Columns > Finish, or multiply by 1.
4. Trim spaces with =TRIM(A2) in a helper column, then paste values back.
5. Standardise categories with Find and Replace: "n/a", "NA" and "N.A." become one value.

Then summarise

Select the table, Insert > PivotTable, new sheet. Drag Region to Rows, Status to Columns, Amount to Values. You now have a totals grid that would take a dozen SUMIFS to build by hand, and it refreshes when the data changes.

Three settings worth knowing

• Value Field Settings lets you switch Sum to Count or Average.
• Group lets you roll dates up to months or quarters.
• Show Values As > % of Column Total answers "what share" questions without a single formula.

Keep the raw export on its own sheet and never edit it directly. When someone asks for a different cut of the data, you change the pivot, not the source.