QuickBooks Online 2021 sales by product or service or item graph with the help and use of Excel. Let’s get into it with Intuit QuickBooks Online 2021. Here we are in our QuickBooks Online test drive a practice file, which you can find by searching in your favorite browser for QuickBooks Online. At test drive, we’re in Craig’s design and landscaping services. Going down to the reports. On the left hand side, we’re looking for the sales reports.
00:28
Once again, this time breaking it out by item, or service or product things that we sell, we’re going down to the sales and customer section down below. Last time, we took a look at the sales by customer. This time, we want to take a look at the sales by product or service, selecting the summary report. So we want the sales by product service summary. Product slash services is not as easy to say is like items in the desktop version that items sales by product slash service Summary Report.
00:59
So we’ll select that item, or that report range change up top from a 101 to zero to 12 to I hit a space instead of a tab, so 1231 to zero and then run that report. Now we’re going to have the supporting report here is supporting or this report is supporting a line item or line items on the income statement. Let’s open up the income statement as well. I’m going to duplicate a couple tabs up top, right clicking on the tab up top, duplicate one time, I’m going to duplicate again, right click on the tab up top duplicate again, we’re gonna have the balance sheet on the one to the right, we’re going to then going to have the report we’re working on in the middle and then another tab just in case we want to do other stuff so that we have the freedom to do so.
01:44
So we’re going to go back to the one on the right. And then we’re going to be opening up our reports down below on the left hand side. And then we’re going to be opening up our profit and loss. I said balance sheet before we’re opening up the profit and loss report here. And this is going to be then range change up top from a one a one to zero to 1231 to zero and run that report. So now we have our profit and loss, I’m going to close the hamburger hold down Control, scroll up just a bit to get us up to that one to five. Now on the income statement, the profit loss income statement p&l, I’m going to shorten up the income line item.
02:21
Typically, we don’t have a lot going on in the income line item in terms of separate accounts. But we hopefully have more money going into the income or revenue line item than the expense line items. And then we’re resisting the urge to set up income accounts that are going to be by customer or by item, so that we can then shorten up the amount of information on our profit and loss report and still have that other added detailed information with other reports. So we can break out, for example, our income or revenue lines, in this case by item by things that we sell.
02:55
And that’s what this report is going to be doing over here. So I’m going to scroll back down, if we go all the way to the bottom here we got the 10,000 to 8005. That should tie out to the 10,200 it’s a little bit different. Notice it can be different because the QuickBooks system is not going to force these two things to be in alignment as much as they do for say accounts receivable and accounts payable. As we look at the related, you know, subsidiary reports of like an accounts receivable, aging accounts payable, aging, and so on.
03:27
So in other words, if I was to open up and look at our data input sheets, if we’re if we’re using the invoice, or the sales receipts to create our sales items, then of course, we’ll be generating the products and services within them. And these two reports should match. If however, you’re just depositing things into the checking account, and recording them to income without selecting an item for them, then QuickBooks doesn’t have the information to make these added reports.
03:57
So it’s just something to keep in mind. So that when you create your accounting system, you see the pros and cons of each of those decisions. Whether you can have a more simplified process, possibly by going right to the to the check register, or rather, you’re going to enter the data input forms, such as invoices and sales receipts. Okay, so we’re gonna close this hamburger backup. And then we’re going to go back to this tab. So this is going to break it out by the things that we sell. Now I’m going to close this up that being the things of inventory items and or service items that we sell. So these are all the items that we have.
04:31
And we’re going to do our similar kind of thing here. We’re going to export this then to excel. And then we’ll try to create some charts based on the items that we sell. So then we can look at a visual of our revenue broken down by the things that we are selling, which could be a good added detail to a report or if we’re given a presentation or something like that. Now this one has a lot of sub sub categories. So we’re gonna have to make some decisions on how we want to be breaking this out. We got a little bit More formatting that we’ll need to do once we get it into Excel.
05:03
So if I go back to excel here, I’m going to make another tab. This is the Excel sheet we’re working on, I’m going to make another tab, this is where I’m going to start the data. Going to go back then to QuickBooks, let’s export this report by going up top and simply exporting the report, it then opens up down below, and we’re gonna open that up. And then we’re going to copy the data. So I have to enable the editing, I believe, yeah, enable the editing. So I can edit it. And then I’m going to select the whole triangle, and then hold Ctrl C to copy it on the keyboard Ctrl C, we’re gonna then go to where we want to put it, our worksheet here and go to Control V.
05:43
And then I’m going to call this sales by item, see how much smaller that is, that’s product slash service, but sales by item, and then data, this is gonna be the data file. Okay, so then I’m going to, I’m going to remove the header, we don’t need the header. So I’m going to select these rows and just right click on them and delete them all together, I’m going to hold down Control, scroll up a little bit. So we get up to a little bit larger information. Here. We are then at these one, four, or 5%, or one, four, or 5%. And then what we need is this middle column, so all I really need is this middle column. So I’m going to try to trim this data down, I’m going to get rid of this line 33,
06:22
I don’t need that, gonna delete that. And then up top, I don’t need any of the columns. Now there’s no formulas in here that are going to get messed up if we delete the columns, I do not believe. So I’m going to select column B and just delete that entire thing. We’re going to select column C to G, and just delete that entire thing. So we’ll just delete that. And then we have some questions as to how much detail Do we want, because we have the design categorization and the total designs down here. And then we’ve got the trimmings or the landscaping, total landscaping, and then total pest control, and then not specified down here.
06:57
So we could just break down to those main those main categories or we can further break down to the categories within them. So for now, and you may not have any of the all these sub categories like this. And you could also create kind of reports, that would just be breaking out, say that design information and break out those into a another graph. So within design, you might have fountains, concrete and whatnot, what are going to be the most common things in there, you can create a graph that would add up to this total, like a pie chart and whatnot.
07:28
And you could do the same for the landscaping. So you can say for example, you know, I can, I can highlight all these and make a pie chart, although I would have to trim these down, because there’s another sub category down here. But I’m just going to make the main three categories. And so we’ll pick that as the ones so that what I want is the total design. So everything else I can kind of delete. So I’m just going to say everything from up top down to here, I’m going to remove, I’m going to delete, and then we’re going to say landscaping. So I’m just all I want is the total landscaping. So I’m going to go on down to here, just delete everything else, delete.
08:04
And so I just got the total. And then I’m going to say pest control, I just want the total pest control. So I’ll delete everything else. So let’s delete that. And then the non specified, I guess I have to keep that if I want to, if I want to tie out to the total down here. So if I was to then double check the total, we’re gonna say this equals the sum of these items, and it’s adding up to zero, that’s unusual. Let’s try to format these a little bit differently. I’m going to copy these cells, I’m going to put them down here and I’m going to paste them 123, I’m going to paste them 123 and see how they’re kind of formatted to the left here.
08:40
So something went a little bit wrong with the formatting because they should be right aligned. So if I double click on them, and hit enter, now they’re going to be now they’re aligned to the right, which is seems correct. And then I’m going to say the sum of these items is going to add up here. So sometimes the formatting can get a little bit funny when you when you copy it over here. So there we have it. So that looks correct the 10,002 80. I’m going to delete these items up top, right click and delete those items up top, and there’s just our data, then I’m going to delete the total for now let’s delete the total for now. And this time, let’s try using the filter.
09:20
So if I select this data, and I go to data up top, and I just use a filter, then I can sort the data by the filter, I’m going to go from Z to A. So similar kind of thing that we saw before, then you can take the filters off if you if you choose. And then of course we can add our graph. So I can highlight these items, go to the Insert. And we can then go to the pie chart. And then we can add a graph. So there’s our good old pie chart. And we can then say that we want to be breaking this out maybe the legend once again. Let’s have the data labels, data labels outside like that.
09:59
And so it looks something Then looks something like that, I’ll pull this over here and that over there, we could put this here. So something, you know, something like that we could change the color of it. And so that’s going to be, you know, not too much activity going on here. But this, you can see how we kind of trimmed down the data to what we need, and make it make a nice graph out of it. So you can export what you need, make the graph out of it, and whatever kind of format you would like. Again, you could put the graph kind of on top this time.
10:30
So instead of making another tab, let’s just try to put the graph on top and see what that would look like if I printed it. Now when I print it, I’m going to be off the graph on the sheet, that I’m going to go to the File tab up top, and I’ll just say print. And we’re going to print it to the cutepdf printer.
10:46
Again, we’ve got to print the entire workbook. So the entire workbook, there’s 49 pages, if I go all the way down to the bottom, then just to look at the new stuff. There’s the new stuff right there. So there it is, and I don’t and I just put the graph over the data. So I didn’t have to make two tabs and hide a tab in that case. So here’s the graph above it, that one had a data tab.
11:08
So if I wanted to print this without the other data tab, I would have to hide the other these other tabs up top, let’s go ahead and do that I’m going to go back on over. So so I don’t have to hide anything for this one. This one, I made a data tab. So I’m going to I’m going to hide that tab, I’m going to hide this data tab, I’m going to hide that this is going to be hidden, and then this one, I’m going to hide the Data tab. And this one, I don’t even need to hide the Data tab.
11:36
Because it’s data and graph, you know it’s data graph. It’s got the data and the graph. And we did it we didn’t need we don’t need to hide it. Because it’s we’re just going to put it over the top. So then I’m going to let’s try this again, we’ll go to the File tab, we’re going to go to print, we’re going to print it to the cutepdf printer, we want to print the entire so now it’s just got the chart, if I go back on it, that’s because I’m on the chart, I gotta be off the chart, and then try it. Again print.
12:11
So now I can print the entire workbook once again, entire workbook. 41 pages scrolling down to the bottom, we’ve got our chart, which is now a little bit bigger fitting on a page that doesn’t have a title. But you know, I’m not, we’re not getting too detailed, just set an example. And then the data tabs have been deleted and removed. Looks beautiful, full of wonder. There’s wonderfulness in it. So let’s go ahead and print it then. gonna print it. And it’s going to ask us where do we want to put it’s got 41 pages. So you got to give it a little bit of time.
12:49
Because this is like this is a work. So then we’ll save it over this financial statement one say Yes, please. And then let’s open it up. So we can double check that last report, just the last one. So I’m going to try to scroll all the way down to the bottom. So we can double check the one that needs some another check on it. So there we have it, there it is, and then the ones above it, the Data tab isn’t there anymore. So looks perfect. And then we’re gonna go back to the Excel sheet over here and just unhide those ones that we hid. So we’ll get I’m going to put my cursor on the first one and then hold down shift. So they’re all like, brighter, brighter than they were before.
13:32
And then we’re going to then unhide unhide unhide one at a time. There’s that one. And then I’ll right click and unhide that one, just so you can see it when you go in there and you don’t have to unhide them. I don’t want them to be hidden for you. And then unhide this one but if anybody does try to hide these things, this is how you find them. Well, I don’t know where they hide, but you can pull them out by doing that.