00:00
QuickBooks Online 2021. net sales by customer graphs with the help and use of Excel. Let’s get into it with Intuit QuickBooks Online 2021. Here we are in our free QuickBooks Online test drive practice file, which you can find by searching in your favorite browser. For QuickBooks Online test drive, we’re in Craig’s design and landscaping services, we’re going to go down to the reports on the left hand side, this time, we want to be considering our sales items.
00:28
And it’s always good to be breaking down our sales items into different groupings in different categories. This time, we want to break down the sales items by customer who we sold to. So to do that, we’re going to be in our sales and customers information. And we want to take a look at our sales by customer and we want the summary report. So we’re going to be looking at the sales by customer summary, we’re going to open that up, we’re also going to be opening up the income statement because this report will of course, support line items on the income statement. gonna change the date up top first, let’s do a range change range change a 101 to zero to 1231 to zero, we’re going to run that report.
01:06
And so there we have it. Let’s go up top and now make a few duplicates of our tabs. So we’re going to right click on the tab duplicate, I’m going to make another one right click on the tab duplicate. And then I’m going to have this one on the right Dr. Profit and Loss one in the middle, that’s going to be the report we’re working on the one on the left, it’s just going to be a tab open in case we want other stuff that we need to do that we have the option to do.
01:30
So we have our options that are open, we’re going to then go to the reports on the left hand side while we’re on the tab all the way to the right. And then we’re going to open up our P l profit and loss or income statement range change up top from a one a one to zero to 1231 to zero, running that report, close in the hamburger up top holding down control scrolling into that one to 5%, one to five, we’re going to be looking at then the income items. So these income items up top.
02:00
Now we’re breaking out that into income items in different ways. And remember that when we think about the income items on the income statement, we typically only have I’m gonna collapse this item, one or two line items for income most of the time because we only do one or two things, we’re not going to get the detail typically, in the income statement for things like who we got money from customers, or the detail of the items, what we did services, or or the things that we sold inventory, because we’re going to use other reports to do that.
02:31
One of those reports being the sales by customer, obviously, the income being the performance in terms of how we did in terms of revenue generation through the period, very important number. And that’s a great thing that we can build graphs on. So we might want to when we do our presentation purposes or provider information to the client or to our supervisor or whatnot. At the end of the period, we might want to break down that income number and say, Hey, here’s where your income is coming from, here’s your here’s your best clients.
02:58
Let’s do a visual of that. So we’re then going to go to the sales by customer summary, closing the hamburger on the second tab, scrolling all the way down, we get the total of the 10,000 to 8005. Note, if I go back to the income statement, we’re currently at 10,000 to 8877. So it’s a little bit different there. And that’s because once again, you can it’s not quite like the accounts receivable and the accounts payable on the balance sheet where the IRS with the IRS, where the QuickBooks will basically force you not to be able to enter something to those accounts accounts payable and the AR without selecting a customer or a vendor respectively.
03:37
For accounts payable, I’m sorry for accounts receivable and accounts payable on the income line item, you can do that. So it’s possible for you to enter something without selecting a customer. And that would mean that you’re not going to be using a form. So if you didn’t use like an invoice or sales receipt, then it’s possible that these these things don’t line up. So just note that that is possible. But if you make all your sales with invoices and sales receipts, that of course have customers attached to them, then they should line up and you should be able to generate this report.
04:09
So then we’re going to go back on over here. So I’m going to go ahead and note that and then we’re going to print this out, we’re going to export it to excel, I’m going to go to our other Excel tab, which is this one, I’m going to add a new worksheet, we’re going to add a new worksheet, I’m going to pull it to the end, where we can put this new graphing stuff, I’m going to close this other graph I have here, I’m gonna close that graph because we don’t need that one.
04:30
And then we’re going to go back and export. So let’s export this thing going up top, exporting it to excel thing pops up at the bottom because I’m in Google Chrome. And that’s what that’s how Google Chrome does it. And then if you’re if you’re on something else, it might be up top to the right. We’re going to open that up. Then I’m going to enable the editing. We’re going to trust QuickBooks not to infect my computer, and whatnot, which is hard to do these days, but QuickBooks I kind of trust QuickBooks, so we’re gonna visit Right click here, and then copy.
05:04
We’ll copy that. And then we’ll go on back on over here and we’re going to put it on the sheet six, we’re going to put it in a one, we’re gonna say Ctrl V, there’s going to be our baseline data. Double clicking down below, we’re going to say this is sales, by customer data, this is going to be our data tab, holding down controls, scrolling up a little bit, so we’re at like the one to 5%. Now, one to 5%. And then I’m going to do our same thing, I’m going to clean this thing up, we got a little bit more data than we seen in the last graph that we worked on. So we’ll clean it up a bit and just double check it that it’s all good.
05:38
So I’m going to then delete these ones up top, I don’t need any of this, I’m going to right click, I don’t want that delete, not useful. I’m going to then format everything in the same format by clicking somewhere off the section that was like formatted, so that I can then go to the home page clipboard Format Painter, and then paintbrush the entire worksheet. So now it’s all got one standard formatting, then I’m going to format it the way I want to format it, because this is my worksheet, right clicking in on it, and then we’re going to format the cells go down to the currency, we’re going to put brackets for the negative numbers, remove the dollar sign, make it currency, we’ll keep the decimals.
06:17
And then Okay, so that looks good. And then all these ones that have this subtotal item here, these sub accounts, we’re gonna I don’t want the sub accounts, because that’s going to double up my total. So this one is a formula, I’m going to copy that formula. And I’m just going to put it up top, I’m going to put it up top right there, but I’m going to paste it 123, pasting it 123 values only, in other words, and then I’m going to delete these other three, which are all subtotal lines. And you could see how much longer that makes a report those kind of subtotal things.
06:48
So then I’m going to right click and delete. And we’ll go back on down. And we have that that same thing happening right there. With these three lines, so I’m going to do the same thing, I’m going to copy that one, gonna put it up top where the name is right click and paste it 123 values only. And then delete rows. 19 and 20 are these two items, right click, delete, and then the total. So I think that’s good. And then the total, I’m gonna re total it by using the trusty sum function. Most Famous function of Excel, well known 10,002 8005. Let’s see if that ties out.
07:29
I’m going to delete the other one that we had open here. This one, I’m going to delete that not needed anymore. And let’s just check if our total 10,000 to 8005. Did I mess anything up? No, I didn’t still looks good. Now I’m going to delete the total column for right now I’m going to delete it for right now. Now that I checked it, with double checked two checks, then I’m going to add our our table on this, I’m going to select these range, I’m going to go to the Insert, let’s put a tables and put a table on that. slap a table on it. And then we’re going to then use our drop down and I’m going to sort it from top to bottom, top to bottom.
08:15
So there we have it. And then now if I sum this thing up, see if it still adds up. See if it still adds up to what it should should 10,002 80. So that looks good. Now this thing’s too long, really for a good for a good pie chart. So we’re gonna want to break down like the top 10. Maybe that’s usually what we do, it’s a good round number, when you when you’re trying to impress, you know, get people’s attention. These are our top 10 customers, top 10 customers by sale 1-234-567-8910. And then everything from this one on down, we’re going to put that into a total, that total if I just highlight these is coming out two to 208.
08:56
So I’m just gonna say right, and then everything below that, I’m just gonna say it’s going to be 2208. And then we’ll delete and then these rows, I’ll just remove these rows altogether. These people are not worth being on our pie chart, because they didn’t sell enough, they buy more stuff you can see on our pie chart. But until then, you’re just going to be part of the other category, other. And then we’re going to I’m going to make this a little bit smaller. And then we can make our pie chart here.
09:25
So we’re going to go and highlight our center data. And we can go then insert. We want them to pie chart once again, let’s do this fancy donut. It’s not a pie chart. It’s a donut. It’s not a Danish it’s a donut because there’s a hole in the middle. And then we’ll open this up and say maybe we want like the data labels on it. So data labels. So there’s our data labels with those things. Seems like it’s taking up an awful lot of room.
10:00
Data Labels. Let’s see more options just to check them out here. One thing you can do is actually pull them outside, I can pull them out like this, maybe can do that. So let’s try that out. still seems like a lot going on here. 10, there’s 10 of them. Maybe I should break it down to like the top five, I’ll put that one in the middle. said, okay, does that bother anybody? Okay, so something like that. It’s kind of fancy. And now where do those totals come from, we can do our wakeskate, we could say this equals this divided by the total here.
10:44
And that’s where those numbers come from, we can personify this column, Home tab, percent of fi and then sum it up over here, we can sum it up, just to prove that it adds up to 100%. And there’s our top 10. There’s our top 10 items this thing got. So now this is picking up. This thing is picking up the data. Let’s see if I can change the selected data here to just include these just want those my data, okay, and then adjust. Okay, we’re back. So I added the data kind of messed things up. But so now we’re back. So that looks pretty good, pretty nice chart.
11:29
Of course, we can do a standard, a standard graph here as well. And you can see how this if they ask you again, if someone asks you about, you know, you know the other line, well, that’s everything else and other up here that cool cars, that’s how the calculation is happening on the 21%. So it’s a nice little visual, and then you can break it down. If you get any more questions on it. Let’s add it, we can add, of course, another graph, we can go to the Insert.
11:55
And we can say let’s make let’s make a like a normal graph. This is going to be a histogram, we’ll just do this has recommended grass right here, actually, let’s check out the recommended grass they give us. So this is kind of interesting. So we have our top to bottom here. It’s kind of an interesting graph that they recommended for us. good visual. Go with that. So that’s good. So then we can add the title and whatnot. But this is just an example. Then if you want to print this, you can put this over the data. And then you can print this all in one PDF.
12:34
Or we can do as we did before and create another another tab, we have a data tab and then our actual charts. So this is going to be sales by customer. And this is not the Data tab. This is what actually this is the thing, real thing and just copy this over here. And then I can take this and maybe copy this one over there. So there we have it. And then we can check to see if it fits on a page and we can fit it exactly on a page, we might even make it landscape, we might say I want to make it landscape. So we can do the landscape option like that.
13:14
And then maybe that might be able to make it like a wider graph. for free. So choose. And who’s to stop you from choosing to make a wider graph. Nobody make graph wide if I want to. All right. So it’s pretty good. So then if we’re going to print this, then we can hide the data file once again. So I’m going to go back over here, I’m going to hide this so no one knows it’s there, it’s going to hide this one. And then I’m going to hide this one so that when we print it, they won’t show up then the data ones and just nice, beautiful graphs will show up, I’m going to go back to the journal tab so I can do that printing option.
14:00
So let’s save it as we’re at right now go to the File tab and down to the printing options. And then we’re going to the cute PDF printer we want the entire workbook to be printed. Once again, it’s got 40 pages in it now scrolling all the way down. I just want the new stuff, just want to check out the new stuff. I’m not going to check everything else because I’ve already double check that and two checks is enough, I don’t need three checks.
14:26
Three checks is going overboard. That’s when you got kind of a checking problem. Like you check things too much. So there’s our last one that looks good. The data tab has been hidden. So we’re at this graph. And then we’re at this graph. So all the data tabs have been hidden it looks like so let’s go ahead and print this out. Then we’re going to go to the printing option, which will save it as a PDF file. Once again, all 40 pages of our PDF. We’re writing a book. It’s going to be an epic novel in data format.
15:00
And then it’s going to ask us where do we want to put it. And we’re going to put it on the financial statement information there, rewrite that. And let’s just check it out. It should be right there now. So this is our double check. But I’m only checking the new stuff. The new stuff is the only thing that only has one check right now, everything else has already got two checks. So down here, here’s the new stuff. So that looks good. And we’ve got a nice graph.
15:25
And you can put all kinds of different graphs for that. And again, it’s really good to do this for any kind of data, you know, any kind of data that you can then export and use for graphs, any kind of database program, even if it’s not QuickBooks, you can typically export and make fancy graphs in Excel that are pretty easy. And then I’m going to, you know, you can have a lot of tutorials on it, there’s complication to it, but just to make a basic one, pretty easy.
15:48
So I’m going to put my cursor here and then hold down Shift, go back over here, and then right click, and unhide so we’re going to unhide so that we could see the hidden stuff, we’re going to unhide there. And notice if you ever encounter an Excel worksheet where it’s like something’s wrong here, something’s hidden, then this is how you can unhide Well, now I hit everything again.
16:15
That’s not what I wanted to do. unhide so I’m going to unhide all of it. So I’m going to say I for some reason, I’m going one at a time, I can’t unhide the whole thing, which is kind of annoying. unhide unhide I’m doing something wrong. Hold on a second, says ungroup unhide. I’ll just do this this way. There’s got to be a faster way to do this. I know there is but and we’ll do two more unhide and one more time unhide. All right, there’s a slow way of unhiding every sheet