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

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.

Continued...

» Register now

The full article is available to registered AccountingWEB 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.

Comments

.

stuartbisset | | Permalink

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.

XLCubed is the way

David Carter | | Permalink

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

XLCubed?!

David Carter | | Permalink

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?

XLCubed

stuartbisset | | Permalink

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.

.

David Carter | | Permalink

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

Pivot table limitation

AnonymousUser | | Permalink

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

Renewed Interest in Pivot Tables

stuartbisset | | Permalink

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

Stuart
:-)

Just as quick....

AnonymousUser | | Permalink

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

Advertising "clutter"

tony.stevens | | Permalink

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.

.

David Carter | | Permalink

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.

Pivot table limitation

AnonymousUser | | Permalink

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 ?

Pivot Table Sub-Totals

AnonymousUser | | Permalink

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

This book might help

tony.stevens | | Permalink

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.