REF! errors – source data was moved/deleted, refresh pivot table

Excel pivot tables provide a powerful way to summarize, analyze, explore and present your data, enabling you to see comparisons, patterns and trends quickly and easily. In this comprehensive guide, we‘ll walk through how to create and customize pivot tables in Microsoft Excel step-by-step. Whether you‘re new to pivot tables or just need a refresher, you‘ll learn everything you need to become a pivot table pro.

We‘ll cover:

• Creating a basic pivot table from any dataset
• Customizing the pivot table layout, style and formatting
• Grouping, sorting and filtering data
• Adding calculated fields and custom formulas
• Visualizing your data with pivot charts
• Best practices, shortcuts and troubleshooting tips

To illustrate the concepts, we‘ll use a sample dataset containing sales data for a fictitious company. The techniques can be applied to any dataset in an Excel table or range.

Let‘s get pivoting!

Creating a Basic Pivot Table

Here‘s the sample data we‘ll be using throughout this tutorial:

INSERT SCREENSHOT OF SAMPLE DATA

It contains sales information broken down by sales rep, region, product category, units sold, revenue and more. Our goal is to summarize and explore this data using a pivot table.

To create a pivot table:

1. Select any cell within your data range
2. Go to Insert > PivotTable on the ribbon
3. Verify the Table/Range selected contains your data, choose where to place the pivot table (new worksheet or existing worksheet) and click OK

INSERT SCREENSHOT OF CREATE PIVOTTABLE DIALOG

Excel will insert a blank pivot table and open the PivotTable Fields pane on the right:

INSERT SCREENSHOT OF BLANK PIVOT TABLE AND FIELDS PANE

The PivotTable Fields pane displays all the columns from your dataset. From here, you can drag-and-drop fields to build your pivot table report.

There are 4 areas:

• Filters: Allows you to filter the entire pivot table by one or more fields
• Columns: Fields placed here are column headers in the pivot table
• Rows: Fields placed here are row headers
• Values: Contains the numeric values that will be aggregated (summed, counted, averaged, etc.) for each unique row/column intersection

To build our initial pivot table:

1. Drag the "Product Category" field to the Rows area
2. Drag the "Revenue" field to the Values area

INSERT SCREENSHOT OF COMPLETED BASIC PIVOT TABLE

Excel automatically creates the pivot table with:

• A row for each unique Product Category
• Revenue values summed for each category
• A grand total row

With just a few clicks, we‘ve summarized our data by product category and revenue. The real power of pivot tables is how easy it is to change this view…

Customizing Pivot Table Layout and Design

Let‘s customize our pivot table to display Revenue by Sales Rep and Region.

In the PivotTable Fields pane:

1. Remove the "Product Category" field from Rows
2. Add the "Region" field to Columns
3. Add the "Sales Rep" field to Rows

INSERT SCREENSHOT OF FIELDS PANE SETUP

The pivot table updates to show revenue totals by the sales rep, with columns for each region:

INSERT SCREENSHOT OF UPDATED PIVOT TABLE LAYOUT

To apply a style and formatting:

1. With the pivot table selected, go to PivotTable Tools > Design on the ribbon
2. Choose a style from the PivotTable Styles gallery
3. Check desired formatting options like banded rows/columns, row headers, and subtotals

INSERT SCREENSHOT OF PIVOTTABLE STYLE AND FORMATTING OPTIONS

Play around with the layout and formatting until you get a pivot table design you like. You can always adjust styles and options later.

Grouping, Sorting and Filtering Data

Pivot tables include robust options for grouping, sorting and filtering your data.

To group a field by a number range:

1. Right-click any value in the "Units Sold" field and select "Group"
2. Specify the starting, ending, and interval values
3. Click OK

INSERT SCREENSHOT OF GROUPING DIALOG

The "Units Sold" field gets grouped into buckets based on the range:

INSERT SCREENSHOT OF GROUPED FIELD

To sort data:

1. Select any cell in the "Sales Rep" field
2. Right-click and select "Sort" then choose ascending or descending

INSERT SCREENSHOT OF SORTING MENU

The rows rearrange sorting the sales reps alphabetically.

To filter a field:

1. Click the filter arrow next to the "Region" field
2. Uncheck "(Select All)" and choose which regions to view
3. Click OK

INSERT SCREENSHOT OF FILTER MENU

Now only data for the selected regions appears in the pivot table. Filters can be applied to any row, column, or report filter field.

Adding Calculated Fields and Formulas

Calculated fields and custom formulas allow you to extend pivot tables with additional metrics.

To add a calculated field:

1. In the PivotTable Fields pane, right-click any field and select "Add Calculated Field"
2. Enter a name for the new field
3. Construct your formula referencing other pivot table fields
4. Click Add then OK

For example, to calculate Profit:

INSERT SCREENSHOT OF ADDING CALCULATED FIELD

1. Name it "Profit"
2. Enter the formula =[Revenue]-[Cost]
3. Add the Profit calculated field to the Values area

The pivot table now includes a Profit column calculated from the formula.

Custom formulas can perform nearly any calculation you need. Common uses include:

• Variances between values
• Ratios and percentages
• Calculating commissions or bonuses
• If/then conditions

Visualizing Data with Pivot Charts

Pivot charts allow you to plot your pivot table data graphically, making insights more visible.

To create a pivot chart:

1. Select any cell in your pivot table
2. Go to Analyze > PivotChart on the ribbon
3. Choose a chart type and style
4. Click OK

INSERT SCREENSHOT OF PIVOT CHART

Pivot charts are interactive – as you change the underlying pivot table data, the chart updates automatically.

To change the chart type:

1. Select the chart
2. Go to Design > Change Chart Type
3. Choose a new chart type and click OK

Experiment with different chart types to see which one represents your data best. For further customization, you can right-click any chart element (such as the title or legend) and select "Format" for additional styling options.

Pivot Table Best Practices and Troubleshooting

Some tips for working with pivot tables:

• Use Tabular layout for a flattened pivot table view
• Double-click any value cell to see the underlying data
• Refresh a pivot table after updating source data (Analyze > Refresh)
• Drill down to different levels of detail by double-clicking row/column labels
• Copy/paste or duplicate a pivot table to experiment with different setups
• Use the Value Field Settings to show values as % of totals
• Clear old items in the PivotTable Fields pane for unused fields
• Consolidate pivot tables from multiple tabs on a summary sheet

Common issues and resolutions:

• Trouble changing calculated field formulas – create a new calculated field instead
• Performance is slow – limit source data, split into multiple pivot tables
• Lose pivot table after source data refresh – use Table feature for dynamic data ranges

Conclusion

We‘ve covered the essential skills you need to create, customize and use pivot tables effectively in Excel. Pivot tables are an incredibly flexible way to quickly summarize and report on your data with endless customization options.

Practice making pivot tables with your own datasets to master these techniques. Experiment with different setups, layouts, styles, calculated fields, and charts. Over time, you‘ll develop an intuition for structuring pivot tables to extract meaningful insights from your data.

This tutorial provides a solid foundation for working with pivot tables, but there‘s always more to learn. Consult the Microsoft support documentation for your version of Excel to dive into advanced features and functionalities. Most importantly, have fun pivoting your data!