Let's take a look at what you need to know when working with Pivot Tables. A pivot table requires a data 2
source. 3
The best case scenario your data source is an actual Excel table. 4
When you create a Pivot Table out of this data source you're actually creating a pivot cache. 5
This is something that happens in the background and we can't actually see it. Based 6
on this pivot cache is our pivot table. 7
To us it looks like the pivot table is based on the data source but in reality it's not. The pivot table 8
is directly connected to a pivot cache 9
and this pivot cache is connected to a data source. 10
When you're creating a pivot table you creating a pivot cache. When you are copying this pivot table, pasting 11
it somewhere else and then changing the look of it you are using the same pivot cache. 12
This helps to optimize the data usage of your Excel files. 13
Now down here, I've added some common pivot table tasks and the VBA code associated with that. 14
We're going to practice most of them together but let me just take you through some of the important 15
ones. 16
If you want to declare a pivot table you can Dim a variable. My default variable for this is PT 17
as a pivot table. 18
And then since this is an object variable you are going to Set "PT = ActiveShee.PivotTables" 19
and inside the brackets you can put in the pivot table name. 20
You can also reference pivot tables using their index numbers. To refresh a pivot table 21
you actually need to refresh this cache right here, which belongs to the pivot tables. 22
It's "PT.PivotCache.Refresh". If you want to refresh all pivot tables 23
you just have to reference the active workbook and say RefreshAll. A pivot table also has a 24
DataBodyRange. 25
Just like a list object, or a table has that DataBodyRange, you can reference the DataBodyRange 26
of a pivot table and then change its font property as required. 27
You can also change the number format of specific pivot table fields. Wince pivot 28
cache is an important driver of the pivot table 29
you might want to count the number of pivot caches that are existing in your workbook. 30
Sometimes you might want to duplicate your pivot cache for whatever reason or you might want to check 31
that you don't have duplicate pivot caches and that you're actually using the same pivot cache. 32
You can check the memory usage of each existing pivot cache and you can change the pivot cache from 33
one pivot to another pivot. 34
Instead of you changing it's data source you can directly change its pivot cache which automatically 35
changes its data source. 36
Make sure you keep this handout available if you're working with Pivot Tables. In the second 37
milestone of this course, 38
when we work on our regional sales tool, we're actually going to be working with tables and pivot tables. 39
Make sure you check that out if you're interested to find out more about pivot tables and that actually 40
is coming up in the next section of the course. 41
But for now let's switch to Excel and let's practice a little bit. Just like with any task that you're 42
not so familiar with in Visual Basic and Visual Basic code, it's always best to record a macro. Because 43
that's going to give you an indication of the object and the members of these objects that you can reference. 44
And then you can do more research in the object browser to find out what its other properties and methods 45
are. 46
So let's do that first. 47
And let's switch to tab pivot tables. 48
First thing we're going to do is to record a macro that's going to create a pivot table based on the 49
comp table. Comp table is a table inside the tables tab. 50
This is where we had information on the company, sales document, customer code, article number, and quantity 51
sold. 52
I'm going to create the pivot table and put it here. Next, I'm going to change the layout of the pivot table 53
and I'm going to do all this while the macro is recording. 54
This is going to give me a good idea of the object names that are involved and also the members of these 55
objects. 56
This way I have a good idea where to look inside the object library. Number 3 to 5 are tasks that 57
I'm going to do directly in the Visual Basic Editor. 58
Let's start off by recording a macro. 59
Let's go to the developer tab and click on record macro. 60
I'm going to call this "create pivot". 61
Let's go to the Insert tab, insert a pivot table. The name of the table is "Comp table". 62
I can either type it in here or I can go and select it from here. 63
When you get this type of angled arrow and you click with your mouse you get the entire table selected 64
and you can see it reflected here. 65
I want this pivot table on the existing worksheet and I want it to start from here. 66
That's it. 67
I'm going to click on OK. I get a placeholder for my pivot table here and I get the opportunity to drag 68
and drop things. 69
I want customer code in the rows. 70
I'm just going to click on it and drag it down here. 71
I want quantity in the values field. 72
I'm just going to drag it here. By default 73
Excel gives me the sum of quantity. In this case it's fine but I also want to see the percentage quantity. 74
This means I can drag quantity once again to the values field. 75
I'm going to change the header of this to read percentage quantity. 76
I'm going to click inside the values field here. 77
Right mouse click, select Show values as, and select percentage of grand total. 78
Now I can see the percentage contribution for each of these customer codes. 79
Let's stop the macro recorder. Go to the developer tab, click on stop recording and bring up the Visual 80
Basic Editor. Our macro must be inside this module 81
but let me just rename this to "Lesson pivot tables". 82
Notice that the first line of code, that's when I created the pivot table. 83
Right? 84
But what it does is that it's actually creating a pivot cache and a pivot cache belongs to the active 85
workbook. As the source data 86
It's put in comp table and then we have a ".CreatePivotTable" with its own arguments. One of which 87
is table destination. 88
And that was where we clicked on A8. 89
Then we have the table name. 90
That's the default name that we got is Pivot Table 1. 91
That's something that you can change just like you can change table names. 92
You can also change pivot table names. 93
You can do that inside the analyze tab. 94
Right on top here. 95
The next bit of code shows us a lot of properties which are being set. 96
A lot of these are things that we probably don't need and we can safely remove. Because remember the 97
macro recorder always records a lot more than you need. 98
Down here this is where we drag the customer code for the rows and we can see that it's referencing 99
our pivot table, pivot table 1. It's referencing it by name and it belongs to the active sheet. 100
Then it's digging deeper in there to the pivot fields and the specific pivot field that we selected 101
was customer code. 102
Then we have the orientation property which is set to Excel row fields and the position. 103
Next we added quantity to the value field. 104
This time it used the add data field method and its argument is xlSum. 105
Remember we added quantity 2 times and the default name we got the second time had a 2 in the end. 106
But it kept the xlSum and then we changed it, we changed the caption property to percentage quantity 107
and we also changed the calculation property to "xlPercent of Total" 108
And it also gave us a number format here. 109
Now that we have any idea about the pivot cache and the pivot table let's do some research inside 110
the object library. 111
I'm just going to type in Pivot Tables and let's take a look at the classes that come up. 112
We have a pivot tables collection. 113
We can take a look at each single pivot table by referencing either their index numbers or their names. 114
We can add pivot tables and we can count pivot tables. For each single pivot table 115
we have this huge list of properties. 116
Notice that DataBodyRange is a property. 117
If we wanted to reference this part of the pivot table we can use DataBodyRange. 118
Let's go and look at some of the methods, AddDataFields. 119
That was when we added quantity we use the AddDataFields method. 120
We also have PivotFields. 121
We can RefreshTable and so on. 122
If you're going to be working with Pivot Tables, I would suggest that you spend some time and go 123
through the different properties and methods that are available inside the object library and you can 124
see all the classes listed here. 125
For example, pivot fields is a collection. You can again reference them by item or by name. You can count 126
the number of pivot fields and you can take a look at each single pivot field. 127
We have a Calculation property. 128
Down here we have the Position property. That was what we saw the macro recorder record. 129
Now let's switch to the code window. 130
Click on F7 and add our own sub procedure to format this range here. 131
I'm going to go with sub and call it "Pivot_Formatting". 132
And let's switch to single procedure view. When you need to reference your pivot table a lot it makes 133
sense to create an object variable out of this. I'm going to "Dim PT As PivotTable". 134
Next, we're going to set it, so "Set PT = ActiveSheet" 135
In this case it is the active sheet 136
If your pivot table is not on the active sheet you can reference it using the code name of the sheet 137
or the name of the worksheet. ".PivotTables" 138
I'm going to reference it with its name. 139
It's called "PivotTable1". 140
Next, let's change the number formatting for this pivot field. 141
So "PT.PivotFields" 142
The name of the pivot field is "Sum of quantity". 143
You just have to make sure that you spell it properly. 144
".NumberFormat = ..." 145
I want to set this to have a thousand separator. 146
You just have to use the custom formatting references that you're probably used to on the Excel side. If 147
you right mouse click and go to number format and go to custom. 148
You can see we have special symbols that we can use as placeholder for numbers which is the hash tag 149
and the zero. 150
I'm just going to copy this one, click on cancel 151
Go back to code view, put in the quotation marks, and paste this in. 152
First argument is always how positive numbers can be formatted. 153
Second argument which is split with a semicolon is how you want negative numbers to be formatted. 154
I want my negative numbers to be inside brackets. 155
This should now format this pivot field right here. 156
Let's just play this. 157
Here we get that right. 158
Let's also do a reference to the DataBodyRange. 159
Let's say we just want color the background of the DataBodyRange to a gray color. 160
First let's reference the pivot table. 161
Then I'm going to use the "DataBodyRange.Interior.Color =". 162
Let's go with "VBA.RGB" 163
and I'm going to use 226 three times. 164
Let's just run this and I get a gray background here. 165
What about refreshing pivot tables. 166
Let me just create a separate sub procedure here call it "refresh pivot". 167
This part of my sub procedure is the same. 168
I'm just going to copy and paste it. If you want to refresh specific pivot tables by name 169
all you have to do now is say "PT.PivotCache.Refresh". Because you're actually refreshing 170
its pivot cache. 171
Even if you have different pivot tables in there but they all use the same pivot cache. 172
All you have to do is to reference one of them and refresh that pivot tables' pivot cache. Your other 173
pivot tables will refresh automatically. If you wanted to refresh all pivot tables in your workbook 174
you just need to do a refresh all for this workbook. "ThisWorkbook.RefreshAll" and that refreshes all 175
your pivot table connections. 176
How can you check how many pivot caches you have and how can you change a pivot cache of an existing 177
pivot table? 178
Let's do a query here 179
in the immediate window. If you don't have the immediate window activated go to View and click on immediate 180
window. 181
What I want to do is to ask how many pivot caches I currently have running. 182
I'm going to go with "ThisWorkbook.PivorCache.Count" I have currently one pivot cache. 183
What if I copy and paste this table here and just make some adjustments? I'm going to press Ctrl+C 184
on this and Ctrl+V here. 185
I'm going to change this a little bit I'm going to get rid of the quantity percentage and I'm also 186
going to bring in the article code and get rid of the customer. 187
I have two different pivot tables, one pivot table shows me the quantity by article and the other 188
one the quantity by customer code. 189
Now let's check how many pivot caches we have. 190
How many do you think we have? Still 1. 191
Because they are using the same pivot cache. 192
This is a great feature in Excel because it helps you improve the performance of your workbooks. 193
The more you duplicate pivot caches the slower your workbook can get. 194
What if we wanted to change the pivot cache of this pivot table to another table? 195
In tab table 2, I've added a different table that has a similar set up. 196
This table is called CompNew and I want to write a procedure that's going to change the data source 197
of this second pivot table which is now called PivotTable2 to the new table inside table 2 tab. 198
Down here let's create a new sub procedure and call it "Change pivot cache". 199
Whenever you're changing the data source of a pivot table you're actually changing its pivot cache. 200
And if you don't happen to have another pivot table using that pivot cache you actually need to create 201
this pivot cache. 202
I'm going to 203
copy and paste this right here. 204
The pivot table for which I want to change the pivot cache is PivotTable2. 205
I'm just going to change the referencing here 206
and let's switch to single procedure view. 207
Let's go through the hierarchy. "PT.ChangePivotCache" the argument for it is the pivot cache 208
that we want. We want to create a new pivot cache. 209
I'm going to go with "ThisWorkbook.PivotCaches.Create" What is my source type? 210
It's a database, xlDatabase. 211
Second argument, what's my source data? 212
My source data is called "CompNew" and I need the quotation marks in there. 213
Let's just run this. Something looks like it changed. 214
Let's just check in the immediate window. 215
How many pivot caches we now have? 216
What do you think? 2. Because we just created a new pivot cache. 217
And if I click on this table, go to change source data and take a look at my new source data 218
it's set to "CompNew". 219
What if I wanted to change the pivot cache back so that this pivot table can have the same pivot 220
cache as an existing pivot table? Which is my PivotTable1. 221
Let's do that quickly "Sub Change_back_PivotCache". 222
I'm just going to do this in one step. I'll go with "ActiveSheet.PivotTables("PivotTable2").ChangePivotCache" 223
. 224
And now because we already have a pivot cache for PivotTable1, all we have to do is to reference 225
PivotTable1. And we're going to make the pivot cache, 226
so basically the data source of PivotTable2 identical to PivotTable1. 227
Let's run this quickly. 228
We see that something changed in the background. 229
Let's quickly count the number of pivot caches. 230
How many? 1. 231
Right. 232
Because we made this the same as this pivot table. 233
And if you just double check here, inside change source data. 234
We see that we're back to our original comp table right here. 235
This is the basics of working with Pivot Tables. 236
We're going to work a lot with it in the next section.