Hi, for quite some time now, we have been watching how column fields row fields and value fields have been behaving while working with data. Now we talk about report filter. And once having discussed this, I will talk about a particular trick, which I'm sure you love it if you have not worked with that trick before. So watch closely. Let's assume that I have picked up division in the row field. And in fact, let me put division later.
Let me put ating dating in the row fees. And let me put name in the main action area which we have referred to the specifically the value fields. So what this tells me is there are 417 employees and how have they been distributed in different rating pools. Now, when I give division to this report, filter person, or I just put the division in the filter section of this box, it both means the same thing. It acts like a master filter, which whereby if I choose just one division in Ed, it gives me The report based on only that division ad, so it acts like a master filter. If you choose a particular option or group of options, it quickly filters into subsequent pivot table report based on that.
Now, this you would have figured it by now, that's not the trick I'm referring to mind trick is about to be discussed and discovered right now, assume there were 20 divisions, and you would have made a very elaborate report based on that. And the 20 reports the 20 divisions have to be prepared based on individual sheets. So for example, I want at divisions data, this to be copied, a new sheet should be generated and the data should be pasted there. And the sheet should be named as ad. Now that looks like quite a bit of work if you had multiple divisions that runs into 10s and maybe even hundreds of times. Now see the trick.
Assume that this is all just the first thing I'm doing making sure all the options are selected now Go to the Pivot Table Tools Options tab. It's called analyzing the later version of Excel 2013 onwards, but right now it's called options in 2010. Now, on the extreme left, I see a drop down of options. Please note, I am not clicking on the word options. In fact, I'm going to click on the drop down of options. Once I do that, I get something called show report filter pages.
Now once I look at it, let me correlate it with a certain logic. In the report filter section, you have given division, correct. So when I correlate division with report filter, let me reread it, it says show division pages. Again, really the same thing, show division pages show division pages, what does it tell you? It tells you that there are n number of divisions and you want to see all of them in different pages as you press OK, right now, in few seconds, you get these sheets generated, which are in the name of the division, and that has automatically been given Integrated, you don't have to do a single piece of copy and pasting. So if ever you have made a pivot table and you like the format, and that format needs to be replicated for a number of divisions or number of cost centers or number of countries, this is a trick you're going to follow.
You ensure that something is existing inside this report filter, that it needs to be made sure why because if you don't put anything in that report filter, and then you go ahead and try to click on the drop down, this option seems to be deactivated. So this can potentially replicate hundreds of worksheets in no time. And you see that as we take bigger projects as we progress