Pivot Table for Accounts Payable/Receivable and Inventory

Last updated on March 19th, 2021 at 06:19 am

switch language to bahasa

The topic in this page is the continuation from the “Accounts Payable and Receivable in Excel report” 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.

1. Make a new worksheet

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”.

Create a new worksheet for the Accounts Payable/Receivable and Inventory Pivot Table
Naming the Excel Worksheet
(please ignore the name)

2. Create the Pivot Table

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.

Excel Menu to create Pivot Table
(please ignore the sheet name)

It will open the Create PivotTable window as seen like the image below.

Create PivotTable window

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, just leave it as it is, then click OK.

Blank Pivot Table and PivotTable Field List window

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.

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.

Type “ptCUSU” to name the Pivot Table

3. Arrange the column fields

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.

Setting Pivot Table column fields
(please ignore the name)

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.

4. Set the value field

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

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.

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.

Formatting the value field.

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.

Set the Y/M row field
(please ignore the name in the image)

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”).

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.

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.

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.

381 replies on “Pivot Table for Accounts Payable/Receivable and Inventory”

Hey There. I discovered your blog the usage of msn.This is a very neatly written article. I will make sure to bookmark it and return to readmore of your helpful information. Thank you for the post.I will certainly comeback.

Hi there just wanted to give you a quick heads up and let you know afew of the images aren’t loading properly. I’m not sure why but I thinkits a linking issue. I’ve tried it in two different browsers and both showthe same results.

I am not sure where you’re getting your information, but great topic.I needs to spend some time learning much more or understanding more.Thanks for excellent info I was looking for this info for my mission.

I’m really impressed with your writing talents and also with the format on your blog.Is this a paid theme or did you customize it your self?Anyway stay up the excellent high quality writing, it is uncommon to seea great blog like this one nowadays..

OBJECTIVE: To assess Danish obstetricians’ and gynecologists’ personal preference and general attitude towards elective cesarean section on maternal request in uncomplicated single cephalic pregnancies at term.link source

A fascinating discussion is worth comment. There’s no doubt that that you should write more on this subject matter, it may not be a taboo subject but generally folks don’t discuss such issues. To the next! All the best!!

The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my option to read, but I truly thought youd have one thing interesting to say. All I hear is a bunch of whining about something that you possibly can fix when you werent too busy searching for attention.

Can I simply say what a reduction to seek out somebody who actually is aware of what theyre speaking about on the internet. You undoubtedly know the best way to convey a difficulty to light and make it important. Extra people need to read this and perceive this aspect of the story. I cant consider youre not more widespread because you positively have the gift.

I’m really enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a developer to create your theme? Superb work!

An attention-grabbing dialogue is price comment. I believe that you should write extra on this matter, it won’t be a taboo topic but typically individuals are not sufficient to speak on such topics. To the next. Cheers

of course like your web site however you need to check the spelling on several of your posts. Several of them are rife with spelling issues and I in finding it very bothersome to tell the truth then again I’ll definitely come again again.

Simply desire to say your article is as astonishing. The clarity in your post is simply excellent and i could assume you’re an expert on this subject. Well with your permission let me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please keep up the gratifying work.

I just couldn’t go away your website before suggesting that I extremely loved the standard information a person supply to your guests? Is gonna be again incessantly in order to check up on new posts

Coi Trực Tiếp Viettel Vs Bình Dương Trên V League 2021 Ở Kênh Nào? tcvn 104-07Đội tuyển chọn nước Việt Nam chỉ cần một kết trái hòa có bàn thắng nhằm lần thứ hai góp mặt tại World Cup futsal. Nhưng, để thực hiện được điều đó

Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

I am really impressed with your writing skills andalso with the layout on your blog. Is thisa paid theme or did you modify it yourself? Eitherway keep up the excellent quality writing, it’s rare to see a nice blog like this one today.

Hi! I just wanted to ask if you ever have any issues with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no back up. Do you have any methods to prevent hackers?

Aw, this was a very nice post. Taking a few minutes and actual effort to produce a really good articleÖ but what can I sayÖ I put things off a whole lot and never manage to get nearly anything done.

I am not sure where you’re getting your info, but great topic. I needs to spend some time learning much more or understanding more. Thanks for magnificent information I was looking for this information for my mission.

Hey there! This is my first visit to your blog! We are a group of volunteersand starting a new project in a community in the same niche.Your blog provided us beneficial information towork on. You have done a outstanding job!

I like the valuable information you supply for your articles.
I will bookmark your blog and check again here frequently.
I am reasonably sure I’ll be informed lots of new stuff
proper right here! Best of luck for the following!

Hello there! This post couldn’t be written much better! Reading through this post reminds me of my previous roommate! He constantly kept talking about this. I am going to send this post to him. Fairly certain he’ll have a very good read. I appreciate you for sharing!

Its like you learn my mind! You seem to grasp a lot about this, such as you wrote the guide in it or something. I think that you simply could do with a few percent to power the message home a little bit, but other than that, this is excellent blog. An excellent read. I will certainly be back.

Nhà Cửa Dòng Xem Thẳng Soccer Bdtt Tv xem bong da trực tiepTrong vượt khứ, Bình Dương đang được giật ưu thế về đo đếm cùng với 15 thắng lợi, 8 trận hoà và chỉ thua SLNA 12 lần.

I blog frequently and I genuinely thank you for your content.This article has really peaked my interest. I will bookmark your blog and keep checking for new information aboutonce per week. I opted in for your Feed as well.

Have you ever considered publishing an ebook or guest authoring on other sites? I have a blog based on the same topics you discuss and would really like to have you share some stories/information. I know my audience would value your work. If you are even remotely interested, feel free to shoot me an e mail.

You can certainly see your skills within the article you write.The arena hopes for more passionate writers such as youwho are not afraid to say how they believe. At alltimes follow your heart.

I’m curious to find out what blog platform you’re working with?I’m experiencing some small security issues with my latest blogand I’d like to find something more risk-free. Do youhave any recommendations?

Hello! This is my first visit to your blog! We are a team of volunteersand starting a new project in a community in the same niche.Your blog provided us valuable information to work on. You have done amarvellous job!

เวฟนี้ดีจริงไม่ผ่านเอเย่นต์ ทุนหนา จ่ายไวไม่อยากให้เชื่อ แต่อยากให้ลอง

Leave a Reply

Your email address will not be published. Required fields are marked *