QuickBooks Online 2021. net, accounts receivable graphs with the help and use of Excel. Let’s get into it with Intuit QuickBooks Online 2021. Now, here we are in our QuickBooks Online test drive file, which you can find by searching in your favorite browser for QuickBooks Online test drive or in Craig’s design and landscaping services, we’re going to go down to the reports on the left hand side, we’re looking for reports for who owes you the money.
00:27
So who owes you money, we want to take a look at the customer balance summary, I’m going to be opening up the customer balance Summary Report. And I also want to take a look at the balance sheet report. So let’s copy the tab up top and let’s copy it two times, I’m going to right click on it and duplicate the tab, it’s not really copy, it’s duplicating completely different thing, right click on it again and duplicate the tab. So we got two tabs, going to put the balance sheet on the one to the right, then the report we’re working on in the middle, and then have another tab in case we want to do other stuff that’s open for us to do that stuff.
01:01
So then I’m going to go down to the reports again, in the tab all the way to the right, let’s open up a balance sheet report as well opening up the balance sheet report range change up top range change from a 101 to zero to 1231 to zero, running that report, close in the hamburger in the upper left, holding down control scrolling up to get to that perfect one to five percentage zoom, we’re looking at a report that’s going to be a summary report that expands on the balance sheet account of the 5002 8152. The story typically being in your mind, you’re saying okay, I’m doing work for a client or something, they ask,
01:38
How much money do people owe us and we say 5002 8152. And then they say, well, who owes us that money, and when can I collect on it. And that for that we run the other report, which can’t just be the drilling down on this report, because I need it, I need it by customer, not by date. So we’re gonna go to this report and say, all right, we have this report on the second tab, closing up the hamburger for this tab, range change up top, do we have a good range, let’s make a custom range.
02:05
And we’ll just make it as of 1231. to zero, run that report, the total of this thing broken out by customer is that 5000 to 8152. That’s the same as is over here five, to eight 152. Now many of these reports, we might want to make visuals from them, we might want to make graphs and charts and whatnot to add to our presentations. So the best way to do that oftentimes is just to export the reports to excel, because Excel has a huge amount of functionality to do the graphs and charts. So we’ll just show some some examples of a couple ways to do that.
02:43
This isn’t an Excel course, of course. But you know, it’s really useful to be able to do this with any kind of database program, because any kind of database program is going to have some you know, numbers in it oftentimes, and oftentimes, if you could just export that to excel, almost every database program has an export into a financial spreadsheet program. And then you can take that data and put it into other formats that might be more visually pleasing.
03:07
So such as a graph. So this one is is a type of form that we could create a pie chart with, because they all sum up to a total down here. Or we could create, you know, a standard bar graph and stuff like that. So let’s export this data. So I’m going to go to the drop down, I’m going to export it to excel. So let’s export it to excel. Now, I’m ultimately going to want to put it on another tab over here again in our other Excel worksheet that we’ve been putting all those reports on.
03:36
And just imagine we’re going to add like a graph to this one. So I’m going to, I’m going to hit the plus button down below. And then I’m going to I’m going to open up this Excel worksheet that we just downloaded the customer balance summary item, let’s enable the editing. So I’m going to enable editing. And then I’m going to copy the whole sheet by selecting the triangle in the upper left right clicking and copy, we’re going to copy it, and then close and then I’m going to minimize this thing, put my cursor in a one and then Ctrl V to paste it.
04:06
So there we have our information. So I’m going to put my cursor down here and I’m going to call this the accounts receivable customer. Let’s say accounts payable graph, graph data. So this is going to be the data file. Now what I’m going to do is I’m just going to adjust this, this information, so that I just have a column with the data in it. So I don’t need like the headers,
04:30
I don’t I don’t want any of the headers or anything like that. So I’m going to just remove this altogether. And I’m gonna do this fairly quickly, just because I know this isn’t an Excel course but I’m going to delete this I’m going to hold down Control and zoom in a bit. So I’m at that 145 so you can see what I’m doing hopefully one four or 5%. And then down here, notice we have some subtotal activity.
04:51
So this is totally up like those two or three lines. So what I want to do there is I just want the total for Freeman sporting goods. So what I’m going to do is copy this, I’m going to copy this, I’m going to paste it up top, pasting it 123 just the values only. And then this whole little summary thing that has these two items, which are jobs, I’m going to delete those rows. Because I’ve included it up top, I’ll double check my totals down below to make sure that I’m not off on the total.
05:23
So I’ll delete this, don’t want that I’m going to do the same thing. Down here, this one is totaling up a couple of columns, I’m going to copy that total and paste it 123 values only right there. And then get rid of this subtotal kind of thing for the for the jobs and right click and delete that. And so there we have it. And then if I just re sum up this data, we’re gonna sum up this data, see if it still ties out to what what it should, which is the five to eight 152. Is that what it is over here? That’s not what I’m trying to look at. I’m trying to look at this here, five to eight 152. It looks good.
06:04
Okay, so I’m going to go back on over. And let’s do some more formatting here. A little more formatting. And so then I’m going to format the entire worksheet, basically, with a cell on this side, just so all the cells have the same formatting to start with. So I’m going to go to the Home tab, clipboard format paintbrush, click my cursor on the triangle, and that’ll format the entire thing to have uniform formatting, then I’m going to right click on this thing, I’m gonna, I’m going to format the cells, formatting the sales, you don’t have to do all the formatting By the way, but I’m going to go to currency,
06:40
I usually go to currency, none. I’ll keep the decimals I guess, I guess, and then have negative numbers be bracketed. And then okay. And so there we have it. And so now I just want to add, I can just take this data and add graphs to it now, which is quite easy at this point. So I could then say, I’m going to just highlight this data, I’m not going to include the total, so not including the total.
07:05
And then I’m going to go to the Insert. And we’ll just grab a graph. And this is one that adds up to a total down below. So a graph that works is a pie chart. So we can go pie chart, maybe pie chart. And so then there’s a bunch of different kind of settings you can get into with the pie charts. Obviously, this has a lot of data down below. And I’ll talk about how we can adjust that. So it might look better, something like this. But again, there’s a lot going on probably too much.
07:33
But let’s keep it at that for this point at this time. And then you can you can adjust your settings over here. You can have the chart title, like I might not need the chart title, for example, some might say I don’t want the chart title. So I can remove the chart title, for example. And data labels going to kind of have to need the data labels in some way. So I’ll keep the data labels, I’m going to put the data labels like outside like this one like that, maybe again, really busy looking like that the legend, you can add the legend or not. So maybe you don’t want the data labels and you have like your legend.
08:11
On the right hand side. That’s another way that you can kind of populate this information. So a lot of different lots of different ways that you can, you can organize them. Let me just add the percentages again, just so we could see the percentages. So if I go to the legend down here, top left, bottom, more options. Well, I’m going to close this up. And then I’m going to go to the one that had the percentages with the data labels, data labels, here, and then I’ll get rid of a legend for now.
08:47
And so just so you can see where the percentages are coming from because obviously, if you were to add a graph, and then we’ll make it look a little bit nicer if you were at a graph like this, then they’re going to ask you what what are these percentages come from. And you’re going to be wanting to say, well, like they come from this report, which is basically generated from this report, which is supporting the information on the accounts receivable here. So we’re breaking out that accounts receivable in a visual way.
09:13
And like a pie chart type of way, the percentage calculations then would be calculated something like this, just so you can see the calculation, it would be this number divided by the total, right, that number divided by the total would be the percentage calculation if I then make this one so it doesn’t move. So I can copy it down by making it an absolute reference by putting my cursor in V 17. Selecting f4 putting the dollar sign before the B and the 17. Then I can copy that down. I could copy that down. And there’s our percentages.
09:45
If I make this column, then a percent column so there we have our our percentages. If I sum this up, we have the sum. And so we have that information for the sum 100% And you can see our corresponding percents over here. Now some other things you might want to do to this, you might want to say, hey, look, it would be kind of nicer if I had this in alphabetical order, or maybe an order by the biggest number on top. And maybe I don’t need all the littler numbers, I would probably want to cap it somewhere. So let’s try that, let’s, let’s say we take this whole thing, let’s just take this all this data. And I’m gonna put it down here and try another one.
10:27
So we’re gonna copy this data, I just need this. And let’s put it down here. Now when I start to organize the data, like you could just simply organize the data here, and then go to your data tools and add filters, and then use these filters to organize the data and you’d want it organized in this column, I like to, instead of using that filter option to actually make a make a table out of it, because it’s less likely that I jumble up the data that way, in my opinion.
10:55
So I would actually make a table usually by going to the Insert tab, make a table from it, and say, okay, table, so there’s our table, and then I’ll sort then I get my little sort handles, and it’s less likely than that I jumble the data, meaning having a misalignment of a number and the item to the right, so then I’m just gonna sort this from top to bottom. So we have that, that looks good. Now what I’d like to do down here, if I, if I sum this up, then if I sum this up, we still add up to what it should add up to the five to eight, one.
11:31
Now these bottom line numbers, maybe I want to stop it somewhere and say everything else, I’m going to put the top 123456789, maybe that’s good, I put the top nine there, and then everything else I’m going to put into other. So this whole piece is going to add up to 121 910. So I’m going to say right there, 121 910 121 9.10. I’m going to delete these columns altogether. So delete, right click, delete those columns. And I’m simply going to call this other other. And there’s our total still totals up to that. But I put everything that’s below that amount in other, now we can make another pie chart. And this might make it a little bit easier to read.
12:17
So we can go then to insert. I’m just selecting the data in the middle here, insert, and then we’re going to go to the pie chart that we’re going to insert, insert the pie chart like so. And there we have it. So there’s our pie chart. And once again, we can kind of adjust this, I don’t need the title, maybe. And then the legend, I can’t let’s do that data thing, again, data thing, and put those maybe on the outside. So that looks a little bit a little bit easier to see.
12:48
Notice you also have other options with the with of course, the charts you can enter in on. So if I was to say insert a pie chart, and do something like this. The reason I don’t really like this one is because it kind of emphasizes you kind of visually cheat in some way, because it makes the ones that are being emboldened look a little bit bigger. So if you wanted to emphasize how big these two clients are, you can put them over here and they would look bigger, right? It’s deceptive to me. So even though it looks kind of cool. So I don’t really like that chart personally.
13:24
But the other one we see commonly is going to be this one, which is kind of neat, this chart. So it does it in that kind of circle fashion. So we see that and you got similar similar options over here. So you could say, you know, I can remove this, the legend, still kind of a lot going on in the legend. So maybe if it’s on the right, it’s not too bad. So put that on on, on the left, put it on the left. So something like that, it’s kind of a neat way to put the data. One more, one more option, of course you could have is to is to have well not you have a ton of options.
14:01
But notice that this because this sums up to a total down here, you have a good thing to do a pie chart with. But you can also always do a graph chart. So this, when you can do a pie chart, it’s nice to put that in play because you can’t do it like all the time. And you can do one of these kind of things, you know, most of the time with most data, so we could do something like that, and do a similar kind of process. So we got our title, and then our then our items down below.
14:28
And of course we have a bunch of different options for the look and feel of the graph and the color of the graph. Now once you have this, then you can then you can copy it on another page, you might say, Well, how do I fit this on my report, really, you don’t really want the data on the report, all you want is the actual the graph on the report most likely. So one way you could do it is you could try to put the graph kind of on top of the data like this.
14:54
And then and then you know you got your data underneath there. And you could just have your graph basically on top Have it that’s one one method you can do. Or you can put the graph completely on a on a different page. So I can then add another page, for example, and put this say this is the AR graph. And then I can just take this, maybe let’s take like, this one’s kind of neat. Let’s take that one, I can copy that graph, and then paste it here. And then I can have it just on one page.
15:24
And then I can alignment, I can align this to fit properly, on a page like that, right? Okay, and then I can take my other one, like, I can take this one, maybe have one like that looks like that, and bring it over here, I should put a title on it. And then we can have one that looks like that on a page. And we can fit it nicely on the page. And then if I wanted to print this thing, what I could do is just hide this whole tab right here. So I can right click on this and hide this tab.
15:56
So now that tab is totally hidden. So then if I go, and I know this doesn’t look as nice as possible, because again, this is just like an example what you can do. But then if I go then to the to the print, and I want to print all my reports together again, and I’m going to print the entire workbook. For some reason that was hidden. So I went back on over here to the to the journal tab. Let’s try it again, just to check it out and then go to print, I want to print the entire workbook.
16:25
So we’ll print the entire workbook, which is now quite long. So it’s 38 pages, let’s go all the way down to the bottom. So we go down to the bottom, there’s our graphs, right and that and the one above it is now been hidden. So it looks like it is picking up just the one that we want that’s open. And that’ll allow us to then print it as a PDF all on one, one file again, even though we have the data on a separate tab. So I’m going to go ahead and print this using our cutepdf printer.
16:53
And let’s see what that looks like. So I’m going to say that I’m going to read I’m going to overwrite this statement again, say okay, and then let’s check it out. Let’s give it a double check here. We’d like to have two checks before we proceed. So we’re going to then say that, we’re going to open up our, our report here on the PDF file that we created. And let’s scroll this all the way down.
17:20
So we can complete our second of two checks for the double check. So there it is, there’s our new report. And so it just tacks on beautifully. So I’m going to close that back out. And then if you want to unhide this cell over here, like if you want to change the data, now, I have a hidden cell. So you’re like and you wouldn’t even know it unless you like so but you can always test if you have a hidden cell by holding down shift and selecting the two cells there. And then right click on the hidden cells and say unhide and then boom, there it is. It’s right there. It was hidden. But we found it and we unhide it and so there it’s back. So there’s back okay.