PivotTable operation primer - Valid data area pg 233 234

31. PivotTable Operation Primer

A Beginner's Guide On PivotTable Operations

The PivotTable function is accessible from the menu Data/PivotTable and PivotChart report. This function can be used to generate summaries of our database.

Valid Data Areas

Before we are able to use it, we must have a proper data area (or database area). A data area is a rectangular area of rows and columns. The topmost row must contain titles in columns. Data must start immediately below the row of titles. Row of titles can start at any row. The titles in the row must be adjacent to each other (not be separated by any blank cell). The last row in the data area is not fixed. It is the last cell in any column in which all the cells above it contain data without any blank cell in between. If a row contains all blank cells, the last row of the data area is the first row above it.

Example 1:

Here is a sample of a valid data area:

	A	B	C	D	E	F	G	H
1
2	X	X	X	X	X	X	X
3	X
4	X
5	X
6	X
7

In the above, the data area is A2:G6 (X represent data)

233

Example 2:

In the following example, there are two data areas: A2:C6 and E2:G5 (X represent data). A blank column (column D) acts as the separator between the two different data areas.

	A	B	C	D	E	F	G	H
1
2	X	X	X		x	x	x
3		X				x
4		X				x
5		X				x
6		X	
7

Example 3:

In the following sample, there are two data areas i.e. A2:F4 and D6:D8. A blank row (row 5) acts as the separator:

	A	B	C	D	E	F	G	H
1
2	X	X	X	X	X	X
3				X
4				X
5
6				X
7				X
8				X
9