 QuickBooks Desktop 2023. Accounts receivable graphs, exporting data to Excel, creating the graphs within Excel. Let's do it within two-its. QuickBooks Desktop 2023. Support Accounting Instruction by clicking the link below, giving you a free month membership to all of the content on our website, broken out by category, further broken out by course. Each course then organized in a logical, reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems, PDF files, and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. Here we are in QuickBooks Desktop 2023 sample raw castle construction practice file provided by QuickBooks going through the setup process we do every time. Maximizing the home page to the gray area, going to the view, drop down noting we got the 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, changing the range in 01, 01, 2, 4, 2, 12, 12, 31, 2, 4, January to December 2024. Customize it. Font number changed to 12. Yes, okay, yes, okay, I got my okays and yes backwards. But reports drop down, company and financial, this time the balance sheet. And then we're going to go to the drop down and change the fiscal year 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 going to do that the whole time. So there we have it. 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. And we're going to get that information from the sub report. So you'll recall last time if I hit the report drop down, 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 aging, and then the customers receivables, and we're getting that information. I'm going to focus in on the pie chart and maybe we can do the aging to 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. 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. 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, 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. 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. 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, but I'm going to go to an existing workbook. I'm going to browse and say, I wanted 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. 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 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. 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. 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 and 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. But I think 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 some at 190% which you can see down here. So it's it's large so you can see it you can also hold down control and scroll scroll in. 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 and jobs. We've got all this added added junk that I would like to be removing. So so what I'm going to 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. What I want is really just 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. So first I can look at this and I can say okay are there formulas in here notice that there's a formula here. I don't really need the formulas. I just want raw data. 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 pastes it down without any formatting and without any formulas. So now we've removed the formulas 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. 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 Excel's baseline font so I'm going to put my cursor here home tab clipboard format painter and then paint brush 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 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 can do whatever you want 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 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 Alan Robert I just want the total 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 and this one I want the total so I'm going to take those two rows right click and delete. I'm going to take I want there's there's the total Brian cook I just want the total so I'm going to 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 totaling this up so well if I miss something I can always check it again 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. I'm going to take these two right click and delete and you wouldn't have to do this 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. And then these 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. I can sum this up equals the SUM, our favorite formula in Excel. Just to double check that if I have it right 98105, does that still tie out to what we had in QuickBooks 98105? It does. So that looks good. Now I'd like to get rid of these totals. Now you could go each and each one double click on the cell which is right here and delete the total. But you could try to use some other tricks, like one thing you might try is you might say, instead of using some fancy formula, I could try to copy this and paste it right here. And then if I retype in some of this stuff, sometimes 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? 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 trick in Excel. So a little faster to do that. 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. 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. So I'm going to right click and delete. And 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 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. 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. And I could 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 98105. Then I could use this to make a pie chart although notice I've got a lot of customers here. So I'm over 10 people so the pie chart might have some little slivers, 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. 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. 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 is that. But you might say okay that gets a little crazy because I got too many people down here that's probably going to distract people more than add to this information. So I could try to do it. Let's delete this pie chart and say we're going to stop it at 10 the way QuickBooks did. So here's 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. And so if I go back to QuickBooks and look at their pie chart, they went down to that person. And then underneath it, everything else, I'm going to just select it and say what does that add up to? It adds up to 6, 2, 7, 9. So I'm just going to put here 6, 2, 7, 9 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 98105. Now if we create our pie chart, we can say okay, let's insert the pie chart now, insert the chart. And so there we have it. So now it looks similar to what we had 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 the percentages inside, that one's kind of clean looking. 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. 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 because you might say, well, that's still kind of busy looking. 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,261. So 20261, for example, and then maybe I delete all this stuff. So now you've got a pie chart that might be easier on the eye depending on how many slices you want on the pie chart. You've got that kind of flexibility that you can do with the pie chart. 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 one that's based on how old the items are. So for that, and I know QuickBooks is quite small right now given what we were looking back before, but I'm going to go to the Customers and Receivable. This is the AR Aging Summary Report. And if I just look at the totals down here, then 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 current. What is it current? And then we've got here. We've got 1 to 30, 1 to 30. So I'm going to say here's a little dash. So this is 1 to 30. 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 90. 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, all right, the first one's 93, 105, 19, 93, 105.19, 31 to 60, 0. And then there's 5,061 to 90. So here's there's 5,000 here, boom. And then we've got the total and that should total up to the 98105. And then we can just make a pie chart. I'm going to put zeros here and zeros here. Oh, this would be 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. 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. And so, you know, you could change the color schemes, you know, quite easily. So much more flexibility to put this stuff into Excel. 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 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, I'll try to maximize this to 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 be my AR graph. So then then I can report then I can print just the graph. 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. So if I wanted 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 Qt PDF printer, everything on one report. I can print the entire thing, entire workbook. 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. 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. So unhide. And so now the data is back. So that's a general idea and these concepts can be applied. We'll do a couple more of these. I know this isn't an Excel course. So I'm doing this kind of quickly, but it's really useful to be able to export this stuff to Excel for grass and other kind of visuals and budgeting and that kind of stuff. It's good stuff to know.