Learn Advanced Excel for Data Analysis: Formulas, PivotTables and Reports
Structure and prepare data
Keep each record on one row and each field in one column. Use one header row and consistent formats. Look for blanks, duplicates, extra spaces, and inconsistent category names. Preserve a source copy so cleaning choices can be reviewed.
Use formulas
SUM, AVERAGE, COUNTIF, and SUMIFS calculate and compare measures. IF can classify records, while lookup functions match information across tables using a stable key. Check how formulas handle missing or unmatched values; a returned result does not prove the match is correct.
Summarize with PivotTables
PivotTables group records by category, date, or region and calculate totals, counts, or averages. Refresh them after source changes and confirm each value field uses the intended aggregation.
Make reports readable
Use line charts for trends and bar charts for category comparisons. Add clear titles, readable number formats, and only the highlights needed to answer the question.
Practice
With sales data containing date, region, category, quantity, and unit price, add revenue, use SUMIFS for regional totals, and create a monthly PivotTable by category. Add a chart and write two evidence-based observations.