QuickBooks Desktop 2023 accounts payable graphs, exporting data from QuickBooks to excel creating the graphs in Excel. Let’s do it with Intuit QuickBooks Desktop 2023.
00:16
Here we are in QuickBooks Desktop sample Rockcastle construction practice file provided by QuickBooks going through the setup process we do every time maximizing the home page to the gray area, go into the view drop down, noting that we got the highest Icon Bar and open windows list checked off open windows open on the left hand side,
00:36
go into the reports drop down company and financial opening up the profit and loss range into the change in from a 101 to four to 1231 to four January to December, that is 2024. Customize it and then go to the fonts and numbers change it bring it on up to 12. Okay, yeah. Okay.
00:58
And then go into the reports drop down again, company and financial this time the big balance sheet, hitting the drop down, we’re going to the fiscal year 1231 to four to customize fonts and numbers changing the font to 12.
01:12
And okay, and saying yes, and okay. That’s what we do every time opening up the major to financial statement reports all other reports pretty much given us more information expanding upon one or multiple line items in the major financial statement reports we have open now.
01:29
Now we’re looking at the graphs that are related to the accounts payable, given much more detail on the balance sheet account of the accounts payable,
01:38
noting remembering Accounts Payable represents us owing vendors for goods and services provided to us. And we can see the sub report have that broken out by vendor by going to the reports drop down up top company,
01:54
and I’m not company vendor and payable, vendor and payable, I should say, because that’s the proper thing. That’s what we’re doing. And then we’re going to go to the vendor Balance Summary right here, boom.
02:05
And there’s the 26 636 92, which should tie out to the big balance sheet. 26 632. That looks good. Back to the vendor Balance Summary,
02:15
let’s make it a little bit larger on the font just so you can see it more clearly, more clearly, because it’s bigger, and your if your eyeballs are not as good like mine, then you can see it better.
02:28
So there it is. Now we want to make a graph of that. So we have a graph in Excel we looked at, or in QuickBooks we looked at last time,
02:35
we could find it by going to the reports drop down vendor payable. And we can take a look at the graph the graphical accounts payable, graphical. And there it is, it’s nice, it’s colorful, but it’s pretty static, we can’t do much more with it.
02:51
And I would like to do much more with it having different colors possibly breaking out in a different format over here on the vendor side of things as well.
03:01
So it would be easier than I would practice then taking the data where it comes from exporting it to Excel. And that’s a good practice to kind of see how that graph is made,
03:11
how we can apply it to other graphs, and then some Excel tools to simply make it in Excel. And this is a nice, easy report to make a pie chart from so let’s do it.
03:21
I’m going to go back to the vendor Balance Summary, we’re going to go to Export to Excel, I’m going to create a new worksheet, I’m going to put it into the existing workbook, if you don’t have a workbook, that’s okay as you can just make a new workbook,
03:33
but I’m going to put it into the existing workbook, which is going to be that month in reports that we’ve been working on. And then okay, if you if you this is the first time you don’t know what I’m talking about, then we’ve been working on this report. But you don’t have to you can make your own. And so there it is in Excel,
03:50
I’m going to maximize this, I’m going to grab that sheet by clicking on the left click and drag it to the right. So it’s the last thing we have here, double click on it so I can change the name.
04:01
I’m going to call it AP data data for our graph. So there it is. Now I’m very zoomed in. Because when I work on QuickBooks, I wanted to zoom in the screen. So I’m going to go into my settings.
04:12
These are my window settings for the display settings. And I’m gonna scale it back down to just 100 the recommended settings because I’ve been zoomed in. So if you need to do that, if you have different settings you can do that looks a lot smaller over here.
04:25
Now, I’m going to zoom back in by going down here instead of 100%. I’m going to hold Ctrl and scroll in so I can see a bit more. So there it is. And I can see are there any any special formatting tear no the total totals up so that looks good.
04:43
I’m going to delete the total column. I don’t need the total column. I’m going to click on column nine tene. That’s a row not a column. I’m going to click on row 19 Number 19, which is a row. Don’t you know what a row is compared to a column rows are horizontal all columns,
05:01
okay, I know, I just messed up, I’m gonna put my cursor on number one up top, the row, the row, right click on it and high and then delete it. And then put my cursor on the column of A, we don’t need it either, right click on it and delete it.
05:20
Now notice the formatting is a little bit different because it’s QuickBooks formatting. Here, we’ve got this, I want to bring it to Excel formatting.
05:27
So I’m going to put my cursor on Excel outside the data, go to the Home tab, and go to the Format Painter. And then I’m just going to click the little triangle formatting the entire thing, then I’m going to put my own formatting in the entire worksheet right clicking on it, format the cells.
05:46
And I like to make the formatting then be currency, bracketed numbers for negatives, no dollar sign, let’s get rid of the decimals because we don’t need them.
05:56
We don’t need no decimals, that’s just going to make more problems. So there we have it. And then I’ll make this a little smaller between A and B, by putting my cursor between A and B, and then making it smaller like that,
06:12
I’m gonna, I’m gonna select the data here, I’m gonna select the data, let’s put makes make it into a table. And so I’m going to insert a table, Insert Tab table, boom, and it’s got this thing down here,
06:26
I’m gonna say, okay, table iced, it’s been table alized. And then we can sort it using our little filtering options up top sorting it by who we owe, let’s go from Z to A.
06:36
So this is the top person that we owe money to for goods and services, we provide it from the vendor, or we bought from the vendor for our business,
06:44
we could add a total column down below if we so choose by go into the table design, put the total column back in, there’s the 26 636, which should tie in still to what was on QuickBooks 26 636, it should tie out to what’s on the balance sheet,
07:00
which was the 26 636. It does, indeed. So there we have it. And so now, we can simply make this into a pie chart, we could do that by just selecting the data, just going to select this data. And notice, we might want to like remove or shorten it a little bit,
07:19
but we’ll make a pie chart with all of it first, and then we’ll see it has some skinny, some skinny slices, and then we’ll adjust it. So the pie chart is easy as pie to to to make, we just go to Insert pie chart.
07:34
Boom, there’s the 3d one. Again, I don’t like the 3d one. But you can do this, they also have this disc one, which is kind of cool. That one’s a new age, new age pie chart, but we’ll go to the standard pie chart up top, there it is. Maybe we don’t need a title. Maybe we do that.
07:49
But it’s got a lot of slices. It’s quite small. It’s got too much going on here. So what am I going to do, I’m going to say let’s go let’s make make it like the one in QuickBooks 1-234-567-8910. So this one down here, everything below that, let’s let’s make that other,
08:07
that adds up to 2985. So I’m just going to make this 12985 and delete all these column or row 13 through 18. Right click and delete. We’re gonna make this other now. And so now it looks a little bit a little bit nicer, can make it larger, maybe.
08:31
So but maybe it’s still it’s still too much going on there. So maybe we shortened it down a little bit more, maybe I’ll make it up to like here. So I’m going to say that adds up to 2732732 or 7232. I’m dyslexic 7232.
08:50
That’s okay, I figured out this, we’re going to right click and delete, it still adds up to the 26 637, which ties out to here, right. So we’ve got 26 Six, that’s right. And then this is going to be other. So now we’ve got a nice smaller kind of pie chart,
09:12
we might want to drag it down, we might want to adjust the design of it possibly adding adding a key a different kind of format of the key percentages in it something like that maybe.
09:27
And again, we have a lot more options to adjust the color scheme of it and whatnot, we can adjust the color scheme and so on up top, something like that or whatever. And so you got a lot more flexibility.
09:40
That’s the point. So then we could do the same thing with the other I know this is quite small because I’m going back over here and this the looks small but the other chart is based on this chart is based on the aging report.
09:54
So if I go to the aging report, reports drop down and we go into To the vendors and payable, a p to the ag and current one to 30. Let’s I’m just going to make the totals are all we need.
10:09
So I’m just going to go down here and say alright, well then let’s just, I don’t even need to export that I’m just gonna go, little table, I’m going to put that whatever that is so that it doesn’t like format my numbering weird.
10:23
Well, let’s do this, this is going to be current kind of need one for current, and then that and then one to 30. And then that and 31 to 931 to 60. And then that and 61 to 90, and then over 90,
10:41
those are our categories, typically. And now let’s just go plug in the numbers, which I could just I don’t have to export, I’ll just tack on them there. 23177722317 7.72,
10:55
That right 232317, and then 345 0.2345 0.2. And then and then that should add up to 26. That’s it. So there’s nothing in this category, that category which should total up sum it up.
11:17
So messed up. Little that doesn’t tie out 26 528, this comes out to 26 630-623-1777 to 23 177. So there it is, no 23 17772. And it’s too small, I can’t see it. 345-920-3459 20. Okay, there we go. And then we could just make this into a bar chart or whatever. So we can go insert up top and put it put a bar chart or something like that.
12:06
And see how easy that is. And now we’ve got all the flexibility, we can change the column sizes and the x and y axis numbers and so on to adjust that if we so choose, we can just the color scheme of it, we can do different, you know, we can do a whole bunch of other stuff with it.
12:25
Now, if I wanted to not show the data files, we can once again make another tab down here, I’m gonna do that with a little plus item. I’m just going to call this the AP graph. And then maybe I just copy the graphs over here.
12:39
Maybe I just want the graph, I’m going to Ctrl C, copy it, paste it on down right here. And then I’ll copy this graph, copy it, paste it on down right there.
12:49
I’m gonna go to the View Page Layout back on over so I can see the end of the screen say how large could I make these graphs? Maybe I want to make it landscape, so that this time so that I can make it bigger.
13:04
So now I could say okay, let’s make it seems like it didn’t switch to landscape. Back on over, it’s on landscape orientation. It’s not making the change. Orientation landscape, bring it here to here. Orientation landscape, okay, whatever, we’ll bring it, bring it into here. And then this one, we can bring it back out.
13:32
So there we have it. Okay, and then if I want to to hide this data tab, I can right click on it. And I can hide it. So that when I print all this stuff on one report, it’ll just it’ll just print what I want here.
13:48
So I can go back to the File tab, I can go to the print, we could say that’s not print, we can go to the print printed using the cute PDF printer.
13:57
And there’s our options still not doing the landscape thing, which is kind of annoying. But I’m not going to get into it too much right here. It’s because we’ll just just given some examples. If I hit the drop down, we want to see the entire worksheet. Then I can scroll down,
14:11
we got 25 pages on this thing. Now, if I go all the way to the bottom, we’ve got our graphs that we made. Here’s the last last graph. If I scroll back up, it’s got the prior graph,
14:24
it doesn’t have the Data tab because we hit it so that’s nice way that we can put all this stuff on one PDF using our Excel which we’ll do in a future presentation. There it is. I still am kind of annoyed that it’s not there. It switched to landscape so now I can make it landscape.
14:43
Why Why aren’t you Why weren’t you working before you make me look stupid Excel. You make me look stupid? I hate when you do that. That’s okay, whatever. I look stupid all the time. Here we go. There.
14:58
So there it is. And then this one. Okay, so So now if I go back on over here, file, and we were going to say print it and then the entire worksheet and I can go back to 26 page.
15:20
So there it is. So now it’s on landscape so we can make a larger one landscape and it still shows on a PDF. If they were to watch it digitally instead of printing it, it looks nice if they’re gonna print it.
15:32
Then we got this issue where you got to staple it sideways and whatnot, which is just drive me crazy, I swear. But there it is. So there it is. Okay,