*Pivot tables

Think of a pivot table like organizing a filing cabinet. You have a drawer full of citizen report data scattered everywhere, but you want to quickly see how many people live in each state by gender. A pivot table organizes this information into a clean grid where you can instantly see: "Bavaria has 3.2 million men, 3.3 million women, and 15,000 diverse citizens."

What is a pivot table?

A pivot table takes rows of data and organizes them into a summarized grid. It's like a smart calculator that can instantly group and count your information in different ways.

Perfect example: You have a dataset with thousands of rows showing state, gender (male, female, diverse), and citizen counts. Instead of scrolling through thousands of entries, a pivot table shows you a clean summary with states down the left side, genders across the top, and totals in each cell.

When should you use pivot tables?

Pivot tables are perfect when you want to answer questions like:

• "What are my totals by category?" - Like total citizens by state and gender

• "How do different groups compare?" - Like population distributions between regions

• "What patterns can I see?" - Like which states have the most diverse gender distribution

• "How do two things relate?" - Like how different age groups are distributed across states

Real-world examples:

• Population statistics: Citizens by state and gender

• Budget analysis: spending by department and quarter

• Health data: vaccination rates by district and age group

• Service usage: public services by neighborhood and time period

How to create a pivot table

1

Prepare your data

Your data should have columns that can be grouped together. For our example:

• State column (Bavaria, NRW, Berlin, etc.)

• Gender column (male, female, diverse)

• Citizens column (the numbers you want to sum up)

Column limitations: Columns used for rows and columns in pivot tables can have maximum 50 unique values (cardinality). If your data has more than 50 unique values in a column, you should filter it first, group similar values, or choose rows for that column instead.

State	Gender	Count
Bavaria	male	6,600,000
Bavaria	female	6,900,000
Bavaria	diverse	1,500
Baden Württemberg	female	5,950,000
Baden Württemberg	diverse	1,300
...	...	...

2

Turn on pivot table mode

1. In your insight, pick your dataset and columns as usual

2. Find the "Make pivot table" toggle switch

3. Turn it on - you'll see three new sections appear:

   • Rows (what goes down the left side)

   • Columns (what goes across the top)

   • Values (what gets counted or added up)

3

Set up your rows

Rows are the categories listed down the left side of your table.

1. Click "Add row"

2. Choose "State" - this will list all German states vertically

3. You can add more rows for sub-categories if needed

Result: Your table will show Bavaria, NRW, Berlin, etc. listed vertically

State
Bavaria
Baden-Württemberg
...

4

Set up your columns

Columns are the categories that go across the top of your table.

1. Click "Add column"

2. Choose "Gender" - this will show male, female, diverse across the top

3. You can add more columns for additional comparisons

Result: Your table will show the three gender categories as column headers

State	male	female	diverse
Bavaria
Baden-Württemberg
...

5

Choose your values

Values are the actual numbers that appear in each box of your grid.

1. Click "Add value"

2. Choose "Citizens" - this will be summed up

3. The system automatically adds up numbers or counts items

4. You can also change the formula by going to the ... dots

5. You can add multiple values if needed

Result: Each cell shows the total number of citizens for that state-gender combination

State	male	female	diverse
Bavaria	6,600,000	6,900,000	1,500
Baden-Württemberg	5,700,000	5,950,000	1,300
...	...	...	...

6

View your pivot table

1. Go to the Chart tab

2. Select Table as your chart type

3. Your pivot table displays as a clean grid:

Now you can instantly see population patterns across all German states!

Data explorer preview limitation: The data preview in the Data tab doesn't display nested groupings properly. With multiple row or column levels, you'll see flattened values like "Bundesland:Bayern" instead of proper nesting. Always switch to the Chart tab with Table visualization to see your pivot table's proper nested structure.

Pivot table suggestions

The system provides helpful suggestions when creating pivot tables:

Column suggestions appear when:

• You have columns with reasonable cardinality (under 50 unique values)

• Columns contain categorical data that works well for grouping

• The system detects common patterns like dates, locations, or categories

What the suggestions show:

• Recommended rows: Columns that work well as row categories

• Recommended columns: Columns that work well as column headers

• Recommended values: Numeric columns that can be summed or counted

• Smart combinations: Proven combinations that work well together

Example suggestions for demographic data:

• Rows: State, City, County

• Columns: Gender, Age Group, Year

• Values: Population Count, Citizens, Households

Pro tip: Start with the suggested combinations - they're based on what works well with similar data. You can always customize later.

Advanced pivot table features

Multiple levels

You can add multiple row or column categories, like organizing a bookshelf by genre, then by author:

Multiple rows: State → City → County Multiple columns: Year → Quarter → Month

This creates nested categories that let you drill down from broad to specific.

Dragging to reorder

Once you've added categories, drag them up or down to change the order:

• Most important category first - usually goes at the top level

• Sub-categories below - provide more detailed breakdowns

Filtering within pivot tables

You can still use regular filters even after creating a pivot table:

• Filter to specific states

• Show only certain years

• Focus on particular demographic groups

Common pivot table patterns

Time series analysis

• Rows: Year, Quarter

• Columns: Category, Department

• Values: Budget, Count, Revenue

Geographic analysis

• Rows: State, County

• Columns: Service Type, Age Group

• Values: Citizens, Usage Count

Demographic breakdown

• Rows: Age Group, Income Level

• Columns: Gender, Education

• Values: Population, Survey Responses

Troubleshooting common issues

"My pivot table is empty"

• Check that you've added at least one value to count or sum

• Verify your row and column categories have matching data

"Too many empty cells"

• This happens when not every combination has data (like "Bavaria + divers" might be a small number)

• Consider using fewer categories or filtering your data first

"Column has too many values"

• Columns are limited to 50 unique values maximum

• Try grouping similar values together or using filters to reduce the options

"Pivot table is too slow"

• Large datasets with many categories can be slow

• Try filtering your data first or using fewer row/column combinations

"My nested groupings look wrong in the data preview"

• The Data tab preview shows flattened values like "Group:Value" for nested groupings

• This is normal behavior - the preview doesn't handle multiple levels well

• Switch to Chart tab → Table visualization to see proper nested structure

• Always use the table visualization to verify your pivot table layout

Best practice: Start simple with just one row category, one column category, and one value. Once that works well, add more complexity gradually. Always check your final result in the Chart tab rather than relying on the data preview.