PivotTable Generating Report using PivotTable operation 1 to 7 pg 235 to 237

Generating PivotTable Report

PivotTable is used to summarize data in the data area. It can find totals (values or quantities) of a unique item in the data area and combinations of more than one unique item in the data area.

284

NOTE

You can construct a data area with following details: If you want to try the following steps, make sure you have a data area ready.

Fill adjacent cells in a row with titles: Date, Mth, Account-Debit, RM-Debit, Account-Credit, RM-Credit, Item Purchased, Qty purchased, Item sold, Qty sold. Fill data in rows below the titles.

1. We must first activate a cell in the data area. This would let Excel recognise the data area (behind the screen).

2. Then click the menu Data/PivotTable and PivotChart report. The PivotTable and PivotChart wizard Step 1 of 3 dialog box will appear.

PivotTable and PivotChart Wizard - Step 1 of 3		? x

Where is the data that you want to analyze?
	Microsoft Excel list or database
	External data source
	Multiple consolidation ranges
	Another PivotTable or PivotChart

What kind of report do you want to create?
	Pivot Table
	PivotChart (with PivotTable)

?	Cancel		Back		Next		Finish

3. Under Where is the data that you want to analyze, select Microsoft Excel list or database (usually, this is automatically selected). Under What kind of report do you want to create, select PivotTable (usually this is automatically selected).

235

4. Click Next. The PivotTable and PivotChart wizard appear. Step 2 of 3 dialog box will

PivotTable and PivotChart Wizard - Step 2 of 3

	Where is the data that you want to use?

	Range:	$A$4:$AC$30000			Browse...

? Cancel	Back		Next		Finish

5. Under Where is the data that you want to use?, enter the address of the data area. Usually, this is automatically selected. It is good idea to check the range identified by Excel. If the range address is not as you expect, cancel the operation, correct the data area and start all over again. (If you follow the guidelines on the data area, it should be okay).

6. Click Next. The PivotTable and PivotChart wizard appear. Step 3 of 3 dialog box will

PivotTable and PivotChart Wizard - Step 3 of 3

	Where do you want to put the PivotTable?
		New worksheet
		Existing worksheet

	Click Finish to create your Pivot Table.

? 	Layout...	Options...	Cancel	Back	Next	Finish

236 7. Click Layout. If Microsoft Excel cannot identify a proper data area, the following message will appear:

Microsoft Excel

The PivotTable field name is not valid. To create a PivotTable, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

			OK

Otherwise, the PivotTable and PivotChart Wizard appears. Layout dialog box appears.

PivotTable and PivotChart Wizard - Layout			?x

Construct your PivotTable by dragging the field buttons on the right to the diagram on the left.

	PAGE	COLUMN			SRC			ACCOUNT		BANK REC
							DATE		RM-DEBIT	DEBTORS
							MTH			ACCOUNT		DEBT AGE
		ROW      DATA  		SRC NO.		RM-CRED		DEBT AGE
                            OTHER RE	CHECK A		CREDITOR
                            NOTE		CHECK AC	CREDIT A


						Help	OK		Cancel