Jul 30, 2014 - Many Excel experts believe that pivot tables are the single most powerful tool in Excel. The pivot table layout from Compact to Outline or Tabular layout. In the first tab of the options (or the layout tab on a Mac), uncheck. A pivot table is a way to summarize and view large amounts of raw data in an easy to read format. The pivot table doesn’t change your raw data, but rather creates a new view of it.
Recently, a friend of mine wondered about the Drill-Down and Drill-Up buttons in the Pivot Table Tools tab of the Ribbon. Why are these perpetually greyed out? They take up a lot of space in the Ribbon.
How is anyone supposed to use them? Look for the Power Pivot tab to the left of PivotTable Tools tabs After some research, there is a way to use them, but you have to use the Data Model and use the Power Pivot diagram view to create a hierarchy. If you don't have the Power Pivot tab in your Ribbon, you will have to find a co-worker who has the button in order to create the hierarchy. (Or, if you just want to try the feature, download the Excel file that I created: ) Look for the Power Pivot tab to the left of Pivot Table Tools in Excel. First step - convert your pivot source data set to a table using either Home - Format as Table or Ctrl + T. Make sure that the option for My Table Has Headers is selected. Create Table.
Use Insert - Pivot Table. In the Create PivotTable dialog, choose the box for Add This Data to the Data Model. Create Pivot Table. Here is the PivotTable Fields before you create the hierarchy.
Pivot Table fields. Click the Manage icon on the Power Pivot tab in the Ribbon. (Many instances of Excel 2013 and 2016 do not have this tab. It does not appear on the Mac.) Manage button on the Power Pivot tab in the Ribbon. In the Power Pivot for Excel window, click on the Diagram View icon. It is near the right side of the Home tab. Diagram View button.
Use the resize handle in the lower right corner of Table1 to enlarge Table1 so you can see all of your fields. Click on the first item in your hierarchy (Continent in my example). Shift-Click on the last item in the hierarchy (City in my example). You could also click on one item, and Ctrl-Click on others if the hierarchy fields are not adjacent.
Once you have the fields selected, right-click any of the fields and choose Create Hierarchy. Create Hierarchy. Hierarchy1 is created and is waiting for you to type a new name.
I will name my hierarchy Geography. If you click away from Power Pivot, Hierarchy1 is no longer in Rename mode. Right-click Hierachy1 and choose Rename.
Rename Hierarchy. Close Power Pivot and return to Excel. The Pivot Table Fields now shows the Geography hierarchy and More Fields. Your Sales field is hidden under More Fields. I somewhat understand why they hide Continent, Country, Region, Territory, City under More Fields. But I don't understand why they hide Sales under More Fields. More Fields To build the pivot table, check the box for the Geography hierarchy.
Open More Fields by clicking the triangle next to it. Choose Sales. Create Pivot Table There is a lot to notice in the image above. When you initially create the pivot table, the active cell is on A3 and the Drill Down icon is greyed out.
However, if you move the cell pointer to North America in A4, you will see that Drill Down is enabled. With the cell pointer on North America, click Drill Down and Continent is replaced by Country. Click Drill Down button. With the cell pointer on Canada, click Drill Down and you will see Eastern Canada and Western Canada. Note at this point, both the Drill Down and Drill Up buttons are enabled.
Drill Down and Drill Up buttons are enabled. I clicked Drill Up to return to Country. Select United States. Drill Down three times and I end up at the cities in the Carolinas region. At this point, the Drill Down button is greyed out. Drill Down button is grayed out. Note that from the Continent level, you can click Expand Field to show Continents and Countries.
Then, from the first Country, choose Expand Field to reveal Regions. From the first Region, use Expand Field to show Territories. From the first Territory, click Expand Field to reveal City.
Expand field. All of the screenshots above are showing the pivot table in my default view of Show in Tabular Form. If your pivot tables are created in Compact Form, you will see the view below.
(To learn how to have all of your future pivot tables start in Tabular form, see ). Change report layout. What is the advantage of the Hierarchy? I tried creating a regular pivot table without a hierarchy. I still have the ability to Expand and Collapse fields.
But if I want to show only the regions in Canada, I would have to add a slicer or Report Filter. Advantage of the hierarchy Watch Video.
Learn Excel from MrExcel Podcast, Episode 2196: Drill Up and Drill Down in Pivot Tables. Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. There's a mystery in pivot tables. If I insert a pivot table here, you see that we have Drill Up and Drill Down fields, but they never light up. What's up with this? Why do we have these? How do we make them work?
Alright, this is a great, great question and unfortunately, I feel bad about this. I'm trying to do all of my life in Excel not ever using the Power Pivot tab.
I don't want you to have to pay the extra $2 a month for the Pro Plus version of Office 365, but this is one- this is one- where we have to spend the extra $2 a month or find someone who has the extra $2 a month to set this up. I'll take this data format as a table. It doesn't matter what format I choose, the format is not important; just getting us a table is the important part. Power Pivot, we're going to add this table to our Data Model, and then click Manage. Alright, so here's our table in the Data Model.
We have to go to Diagram View, now we'll make this a little bit wider so we can see all the fields. I'm going to choose Continent; I'm going to Shift+click on City. Now that makes up my Drill Down, Drill Up, the hierarchy.
And then we'll right-click and say Create Heirarchy. And they give us a name- I'm going to type 'Geography' for my Hierarchy, like that. Great, now, with that one change, we insert a pivot table- and this will be a Data Model Pivot Table- and you see that we can add Geography as its own hierarchy. Now, the one thing I don't particularly like about this, is everything else moves to More Fields.
So we choose Geography and it flies to the left-hand side. And while that's great, I also need to choose Revenue, and they took the fields that weren't part of the Hierarchy and moved them to More Fields. So it's like, I get it, they're trying to hide the fields I'm not supposed to choose, but in the process of doing that they also hid More Fields- the Revenue or Sales down here.
So, a little frustrating we have to go to more fields to get the fields that aren't part of the Geography, but that's that's the way it goes. So, now, now that we have that let's take a look at what works here. I'm sitting on Continent, I go to the Analyze tab and nothing lights up, it didn't work. No, it did work, you just have to come to North America and then I can Drill Down and it replaces Continent with Country. And then from Canada I can Drill Down and get Eastern Canada and Western Canada. From Eastern Canada drill down, I get Ontario and Quebec.
Ontario, I get those cities, I can drill up Drill Up, Drill Up, and choose United States; Drill Down, Drill Down, Drill Down. Alright, so that's how it works. Give it a try, you have to have the Power Pivot tab or find someone with a Power Pivot tab. If you just want to try it, look in the YouTube description there'll be a link to the web page and there's a place there on the web page where you can download this file, and you should be able to use the Hierarchy even if you don't have the Power Pivot tab. If you're in Excel 2016 or Office 365, it should work. Now, you know, see, I guess the thing that I'm not sure I'm a fan of is the fact that they're getting rid of the other information, as opposed to using the Expand icon, which would then expand into the next group, and the next group, and the next group. We've always had the Expand icon, but even then it's working a little bit differently.
Here, if I would, I can actually sit there in North America and expand one level at a time without having to choose each additional one from the data model. It looks like we have to move the cell pointer over, one bit at a time. Alright, now, this tip was really just, kind of, discovered. The Excel MVPs had a conversation with the Excel team about these buttons, so not covered in this book. But a lot of other great tips covered in MrExcel LIVe, the 54 Greatest Tips of All Time. Wrap-up for today: Why is Drill Up and Drill Down constantly grayed out? Well, you have to create a Hierarchy.
In order to create a Hierarchy, you have to go into Power Pivot; into the Diagram View; select the fields for the heirarchy; and then right-click; and Create Hierarchy. I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel. MrExcel.com debuted on November 21, 1998.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided 'as is' and we do not guarantee that they can be used in all situations. This site contains affiliate links.
Any affiliate commissions that we earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com running. You can earn a commission for sales leads that you send to us by joining our.
Excel ® is a registered trademark of the Microsoft Corporation. MrExcel ® is a registered trademark of Tickling Keys, Inc. All contents © 1998 - 2019 MrExcel Publishing All rights reserved.
Pivot tables offer powerful ways to summarize data, but many people find that it takes as long to format and tweak a pivot table as it did to create the pivot table. A new Pivot Table Defaults feature shipping this month to Office 365 subscribers will allow you to specify your favorite pivot table formatting as a default. SETTING PIVOT TABLE DEFAULTS In the past, pivot tables were created in the Compact layout shown in Figure 1. Multiple fields in the Rows area are all collapsed into column A with a generic heading of “Row Labels.” Empty cells appear in the pivot table as blank instead of zero. Subtotals appear at the top of each group instead of the bottom.
Figure 1 The new feature lets you set the default layout for your pivot tables. There are two ways to do this.
Go to File, Options, Advanced. Scroll to the Data section. The first item should be Make Changes to the Default Layout of Pivot Tables. Click the button for Edit Default Layout. Open the Report Layout dropdown and choose Show in Tabular Form. Choose the checkbox for Repeat All Item Labels. Change the Subtotals option to Show Subtotals At the Bottom of the Group.
Click the PivotTable Options button. In the PivotTable Options dialog, type a zero (0) for the setting called For Empty Cells, Show. The next time that you create a pivot table, your settings will appear in the pivot table as shown in Figure 2. Note that you now have useful headings in A4, B4, and C3. Totals appear at the bottom of each group.
Empty cells are replaced by zero. Figure 2 The second way to set the defaults is useful if you have a pivot table that’s already in the correct format. You can base the defaults on that pivot table. Open the workbook that contains the pivot table. Select one cell in the pivot table. Go to File, Options, Advanced, Data, and click the button for Edit Default Layout.
Use the Layout Import feature by entering a single cell from the pivot table in Layout Import and clicking the Import button. All of the settings from the pivot table will become the default for future pivot tables. EXCEL.USERVOICE.COM If you use Excel 40 hours a week, you probably have a small wish list of things that you wished were different about it. With Excel.UserVoice.com, you can communicate those ideas directly to the Excel team. If others agree with you and vote for your idea—an idea needs at least 20 votes before the Excel team will respond—it’s possible that your idea could be incorporated in a future monthly release of Office 365.
This new pivot table feature began as an idea I posted at Excel.UserVoice.com. I had been grumbling about the Compact layout introduced in Excel 2007 for years and wished that Microsoft would let me specify that all future pivot tables would start in Tabular layout instead of Compact. And while conducting seminars for IMA ® (Institute of Management Accountants) chapters, I would encounter members in the audience asking for all pivot tables to start in Classic Mode or various other requests. So I submitted my idea to Excel.UserVoice.com in early 2016 and asked others to vote for it.
The idea had already received a few hundred votes before the Excel team indicated that they were going to implement a larger version of it. ONLY THROUGH OFFICE 365 Microsoft used to release a new version of Office every two to three years. This development cycle was slow. If you had a great idea just after Excel 2013 shipped, you would have to wait three years for the next release of Office. Instead of paying $400 every three years for the next release of Office, Microsoft’s new strategy is that people will pay $10 a month for a continually updated version of Office.
This product is called Office 365. New features could come out every Tuesday, but realistically, a new feature might arrive once a month. Other features that are only in Office 365 include the funnel chart, map chart, MAXIFS function, TEXTJOIN function, and icons. Other small improvements, such as the black theme, are also released this way. Those of you who create CSV files might have noticed that Excel would nag you twice during the Save As process that you might lose features with that file format. If you’re a CSV pro, it could be rather annoying to be nagged twice. A recent update to Office 365 reduces the nagging by 50%.
Features like this take 13 weeks to roll out through Office 365. The Pivot Table Default feature was rolled out to the Office 365 Insider Fast channel during the first week of March 2017. If you need the feature today, search for “How to Become Office Insider Fast” for information on changing your subscription plan. SF SAYS The Pivot Table Default feature allows you to specify the Classic pivot table layout or any other setting found in the Pivot Table Options dialog. Save Save Save.