 QuickBooks Online 2023. Sales by customer graph. Get ready to start moving on up with QuickBooks Online. We're going to be using the free QuickBooks Online test drive searching in our online search engine for QuickBooks Online test drive picking the option that has Intuit.com and the URL Intuit being the owner of QuickBooks. 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. We're going to be selecting the United States version of the software and yes verify that we're not a robot. I am C-3BO human cycle relations. My parts are showing. Zooming in by holding down control up on the scroll wheel currently at the one two five percent on the zoom in noting that in the cog dropdown we're currently in the accountant view as opposed to the business view we'll try to toggle back and forth between the two views so you can see where stuff is under both of them. We're going to right click on the tab up top to duplicate it as we do every time because we're going to open up our reports in them right click in the duplicated tab to double duplicate it going back to the tab to the middle and the reports on the left open up the big balance sheet one of our faves on the reports as that's thinking tab to the right reports on the left open up the profit loss the P&L the income statement close the hamburger otherwise known as the hand boogie and changing the ranging from oh one oh one two two tab 1231 two two tab run it to refresh it and tab it to the middle to do the same thing close on the boogie ranging to the changing oh one oh one two two tab 1231 two two tab and run it that's the setup process that we do every time I know I did that fast but that's because we do it every time these are the major financial statement reports most other reports giving some information about one or multiple line items on these we've now been generating reports in order to export them to excel and then use excel to create charts which we might add to our package of reports we might provide to a client or a supervisor at month end quarter end year end for example anytime we see like a sum of numbers and a total we can make it into a pie chart or a bar chart if we want to so we could break out these assets for example as a percentage of the total assets quite easily we could do the same for the liabilities and the equity we have done the same for the sub ledger account for accounts receivable by customer breaking it out into a pie chart we've done it for accounts payable sub ledger breaking it out into a pie chart for the sub ledger of accounts payable by vendor now we're going to the income statement where it is quite common to try to break the income out by customer or break the income out by by by customer or by what we sell by item by inventory item and service item now you could also break it out by gl account and this one has a significant amount of gl account so that might be something worth doing so you can make a pie chart that way but most companies don't have that many gl accounts so but they do have a lot of things that they might sell which are listed in the item list service items inventory items which are in a sub report and they have a lot of customers so we can break it out by customer and then of course you could do the same for the expenses the expenses do typically have a lot of gl accounts so breaking out all the expenses in a pie chart by by gl account works that would make sense or you can break it out by vendor but oftentimes we're of course focused on the revenue side of things that's the exciting side of things so we want to break that out this time by customer now remember that these sub ledger reports are only going to be functioning and working if you're using the proper sales forms so if I hit the plus button you'll recall that there's different ways you might have a sales cycle set up and that will be dependent upon the type of industry you're in if you're just getting paid by youtube you're getting paid from gig work platforms are paying you you might wait till it clears the bank and then just record the revenue with a bank deposit form the deposit that works just fine but the deposit form is not the form designed to record sales such as the invoice is and the sales receipt and therefore you're going to lose some of the sub ledger details so you're not going to have the sub ledger detail to run these reports as easily if you're in a cash based system but have a cash register it's likely you're going to use the sales receipt and then deposit the sales receipt is the one that if you're tracking the customers as you do the data input will give you that added information and if you add the items as you do the data input it'll give you the added information for a sub ledger to run sales by what you sold invoice the same thing on an accrual system if you use an invoice you should have the customer and the item data to run these sub ledger reports and then you can make graphs out of them and whatnot so let's open it up let's open up the report right click in the tab up top i'm going to duplicate it again so that we can open up another report and we're going to find that on the left hand side i'm going to close up the boogie and scroll down to the sales now there's a couple sales reports here you got the sales by customer summary but that has like the cost and goods sold stuff in it what we want over here is the sales by customer the sales by customer not the income by customer that's the easiest report to work with so i'm going to open it i'm going to change in the range in from mode one oh one two two tap twelve thirty one two two tab run it to refresh it there we have it now it's got some sub accounts down here and those are going to mess us up i don't really want the sub account info in there so i'm going to collapse the data and now we've just got a set of numbers and a total at the bottom perfect pie chart potential and so notice that the total down here doesn't tie out exactly to what's on the income statement if i go to the income statement for example i've got 10 200 instead of 10 280 or whatever it was you want to be aware of that it might not tie out because quickbooks doesn't force you to add a customer every time you post something to an income account however if you are using the invoices and sales receipts then it it should tie out right but if it's a little bit different then you want to recognize that and possibly adjust it in excel in order to run your reports to make it tie out possibly and and then note that or at least be able to note that so when people have a question you can answer their questions you know related to it so that's just something to keep in mind remember that the the accounts receivable on the balance sheet and the accounts payable do tie out almost all the time because quickbooks forces you to add the proper information customer and vendor every time you hit the these two accounts so that the sub ledger breaking out by customer and vendor will tie out they don't do that for the sales side of things which has its pros and cons i think it's a good thing that they don't really but it means that the sub ledger won't always tie out if unless you enter all the data a certain fashion okay so noted so now we're going to go to the tab to the right let's export it select in the drop down we're going export to excel you do need excel to export it of course we're going to open it up in excel there it is i'm going to i'm going to enable the editing now i'm going to pull this over to our other excel report that we've been putting all of our data into so that we can practice putting our pie chart along with all of our other reports possibly being able to print them out all on one pdf form if you don't have that other form that's okay you could just use this one to practice with but i'm going to take the whole thing put in my cursor on the triangle right click and copy the whole the whole thing and then i'm going to go to this report that has all of my reports on it go to the last tab and then hit the plus button i'll put it in a one or i can select the entire sheet right click and paste it and then i'm going to rename it double clicking down here this is going to be the sales by customer data this is going to be the data tab because i'm going to create another tab where the actual graphs are going to go and then i'm going to hide the data tab so that i can print all of this stuff on one pdf file without the data tab but just the nice charts that we want to be adding so i'm going to hold control scroll up on this a bit and we've got all we need here but i'm just going to clean it up like i typically like to do i'm going to change the font so i don't have this funny font situation in other words over here i'm in colibri 11 over here i'm in ario 8 i want to pick one everything should be the same i don't like this funny business with all this different fonts i'm going to make it the standard excel colibri 11 by going to the home tab paint brushy that cell and then i'm going to paint brushy the whole thing with the triangle so everything's the same and then i'm going to right click and then format it the way i want to format it because this is my this is this my excel worksheet i'm going to go to the currency down below let's put some brackets around the negative numbers get rid of the dollar sign decimals those need to go to and okay all right and then i'm just going to delete all the stuff i don't need all i want is the raw data i don't want the i don't want the title so i'm going to put my cursor on row one and drag on down to row five let go right click on that stuff and delete it get out of here and then i'm going to go down i don't need the total i'm going to put my cursor on row 23 drag down to row 30 or whatever right click and delete all that stuff so that looks good and then sometimes these cells still have a formula in it it still says equal so i just want to make sure there's no formula i just raw data so i'm going to put my cursor on column B and then right click the selected area and copy it and i'm just going to paste it right down on top but this time just with the 123 so it'll paste just the values only just the raw data don't give me any formulas just give me the data there we have it and then i'm going to make it a skinnerize this i'm going to i'm going to put my cursor between the B the C and skinnerize it dragging to the left that's how that's how you put a cell on a diet in excel you just drag it to the left like that it's really easy so then we need to sort this from highest to lowest Z to A as they say in excel sorting terms now you could do this by selecting this whole thing and then go to the data and then go to the filters and then you got your filters but i don't like to do that because sometimes i think stuff gets jumbled up i want a table i'm going to add a table so you know that you know by now that i'd like that i like the table thing so i'm going to put my cursor i don't need to highlight the whole thing i could just put it anywhere in the data as long as the data is all stuck together and then go to the insert tab and then table and it'll pick the data that we want typically it should and then we're going to say okay you want to double check these things and then i'm in the table design i'm going to add a total to it the total there it is 10 to 80 does that still tie out to what's in quickbooks did i mess anything up yet i'm going to close this out let's check it out 10 to 80 so i'm still good it's still good it's still good so then i'm going to hit the drop down and sort it from Z to A top to bottom boom so now there's too many things to make up a pie chart but i'll just show you just to show you what the issue is if i select just the meat not the total not the header columns just the data and then i go to insert i can make a pie chart out of it by just selecting the pie chart and boom but the slices you see you see the problem the slices are too small no individual can get any sustenance that's at all satisfactory from a pie slice that's that that's that small so we have to fix it so i'm going to close it here now note before i do that just what it is doing with the pie charts is just making a percent so i'm going to i'm going to skinnerize the C column putting my cursor between the C and the D and then in D2 i'm going to say this equals this number divided by the total that's all we're doing to make the percent i'm going to put my cursor on column D and home tab number group percentize the whole column put my cursor and drag down with the fill handle and then sum it up at the bottom equals the sum and that should be 100 percent so you would think you might need to use this column right to make your pie chart but no you just choose the data and of course excel does that calculation for us but there's too many there's too many so that we have to say some of this stuff isn't going to make the cut so we're going to say one two three four five six seven eight let's stop it like there let's and say let's say that all this other stuff underneath is it's just going to be another they didn't make the cut you're not going to be in my pie chart because you're not a good enough customer buy more stuff and you can make it in my pie chart so anyways so we're at the two nine two one so i'm going to put that in here two nine two one call this other and then all these people down below they're off the they're off the team you're off the team you guys are out of here until you start picking up your sales buying more stuff so i'm going to delete that get out of here and then i can still see the total is at the two ten two eight one that's pretty close here i rounded it i did a rounding thing because i uh i'm not showing the pennies here so that's good i'll keep i'm okay with that and then i can create a pie chart so now let's select the data and see if the slices are good enough to serve to people insert they have to be big enough i mean to serve to someone it's not worth putting it on a plate if it's if you got a skinny slice okay good enough good enough some people don't like super big slices so we're going to say there we go we could pull some of the and then of course you can adjust this you can get rid of the title you can do what you want you can change the color and so on you got a bunch more flexibility in here than doing other stuff that's kind of a neat little color i don't know i'm not really good at the color thing but then you can select this whole thing and then uh total and or insert you might have another chart and you might say what do you recommend excel for the charts and it's like we saw that one last time we did that one so let's just this one isn't too fancy or anything but it's got that diagonal stuff on the bottom you could get rid of the title you could you you could and you could use whatever you can make it you know like that or something i can change the colors of it i should probably have the colors match in some way and so on and so then i'm going to put this this charts on another tab so i can hide this tab and then print everything out without all the data because i don't want to see the ugly data in my when i give it to my clients so i can then hit the plus button here and double click on it i'm going to call this sales by customer graph and then i'm just going to go back to the left and say i'm holding down control to select those two right click and copy i'm just going to put that over here in a one paste it i'll check i usually go to the page layout so i can see where it fits on a page and then you've got these dotted lines i don't think that's big enough so i'm going to change the orientation you got to be off the graph to change the orientation by going to the page layout and i want to orientate it landscape to give me more detail there's not enough room there's not enough room for my for my flowers or my lettuce i need to landscape it this needs some land i need some hills with some tears or something so i landscaped it and so now i can make this large enough boom so that looks pretty good you know again so you could put a title up top you could put a title on the graph but you could put a title on the on the whole page which might be an easier thing to do you could just call these you know sales graphs up top so it depends on what you want to do that's the just the general idea of how you put some graphs into your presentation and then i can hide the data tab by clicking on the data tab right click and hide it i could do that to this data tab right click and hide it i could do that to this data tab right click and hide it and then i want to be on something other than a graph when i go to print it to check it out file tab print and then i'm going to use the cute pdf printer it's free i'm not advertising for them but that's just what i'm using to make a pdf out of it i'm going to choose the entire worksheet instead of just one page and then if i scroll through this i've got all of my reports that i can print out on one worksheet and i have more flexibility now to put those graphs in there as well notice the other option that you had to put everything on one pdf within quickbooks which is a neat option is to go to the reports and quickbooks has that nice management report which is great but if you want to make any changes to your reports like adding grid lines or changing the font slightly or you know making their title black and white as we've seen or adding these kind of graphs which you can do in excel then it's a little bit more difficult i don't think you can add them to this kind of default report i mean possibly i mean if they could set something up where you can upload you know a page it would be nice but i don't think you have the capacity to do that at this point in time so this is a great little tool but it's not as flexible as as if you were to use excel and word to make any kind of adjustment and basically group things together would be the general idea so you want to think about what's the best option for you all right i'm going to go back i'm going to unhide i'm going to do that by putting my cursor somewhere over here holding down control and go into the to the to the right so those are selected right click and unhide and then i'm going to hold down shift and select all of them and say okay so now that they should all be back my data tabs are back so that's how it goes if i go back to the to the quickbooks hit the cog drop down i don't think we've been anywhere special but if we just look at the business view we've just been hanging out over here in the reports which in the business view you got to go from the home get things done in the home page to the to the business overview and the reports to find that