Data for a more complex pivot table

Figure 17-3 shows part of a large worksheet table. This table contains 15,840 rows containing hierarchical budget data for a corporation. There are five divisions, and each division contains 11 departments. Each department has four budget categories, and each budget category contains several budget items. Budgeted and actual amounts are included for each of the 12 months. The goal is to summarize this information with a pivot table.

g kW* jurpi EJ «frpflj j tt wfu* Mipnm«a{ cfr-tpM^iMn Ulm


S h iSi-Tcv-(u C>--:J CL ~ifK lulim Î:rmii«ri ta h iir-ij Curtj Prhwj'i Csnpnulim rijiril Fjiri

7 HLtertriop ruf* a Ptni*!B"; u'nfif imlkn Tr*Jplnç

S h ,i -wirj Cuiii Ç^-ifn-mlton_; ;r.ltrtr-ri-i nsi +1« iwa


L: K in, -a j Lu"_i PipupiLa-f —_-: ■ I■ Iy

:i h CuMPTOCMa's jtapBfetiASwnftciH Cffa*

3T h ß &*11 ftjMMK P■ 1 i*4»«üHt*Ppmt iïfiï .1*'

ai hJwriu CMihuiriirt juptfinttiiiim IwtLlilr,

ïi h J-M.-HJ IMd Viunv-f 'J'-Vv" ".ri 'itl Vlli

.M h Mim ùu PMHife'f lî h toirlu Duftj Î*jLt ?rr»rL

N IrTic^H j DmiJ »Î^I ?»r-rnl n h. A.'wir s fuiii PtwKa-; f jilprrtwrt

Ji lilijriïi Ct^lH^Btkw_

ItVi lira

IMi Mil w

Figure 17-3: The data in this workbook will be summarized in a pivot table.


This workbook is available on the companion CD-ROM. The file is named % budget pivot table.xlsm.

Figure 17-4 shows a pivot table created from the data. Notice that the pivot table contains a calculated field named Variance. This field is the difference between the Budget amount and the Actual amount.

Figure 17-4: A pivot table created from the budget data.
0 0

Post a comment