QuickBooks Pro Plus desktop 2020. To export balance sheet reports to excel Get ready because we bookkeeping pros are moving up to hilltop with QuickBooks Pro Plus desktop 2022. We are in our free QuickBooks sample file sample Rockcastle construction going through the setup process with the view drop down the open windows list left hand side company dropped down homepage, middle area, maximizing the homepage and prior presentations, we were taking a look at generating reports printing reports, organizing the reports as if we were going to be sending them to someone such as a supervisor, or to a client.
00:39
Last time we did that by zipping the PDF files imagining we can attach those zip files to an email looking something like this. And these are the reports that we have then generated. We’d like to take those same reports this time, export them to excel imagining that then we’re going to use Excel to try to condense using a cute PDF printer, a PDF printer to print them as one PDF file with one attachment multiple reports on it, which can be impressive and can be useful to the recipient and be a little less daunting for them.
01:16
So in order to do that, we’re going to generate the same reports. And you can practice doing this with any kind of reports you would like. But in prior presentations, we set up some reports in our memorized reports, which we’re going to find by going to the reports drop down the Report Center. And then in our memorized section, we imagined that we set up these reports in the client month and information that are going to be designed so that we can just package this information up at the end of the month, processing the reports and sending them out,
01:48
I want to see this in a list mode. So I’m going to hit the list mode over here, we got our summary reports for the balance sheet. And then the standard balance sheets, we’re going to be generating them, exporting them to excel, putting them all on one Excel tab. And then using the CutePDF printer to make one PDF file with these reports, we’re gonna run the report starting this out with running notes. Again, you could practice this with any kind of report that you so choose.
02:13
These are just within lines with our practice file. So now last time, we hit the drop down here and we printed the reports and save them as a PDF. We could email them but that would be tedious if we had nine reports, as I believe was the number that we had here to give to somebody. So what we want to do is use this Excel option and export it to Excel. So let’s go to the Excel drop down say we’re going to create a new Excel workbook. So the workbook is the actual Excel file like a Word document in comparison to word and in the worksheet or the items in the workbook.
02:47
Sometimes people use those interchangeably, because they can get a little confusing similar terminology, you might want to go to the advanced settings down here before you set it up. Let’s take a look at them keep the following format options. So I want the fonts. I’ll keep the fonts, the colors and the space between columns, we’ll keep the default settings row height. So you could like kind of adjust these and see what the impact will be and practice basically exporting multiple reports to see what your favorite options will be. So we have the Autofit set column with to display all data. So I’m going to keep that Freeze Panes.
03:24
This one I don’t like so that’s the one I was looking for. I’m going to unfreeze those, those results in Freeze Panes when you export them to excel, which to me are not that useful typically Show Gridlines I like to see the gridlines. So I’m going to keep that auto outline. So allows collapsing and expanding auto filtering allows custom data filtering include QuickBooks export guide, I don’t like seeing the export guide. So I’m going to unclick that create backup worksheet during update.
03:56
Now I’m going to keep the default Show Report Header on printed report only on printed report and screen and repeat row labels on each page. So I’ll keep that there will show the header of the report there. So I’m going to say okay, let’s do it. And then I’m going to keep the first option up top create new worksheet, and then we’re going to keep it in a new workbook. We’re not going to go to the existing workbook this time. But we will do that next time when we’re then going to save to a workbook that we had already set up and started with the initial report.
04:29
So I’m going to export this and hopefully it’ll find my Excel program which of course you do need Excel for this to work properly. And then open the Excel program and then export this report to it just like that. And there it is. Now you can see this report has a little bit different formatting to it. You can then go in and try to figure out what your favorite formatting is in Excel. I won’t go into too much detail with that right now. But I’ll take a look a little bit of a look at it and just realize that you have kind of more formatting options within Excel,
05:02
although those formatting options are a little bit different, and it does take a little bit more work if you’re going to do some formatting options within Excel that you’re not doing in QuickBooks, because you would have to in essence, reformat them every time you export. So if you can export it in such a way that the default settings are as appropriate as you would like, that would be nice.
05:21
Note that you don’t see the header up top here. But if you go to the layouts on down below, and we go to the Page Layout, the second item, you’ll see the header is in the header area. Also note that it does lean to the left because they start in column A, so it’s not quite as centered, you could go through this and center it more by centering it basically in this page, using the settings in Excel to help you kind of put it more in the middle.
05:45
But that’s going to be one of the issues when you print this report. In other words, if I go back to the normal view here, you can see this dotted line, this light dotted line is basically where the end of the report is. And you can work at the formatting if you so choose to put this more towards the middle of the page from there. So now I’d like to save this, I want to save this into our folder here. I’m going to copy the destination of it. And I’m going to go back over and I’m going to say Save File Save As browse to where we want to put it, it’s going on that desktop, so I’m going to put that right there. And I pasted my location, so it’s in the desktop, here it is.
06:27
And I’m going to just call it reports reports for March, march 2023, I think it was, we’ll just call it that. I’m going to say save it. And so there we have it. So now it’s showing up in our folder, there it is, let’s do the same thing for the rest of the reports. For the rest of the reports, I want them to come back over here and save to the existing workbook. So I’m going to save them now to the existing workbook. This one I might want to rename the tab to call it summary balance sheet.
07:03
So I’m going to double click on the tab down here, some balance sheet. To give it the abbreviation I’m going to close this one up and save it. And then when I do it for the next report, it should open it back up and save it to that area. So let’s try it. Let’s go to the Report Center. Let’s do the next one. Run in Port Report Number two, I’m going to export it to Excel. Now this is where it gets a little bit tricky, tricky because it says create a new worksheet versus update an existing worksheet.
07:33
And you might say hey, I’m updating the existing sheet that I just made. But the worksheet is actually the sheet within the workbook, which is kind of confusing. So what you want is a new worksheet within the workbook. So the terminology of Excel remember is if you compare it to like Microsoft Word, the whole document in itself, the Excel document, in essence is called a workbook.
07:57
And then the sheets that are within that workbook are called worksheets. So we do want to have a new worksheet, we don’t want it on the same worksheet, although you could put it on the same worksheet, but I’m going to put it on a new worksheet here, so I can edit it on its own sheet. And then I want to put that into the existing workbook. So now it’s going to open up the same workbook, but it’s going to put it on a new worksheet. So I could go then to browse and save that. Now we want to say that this is going to go into the reports March and save that.
08:32
And I’m going to say okay, once it’s there’s our location, I’m going to export it. Now you could try putting them all on the same worksheet. And that might make it easier to kind of print but you can’t print the whole workbook. So So I think that’s easier to kind of format the different worksheets. In other words, if I tried to export from Excel to the same worksheet that we had before, then it could be a little bit more difficult for me to format them and print them. Given the fact that some some of the reports could be long. So what I’m going to have is that this workbook, this workbook is now open.
09:07
We now have two tabs two worksheets down below, it puts the new one on the left hand side, I want to grab that new one left click on it, drag it to the right. And this is going to be we called it the some balance sheet vertical analysis, vert analysis. I’ll call it something like that for the abbreviation. And so there we have it. If I go to the second item over here, we could see it that looks looks good. I’m going to go back to the first view. And so that looks good if I was to print it.
09:40
Now I could go to the File tab. I could say if I was to print this, then it usually prints the sheet that you are on but you can hit the drop down here and say I want to print the entire workbook and now it will print the two sheets. Now note again, it’s kind of the the data’s kind of up in the corner here because it starts on the first One sell. So you could do some formatting to basically center this if you so choose and, and do some Excel formatting there, I won’t get into that now, but in essence, you’ve got your two sheets. Now instead of printing it to a printer, I can print it then to the cute PDF printer,
10:17
which will take those two sheets and then save it as a PDF file. Now given me two reports on one PDF file, which is going to be our ultimate goal, so I’m going to go back on over, I’m going to close this out, I’m going to keep doing this going to save this one. And let’s do it again. Then I’m going to go back on over here. Let’s go to the Report Center and open up the next report. Report Number three, and export VAT one, two, so I’m going to open that one up, we’re going to go to the Excel drop down, we’re going to create a new worksheet, create a new worksheet, but it’s going to be in the existing workbook in the existing workbook.
10:56
Now, it’s already saying where I want it to go, it should be going to the right place. Now, because I just sent the last one, so I’m going to trust it, I’m not even going to browse, I’m going to live on the edge on the edge of things. risk. Risk is my friend. That’s what I do. And so hopefully, it’s going to open up the right one, there it is, puts that sheet right here on the balance sheets, I’m going to pull it I’m going to grab it, pull it to the right. And then this one, we’re going to call this one called the summary balance sheet by month, some balance sheet by month, let’s say and then I’ll save that one, save that one. And let’s do the next one.
11:37
We’re going to go back on over Report Center, we’re on Report Number 4/4. Report. Now, I’m going to go to Excel create a new worksheet. But we’re going to put this worksheet into an existing workbook it’s going to the right location, I don’t need to browse, I don’t need to browse. It knows what it’s doing. The computer knows. So we’re going to open that thing up, put in the new worksheet. This is the summary comparative Balance Sheet. This one’s a little bit longer, but I think it’ll still fit on one page, I’m going to grab it, left click on it, drag it to the right, it still fits on one page. So I’m going to call this to summary, comparative Balance Sheet.
12:22
Do it again, close it back out, save it back on over to the QuickBooks and go to the reports we’re on number five, open up number five here. A couple more times on this, we’re going to go to the Excel drop down create a new worksheet, but it’s going to go to an existing workbook, go into the right location, export it. So now we got Report Number five, which is the standard balance sheet, standard balance sheet opening up here hopefully, maximizing grabbing the sheet, dragging it to the right, double clicking on it, I’m just going to call out the BS balance sheet. Close that back up. Save it QuickBooks reports. We’re on number six.
13:16
Now I think number six. So let’s open that up. We’re going to go to the Excel drop down, create a new worksheet. And then we’re going to send that to the existing work book location. Looks good. Exporting that one on out. And so there we have the new worksheet that should be opening up in the existing workbook, dragging this to the right. And we call this what I call this, the balance sheet vertical analysis, dragging it to the right. Double clicking on the tab balance sheet verts analysis closing this back out saving it how many more this is tedious. How many more do we have number seven, couple more. And then we’ll format it in Excel using the cute PDF printer.
14:15
We’re on number seven, seven, export, create new worksheet. And then we’re going to put that into the existing workbook. Location looks good. Opening up Excel, maximize drag it to the right. Balance Sheet balance sheet. This is the balance sheet and this one is by month balance sheet. That’s not a be by month, month, like so. Closing that out saving it back to QuickBooks. And we’re going to go to the reports number eight. Almost there. This is the last One, then we have to stop after this one, because that’s the last one we have. And we’re going to create a new worksheet again, exporting that to the existing work book. And so existing workbook and export.
15:16
And that’s the comparative Balance Sheet. So I’m going to say bring that to the right, double click on a comparative Balance Sheet. There we have it. Now, if I now if I go all the way to the left, we’ve got all this stuff on one tab now on one Excel sheet, and I can print it using the cute PDF printer, putting it on one PDF file, by going to the file drop down, I’m going to say we want to print it, then I’m going to use the cute PDF printer drop down the cute printer, I want to see I want to use the cute one. There, we’re going to go and then it says and then we want the workbook, we want to say it’s going to have the entire workbooks going to be printed.
15:56
So there’s our information. On the right, we’ve got the summary balance sheet, and then the summary vertical analysis balance sheet. And then we’ve got the summary comparative Balance Sheet, and the standard balance sheet. And then the balance sheet vertical analysis. And so we just got a whole bunch of good stuff. And then we can just print it all in one PDF document using the cute PDF printer, which you can find I believe it’s free. I’m not promoting it or anything, I’m not a sponsor, but that’s what I use. And I think it’s free, very useful to do something like this.
16:33
And that it’s going to basically print that whole thing here so that it’s going to the proper place, I think so I’m going to save it. And now if I was to send this to somebody, our options then are to send the one by one as a PDF, like we looked at last time, or we can use the zipped file, which is a little nicer, but it still has a bunch of stuff. Or we could send this one PDF file that has all the reports in it.
16:59
And again, you could go in there and format it a little bit a little bit nicer, you could try to center all the reports and stuff, which is a little bit more tedious. But you got the one attachment, which is kind of nice. And you’ve got the the red in here, on the negative numbers if you had any colors that were taking place within it. So here’s our summary balance sheet vertical analysis, here’s our summary balance sheet by month that we have in here, here’s gonna be our summary, comparative Balance Sheet. And then we have our balance sheet standard.
17:32
And then we’ve got the balance sheet by month I think it was or something though, that’s the balance sheet standards or vertical analysis balance sheet, and then the balance sheet vertical analysis continued balance sheet by month. And then that’s continued. And then we got the comparative Balance Sheet, and so on. So notice, this, this gives you kind of like a nice package of stuff. And note and again, notice how it kind of printed those, those negative numbers. So it might give it a little bit nicer formatting in that way. It does give you more formatting options within Excel to if you have more complex reports.
18:09
And that allows you to send a whole lot of information that you kind of possibly impress the client or supervisor with the number of reports that you’re kind of putting together without also overwhelming them or annoying them by giving something with like like 10 attachments, or 10 different emails or a zipped file that has 10 different files in it. Hopefully the most important reports then being on top and they can drill down into the more detail if they so choose.