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. 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 see them all on the screen if you change the zoom from 100% to 75%)
1. Create your pivot table
Click anywhere on the data. From the main menu select Data-Pivot Table and PivotChart report.
The Pivot Table Wizard, Step 1 of 3 screen appears. Click on Next
Step 2 of 3 The Range box should say $A$1:$K$27. Click on Next.
Step 3 of 3 Click on Layout
The COLUMN-ROW-DATA box appears. To the right are the column headings of the spreadsheet. Drag and drop these fields from the list.
...ACC...........into the ROW area
...CUSTNAME..into the ROW area below ACC
...NET............into the DATA area
Now click on OK, then Finish. The Pivot Table is created in a new worksheet.
2. Now remove the Subtotals
Excel has created subtotals for A001, F003 etc., which make the screen difficult to read. To remove them, place your mouse pointer over the grey field button 'ACC' in cell A4.
Double left click the mouse. The 'Pivot Table Field' box appears. In 'subtotals' at the left, change from Automatic to None. OK.
The subtotals are removed.
Related material in ExcelZone
For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:
Subscribe to the ExcelZone newswire
To keep up with David Carter's Five Minute tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.
You might also be interested in
Replies (13)
Please login or register to join the discussion.
.
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
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?!
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
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.
.
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
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
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....
Is to have the pivot table toolbar and remove the subs from the dropdown tab for pivots in XL2003
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.
.
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
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
Is there a quick way to remove all sub-totals or do you have to amend each field individually?
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.