This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Problem: I have 10 kazillion records in an Access table. I would like to create a pivot table for this data.
Strategy: You can create a connection to the Access table and build the pivot table in Excel. Follow these steps:
Start with a blank Excel workbook.
Select Data, From Access.
Browse to your Access database and click Open.
The Select Table dialog shows a list of all the tables and queries in the database. The Type column says VIEW for queries and TABLE for tables. Choose the desired query or table and click OK.
In the Import Data dialog that appears, choose to create a pivot table report and click OK.
Results:Excel will display the PivotTable Field List dialog, with all the fields from your table or query.
Bottom line: If the pivot table field list went missing on you, this article and video will explain a few ways to make it visible again. I also share a few other tips for working with the field list.
Skill level: Beginner
Watch on YouTube (and give it a thumbs up)
The Pivot Table Field List Disappeared
Typically when you select a cell inside a pivot table, the pivot table field list automatically appears on the right side of the Excel application window in a task pane.
However, the pivot table field list can go missing (get disabled) if you accidentally press the close button in the top right corner of the field list.
The close button hides the field list. This means that it will NOT reappear when you select a cell inside a pivot table.
So how do we make it visible again? Here are a few quick ways to do it.
Method #1: Show the Pivot Table Field List with the Right-click Menu
Probably the fastest way to get it back is to use the right-click menu.
Right-click any cell in the pivot table and select Show Field List from the menu. This will make the field list visible again and restore it's normal behavior.
The field list will disappear when a cell outside the pivot table is selected, and it will reappear again when a cell inside the pivot table is selected.
The Field List Button is a toggle button. This means we only have to turn it on/off once to keep the setting. When we click the close button in the top-right corner of the field list, the toggle will be turned off. The field list will be hidden until we toggle it back on.
Method #2: Show the Field List from the Ribbon
The field list can also be toggled on/off from the ribbon menu.
First select any cell inside the pivot table.
Click on the Analyze/Options tab in the ribbon. The tab is called Options in Excel 2010 and earlier.
Click the Field List button on the right side of the ribbon. This is also a toggle button that will show or hide the field list.
Field List Tip: Undock and Move the Task Pane
The most common reason the field list close button gets clicked is because the field list is in the way. Sometimes it covers up the pivot table and forces you to scroll horizontally.
We can actually move the field list outside of the Excel application window. You can even move it to another screen if you have multiple monitors.
When you hover the mouse over the top of the field list, the cursor will turn to cross arrows.
Left-click and hold to drag and move the field list.
To re-dock the field list, double-click the top of the field list window. That will automatically move it back to its default location on the right side of the Excel application window.
Use PivotPal to Modify the Pivot Table from the Source Data Sheet
The field list always disappears when you click a cell outside the pivot table. I have always thought it would be nice to be able to see the field list while working with the source data sheet for the pivot table.
So I built this feature into the PivotPal add-in. In the video above I explain how you can use PivotPal to build and modify the pivot table while looking at the source data sheet.
This feature saves me a ton of time every day. I don't have to jump back and forth between the source data and pivot table sheets. This is especially useful when searching for a field that I don't know the name of.
PivotPal is an Excel Add-in that is packed with features. It will save you a lot of time when working with pivot tables.
Click here to learn more about PivotPal
What Are Your Favorite Field List Tips?
Do you have any other tips for working with the pivot table field list? Please share by leaving a comment below. Thanks!
