1015 Pivot Table - Report Filter - Generating 100s of Reports in Few Seconds

Advanced Excel Crash Course Section 10: Pivot Table
3 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.26
List Price:  €129.16
You save:  €36.90
£78.90
List Price:  £110.47
You save:  £31.56
CA$135.76
List Price:  CA$190.06
You save:  CA$54.30
A$153.66
List Price:  A$215.14
You save:  A$61.47
S$134.30
List Price:  S$188.03
You save:  S$53.72
HK$782.11
List Price:  HK$1,094.99
You save:  HK$312.87
CHF 88.79
List Price:  CHF 124.31
You save:  CHF 35.52
NOK kr1,070.17
List Price:  NOK kr1,498.28
You save:  NOK kr428.11
DKK kr688.01
List Price:  DKK kr963.24
You save:  DKK kr275.23
NZ$165.58
List Price:  NZ$231.83
You save:  NZ$66.24
د.إ367.20
List Price:  د.إ514.10
You save:  د.إ146.89
৳11,008.59
List Price:  ৳15,412.47
You save:  ৳4,403.87
₹8,303.29
List Price:  ₹11,624.95
You save:  ₹3,321.65
RM473.19
List Price:  RM662.48
You save:  RM189.29
₦156,506.34
List Price:  ₦219,115.14
You save:  ₦62,608.80
₨28,049.28
List Price:  ₨39,270.12
You save:  ₨11,220.83
฿3,608.38
List Price:  ฿5,051.88
You save:  ฿1,443.50
₺3,233.78
List Price:  ₺4,527.43
You save:  ₺1,293.64
B$503.03
List Price:  B$704.27
You save:  B$201.23
R1,898.90
List Price:  R2,658.54
You save:  R759.63
Лв180.15
List Price:  Лв252.22
You save:  Лв72.06
₩133,999.91
List Price:  ₩187,605.24
You save:  ₩53,605.32
₪366.35
List Price:  ₪512.91
You save:  ₪146.55
₱5,593.09
List Price:  ₱7,830.55
You save:  ₱2,237.46
¥15,066.66
List Price:  ¥21,093.92
You save:  ¥6,027.26
MX$1,690.23
List Price:  MX$2,366.39
You save:  MX$676.16
QR365.81
List Price:  QR512.15
You save:  QR146.33
P1,369.30
List Price:  P1,917.08
You save:  P547.77
KSh13,273.67
List Price:  KSh18,583.67
You save:  KSh5,310
E£4,710.13
List Price:  E£6,594.37
You save:  E£1,884.24
ብር5,696.11
List Price:  ብር7,974.78
You save:  ብር2,278.67
Kz83,441.65
List Price:  Kz116,821.65
You save:  Kz33,380
CLP$94,734.02
List Price:  CLP$132,631.42
You save:  CLP$37,897.40
CN¥719.86
List Price:  CN¥1,007.84
You save:  CN¥287.97
RD$5,933.04
List Price:  RD$8,306.49
You save:  RD$2,373.45
DA13,480.42
List Price:  DA18,873.13
You save:  DA5,392.71
FJ$226.79
List Price:  FJ$317.51
You save:  FJ$90.72
Q783.08
List Price:  Q1,096.35
You save:  Q313.26
GY$20,985.05
List Price:  GY$29,379.92
You save:  GY$8,394.86
ISK kr13,738.62
List Price:  ISK kr19,234.62
You save:  ISK kr5,496
DH1,007.92
List Price:  DH1,411.13
You save:  DH403.21
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,685.15
List Price:  ден7,959.43
You save:  ден2,274.28
MOP$807.89
List Price:  MOP$1,131.08
You save:  MOP$323.19
N$1,891.51
List Price:  N$2,648.20
You save:  N$756.68
C$3,691.90
List Price:  C$5,168.80
You save:  C$1,476.90
रु13,305.26
List Price:  रु18,627.89
You save:  रु5,322.63
S/369.78
List Price:  S/517.70
You save:  S/147.92
K378.11
List Price:  K529.37
You save:  K151.26
SAR374.99
List Price:  SAR525
You save:  SAR150.01
ZK2,562.18
List Price:  ZK3,587.15
You save:  ZK1,024.97
L458.93
List Price:  L642.52
You save:  L183.59
Kč2,328.03
List Price:  Kč3,259.33
You save:  Kč931.30
Ft36,496.67
List Price:  Ft51,096.80
You save:  Ft14,600.13
SEK kr1,049.30
List Price:  SEK kr1,469.07
You save:  SEK kr419.76
ARS$85,200.41
List Price:  ARS$119,283.99
You save:  ARS$34,083.57
Bs693.12
List Price:  Bs970.40
You save:  Bs277.27
COP$390,541.29
List Price:  COP$546,773.43
You save:  COP$156,232.14
₡51,032.06
List Price:  ₡71,446.93
You save:  ₡20,414.86
L2,476.89
List Price:  L3,467.75
You save:  L990.85
₲731,475.55
List Price:  ₲1,024,095.03
You save:  ₲292,619.48
$U3,846.42
List Price:  $U5,385.15
You save:  $U1,538.72
zł398.58
List Price:  zł558.03
You save:  zł159.45
Already have an account? Log In

Transcript

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

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.