Locked lesson.
About this lesson
Determining the Field Well options you’d like to use to view your PivotTable data.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the Excel workbook used in the video tutorial and try the lesson yourself.
Field Well Options.xlsx191.6 KB
Quick reference
Field Well Options
PivotTable Field Well Options for set-up.
Where/when to use the technique
When setting up any PivotTable.
Instructions
Hiding/showing the field list
- Click outside the PivotTable (field list goes away)
- Click inside the PivotTable (field list comes back)
- Click the x in the top right of the Field List to dismiss it
- To restore it, either
- Right-click the PivotTable > Show Field List
- Excel 2010: PivotTable Tools > Options > Field List
- Excel 2013+: PivotTable Tools > Analyze > Field List
Changing Field Well/List Views
- Click the icon next to the “Choose fields to add to report” line in the field well header
Fields Sections and Areas Section stacked
- Default option
- Fields listed on top
- PivotTable field areas listed in 2x2 grid underneath
Fields Sections and Areas Section side by side
- Fields listed on left side
- Areas sections listed in 1x4 grid on right side
Fields Sections only
- Only shows the fields list (not the areas section)
Areas Sections only (2 by 2)
- Only shows the areas section, stacked in a 2 by 2 grid
Areas Sections only (1 by 4)
- Only shows the 4 areas sections, stacked vertically
- 00:04 In this video, we're going to look at the Field Well options.
- 00:07 The reason for this is actually twofold.
- 00:09 Number one, so that if you see that I have a different view than you,
- 00:12 it's not a big deal because you'll know how to change it.
- 00:14 And the second one is that it gives you more options for
- 00:17 when you're configuring your Pivot Table to sometimes
- 00:20 make it a little bit easier to actually work with as well.
- 00:23 So what is the Field Well?
- 00:24 Well, that's the area when we click inside our Pivot Table, that usually
- 00:28 pops up in this list on the right-hand side, although sometimes it doesn't.
- 00:31 And the reason being is because you could have dismissed it by clicking
- 00:34 the little close button here.
- 00:36 Normally this Field Well menu will show up,
- 00:38 not when you click outside the Pivot Table, it always goes away,
- 00:41 but when you click back inside the Pivot Table.
- 00:43 So if it doesn't show up, what do you do?
- 00:46 Well, there's a couple of different ways to do this.
- 00:48 Right-click, we can say Show Field List, the very last item on the list,
- 00:52 it will actually bring this back up.
- 00:54 In addition, if you go to your Pivot Table tools Analyze tab, or
- 00:58 in newer versions Office 365, it's just Pivot Table Analyze.
- 01:03 If you click on this, you'll notice that way across on the right-hand side, you'll
- 01:07 see the Field List button, which can also be used to turn the field list on and off.
- 01:11 Now, there are different configuration options for working inside this area, why?
- 01:17 Well, it's because on some screen resolutions,
- 01:19 you don't have a lot of room to work with down in the bottom right-hand corner where
- 01:23 we actually do all of our work.
- 01:24 Notice that when I come down here and say, hey, I'm going to add item name into
- 01:29 my rows area of my Pivot Tables, it's now completely full.
- 01:32 Now, it doesn't stop me from adding more things.
- 01:34 I could come back up and say hey,
- 01:36 I want to take the ChitNumber and I'm going to slide it down in here,
- 01:39 but it gets really hard to work with inside this tiny little window here.
- 01:43 So are there other options to play with?
- 01:44 And the answer of course, is yes.
- 01:47 Under the gear icon on our Field Well, we can actually go and change the views and
- 01:51 there's a few different ones to work with.
- 01:54 You can work with the Fields section and Area section stacked.
- 01:58 This is the default view that we see right now, which has our fields list up the top,
- 02:02 and then the actual Field Well in a 2 by 2 grid down the bottom.
- 02:06 We could change this to go to Field Section Only.
- 02:09 This is just going to give us a list of our fields.
- 02:11 Now, the problem with that, of course, is that we can't actually go and
- 02:14 drag and drop things into different places.
- 02:16 So it's nice to see the list if we want to just check the boxes on or off.
- 02:20 But beyond that, it's not really very helpful.
- 02:22 We have an Area Section Only 2 by 2.
- 02:25 This one can be useful if we need more space,
- 02:27 we don't have the field list with all the fields.
- 02:30 But if we're just trying to rearrange items and drag them up and down,
- 02:33 this gives us a little more room to work with, which can be pretty handy.
- 02:36 We have an alternate view of this as well, the Area Section Only 1 by 4,
- 02:40 which actually stacks them in this direction.
- 02:43 And the last one I want to show you is a hybrid, which is this one,
- 02:47 one of my personal favorites, Fields and Areas Sections Side-By-Side,
- 02:51 which allows me to get into this view.
- 02:53 Now, I don't have a ton of room in this area,
- 02:55 but I can see a massive list of fields, which is useful.
- 02:58 So the nice thing around this is that we can actually flip back and
- 03:01 forth between different views that might be better for
- 03:04 us when we're actually configuring our Pivot Table.
- 03:06 Now, I'm going to flip back into the standard default order right now,
- 03:11 which looks like this,
- 03:12 where I now only have two different rows that I can actually play around with.
- 03:17 The one other thing I want to call out in this list is this, Deferred Layout Update.
- 03:22 Where this becomes super useful is when we have really large datasets, and it takes
- 03:26 a long time to refresh, because we're adding fields onto the actual Pivot Table.
- 03:30 When you click Deferred Layout Update, you can check the box, and
- 03:33 now I can move down this list and say, this POSChitNumber, I don't want it.
- 03:37 So I'm going to pull it off and I'm going to drag it off.
- 03:39 I'm just going to throw it over on the grid here.
- 03:41 This is the same, basically, as throwing it up in the field list at the top here.
- 03:45 The other thing is I'm going to say,
- 03:46 from my field list I really don't want Item Name on the Pivot Table either.
- 03:50 So I'll just uncheck the box.
- 03:51 So now you've got three ways to remove a field.
- 03:54 But notice that nothing is changed on the pivot table and
- 03:57 nothing will until I go and click this Update button right here.
- 04:01 And when I do that, it now updates the pivot table back to what it was and
- 04:05 I can uncheck the Deffered Layout Update.
- 04:07 Very important to make sure you click your Update button before you uncheck this box
- 04:10 or it will just toss the changes that you've actually made.
- 04:13 But this makes it a lot easier when you're trying
- 04:18 to configure big Pivot Tables as well.
Lesson notes are only available for subscribers.