#### Pivot Table: showing data as % of subtotal

```Hi,

I have pivot table data in this format:

Name    Type                  Amount
ABC     1                     \$100
2                     \$200
3                     \$300
4                     \$400
ABC Total                            \$1,000

DEF      1                     \$150
2                     \$260
3                     \$310
4                     \$420
DEF Total                            \$1,140

and so on
....
....
....

I want to see the data as follows:

Name    Type                  Amount                   %
ABC     1                     \$100                     10
2                     \$200                     20
3                     \$300                     30
4                     \$400                     40
ABC Total                            \$1,000                   100

....
....
....

```
 0
8/2/2007 9:16:36 PM
excel 39879 articles. 2 followers.

0 Replies
412 Views

Similar Articles

[PageSpeed] 13

Similar Artilces:

Looking for an excel function which can mirror opposite the data
Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB...

2 Questions on Pivot Tables
Any assistance appriciated. (Excel 2003) 1) I have a simple Pivot table showing 6 groups with their totals and a subtotal eg: America 10 UK 15 Oz 22 etc. Grand Total 125 I want to Pivot Graph this data, no problems. I want however an extra line to show the total on the Secondary Axis. I have almost got it by placing the column in a second time as a Running Total and then as a Line on my chart, hiding all the values until the last point. However I would like if possible the Total as a horizontal line across the chart? 2) I h...

Duplication of labels within pivot table
I am using a cvs file to copy & paste data into a workbook (not saving the file as an excel document and then coping & pasting into the workbook). This workbook is updated weekly from the same hard format reporting system with current information only - all other aspects of the report remain the same. I am seeing duplication of the labels within my pivot table. It seems that the information is being recorded as stacked data not as continuious data within my data base. Could this be caused by the copy & paste directly from the cvs file rather then from an excel file. At this tim...

Averaging weekly data into Months
Ive got a string of weekly data ranging from 1993-2010, which i need to convert from weekly to monthly. Id like to do this so that in the future new weekly data can be automatically converted to its monthly average. Whats the best way to do this? Maybe this will be useful to you... Step1: (If your weekly-date is in column B) Make a new column near your data, with formula in each line as "=eomonth(b1,0)" This will bring month value of each data-line, so later you can summarise/condense your data through this column value. Step2: In your monthly summary, use refe...

Unhide Tables in Acc2k7
Hi! How do I unhide tables in the Acces2k7, please? When I try: Navigation Pane, Access Options, Navigation Options, the Groups for "Tables and Related Views" is empty. Thanks in advance. an Do they show up if you select Object Type instead? Try Office Button, Access Options, Current Database, Navigation Options button. When that window opens, select all the Display Options at the lower left of the window. If that doesn't work, make a copy of your database and hide it away in a safe place. Then do a compact and repair. If that doesn't work, open a...

Drop line on only one data point in series
Is it possible to add a drop line to only one data point in a series? If so, how? I believe attaching an autoshape line to a single data point would also meet my needs. I would like to be able to have a single data point marked with a line that doesn't require repositioning every time I add new points to the end of the series, which is daily. Any ideas? An autoshape would move anytime a heavy truck drove by. You could add custom negative error bars to the series, where the range containing the values contained zero for each point except for the one you want the drop line for. Easie...

Pulling data for a report from a form
Hi, I have a product report list that want to show the on hand qty. so i have this formula in a report field: =onHand([pkProductID],Forms!FrmMyDate!InvDateQty) but when i preview the report it shows #name? instead. Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 To refer to a textbox on another (open) form, use: =Forms!FrmMyDate!InvDateQty -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "josros60 via AccessMonster.com" <u56159@uw...

Sent Mail items not showing up
Using Outlook 2002 w/SP2 on WinXP Pro. Unable to view items in Sent Item folder (Outlook shows normal blank window with "there are no items to show in this view") even though the Item count in the lower left shows over 2600 msgs in the Sent Items folder. It keeps increasing as new msgs are sent. Using the standard view, all standard columns, with NO Filter set. Anybody seen this before? Any ideas? ...

Pivot Options Disappearing!
I created a pivot table which displays data for January Through December. I ran a tesxt and deselected the "Show All" check box so that I can just display Jan, Feb, and March data. When I go to reselect April - December data those options are no longer available! How do I get excel to keep those items on the list so that they can be selected later? Thanks! There's no Unhide option for the data fields. Once you uncheck a field, it's removed from the pivot table. To see it again, you can drag the field back in from the field list Jenn wrote: > I created a pivot tab...

surveys and pivot tables
I have reinvented the wheel by making a paper survey and then entering all the answers into a spreadsheet. the format of the spreadsheet is; the first column is the id of every person surveyed and the next 9 columns are their response to each of the 9 questions (responses are numbered so each response is a number from 1-8)each row is in reference to the answers that the person gave. so a typical row would be: PCA 1 6 1 2 2 1 5 8 1. pca being the ID and the other numbers being the response to the corresponding row's question. The question is: how do i set up a pivot table to analyze ...

Custom Formatting a Chart Data Label
I want to create a chart which will display a zero value on the data label where there is in fact a zero value, and show N/A on the data label where it didn't apply. The formula I am using in the spreadsheet uses a nested formula to make this determination: =IF(AND(B22+C22>0,C22>0),B22/C22,NA()) Presently, it works but displays the #N/A on the chart where I would prefer it simply displayed as N/A. Can I do this with a custom format on the data label?? Thanks so much! I don't know how you all do it but your amazing! -- If you can read this, thank a Teacher... If your read...

pivot table changes when data is refreshed.
Hi, I have a pivot table with grouping based on a field called "period which is of date type. I have grouped it into months and quarters However when i refresh the pivot table whenever the data (which is in seperate sheet) is changed, the groupings are going out. I want t preserve the groupings as they are like a template. How do i stop th changing the format and layout of the pivot table whenever i refres the data? any ideas? please help. regards Kiran:mad ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages d...

How do I add a Trendline to a Pivot Chart
How do I add a trendline to a pivot chart? Help Thanks Leroy Maybe rightclick on one of the series lines in the chart and select Add TrendLine. Leroy wrote: > > How do I add a trendline to a pivot chart? Help! > > Thanks, > > Leroy -- Dave Peterson ec35720@msn.com ...

Redefine field data type
Hi, I am trying to write a function in a module in MS Access 2000 that will change the data type of a field called 'Start' in table 'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I run it nothing happens...no errors or changes to the table. The code finds the table and field, creates a new field called 'temp' then copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The field 'Start' has data type dbDouble. Any help would be great!! Public Function ChangeFieldType() 'Purpose: Changes a f...

sp_columns does not return NVARCHAR data type columns
Hi, our SQL Server Version is 2000 SP4. For several days the SP "sp_columns" does not return NVARCHAR data type columns any more. I reviewd the SP "sp_columns" and it seems, that the used SP "spt_datatype_info" does not return that type any more. A second production server returns that column type. The SP "sp_columns" is used by the ODBC-Driver to determine the table columns. Any suggestions? Thanks a lot, Nils Ok, my fault, it seems that "spt_datatype_info" is a table and there is no NVARCHAR row. After insert...

Charts not recognizing source data if original linked data is changed.
I am very frustrated by Excel (2003) at the moment. I'm relatively new to using Excel in depth. I have always been able to work my way through most intricacies and pitfalls. But now I have been working in a workbook with linked sheets (and with linked workbooks too) and have two problems that I cannot solve. Right now, I have about 47 sheets that are set up as follows: detail data: this is the raw data the the user enters daily totals: this is calculated data from the detail sheets summary sheets: further sums from the daily totals and a chart object that charts these sums (a line or sc...

Publisher calendar, how can I show the previous/next month?
I need to show the past and previous months in blank days at the top of a per month calendar, but can't see how to do that. Thanks. Create a yearly calendar in a new publication. Ungroup, copy the separate months, paste to your main calendar. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jennie" <Jennie@discussions.microsoft.com> wrote in message news:AF4CF698-ABB8-4F01-A667-CC6AA87A9732@microsoft.com... >I need to show the past and previous months in blank days at the top of a per > month calendar, but...

Pivot Chart Created from Pivot Table is Blank
HELP!!! I am following a tutorial in an Excel 2007 book step-by-step. In one of the lessons I was able to successfully and easily create a pivot chart from a pivot table. In my next lesson, I am following the exact same steps (even have started over a couple of times), and when I create a pivot chart from the pivot table in this lesson, it creates a blank chart. WHY DOES IT DO THIS??? I know I have followed the steps correctly. Any ideas on why Excel isn't cooperating with me on this lesson would be soooo appreciated. Hi, Blank charts usually result from having your cursor in...

how do i enter data for a # of years using a formula?
i am working on excel and the book asks that i enter data s=using formulas for specifically the last three years of what i am referencing to. and i have to know how to us the copy command button. can anyone help ...

Microsoft publisher, how do I set it to show multiple pages?
I'm trying to show more than one page in a single viewing pane. As in for the use of a banner. You'd think that'd be a simple and accesable function. I don't see it anywhere... Print preview has this function. Not sure if this is what you are asking... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "BrickShort" <BrickShort@discussions.microsoft.com> wrote in message news:B42E0DAE-9DB6-4587-A157-3C2F5225FA02@microsoft.com... > I'...

Pivot Table Wizard causes crash
Hello, I'm using Excel 2003(11.8316.8221) SP3 When I use the Pivot Table Wizard and press the "back" button to redefine the range of data. Excel crashes. I've tried with no other files open. I can use the wizard to create now pivot tables, but not redefine this existing PT. The file is about 1 meg. Thanks in advance. Mike Just after posting this I found a reference to the problem being the result of having "frozen windows" on the sheet with the PT. I "unfroze" the windows and everything works just fine. Mike "mike in...

Do a calculation in cells with text data format
I have a few columns of cells having a mixed data format of number and text. Is it possible to convert the first row of numbers in text data format for further calculation? Your guidance to accomplish it is appreciated. Thanks, Ray Example? -- Regards, Peo Sjoblom "Ray" <NoSpam-ZQLi@GMail.com> wrote in message news:ei\$Jbmy\$FHA.216@TK2MSFTNGP15.phx.gbl... > I have a few columns of cells having a mixed data format of number and text. > Is it possible to convert the first row of numbers in text data format for > further calculation? Your guidance to accomplis...

Where has the data map tool gone to in Excel 2003?
Cannot find it in 2003. Was there in earlier versions of Excel. It's gone as of Exel 2002. Here is MS's statement and a link to another program for map making. http://office.microsoft.com/en-us/assistance/HA010346591033.aspx tj "macray" wrote: > Cannot find it in 2003. Was there in earlier versions of Excel. If it still resides on the hard drive, you can use it from later Excel versions, through the Insert Object dialog. But I think uninstallation is pretty good at wiping it out. In my case, I have multiple versions of Excel installed on one machine, so Excel 2003...

Access 2007 pivot
I have a complex query that is based on ERP data exports. I am supporting a multi-dimensional matrix organization. A pivot table would be a perfect tool for analysing sales, margins, and changes for different business units. I have been using Excel pivots a lot and also in this case started with excel. However, the amount of data and usability of reports limits the possibility to distributing information in excel. With Excel I was able to all the tricks especially using the calculated item functionality, which is missing from the access pivot component. What I would like to ha...

Subtotal Lines in the wrong place Excel 2003
When nesting subtotals in Excel 2003, the subtotal lines are not in the correct position. Is there a fix or a work around for this problem? Example: Craft Code Crew Count BA Elec 1 BA Elec 1 BA Total 2 Elec Total 2 Hi AFAIK you should start with the subtotal for the right-most column and then add the other columns step by step -- Regards Frank Kabel Frankfurt, Germany "k2quayle" <k2quayle@discussions.microsoft.com> schrieb im Newsbeitrag news:FF252538...