 QuickBooks Online 2024. Sales by customer graphs. Get ready and relax because it's so easy using QuickBooks Online. You'd think it'd be a crime but it's not unless possibly you're doing bookkeeping for like the Clintons or something at which point you'd probably have bleach bid at the ready and any anyways let's get into it. Here we are online in our browser searching for QuickBooks Online Test Drive looking for the result that has into it.com and the URL into it being the owner of QuickBooks. We're going to select that United States version of the software here and verify that we're not a robot. I'm not a robot but I do my work in a rowboat. Is that okay? QuickBooks, is that okay to have a rowboat that I do my work? I'm going to close up the here. Let's open the reports on the left hand side and then in the favorites like we do every time we're going to be right clicking on that balance sheet to open link in a new tab then we'll right click the P&L the profit and loss otherwise no one has the income statement open that in a new tab let's go to that middle tab we opened up the hamburger close it up how are we supposed to eat it when the bun is off of the hamburger we're going to then change the range going back to 2023 010123 tab 123123 tab first a word from our sponsor. Yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is is better than their stupid stuff anyways like this CPA thinking cap for example CPA thinking CAP you see what we did with like with the letters and this CPA thinking cap is not just for CPAs either anyone can and should have at least one possibly multiple CPA thinking caps why because based on our scientific survey of five people all of whom directly profit from the sale of these CPA thinking caps wearing this CPA thinking cap without a doubt according to the survey increases accounting productivity tenfold yeah at least yeah apparently the hat actually channels like accounting energy from the quantum field ether directly into your head allowing you to navigate spreadsheets faster it's kind of like how in like the matrix when neo learns kung fu or at least that's what the scientific survey saying so get one because the scientific survey participants could really use some extra cash if you would like a commercial free experience consider subscribing to our website at accounting instruction dot com or accounting instruction dot think of it dot com running it because we want to refresh going to the tab to the right close up the hamburger and once again bringing it back 010123 tab 123123 tab run it so we can be refreshed now we're fresh and that's the setup process we do every time these are the major two financial statement reports all other reports generally giving more information more detail about one or multiple line items on these two major financial statement reports we're now looking into making graphs and pie charts which can give a little bit more visuals a little bit more information possibly impress our clients a bit more so how can we do that well we've looked at well the places we can do that most likely would be the income area where you might want to start thinking about making graphs and we talked about a graph by customer last time we'll do a graph by item this time inventory or service items then we also went to the balance sheet and we did a graph on the accounts receivable which you would only have if you're doing an accrual base basis for the revenue cycle tracking customers that need to pay you but that's an easy one to make a graph of you also might do a graph with the accounts payable which again you would only have if you're entering bills on an accrual method for your payables you also could do a graph for just your total assets in which case let me just point that out if I go over here and we look at our summary balance sheet we go down to the balance sheet summary right click and open that one in a new tab then another easy graph you might make is to take your total assets and break them out by the category of the assets see how here you have the current assets you might have to do a little bit more formatting because if you have other assets other than current assets you might have some more drop downs but this will give you a pretty nice breakout which is similar to pie charts you might see in like financial when you're investing right because this is the total of your assets right you can do the same for your liabilities that would be a pretty easy graph to make if you export to excel and you made a pie chart of like your current liabilities for example and so that's just an idea but this time we're going on over to the income statement once again now the income statement on the income side of things we usually don't have a whole lot of accounts on the income statement because usually we just want a few accounts on the income statement which we can further break out into more detail with sub ledger reports such as income by customer we looked at last time or income by item service or inventory item which we will do this time if you do have a lot of income accounts you could just export the income accounts like this and then and then basically do a pie chart of the actual income accounts as well if you want if just another idea that you can make a pie chart from that might be useful depending on the industry and business that you are in so note that we're going to start by just like we did last time a sales sub ledger but this time the sub ledger will be by item remember that you're only going to have those sub ledgers if when you enter the data for income you're using the sales forms of invoices and sales receipts if you're using a bank deposit through the bank feeds you will most likely lose the added depth of other financial sub ledgers like the income by customer and income by account reports that might be well worth it if it's a lot easier to do that if you have a simple business structure like gig work that you're just recording income as it goes through the bank and also note that remember that the sub ledgers will not possibly tie out exactly to total income as they should if you recorded something to income without applying a customer or item to it this case an item that's what we're looking at that's because QuickBooks does not force us to record an item every time we post something to income although we typically will record an item if we use the invoice and sales receipts forms every time we record something to income that is different than the balance sheet reports of accounts receivable and accounts payable which will basically always tie out to the sub ledger because QuickBooks will force us to add a customer or vendor to to every time we hit those accounts so that they do force us to have a sub ledger that is in balance now if the if the difference is immaterial as it will be with this report then when we make our graphs we might want to tie into what's on the balance sheet or on the income statement so that we make sure everything ties out on the reports that we're making so let me show you what I mean let's go to the tab to the left we're going into the reports closing up the hamburger close up that hamburger and then we're going to go it's almost food time over here now that we're now that I think about it hamburgers sound good I would like a hamburger but okay focus I'm in the sales and customers area and then we're over here sales by customer we want the sales by product and we want the summary this time so we've got the sales by product summary right click I'm going to open that link in a new tab it's opening to the right so let's go there close up the hamburger let's change that range up top going from 010123 tab 123123 and run it so we can refresh it now this is a bit more detailed of our report it's going to have more information if you sell actual inventory items as opposed to the service items and it's also more complex because we have these drop downs which I can't collapse as easily see I don't have that capacity to collapse them as we did with the other reports so what I really want from this report is basically just the amounts over here the percentages could work too we could use that but really I just want this column so I could export this to excel and clean it up fairly easily and so we'll do a little bit more work to do that this time if you don't have a lot of these categories it would make it easier as well because note like I might make my table my pie chart by just category like this so then there would only be like three items in it that would probably be the easiest thing to do or maybe I want the detail so so now I just want the detail of each line item that we have so I'll probably try to try to break it down to just by account or item category design landscaping and pest control so it would be great if I can export just this but I don't think it's going to let me because I think when I export it to excel it's going to expand this out again so it might be even easier just to type this into excel right I could just type dude dude dude and then these numbers into excel and that would be that would be great but let's do the export thing so we can practice I can hit the dropdown we're going to export to excel and then I'll open it up to check it out remembering that we're going to try to put this enable the editing see it expanded it all again I knew it I told you I told you it would do that okay we're going to add this to the to our worksheet report over here now note that if you don't have this stuff over here that's okay but we're going to imagine that we have all of our reports and we want to add our graphs to the end of it to make them more fancy so we can impress our clients a bit more than the other guy the other guy's lazy doesn't take the extra step go the extra mile in order to to get things done right that's what I'm talking about so in case we're going to go back on over we're going to right click on this tab I'm just going to move it over if you don't have that other worksheet you don't have to move it over but I'm going to move it move it over and I'm going to hit the dropdown and we're going to move it to the month end reports and I'm not going to copy it I'm just going to move it boom tab over here I'm going to pull it to the right I want it on the last thing so there it is double clicking on it so there's our sales by product product I'll just say sales by product data so this will be our data tab I'm going to clean it up hopefully it's going to be a little bit more difficult to clean up than some of the other reports but that's what we're practicing here and I'll do this fairly quickly because we've seen a similar process in the past this would be similar to what you might have to do if you wanted to look at your assets for example and you export your balance sheet and just clean it down so you just have your assets that you want to make a pie chart from or something like that all right so let's hold down control scroll up a bit and I don't need the header so I'm going to clean out the header I'll just take all of this stuff and the column I want by the way is just this amount column so I'm going to note that in my mind and then I'm going to just delete all of the header stuff and then the columns do they have any formulas in it is what I'm looking for is there a formula in here I'm looking at the formula bar up here are there formulae even this isn't a formula so I think we're safe so no formulas okay so I can just delete all the columns except for that column and I don't think it'll mess anything up let's let's delete the totals first though even the total doesn't anyway so let's delete the total I'm going to select this and right click and delete the totals and then I'll delete the columns I don't need I don't need column b right click and delete I don't need columns c to g right click and delete so this is all I need now notice that this one is not formatted the way I would like it to be let me try to format see how it has this triangle here that's going to mess it up because it's not in number format really it's number stored as text so that's not good I could try to format paint and see if that works to get rid of that I'm going to put my cursor over here and format paint I'll format paint the entire worksheet format paint the entire worksheet it still didn't get rid of the green stuff so and that's going to mess up my formatting so the next thing I can do is I can click on it and I get this drop down and I want to convert it to a number if you just double click on it it'll go away as well but I'd like to do that so I don't have to do it to every cell so I could select this whole thing from here down to here that all the ones that have that green thing on it that's and then just do them all at the same time hitting the drop down convert to number that's what we want to see all right then I'll select the entire page again right click on this thing and let's format the cells let's make it currency negative numbers bracketed and red get rid of the decimals don't need them we don't want a dollar sign no dollar sign and okay let's make it bold home tab font crew bold it is bold okay so now I'm just going to delete all the detail so this is the design stuff here under the design here's the parent category all the subcategory and then a subcategory of a subcategory this is a the total of the subcategory of the subcategory subcategory subcategory subcategory and then the total design which is not a formula it's just a hard-coded number so I could just delete basically everything from here to here and I just want the total design so I'm just going to right click and delete all of that and then I'm just gonna do the same thing landscaping this is the parent subcategory subcategory subcategory subcategory and then subcategory and then subcategory of a subcategory subcategory of a subcategory the total of the subcategory of a subcategory and then the subcategory another one in there and then the total of the subcategories which include the sub subcategories in there. So this is not a formula. So I could just delete everything from like here to here. I don't need any of that. I'm just going to delete all that. And then this is the same thing. Here's the parent. And here's the the subcategory. Here's the total. So all I need is the total. Delete this to do it. And then I have the not specified which is its own category of 90. So let's let's check that out. Let's sum it up and just see if I messed anything up equals the sum. See if this still ties out to what's on my report over here. It should be at 10 to 80 oh five. So let's say is that what is 10 to 80. So notice now I just want to check that this 10 to 80 oh five does not tie out to what's on the income statement which should be which is that total income is actually at 10201 about. So I'm actually going to fix it here. I'm just going to say okay it should be 10 oh one. And what I'll do is I'll just dump it into the not specified area. So I'll delete this for now. It should be 10201. Whoa. Wait a second. It should be 10201 10201. And so this needs to be and then if I subtract these two out. Then it's I'll have my plug here of 169. So now I've tied it in exactly to that. What did I this should be 10. This needs to be a negative. That's weird. That's kind of ugly. I don't really like that. But we'll do that ties in at least it ties in exactly 10201 to the to the total. So that's we're okay with that. I'm going to be okay with that. It's kind of weird having a negative. I don't know. I'm not comfortable with this. I'm not comfortable with this. Okay. We'll just push forward here. This is the idea. And then we could we could then sort this. So I'm going to select these. We can then go to the data and then filters. We could sort it that way. But I like putting a table in it. So I'm going to insert a table and then make a table. And this will be the items. I could say inventory items or items. And then and then I'll just put item dollar amount. I'll say dollar amount. That's probably not the best label but I'll do that. And then I'm going to say then we'll we want this from going from highest to lowest which is already in that order. So I'm sorting from Z to A. So that looks good. And then we can simply make our charts. Once again we have a total let's put a total on it too. So we can see what the total is. So I can go home. I can go I can go table designs total it totaled it totally totaled it. And then we could do the percent. Let's do the percent of total which is simply going to be this divided by the total. Excel will put it all the way down because we have it in a table format and then home tab number percentify so we can recognize and then we'll sum it up equals the sum. I could just say alt equals and it doesn't do it because it's in a table. Let's just hit the drop down and say we want to sum it. OK. So there it is. And then I can do a pie chart just like we've done before. We'll select these items and say let's make it into a pie chart. We're going to go drop down. Let's make it into one of them. One of them new fancy fangled ones they have these days. One of them new fancy fangolors. So we have that one. And then I could say maybe I want the legend. I want the legend on the right. I want the legend on the right maybe and then the data labels that's in numbers. So maybe I want the data labels in percent more options data labels as a percent getting rid of the values. OK. OK. And then these last two are kind of on top of each other here. So it probably I should probably combine these last two. But I'll just all wait. I'll just pull them out here so that you can see them. Even if I combine those last two it's still negative. Oh. Oh. What did I do there. How could it be negative. Whatever. I'm going to. OK. So then we can do and now if I wanted to make that chart the other way I could just select this control shift. So I have non adjacent or I'm just going to shift or I'm holding down control as I select both of them because then we could have inserted it just with the pie chart and done the fancy thing over here. And then if I added the labels it would already be in a percentage format. Right. But the pie chart lets you do that the other way. Then I can also say I want to fancy another bar chart. Let's just do the bar chart the other way the other charts we can do are the bar charts like this. We did that we haven't done like just a normal bar chart. We did the fancy one like that. We did we always do a 3D on its side 3D. Remember they have other options here too. So you could try to use one of these where they have this fancy one. But let's do the 3D on its side. We haven't done that one yet. So OK. Either of these is fine. And then we can change the color if we wanted to. And so then there's that one. There is that. And then I can select the drop in down. I can put the data labels in there if I wanted to. OK. So something like that you know. And then I can put so now we can put this on another tab to the right. So that so that I don't want my data in it. So this will be my data tab. I'm going to actually put the grass to the right. So I made a new one. I'm going to double click on this one copy it double click on this one paste it but without the data at the end. And then I'll just cut this control or cut right click cut right click paste it. And then I'll take this one and I'll cut this one right click cut. And then we'll paste it here as well. I want to see I want to try to position that. So I'll go tab to the right tab to the left. Why I want to make it landscape. This one particularly looks like it might look good on a landscape type of Vista. So we'll hit the drop down and select landscape. It didn't do it again. Let's it's because I'm on it. That's why it's doing that. I figured it out. I figured it out. So let's make this one larger. This one is more of a box. So it doesn't really need the whole landscape Vista possibly. But we could still do it. And then maybe I can make the key could be bigger. How would I if I want to put the size. Oh that's way too large something something like that. And then I probably want to make these bigger too. Since I'm oh yeah that's impressive. The title is too small. Why do you have this little teeny tight. Okay whatever something like that. You get the idea. And then I can put this one is design. This one has been this one's made for landscape. Because it's a long I'll put that one like in the middle. We can probably center it in the middle of the page here. Okay. Okay. Let's check it out. Now now the problem is this this tab is still going to print out with this data here and I don't want that. So I'm going to show you we've seen it before but we'll just check that out. So if I was to print this print in the entire workbook. I can't do it because I'm on the graph I think. So if I go over here and I click off of the graph then it's going to let me do it. That's the problem. That's the problem. You're the problem. Okay. That's was a little harsh. Let's go to the entire workbook. And then if we scroll down then so these are my graphs. But then when I get to this graph that's a nice one. See it has that thing there. I don't want that. I don't want that. And then then it has my graph. And they're on two separate pages which I put on two sides this time instead of one above the other. So let's go back on over and say okay I'm going to fix that. I'm going to hide this one right click and hide it. And so now I should be able to print all of this stuff on one PDF file. Give it to the client in one attachment or one one file in a cloud drive or however I want to distribute it. And it'll look this is what it'll look like. It'll have all of this in it. And then like the graphs. There's the AP the sales. And I should make the titles better and whatnot but you get the idea. That's the point. It's the idea I'm after. We'll hit the print button and then I'm going to save it to here. Now before I look at it in there for the final look note that if I select from here I hold down shift to here there's hidden there's hidden stuff. So you can right click and unhide to see all of the hiddens. Now hold down I held down shift so I can select all of them at one time and then OK. And now all of the data tabs have been revealed. So the mystery that we went behind the curtain and we saw Mr. Oz or from that one lady that has the shoes that she taps together that are read that movie. Let's open up the PDF and close up. Okay. And then if I look at it now I know we can still format it better. But but again but I this is a landscape view it's still vertical. That looks good. That's good. And then we get to our graphs down here which is like someone's reading this and they're like that's good. That's like the norm of what I would expect. That's the minimum I expect from the oh my goodness. So look at there's graph. They put charts in the look at that. I don't know what it even means but it's it's at a mosa. It's just beautiful. You got the AP. Wow. Give this guy whoever is our bookkeeper. I think they deserve a raise. I'm just going to I don't care what they put on the invoice. I'm just going to give them like a thousand dollars right now. So in any case there it is.