Pivot Tip 1 - First step, remove those subtotals. By David Carter

Kashflow logo
Share this content

David Carter's Five Minute Tips are designed to help you develop your Excel pivot table skills. If you want to test the tip for yourself, David has included a brief worked example on the pivot practice database.

Pivot Tip #1 - How to remove subtotals quickly

When you create a pivot table with more than one item in the Row area, Excel will automatically create subtotals. Most of the time they get in the way and you want to remove them. To do this quickly:

Double left-click the mouse on the grey Field button of the field being subtotalled. In the Pivot Table Field box, change Subtotals from Automatic to None.

Practice Session:

To practise this tip, open up the Excel file Pivot_Practice.xls. (There are only 27 records in this file. It should be quite easy to...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.


Please login or register to join the discussion.

19th May 2005 19:20

David, I suspect that you are right, however what I don't have a feel for is just how tricky / time consuming creating the "datamart" (is this simply another term for the analysis ready file?) and then the cubes is likely to be. I have no doubt that using XL Cubed would be fairly straight forward however I think that there will be a number of accountants (myself included!) who, although fairly IT literate, may feel slightly outwith their comfort zone when considering how best to create a datamart and an OLAP cube. Obviously this is up to me to resolve that issue with training.

I'm not sure I even need a datamart - all I want is to get the TB (not the transactions) out of the accounting software and into whatever product I use to create the reporting pack.

Xl-cubed does sound good though! Decisions, decisions.... Stuart.

Thanks (0)
18th May 2005 17:39

XLCubed is the way
Stuart, it's 5.25 and I've just got home after a day with Topaz Software, who are one of the first accounts package suppliers to implement XLCubed.

There's no doubt about it - XLCubed is the next level up for an accountant who already knows pivot tables. The facilities for designing your own management report pack are mouth-watering and it only costs £300.

But Topaz have had to do a lot of work to create a "data mart" from their transaction records. You can't use XLCubed until you've created the cubes, and you can't create the cubes until you or your supplier have created the datamart.

It's the old lack of an "analysis-ready" file problem. But to my mind there's no doubt that XLCubed is the way forward. We are going to have to start kicking the Sages, Suns and Navisions of this world into providing a data mart with their packages. David

Thanks (0)
17th May 2005 09:19

Stuart, I've been thinking, as promised. Maybe you are on the wrong tack here with GETPIVOTDATA. Perhaps you should be looking at XLCubed and Alchemex - the ones with the big banner adverts on ExcelZone. These are products which help you design a complete reporting pack in Excel. I think you can download XLCubed. Why don't you try and see how you get on?

Thanks (0)
17th May 2005 15:24

David, Thanks, yes I had come to the same conclusion myself as GETPIVOTDATA is a bit tempermental in Excel 2000. I am currently looking into XL-Cubed but haven't yet decided if its worth the effort as going down this road would also necessitate the intermediate step of getting the data into an OLAP cube. I may still just choose to use INDEX, OFFSET and MATCH to grab what I need from pivot tables.

Thanks (0)
13th May 2005 15:55

Abdulhakeem, nice one! A lot simpler solution than mine to an irritating problem. I think this tip will have to have an article to itself (could we use split screen as well, perhaps?).

Stewart, I agree with you that the pivot table format isn't really good anough to present to management as it stands. The way I've got round it so far is to copy the pivot table, then use Paste Special - Values just to get the numbers over. Then reformat it.

I think you're saying you want the final report set up already formatted, then just slip the numbers in from the pivot table. This seems a good idea, the only trouble is I haven't tried it before.

Need to have a think about this. Re GETPIVOTDATA we had an Any Answers query only a week or so ago, I think. David

Thanks (0)
11th May 2005 10:53

Pivot table limitation
many thanks Mr. David for you idea, but a like to add another one might be better for pivot tables as follows:

copy the same pivot table and past it next to the first one so that you well have two table alike, then change the rows to add the colomn you need which is not included in the first table by doing this you get four row in the first and four rows in the second but only one row is diffrent.

then you hide the duplicated column , so you have one pivot table but with two table togethar, you shuld keep the range same in both pivot tables in order to get the same line of data.

hope this well help..

Regards to all

Thanks (0)
12th May 2005 16:44

Renewed Interest in Pivot Tables
FAO David Carter (or any other PT expert!)

The new Excel zone has inspired me to learn more about pivot tables and I have found the articles and comments so far to be excellent. I take on board your comments about using a pivot table to create a P&L but I feel that such a Pivot Table is not the finished article.

I now intend to use excel as a managment accounts pack in the following way... TB download from accts software to be held in Access or Excel. Create Pivot table(s) to summarise TB into correct classifications (using tips from your article - additional columns to classify P&L, BS, Sales, COS, Admin, Dept1, Dept2, etc, etc) thereby creating the desired analysis. This would be the point where your article stops.

What I intend to do afterwards is lookup the pivot table using the GETPIVOTDATA function to place the information from the pivot tables into reports which are formatted as desired.

Would you agree that GETPIVOTDATA is the best way to do this? As far as I can see its the only way - LOOKUP, INDEX, etc seem to be inappropriate as pivot tables can change size But any formulae which refer to the tables don't change the size of the ranges accordingly.

All views welcomed


Thanks (0)
28th Apr 2005 12:13

Just as quick....
Is to have the pivot table toolbar and remove the subs from the dropdown tab for pivots in XL2003

Thanks (0)
29th Apr 2005 14:23

Advertising "clutter"
I understand the need for advertising to help pay for the excellent work of Accountingweb.

However, I do think the loss of the ability to produce a "printer friendly" web page for reading/printing, which seems to have disappeared recently, is a retrograde step.

Perhaps someone from Accountingweb can comment.

Thanks (0)
17th Apr 2005 19:47

Anita, you have to amend each field separately.

Abdul, it's an Excel problem. The only way I've found of getting round your problem is to insert a couple of blank columns to the left of the pivot table, then pull in two of your four row fields using vlookup. Not very satisfactory, but it does get all four fields onto one row.

Thanks (0)
16th Apr 2005 05:30

Pivot table limitation
I am using pivot table, but I am facing a problem in add 4 rows from a source data sheet contains more than 5000 rows, or some times gives No enough memory.

is this relates to the capacity of the set or excel problem.

any comments ?

Thanks (0)
15th Apr 2005 15:00

Pivot Table Sub-Totals
Is there a quick way to remove all sub-totals or do you have to amend each field individually?

Thanks (0)
13th May 2005 12:18

This book might help
Stuart, I would suggest you purchase a copy of a book called "financial statements.xls".

This will give you clear guidance on what you want to do. Details can be found at http://www.exceltip.com/excel_book/financial_statements.html

Be careful, it is now in its second revision, but some UK sellers are still marketing the original one.

Thanks (0)