{"id":1426,"date":"2021-03-16T17:44:36","date_gmt":"2021-03-16T09:44:36","guid":{"rendered":"https:\/\/indonesiangems.com\/blog\/?p=1426"},"modified":"2021-03-19T06:19:14","modified_gmt":"2021-03-18T22:19:14","slug":"accounts-payable-receivable-inventory-pivot-table","status":"publish","type":"post","link":"https:\/\/indonesiangems.com\/blog\/accounts-payable-receivable-inventory-pivot-table\/","title":{"rendered":"Pivot Table for Accounts Payable\/Receivable and Inventory"},"content":{"rendered":"

Last updated on March 19th, 2021 at 06:19 am<\/p>\n\n\n\n\n

\n
switch language to bahasa<\/a><\/div>\n<\/div>\n\n\n\n

The topic in this page is the continuation from the “Accounts Payable and Receivable in Excel report<\/a>” page. So, after Mr. Adi has made the customer, supplier and product opening balance, now he will make the Pivot Table for Accounts Payable\/Receivable and Inventory on other Excel worksheet.<\/p>\n\n\n\n

1. Make a new worksheet<\/em><\/h4>\n\n\n\n

Double click “Sheet3” tab, then rename it to “CUSU” (meaning CUstomerSUpplier). Double click the sheet icon (create a new worksheet), then rename it to “STOCK”.<\/p>\n\n\n\n

\"Create
Naming the Excel Worksheet<\/em>
(please ignore the name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

2. Create the Pivot Table<\/em><\/h4>\n\n\n\n

Make sure that cell A1 in worksheet CUSU is active. Then within the Insert menu tab, click PivotTable then click the PivotTable. As seen in the image below.<\/p>\n\n\n\n

\"\"
Excel Menu to create Pivot Table<\/em>
(please ignore the sheet name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

It will open the Create PivotTable window as seen like the image below.<\/p>\n\n\n\n

\"\"
Create PivotTable window<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

Type data for “Table\/Range:”. If my reader is following this Pivot Table creation using the sample file provided in part-1, in the Create PivotTable window, the “Location:” will be CUSU!$A$1<\/span>, just leave it as it is, then click OK.<\/p>\n\n\n\n

\"\"
Blank Pivot Table and PivotTable Field List window<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

Because Mr. Adi won’t use a +\/- toggle button in the Pivot Table, so in the Options menu tab of the Excel Application, in the Show category, he turn off the “+\/- Buttons” menu icon. As seen in the upper right of the image above.<\/p>\n\n\n\n

Name the Pivot Table by right click within the blank area of the Pivot Table, choose PivotTable Options, then type ptCUSU for the “Name:” in the PivotTable Options window, then click OK. As seen in the gif animation below.<\/p>\n\n\n\n

\"\"<\/a>
Type “ptCUSU” to name the Pivot Table<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

3. Arrange the column fields<\/em><\/h4>\n\n\n\n

In the PivotTable Field List window, right-click NAME which is in the “Choose fields to add to report:” window, then drag it to the Row Labels area. Do the same for INV, put it under the NAME in the Row Labels area. And do the same for Y\/M, but put it between the NAME and INV in the Row Labels area. As seen in the gif animation below.<\/p>\n\n\n\n

\"\"<\/a>
Setting Pivot Table column fields
(please ignore the name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

Next, drag POST and drop it in the Report Filter area. Also drag SUM then drop it in the Values area. As seen in the image below.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

4. Set the value field<\/em><\/h4>\n\n\n\n

Click the SUM value field (Count of …), then choose the “Value Field Settings…” as seen in the image below.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

In the Value Field Settings window, click “Sum”. The “Custom Name:” will automatically change to “Sum of SUM”. So here the Pivot Table will summarize the value field by summing the value instead of counting the value.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

Click the Number Format button. It all depends on how we want the Pivot Table display the format of the value. Anyway in this case, Mr. Adi choose “Number” for the “Category:”, put zero for the “Decimal places:”, select the “Use 1000 Separator”, and choose a red color for the negative number. Click OK.<\/p>\n\n\n\n

\"\"
Formatting the value field.<\/figcaption><\/figure><\/div>\n\n\n\n

Next, Mr. Adi sets the Y\/M field by clicking the Y\/M field then he choose “Field Settings” option. As seen in the image below.<\/p>\n\n\n\n

\"\"<\/a>
Set the Y\/M row field
(please ignore the name in the image)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n

In this case, pak Adi want the Pivot Table to show the subtotals of the Y\/M field. So in the Field Settings window, he choose “Automatic” for the “Subtotals”. As seen in the image below. (Please make sure that the NAME field setting is also “Automatic” for the “Subtotals”).<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

After clicking the Number Format button, in the Format Cells window, he choose “Custom” for the “Category”, type mmm yy under the “Type:”, then click OK.<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

That’s all what he need for the field setting. The Pivot Table will look something like the image below. It is in a compact report layout.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Actually the creation of the Pivot Table here can be considered already done. Mr. Adi can just copy this Pivot Table then paste it to the STOCK sheet to complete his Pivot Table for Accounts Payable\/Receivable and Inventory creation. Yet, he still wants to make some styling for his ptCUSU Pivot Table.<\/p>\n\n\n\n\n\n\n\n

5. Design the Pivot Table<\/em><\/h4>\n\n\n\n

Because Mr. Adi doesn’t like the Compact Form report layout, so he change it into a Tabular Form report layout by clicking the Report Layout menu icon, then choose “Show in Tabular Form”. As seen in the image below.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

Below is the image of the Pivot Table in a Tabular Form report layout.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Within the Design menu tab, the PivotTable Style Options is optional, it depends on how is the look of the PivotTable we want. Anyway in this case, Mr. Adi only check the “Column Headers” and leave the others unchecked. As seen in the image below.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The coloring of the Pivot Table is also optional. Excel already provided quite many styles template which can be accessed by clicking the down-arrow button as seen in the image below.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Again, it all depends on how we want to see the visual style of the Pivot Table. In this case, Mr. Adi choose Pivot Style Medium 5, as seen in the image below.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

In order the Pivot Table header still visible when he scroll down the page, Mr. Adi freeze the first four rows at the top. Here are the steps : while the View menu tab active, click cell A4, choose Freeze Panes, then choose the Freeze Panes. As seen in the image below.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

From the image below, we can see the Pivot Table’s header stay on top, although the page is already scrolled down to row 94.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Based from the image above, the Pivot Table is like this :
In the row 1, the POST field filter is “(All)”. Meaning the Pivot Table reports both the accounts payable and the accounts receivable name. To see just the accounts payable report, filter the POST field to CU by clicking the down-arrow of the POST field, then select the CU item there. The same, to see just the accounts receivable report, filter the POST field to SU.<\/p>\n\n\n\n

So in reading the example image of the Pivot Table report above:
Adelia Priatni is Mr. Adi’s customer who owe him a total amount of money 11.390.900. It’s from her February 2020 purchase (consist with two invoices), where the subtotal for Feb 2020 is 3.583.700 and it’s from her March 2020 purchase (consist with six invoices), where the subtotal for Mar 2020 is 7.807.200.<\/em><\/p>\n\n\n\n

Create the Pivot Table for Inventory<\/em><\/h4>\n\n\n\n

Finally, to complete his Pivot Table for Accounts Payable\/Receivable and Inventory creation, now it’s time to make the Pivot Table for his Inventory. Just copy the ptCUSU Pivot Table in CUSU sheet then paste it into cell A1 of sheet STOCK. Here are the process.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Within the CUSU sheet and the Options menu tab active, click Actions, click Select, then choose “Entire PivotTable”. As seen in the image above. Ctrl+c (copy keyboard shortcut) on the keyboard, then activate the STOCK sheet. Make sure cell A1 is active in STOCK sheet, then ctrl+v (paste keyboard shortcut).Next, Mr. Adi name the Pivot Table as ptStock. And then he arrange the column fields as follow : <\/p>\n\n\n\n