Excel outlining or auto subtotaling

Any non-Tax accounting topics go here.
#1
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
Was trying out new QBDesktop tax line grouping function (slow and no keyboard shortcuts but ok), exported to Excel, and sorted by tax line grouping.



Other than manually subtotaling each tax line subgrouping, isn't there a way to use "outlining" or "auto subtotaling" or some such Excel feature to do that?



Len Raphael

Oakland CA
 

#2
Posts:
2887
Joined:
21-May-2018 7:50am
Location:
Northern MI and Coastal SC
I have not bothered with this, as I know I can manually enter this stuff faster than getting Quickbooks prepped based on G/L account tax mapping.

But, have you tried the subtotal function, and then set rules for how you want it subtotaled? That should achieve your end desire if it is actually within Excel, and not some spreadsheet within QBD.
 

#3
makbo  
Posts:
6840
Joined:
23-Apr-2014 3:44pm
Location:
In The Counting House
lenraphael wrote:Was trying out new QBDesktop tax line grouping function (slow and no keyboard shortcuts but ok),

What "new function"? I don't see any such thing through ver. 2019, just the same old "tax lines for accounts". Something new for 2020 version, just released?

"An account's tax line associates each income and expense account with the appropriate tax form and line on your company income tax return"

I use this for all my tax clients where I also prepare the business return, including Schedule C, 1120S, and 1065. For me, it's not slow, and all the normal keyboard shortcuts work. The big payoff, of course, comes when you start working on years 2-n, when it is so much faster and easier to start with the Income Tax Summary report.

One disadvantage of relying on Excel for translation to the tax form is that every time you export the data from one program to another, you introduce the possibility of error. I can simply save a PDF of the Tax Summary Report with my work papers since it reconciles directly to the tax return, no intermediate spreadsheet required.

lenraphael wrote:[...] exported to Excel, and sorted by tax line grouping. Other than manually subtotaling each tax line subgrouping, isn't there a way to use "outlining" or "auto subtotaling" or some such Excel feature to do that?

In general, to use this feature in Excel, you first make sure there is a column header row, next sort by the column of interest, then from the "Outline" menu, choose "Subtotal". The dialog box should be somewhat self-explanatory, if you are comfortable with Excel.
 

#4
Posts:
2468
Joined:
24-Apr-2014 7:54am
Location:
Wisconsin
lenraphael wrote:Other than manually subtotaling each tax line subgrouping, isn't there a way to use "outlining" or "auto subtotaling" or some such Excel feature to do that?


That almost sounds like a job for a PivotTable.
 

#5
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
Before i posted here, i had sent an email to the developer of ASAP Excel Utilities which people like me who don't know a pivot table from a multiplication table, rely on. Though I suspect a lot of power users do also.


"Good that you ask; Never do that manually, there are built-in tools for that in Excel.
I recommend to use Excel's built-in "Subtotal" command for this:
# Insert subtotals in a list of data in a worksheet
https://support.office.com/en-us/articl ... a3d4a52b3a
# Excel Subtotals
https://youtu.be/iHsAhxjSmjI
https://www.contextures.com/exceldatasubtotals.html

I also recommend to spend a little time in knowing about Pivot Tables. They can really help a lot.
# Create a PivotTable to analyze worksheet data
https://support.office.com/en-us/articl ... n-US&ad=US

In addition, for your information:
Free upgrade to version 7.6.2 | June 20th, 2019
The latest version, 7.6.2 was released on June 20th, 2019.
Starting from version 7.5 many tools run up to 3 times faster compared to earlier versions.
In version 7.6 the duplicates handling tools run much faster compared to earlier versions.


Download ASAP Utilities
As a licensed user you can download the latest, future and previous versions of ASAP Utilities from the following location:
https://www.asap-utilities.com/download ... rsions.php "
 

#6
makbo  
Posts:
6840
Joined:
23-Apr-2014 3:44pm
Location:
In The Counting House
missingdonut wrote:That almost sounds like a job for a PivotTable.

I don't see how. Grouping and Subtotals are independent of "pivot" tables, a pivot table is merely a way to turn rows into column(s) when you only have a spreadsheet and not a true database to work with. QuickBooks already has many report options that are the equivalent of Excel pivot tables, so I'm still trying to understand what the OP is trying to do that QB doesn't already do for him.
 

#7
Posts:
2468
Joined:
24-Apr-2014 7:54am
Location:
Wisconsin
A PivotTable can be used to take multiple rows with the same description (tax line mapping) and sum them up. That was what I thought was being implied by the need to subtotal.
 

#8
makbo  
Posts:
6840
Joined:
23-Apr-2014 3:44pm
Location:
In The Counting House
Agree on PivotTable. So if there are for example 30 tax lines to which accounts are being mapped, there would be up to 30 columns in the pivot table, and maybe just one row. Is this really what is desired? I still don't understand the problem, although multiple solutions have been offered.
 

#9
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
re: is it a new feature or an old one in QB that lets one print out a tax line grouping report.

I think it's a new feature in QBO but an old existing feature of QBDesktop.

I haven't used QBD for almost a year. Mostly work with QBO.

Btw, turns out that the Thomson Reuters utility app does work for sucking out data from QBD into a file that Thomson Reuters Accounting CS or WPCS can work with for export to UT or GST. Intricate instructions to follow, but an authentication screen pops up in QBD that the QBD admin has to approve until QBD version changes. Somewhat a pita, because if client sends a revised QBD file, probably have to go thru the whole authentication thing again. VO WPCS then should be able to import the file from local pc where the util stored it. A lot of work for small clients.

When the QBO cloud app works, it's easy and fast. When it doesnt...
 


Return to General Accounting



Who is online

Users browsing this forum: No registered users and 20 guests