A step-by-step guide to turning a raw table of data into clear summaries and charts. It covers cleaning data, building pivot tables, summarizing and filtering, and choosing and formatting charts, with practice datasets for every exercise.
What the guide covers
Preparing data
What a well-structured table looks like, removing duplicates and blank rows, fixing inconsistent text and dates, and splitting values that were entered into a single column.
Building pivot tables
Choosing rows, columns and values, switching between sum, count and average, and grouping dates and numbers into useful ranges.
Summarizing and filtering
Filtering and sorting a pivot table, showing values as a share of a total, and adding calculated fields.
Choosing a chart
Matching the chart type to the question: comparing categories, showing change over time, showing parts of a whole, and showing how two values relate.
Formatting charts
Titles, axis labels, colors, gridlines and data labels, and removing clutter so a chart can be read at a glance.
Keeping it current
Refreshing a pivot table when the source data changes and keeping charts connected to the data they show.
Inside the guide
Every chapter follows the same pattern: the skill is explained, broken into steps, practiced in an exercise, and checked against a worked solution. These illustrations show the three kinds of page you will find in the PDF.
Illustrations represent the layout of guide pages. They are not reproductions of any software's interface.
What's in the .zip
Your .zip download
- PDF guideEach skill explained, then broken into numbered steps.
- Step-by-step instructions
- Annotated illustrations
- Practice exercises
- Worked solutionsEvery step, not only the final answer.
- Practice datasetsThe datasets the exercises use, including deliberately untidy data for the cleaning exercises. They are made to open in either Microsoft Excel or Google Sheets.
The software is not included. See Software required below.
Software required
This guide is written for Microsoft Excel and Google Sheets. You can follow it with either one; where the steps differ, both are shown. Neither program is included with the guide; you need your own access to the one you choose, obtained separately from its maker.
About software updates. Software makers update their programs, so menus and button locations can change between versions. When an update changes the steps, the guide is revised, and updated files are free to anyone who bought this guide.
How the guide works
This is a self-study guide. You read each step, carry it out in your own copy of the software, then do the exercises and compare your work with the worked solutions. There is no video, no live instruction, no tutoring, and no feedback on your work. Everything you get is in the .zip.
Is this guide a good fit?
Who it's for
- You are comfortable with basic formulas and cell references.
- You have tables of data and want to summarize them without building every total by hand.
- You make charts but are not sure which type suits your data or how to make them easy to read.
Who it isn't for
- You are new to spreadsheets. Start with Spreadsheet Formulas & Functions.
- You need statistics, database queries, or a programming language. Those are not covered.
- You want video lessons, live help, or someone to review your work.