A PivotTable is only as strong as the data behind it. In this video, the instructor takes a few minutes to talk about some key dos and don'ts for preparing your raw data for analysis.
- [Instructor] Alright, now before we get our hands dirty and start working with actual pivots, it's incredibly important to understand that a pivot table is only as strong as the data behind it. There is a right and a wrong way to structure your source data and this lecture is about helping you understand the difference. A good data structure is rectangular with variables laid out as columns with clear header names and observations laid out as rows. If your data's flip-flopped or transposed, you really won't be able to analyze it in any sort of logical way using a pivot.
Good source data is also free of unnecessary formatting. Formats like number types, font styles, cell fills, etc, won't pass through to a pivot table anyway so it's a best practice to keep things simple and clean on the back-end. Your source data should only contain dimensions and measures. Think of dimensions as categorical fields and measures as numerical fields. Don't add any additional header or footer rows since Excel will automatically identify column headers based on the first row in your source data range.
Finally, your raw source data shouldn't contain any subtotal rows or calculated fields. Pivot tables are designed to do those sorts of calculations for you and to do those in a more accurate and flexible way so adding any fields aside from the raw values in your source data will only make things more complicated. Now on the flip side, a bad data structure might be transposed with variables as rows and observations as columns. It might be laid out in an unstructured or non-rectangular way or maybe it contains unnecessary formatting, calculated fields, misleading column names, or extra header rows.
The bottom line is that the only role your source data needs to play is to store the raw values for your pivot table to analyze. Your best bet is to keep things clean and simple and apply your formats and calculated fields using the pivot table itself.
- Establishing a layer convention and property filters
- Structuring your source data
- How PivotTables actually work
- Number formatting and conditional formatting
- Sorting and grouping options
- Troubleshooting incorrect sorting
- Value summarization and value calculation modes
- Inserting calculated items
- Visualizing data with PivotCharts
- Designing interactive dashboards
Skill Level Intermediate
Part 1: Introduction1m 8s
1. Getting Started
Getting to know IMDb2m 21s
2. PivotTable 101
3. PivotTable Formatting
4. Sorting, Filtering, and Grouping
5. Calculated Values, Fields, and Items
6. Visualizing Data with PivotCharts
7. Wrapping Up
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.