QuickBooks Desktop 2023 accounts receivable graphs, exporting data to Excel creating the graphs within Excel. Let’s do it with Intuit QuickBooks Desktop 2023.
00:15
Here we are in QuickBooks Desktop 2023 sample Rockcastle construction practice file provided by QuickBooks going through the setup process we do every time maximize the homepage to the gray area go into the view drop down no 10
00:30
We got that hide icon bar, open windows checked off open windows open on the left hand side reports drop down company and financial p&l Profit Loss Income Statement change in the range in a 101 to four to 1212 31 to four January to December 2024. Customize it.
00:52
Thought number change to 12 Yes, so okay, yes. Okay, I got my okay isn’t Yes, backwards, but reports dropped down company and financial this time the balance sheet, and then we’re gonna go to the drop down and change the fiscal year.
01:08
So we can customize it, and then fonts and numbers, and then change the font, and then go to 12? Okay, yes, okay, I’m going to stop talking like that, don’t worry, I’m not gonna do that the whole time. So there we have it.
01:21
So now we got the major two financial reports we set up every time, we’re focusing in on a graph giving us the supporting data for the accounts receivable, which will not be broken out by date, but by who owes us the money by customer.
01:34
And we’re going to get that information from the sub reports. So you’ll recall last time, if I hit the report drop down,
01:40
we had a graph, customers and receivables. And that graph was here, accounts receivable graph. And so we have this graph, which is really two graphs, the 18th and then the customers receivables. And we’re getting that information I’m going to focus in on the pie chart,
01:57
maybe we can do the Ag into if we have time. But I’m going to focus in on the pie chart and go to the reports drop down and see where that source data comes from customers and receivables, we want to take it from the customer Balance Summary Report,
02:11
that report is going to give us all the customers here and then the balance due from those customers, which should total out to the total on the account on the balance sheet for accounts receivable as we see here.
02:25
So instead of using their little pie chart, we’re going to export it and make our own pie chart to see how that can be done.
02:34
See how we have more flexibility doing it that way. And then we can use that same concept or theory. Anytime we want to create a pie chart Excel being much more flexible.
02:43
So I’m going to go back on over here and say, Okay, let’s let’s export this thing, I’m going to go to the Excel up top, I’m going to create a new workbook. Clearly you do need excel in order to do this.
02:55
And so I’m going to export it. Now I’m going to put it into an existing workbook because I want to put it with all the other stuff that we’ve been putting together. And but you don’t have to do that you can you can take it to a new workbook.
03:08
But I’m going to go to an existing workbook, I’m going to browse and say I want it to go into that month and report one that we’ve been working with. And then I’m going to export it. So let’s, let’s export.
03:21
And so there it is, I’m going to move this to the right hand side, I’m going to grab it and then move it to to the right, I’m just dragging it to the right. So there is our report. Now notice I’m in 150 zoomed in on my screen.
03:36
And I don’t want to work in excel in that format. So I’m going to I’m going to say let’s go to my settings here. And I’m going to change my display screen back to 100%. When I work in Excel, that’s a little bit tedious to have to do, if you’re going back and forth from QuickBooks to excel.
03:55
But I’m on a very large screen in QuickBooks so that you can see it better, you know, when we’re when we’re doing the presentations. So I’m going to, I’m going to go back down here,
04:05
name it, I’m going to double click on the data, double click on it and I’m just going to say this is a our data. Now I’m going to do this fairly quickly because it’s not an Excel course.
04:14
But I think it’s it’s useful anytime you have a database program to understand that you can export to Excel, and then you know format your data in here. Now I made I just zoomed in so I’m at 190% which you can see down here. So it’s it’s large so you can see it you can also hold down Ctrl and scroll scrolling.
04:31
That’s how I did that. Okay, so what I would like here is just to have the customer name and then and then the the total down below because they had all these sub accounts in jobs.
04:45
We’ve got all this added added junk that I would like to be removing. So so what I’m gonna have to do is go through here and say okay, this one has Albert Robert and then it’s got the remodel and then it’s got the total down here.
04:59
What I want is Religious basically the total. Same with here, I just want the total number, and I just want the name, I don’t really, I don’t even really want the total in terms of the actual lettering.
05:10
So first I can look at this. And I can say, Okay, are there formulas in here, notice that there’s a formula here, there’s a formula here, I don’t really need the formulas, I just want raw data.
05:21
So what I’m going to do is select this whole thing, I’m just going to copy it right click and copy. And then I’m going to paste it down right on top of it, but just the letters only. So just the values only, which is this one, that paste sit down without any formatting and without any formulas. So now we’ve removed the formulas.
05:43
And then I like to kind of adjust the, the font size, because notice the font size over here is eight. And over here, it’s it’s still eight, but it’s bolded.
05:54
And over here, it would be 11. I want to standardize the whole sheet and have my baseline font formatting, I’m going to use Excels baseline font,
06:03
so I’m going to put my cursor here, Home tab, clipboard, Format Painter, and then paintbrush the whole worksheet by clicking on the triangle. So now it’s got like that baseline formatting, then I’m going to format it the way I want it formatted.
06:17
Right clicking, I’m going to format the cells. And then I’m going to say let’s make this let’s make it currency bracketed numbers is what I usually make it, you could do whatever you want,
06:29
I’m going to say none. And then I’m going to remove the decimals because we don’t really need pennies in our calculation. And there we have it. So that looks good. And now I’m just going to format the data.
06:40
So I’m going to remove this date field, I’m going to click on column or row one, just put my cursor right on number one, and then right click that selected area, delete it. And so customer one that looks good, this Allen, Robert, I just want the total.
06:56
So I’m going to have to take these two rows, and delete them, right click and delete. And then these two, again, I just want the total. So I’m going to take those two rows, right click, and delete.
07:10
And this one, I want the totals, I’m gonna take those two rows, right click, and delete, I’m gonna take I want, there’s, there’s the total, Brian cook,
07:20
I just want the total. So I’m gonna take these three rows, because these two add up to that right click and delete. If I mess up, I can double check my number by total in this up. So if I miss something, I can always check it again. Right click and delete.
07:36
I’m going to take these two, right click, and delete. I’m going to take these two, right click and delete. I’m going to take these two, right click and delete. I’m going to take these two, right click and delete. And you wouldn’t have to do this.
07:59
I don’t believe if you didn’t have all those sub the jobs are kind of doing this but in the case, right click, and delete. And then I’m going to take these two, right click and delete these two, right click and delete.
08:13
And then the two, right click and delete. And then these two, right click and delete. And one more time these two, right click and delete. This total column isn’t a formula. So I’m going to get rid of the total over here. I don’t need the total.
08:35
I can sum this up equals the SU N our favorite formula in Excel just to double check that if I haven’t right 98 105 Does that still tie out to what we had in QuickBooks 98 105. It does. So that looks good. Now I’d like to get rid of these totals.
08:54
Now you could go each and each one double click on the cell which is right here and delete the total. But some you could use try to use some other tricks like one thing you might try is you might say instead of using some fancy formula,
09:08
I could try to copy this and paste it right here. And then if I if I retype in some of this stuff, sometimes it’ll it’ll try to copy what I’m doing. So if I say Allard Robert, and then I say, Birch, see how it’s trying to copy what I’m doing right there.
09:27
So that’s an easy way. So I can just say okay, enter, and now it’s copied down without the total in there. So that’s a nice little little trick, and it’s so, so a little faster. To do that.
09:37
I’m going to copy that. And put that under here because the customer didn’t have that subtotal. So I’m going to insert that.
09:44
Notice column C has this skinny column. So there’s nothing really in column C because I made column B larger. So I don’t need column C. I’m going to put my cursor on it. Right click and delete. I’m going to delete this stuff that I made over here so I can copy that stuff.
09:58
So I’m going to right click and delete Okay, so there we have it. So there’s our information, I’m going to delete the total now, because typically I like to put a table in place.
10:08
So I can then adjust the table. So I’m going to select my data set and insert a table. So let’s go to insert, and then let’s make it into a table. So it’s going to insert a table, that looks good. So that is good. And then I can sort this thing with my little dropdowns, let’s get rid of column A,
10:28
there’s nothing in column A right click and delete, I can sort this now by the total, I’m going to sort it by the total from Z to A. So now I’ve got all the customers, the person that owes us the most money to the person that owes us the least money.
10:45
And I could, you know, add a total row here, if I wanted to in here by going to Insert and have a total row. So there’s the total the 98 105, then I could use this to make a make a pie chart, although notice, I’ve got a lot of customers here.
11:02
So I’m over 10, I’m over 10 people. So the pie chart might have some a little sliver, some skinny slivers, that’s why sometimes you stop it at 10, or possibly less than 10. So if I select this, for example, and go up top and say, Insert, this is how easy it is to make a pie chart,
11:18
we just go pie chart. And there it is, there’s our pie chart, boom, that’s how easy it is. Now you can then format it, which gets a little bit more tricky. Because maybe you want your key to look different down here.
11:29
Maybe you want something different on the header of the pie chart, maybe I don’t need a header in the pie chart, you can delete the header. And you’ve got your different formats. Up top that you can present the pie chart in. So there’s that.
11:45
But you might say, okay, that gets a little crazy, because I got too many people down here, that’s probably going to going to distract people more than add to the to this information.
11:54
So I could try to do let’s delete this pie chart and say we’re going to stop it at 10 the way QuickBooks did.
12:00
So here’s 1-234-567-8910. And so if I go back to QuickBooks, and look at their pie chart, they went down to that person like that underneath it everything else, I’m going to, I’m going to just select it and say what does that add up to it, it adds up to six to seven, nine.
12:24
So I’m just going to put here, six to seven, nine, and delete everything underneath it, delete. And we can just delete this whole, these columns, these rows, delete, and this is going to be other other everything else. In other words, still adds up to 98 105.
12:46
Now if we create our pie chart, we can say okay, let’s insert the pie chart. Now, insert the chart. And so so there we have it.
12:56
So now it looks similar to what we had in. In QuickBooks. In our charts, we can try to choose the different options up top. So we’ve got different keys, the percentages inside. so on. So the percentages instead, that one’s kind of clean looking.
13:23
So let’s choose like this one. And of course, you could do some more customization to the charts and add, you know whether you want the percentages here or in the key, and all that kind of stuff,
13:33
I won’t go into that in a whole lot of detail. But you have a lot more kind of capacity to do that stuff once it’s in here. Now, you might even want to do less than then because you might say, well, that’s still kind of busy looking.
13:47
So here you have the capacity to say, well, maybe I want to stop the data up here, maybe I want to sum these up and just have the first whatever. And so this adds up to 20,002 61.
13:58
So 20261, for example. And then maybe I delete all this stuff. So now you’ve got a pie chart, that might might be easier on the eye. Depending on how many slices you want to the pie chart, you’ve got that kind of flexibility that you can do with the pie chart.
14:18
So that’s just a quick idea of what you can do with it. Now the other pie chart that they gave us here, we could look at it real quick in QuickBooks is this is this one that’s based on the how old the items are.
14:33
So for that, and I know QuickBooks is quite small right now, given what we were looking at before but I’m gonna go to the customers and receivable this is the the AR Aging Summary Report AR Aging summary and if I just look at the totals down here,
14:49
that I can just type those totals into like a chart for example in Excel if I want to try to recreate something like that, so I could say here’s the aging. We’ve got we’ve got current What is it current. And then we’ve got, we’ve got here, we’ve got
15:10
one 231 230 Crap. So I’m going to say, here’s a little dashes just one 238, that makes it so it’s not going to try to format it funny. And this is going to be 31 to 60, right, and this is going to be 61. To 90, I believe, and this is going to be then over, over 90.
15:36
And then since there’s not much data, I can just type the data in, I don’t really have to export this from QuickBooks, I can just say, Alright, the first ones 90 310-519-9310 5.19 31 to 60, zero, and then there’s 5061 to 90. So here’s there’s 5000, here, boom, and then we’ve got the total.
16:01
And that should total up to the 98 105. And then we can just make a pie chart, I’m going to put zeros here. And 00, this would be a, a graph chart. So I’m just going to take these insert, and then we can go into our charts. And it would be something like this, right. So you can make a similar chart.
16:26
And once again, we’ve got our options up top and how fancy we want to make the chart and so on and so forth. And you have a lot more options in terms of what you want to make the scale for the X and Y axes and your title and the colors.
16:40
And, and so you know, you could change the color schemes, you know, quite easily, so much more flexibility to put the stuff into Excel.
16:50
And then if I wanted to give this information to somebody, notice that I could give them this graph, which gives them the data on the left hand side, or like, I might then say that I’m going to make a new page.
17:05
And maybe I copy the chart, and put that over here. So now I’ve got my chart on one page. And so then I can go over here and copy this chart, copy the chart, and put that over here. And so now if I go to Page Layout, and back on over,
17:25
I’ll try to maximize this too, to fit on a page. So I’m going to put this down, I’m going to maximize this. So it fits on a page. And then this would be I’m going to double click the My a our graph. So then then I can report then I can print just the graph.
17:45
So I’m doing this kind of quickly. But you can also hide this field. If I don’t want this because this is just my data. Now, I can right click on it, and I can hide it.
17:54
So if I want to print everything from this one Excel report onto one page, I can go to my file tab up top, and I can print it now I can print it using a cute PDF printer. Everything on one report, I can print the entire thing, entire workbook.
18:10
And now if I scroll through this all the way to the bottom of it. We’ve got our our graph right there. And we do not have the data because I hid the data sheet. And so I can export this all to one sheet.
18:27
So that’s one way you might use Excel to kind of format these graphs into your sheet and then not show the data and make it try to make it as easy as possible. I’m going to select these two, right click and unhide them now.
18:41
So unhide and so now the data is back. So that’s the general idea and these concepts can be applied. We’ll we’ll do a couple more of these. I know this isn’t an Excel course.
18:51
So I’m doing this kind of quickly, but it’s really useful to be able to export this stuff to Excel for graphs and other kinds of visuals and budgeting and that kind of stuffs good stuff to know