Pivot Table Operation Primer

PIVOTTABLE OPERATION PRIMER PivotTable function is accessible from menu 'Data/PivotTable and PivotChart report'. This function can be used to generate summaries for our database. Before we 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 which all cells above it contain data without any blank cell in between. If a row contain all cells blank, the last row of the data area is the first row above it. Only one column is required to have non-blank cell to the last row. 4 X 5 X 2 X X X X X X 3 X X 4 X X 5 X X 6 X 7 In the above, there are 2 data areas : A2:C6 and E2:F5 (X represent data). A blank column (column D) acts as the separator. In the following sample, there are 2 data areas ie A2:F4 and D6:D8. A blank row (row 5) acts as the separator : A B C D E F G PivotTable is used to summarised data in the data area. It can find totals (values or quantities) of an unique item in data area and combinations of more than one unique item in the data area. If you want to try the following, make sure you have a data area ready. For working sample, construct a data area with following details: 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. Before we click on the menu 'Data/ PivotTable', we must activate a cell in the data area. This would make Excel recognise the data area (behind the screen). After we click the menu, a dialog box with title 'PivotTable and PivotChart wizard - Step 1 of 3' appear. 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). Click 'Next'. A dialog box with title 'PivotTable and PivotChart wizard - Step 2 of 3' will appear. 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 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). Click 'Next'. A dialog box with title 'PivotTable and PivotChart wizard - Step 3 of 3' appear. Click 'layout'. If Microsoft Excel cannot identify a proper data area, the following message will appear : Otherwise, a dialog with title 'PivotTable and PivotChart Wizard - Layout' will appear. The top portion (above the grey line) contain simple instruction only. On the right side is buttons representing titles in title row in the data area. You can drag any one button at a time to PAGE box, COLUMN box or ROW box or DATA box on its left and release it there. If you drag and release the button on the PAGE box, each unique items available under the title in the data area will appear as the main selection in the top portion in the PivotTable report that will be generated. (You can always drag button out of the PAGE box to revise your selection). If you drag and release the button on the COLUMN box, each unique items available under the title in the data area will appear in the top rows (in columns) in the PivotTable report that will be generated. (You can always drag button out of the COLUMN box to revise your selection). If you drag and release the button on the ROW box, each unique items available under the title in the data area will appear in the left-most columns (in rows) in the PivotTable report that will be generated. (You can always drag button out of the ROW box to revise your selection). If you drag and release the button on the DATA box, each total count of items available under the title in the data area which appear under both COLUMN box and ROW box will appear as the count or value in the PivotTable report that will be generated. (You can always drag button out of the DATA box to revise your selection). (Normally we drag button which represent quantity or value into the DATA box, not button which represent text). (This is because DATA box is where totals of count or value will appear). It is not necessary to put button into PAGE box. You can leave this box without a button. At least, you must have ROW box AND DATA box or COLUMN box AND DATA box filled with button or both. Some buttons may not be useful for the PivotTable function, so we never drag them into the boxes. You can put more than one buttons in each box. It is best to experiment to see the result. Only after you do few experiments (say 10) you will be able to predict what form of report will be produced by the PivotTable functions. After you put necessary buttons into the boxes, click OK. You will get back to the dialog box with title 'PivotTable and PivotChart Wizard - Step 3 of 3'. Click 'Options'. You will get a dialog box with title 'PivotTable Options'. In the box on the right of 'Name' label, you can give a name for the PivotTable report that will be generated. The rest are defaults. You can ignore it if you work the first time. Click OK. You will get back to the dialog box with title 'PivotTable and PivotChart Wizard - Step 3 of 3'. Click 'Finish'. A new sheet with the report in it will be presented to you. Below is a sample result : In the above sample, 'ITEM SOLD' is a title in the underlying data area. What we see below it (Model A, Model B and Model C) are the unique items available in data under the title 'ITEM SOLD' in the underlying data area. It appears on the left side of the report because we drag the ITEM button into the ROW box (left side) in the layout dialog in step 3 of PivotTable wizard. If we click on the little arrow-head on the right of 'ITEM SOLD', a list will appear together with OK button and CANCEL button. The list contain all unique items available in the data area under column 'ITEM SOLD'. Automatically all unique items will appear in the report. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the list. Click OK to make the computer accept our selection and dismiss the list. The report will get updated with the selection. Click 'cancel' to dismiss the list without effecting any changes to the report. We can change the actual spelling of the unique items in the report without affecting the actual spelling in the underlying data. In the above sample, 'MTH' is also a title in the underlying data area. What we see in a row immediately below it (1, 2, 3, 4) are the unique items available in data under the title 'MTH' in the underlying data area. It appear on the top side of the report because we drag the MTH button into the COLUMN box (top side) in the layout dialog in step 3 of PivotTable wizard. If we click on the little arrow-head on the right of 'MTH', a list will appear together with OK button and CANCEL button. The list contain all unique items available in the data area under column 'MTH'. Automatically all unique items will appear in the report. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the list. Click OK to make the computer accept our selection and dismiss the list. The report will get updated with the selection. Click 'cancel' to dismiss the list without effecting any changes to the report. We can change the actual spelling of the unique items in the report without affecting the actual spelling in the underlying data. In the above sample, the word 'Data' on the right of 'ITEM SOLD' refers to the total or sum of numbers (quantity or values). Actually it is a bit misleading. The better word may be is 'Total' or 'Sum' or 'Count'. The repeated items below it are the items that is get summed or totalled in the report. Sum of QTY' means that it is values under title QTY in the underlying data that get summed. Sum of RM-CREDIT' means that it is values under title 'RM-CREDIT' in the underlying data that get summed. Sum of QTY' appear on the right side of ITEM SOLD but below MTH because we drag QTY button to the DATA in the layout dialog in step 3 of PivotTable wizard. Sum of RM-CREDIT' appear on the right side of ITEM SOLD because we drag RM-CREDIT button to the DATA box but below ITEM SOLD button in the layout dialog in step 3 of PivotTable wizard. It appear below 'Sum of QTY' in the report because we drag RM-CREDIT button to below QTY button in the DATA box in the layout dialog. We can change the spelling of 'Sum of QTY' and 'Sum of 'RM-CREDIT' (in this case) without affecting the underlying data. If we click on the little arrow-head on the right of 'Data', a list will appear together with OK button and CANCEL button. The list contain all unique items which we drag into the DATA box. We can select which unique item can appear or not appear in the report by marking or unmarking the box at the left of the unique item in the list. Click OK to make the computer accept our selection and dismiss the list. The report will get updated with the selection. Click 'cancel' to dismiss the list without effecting any changes to the report. If we select only one item from the list, the 'Data' portion of the report will disappear (only values will remain). We can change the actual spelling of the unique items in the report without affecting the actual spelling in the underlying data. If we drag only one button into the DATA box in the layout dialog, the 'Data' portion of the report will disappear (only values will remain). Sometimes, when we drag a button into DATA box, the default in the result is a count of item instead of a sum of the item. If we want to find the sum (which usually is) : Right-click on the report. A floating menu will appear. Select 'Wizard'. We will get the step 3 of the PivotTable wizard. Click 'layout'. We will get the 'Layout' dialog box. Look at the buttons inside the DATA box. If you find the word 'Count of' on a button and you want to change to 'Sum of' :. Double-click on the relevant button. A list will appear. Under 'Summarize by', select 'Sum' from the list. Click 'OK'. We will come back to the 'Layout' dialog box. Now notice the 'word 'Count' is replaced with 'Sum'. Click 'OK'. We will come back to the step 3 of the PivotTable wizard. Click 'Finish'. The report will get updated and the figure in the report is now a sum instead of a count. You can format the report and the content of the report in the normal way you use formatting in Excel. Eg, you can format numbers, font etc. You can change and edit the content (numbers and words) in the report the normal way we do in Excel. The report does not contain any formulas. You can delete the sheet and the the Report to save disk space, if you want. You can always rebuild it again. If you make any changes in the underlying data, you may want to update the report (if you do not delete the report). Just click any cell in the report, then right-click once. A floating menu will appear. Select 'Refresh'. The report will get updated. If you want to create another report, you may get this dialog : Please read the message in the dialog. This message dialog appear after you click 'Next' on the step 2 dialog in the PivotTable wizard. You will not get this message dialog when you build a first report. This message simply means that Excel has detected that you are working on the same data area which a report has already exist. You are given an option whether you want to use the current report as a basis for your new report. If you use the current report, it means Excel will use the same underlying data in the new report. This will save memory and will avoid Excel from getting slower and also safe your disk space and keep your Excel file smaller. This is the best choice (selecting 'Yes') in the dialog. Please read the message in the dialog. This message dialog appear after you click 'Next' on the step 2 dialog in the PivotTable wizard. You will not get this message dialog when you build a first report. This message simply means that Excel has detected that you are working on the same data area which a report has already exist. You are given an option whether you want to use the current report as a basis for your new report. If you use the current report, it means Excel will use the same underlying data in the new report. This will save memory and will avoid Excel from getting slower and also safe your disk space and keep your Excel file smaller. This is the best choice (selecting 'Yes') in the dialog. We can use a shortcut way to build a report. Click any cell in the data area from which you want it to be a base for your report. Click menu 'Data/ PivotTable and PivotChart Report'. A dialog with title 'PivotTable and PivotChart Wizard - Step 1 of 3' appears. Click 'Finish'. You will be presented with a new sheet. In the new sheet you will have a few boxes. You will see boxes with the following instructions : ‘Drop Page Fields here', 'Drop Row Fields here'. 'Drop Column Fields here'. 'Drop Data Items Here'. You also see dialog box containing buttons representing the column titles on the underlying data area. You can drag one button at a time into a box to form a scheme of your report (the same way you do in the Layout dialog). The box with 'Drop Data Items Here' preferably be the last box which you drag a button into. As soon as you drag a button into the 'Drag Data Items Here' box, the port will be ready for you.