Free Microsoft Excel 2013 Quick Reference

Pivot table displaying data rows into columns Results

Hello, I have a Pivot table that displays the totals of a two rows for each specific item in a column of item names. Currently, I've figured out how to get the data I want to display however it displays Row format. I want all the totals to be in their own column. See inventory_Purchase_ebay worksheet for the original pivot table and an example of how I want it displayed. Also I added some fields in the Pivot Table Field List dialog box. How do I delete them from there because as I experiment this list might get too long. thanks.

PS. I am using excel 2003

I know that there must be an easy answer, I just don't know what it is. I have Part Numbers in Column A. These parts are trimmed in a variety of ways, some of them in more than one manner. That is where SEQ comes in. I want to somehow display in one row the Part Number, then in Sequential Order from left to right the Work Centers associated with that part #. Can anyone help?? An example would be cell A9 and A10. I would like to have the end result read : 0001-01-1401-01 Hand Trim Machine Trim....Is this possible? I have tried a Pivot table and have not had much luck.

******** ******************** src="*********>*********>cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - TRIM ROUTES.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD1=
ABCDEF1Part_Number_________Mold NoSEQWC_NBRWork Center Desc 20001-01-0747-01X-074740MACHINE TRIM 30001-01-1128-01X-112840MACHINE TRIM 40001-01-1129-01X-112940MACHINE TRIM 50001-01-1363-01X-136360HAND TRIM 60001-01-1363-01X-136370MACHINE TRIM 70001-01-1374-01X-137440MACHINE TRIM 80001-01-1375-01X-137560MACHINE TRIM 90001-01-1401-01X-140140HAND TRIM 100001-01-1401-01X-140150MACHINE TRIM 110001-01-1512-01X-151240MACHINE TRIM 120001-01-1531-01X-153140MACHINE TRIM 130001-01-1553-01X-155340MACHINE TRIM 140001-01-1657-01X-165760HAND TRIM 150001-01-1732-01X-173215TRIM CLICKER PRESS 160001-01-1732-01X-173220MACHINE TRIM 170001-01-1732-03X-173240MACHINE TRIM 180001-01-1733-01X-173340MACHINE TRIM 190001-01-1741-00X-174160HAND TRIM 200001-01-1741-08X-174140HAND TRIM 210001-01-1741-12X-174140HAND TRIM 220001-01-1741-16X-174140HAND TRIM 230001-01-1821-01X-182140MACHINE TRIM 240001-01-1841-01X-184140MACHINE TRIM 250001-01-1865-01X-186540CRYO -FREEZE 260001-01-1866-01X-186640MACHINE TRIM 270001-01-1866-02X-186640MACHINE TRIM Sheet4
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I have multiple worksheets with common fields. One field i want to keep as page field. I want to consolidate and summarize data from the two sheets. What I did was I created a pivot table with multiple consolidation ranges. The only problem is it doesn't list the page field also t all of the fields. Instead it just displays the rows and columns.

What I want to do is get a comprehensive listing of all the fields and drag and drop into the table like I would if it was single consolidation. Can someone please explain how I can achieve this?

I have a list of 6000 rows with many columns. I built a pivot table from
this data and wanted to perform a sort. The pivot table has five or six
fields with one piece of data. One of the fields only has two values (one of
two distribution center locations). I want to identify the top 450 pieces of
data (sorted decending) for each of the distribution centers and have the
pivot table display. The desired end result would display 900 lines...450
for each DC. How do I do this?

In trying to accomplish this I also ran into another problem. I created a
very simple pivot table from the data mentioned above and wanted to see the
top 450 pieces of data from only one of the distribution centers. Every time
I did the top 450 request I only received 183. I couldn't get the pivot
table to return the number of requested top pieces of data. When I added the
additional distribution center (by checking the check box in the field list
to get all the data), I got back 238.....not the 450 or 900.

I have been trying to learn this tutorial form Microsoft: http://office.microsoft.com/en-us/ex...875651033.aspx

but I can't figure out the section where it finds the percentage of the order amounts. For some reason, I an unable to add the "Order Amount" more than once into the Data Area but this tutorial clearly shows it is possible.

Also, when i initially added the order amount into the Data Area, it displays differenty from the tutorial. Instead of what is shown in the example, my pivot table shows a section saying "Sum of Order Amount" above the "Salesperson" row and a column next to it saying total.

I think this is where the error is at.. i have double checked and tried several times, and even manually clicked the Order Amount to "Add to - Data Area" but the same thing occurs.

I simply cant add two items (The two different "Order Amounts") into the Data Area.
Does anyone know what I am talking about?

I wonder if anyone can help me. I have a spreadsheet (in Excel 2007) which is extracted from another source. This data originally comes into excel in a list with three columns (Quantity, Date and Customer Part Numbers). Within the list the quantity is the number sold, the date is just the month and year ie nov 09, and the part number is its reference. In some months there can be five different entries for the same part no but different quantities. I have managed to create a succesful pivot table to show the part number in column A, then the totals of the order go across the page.

ie

col a col b col c col d col e col f
NOV 08 NOV 08 NOV 08 DEC 08 DEC 08
BA101 22 50 40 22 24
BD509 42 48 30 12 20

What I need is a way to total up the month as a summary and hide or remove the duplicate information. for example for BA101 I want it to say BA101 NOV08 total of 112 (22+50+40) and for DEC08 46 (22+24)

I cant make the columns total a summary and remove the duplicate columns. I just want the totals of the month as a summary with the part number to display on a graph later.

Any help would be greatfully appreciated. I have attached a copy of the file, with the sheet 1 containing the original data. Sheet 3 contains the current pivot table created. I want it like this, but to summarise the months as a whole ie row 7 for product titled 17 - has order of 446+38=484, I need some VBA coding to make it display summary totals for product ordered in same month. I had shaded examples where multiple orders show, but need it to count summary for each month for each product.

Thanks in advance.

Hey so i've been teaching myself excel and have managed to knock up a nice pivot table but have run into what may be a bit of n00b roadblock.
Basically the data i get would be something like the following columns
UNIQUE ID POSITION TITLE DEPARTMENT LOOKUP CODE GENDER(M/F)

Using these columns I turn the lookup code into POSITION GROUP and create an MALE (1 if gender is M else 0) and FEMALE(1 if gender is F else 0)

So the pivot table columns would be:
DEPARTMENT- overall filter
POSITION GROUP- First row label
POSITION TITLE - Second row label
Count of UNIQUE ID- Value
Sum of MALE- Value
Sum of FEMALE- Value

This seems to work ok and displays the total number of men then the total number of women for each job title within each position group
But my problem is once i have the totals for male and female I need to have a percentage field next to each for percentage male vs female

This is what i really can't get my head around I would think i could create a duplicate column for each and then a percentage of function when displaying it but what do i choose it to be a percentage of in that case???

Any advice would be greatly appreciated!
Feel free to flame if this is a retarded question :P

I HAVE SEARCHED AND SEARCHED, but haven't been able to stumble into the right topic to find my answer, but before I start this might sound complicated because I like to be detailed, but it should be fairly easy because I've come very close to accomplishing my goal.. I also want to mention that the amount of raw data on sheet 1 changes every month, so I have a dynamic named range in order to expand down as many rows as it needs to... (I am very un-talented when it comes to excel, so I only have a mild understanding of what this function actually is capable of).

On sheet 1 there are 5 total columns.

Column K (Value) is a calculation of the difference between the Ship Date (Column J) and the Date Requested (Column I). There is Conditional formatting on column K that distinguishes if it is early (less than -7 days, yellow) on time (in between and equal to -7 and 7, green) or Late (greater than 7 days, red)

Column L (Category) is a simple calculation that displays the name Early, On Time, or Late. which I am currently using to populate a pivot table on sheet 2.

Now let me explain what my workbook does as of this moment. When I am on sheet 2 I have a Macro set up that allows you to refresh the data in the pivot table and chart formatting. The pivot table currently counts the amount of Late, On Time and Early deliveries separated by months (with regard to The SHIP DATE, NOT the Date Requested), and also displaying years to avoid confusion (even though the current raw data on sheet 1 is only in one year, 2010, this is subject to change in a few months, so i want to be prepared for a larger quantity of row data).

Now THE PROBLEM IS that I never accounted for column H (untitled, but it is the Delivery Quantity). Which means that the Pivot table on Sheet 2 is counting only a single amount for each Late, On Time or Early delivery. I need to find a way to multiply each row of raw data by its corresponding delivery quantity in column H in order to achieve accurate delivery quantities on the pivot table while still being separated by months..

Template MACRO Assignment.zip

I would love to stay close to this formatting if I can, but I am open to all suggestions, and I want to thank everyone who takes the time to help me out in advance! you guys are the best!

I have a rather large amount of data that I need to put into a pivot table. The table needs to display peoples names, their hours spent on each particular project for each month in the data, and also the total hours they spend each month. The problem is that the source data is in an awkward format. I keep getting a person named 'blank' which shows a different total than the one I'm looking for. This throws the whole table off. Is there a way for me to omit some sections of the source data, and have the pivot table update the fields if I add a new row or column to the source data in the future? I've spent a few hours and can't make anything work, Help would be greatly appreciated,

Wallacer

I am trying to out together a pivot table. I dont really have a lot of expirence working with them so im kinda new at this.

Anyway,

I need to have Both "Division" and "name" as row fields i know that much.

What im trying to do is show " Budgeted Amount" and "actual amount" for each division and broken down even further by "name" the wizard is not letting me do this. and i am very frustrated.

This is kind of how i want it to look

Division Budgeted amount Acutal amount Budget Variance
Asset Management 23,000 (total) 2100(total) -20,900

Name Jon Doe 11,500 1050 -10,450
Jane Doe 11500 1050 -10,450

What do i need for my Row, Column and Data Fields?

"budget Variance" is not a given field- I need to create this field and it needs to be the difference between "Budgeted amount" and "Actual Amount"

How do I do this?

Any suggestions???

What specifically do i need in the data Field?

All of my Numbers come right out of the data for Budget and Actual come righ out of my Data Range, they are not Calculated.

I tried to drag "Budget and Actual" to the data fields but it would only sum, count, avarage, max min...ect. I dont need any of that i just want them displayed how they are is my selected data range, but if i add them as column fields then what do i put into data

Hi ya
I have quite a lot of experience with Excel in formulae but am just breaking into Pivot Tables as it seems the easiest way to achieve the result I need.

I have two columns of data:
Month - adjacent to each transaction
Balance - the balance after each transaction

There are over 1000 rows of data to extract the max and min balance for each month from

I need to extract the max and min balances for each month of the year and display them in a separate table.
For example the results would look like this:
MONTH MaxBal MinBal
Jan £1000 -£2000
Feb £2000 -£1200
Mar £1500 -£3000
Apr £3200 -£1600
May £1700 -£700
etc

I have tried MAX(IF with no avail and the info I have found points to pivot tables.
Any help gratefully received.

Hi everyone,

Our HR department (my #1 customer) is working on a report that will produce a list of staff and when their trainings expire. The way I set it up is, a data connection to the payroll/HRIS system, which has a table listing each cert for each employee (PK being EE ID + Company Code + Cert Type). Then I put it in the pivot table and a single employee looks like this:

Dropdown Report

*CDEFGHI5Data1STAIDBBPBPSCPRMTTPTB6Certification Expires6/3/08*6/27/07*9/28/069/14/07730 day notice5/4/08*5/28/07*8/29/068/15/07860 day notice4/4/08*4/28/07*7/30/067/16/07

Excel tables to the web >> Excel Jeanie HTML 4

Some certs (BPS and MTTP) have a recertification that needs to be tracked separately. In other words, in the source data, there will be another record with Cert Type being BPSREC, for example.

Now, the query works off of a parameter, which is the cutoff date. Only employee who have one or more cert expiring will be pulled, and only those certs that are expiring are pulled. So, if I were to include BPSREC column in Cert Type (it's filtered out in the pivot table) the date would not display. However, end users of the report need to know whether a recert was taken or not, for any expired training.

Right now, I created a calculated field based on a helper column. The helper column, added to the source data, calculates the recert date for any expired BPS or MEDREC. So each source record that has needs the recert information has it (G and H are the recert dates):

Data from ADP

*CDEFGH14MTTP6/5/20053/25/2002*N/A6/28/2008
Spreadsheet FormulasCellFormulaG14{=IF(AND(C14="BPS",D14

I would like to find a way to have a user select a data cell in a pivot table and have a macro go to the corresponding data cell in the (large) source table.

Here is how my workbood is structured:

Sheet 1 = Grid table with item numbers as rows and week numbers as columns. Where there is a value n at the intersection of a row and column is when that item number will be produced in n units.

Sheet 2 = Database table that converts the grid table into a database format for the generation of pivot table from the source data in Sheet 1. In this table is the row number and column number for each data point n in Sheet 1.

Sheet 3 = Pivot table from Sheet 2 that displays data from Sheet 1.

Ideally a user should be able to select a data cell in the pivot table, click a button and have the focus go to the cell in Sheet 1 that corresponds to the data point (which would be at the intersection of the row number and column number in the table on Sheet 2.

If this isn't clear I can post some examples... thanks for any help.

Stu

I dunno if this was done already but,

One way i use pivot tables is often to layout some complex Data that i want
to VLOOKUP into afterwards. I know what i could use GetPivotData, but it
requires too much specific information at times to be an efficient way to
lookup my values.

I also often add some information from other data source in different sheets
(tables,queries,pivots,etc) in the columns following the pivot table columns.

The way the data is display is very efficient but accessing that data for
use in formulas often gets tricky.

Picture this display of pivot (not the real layout of pivot):

A | B | C |
------+-----------+-----------------+-------------------+
1 | region | salesperson | Sum of Sales |
------+-----------+----------------+--------------------+
2 | North | John | 100$ |
------+ +----------------+--------------------+
3 | | Paul | 50$ |
------+-----------+----------------+--------------------+

if in D2 and D3 i add the following formula, i get into problems

=VLOOKUP(A2&"-"&B2,MarginsPerRegionPerSalesPerson, 2, 0)

The proble comes on Row 3. Since A3 is blanked because it is grouped with
A2, A3's value will be empty.

Would it be possible that references to any cell in a grouped cell range to
return the value of the grouped cell, instead of the value of the looked up
cell ?

What i would love is for A3 to return "North" when i access it. A3 should
know it is grouped with A2 so the real value of A3 is really the same as A2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hello - I have figured out how to create a range to allow for dynamic
data sets so that my pivot table can refresh, even when the column or
row size change.

However, when I try to refresh my existing pivot table (by either
clicking the exclamation point, or by going back into the Pivot table
wizard), it doesn't seem to read my source data appropriately. That
is, the dates (which I have displayed as column headers in the pivot
table) are not accurate.

Does anyone know why this is? Should I be doing something different?

In case it helps, here is the formula I am using to name the range:

=OFFSET('FL1'!$A$12,0,0,COUNTA('FL1'!$A:$A),COUNTA ('FL1'!$12:$12))

What I have been doing is pasting a new set of data into the source
spreadsheet, and then clicking refresh in the Pivot table, which I
have on a separate tab.

Thanks for any suggestions!

Some quick questions on pivot tables:

1. How can I preserve the formatting of my pivot-reports? After i
update the column width, wrap-text features are refreshed as well. I
checked the "table settings" option and un-checked "Auto Format", but
this still happens?

2. I setup a workbook with 6 pivot tables drawing upon 1 set of source
data (2 pages are pivot report, 4 are pivot charts). I designed this
looking at some information for month-to-date of April, once I was
comfortable with my results I saved a copy to be used as a template for
each month. Next I pulled information for the month of March, pasted it
into my input page, and refreshed each of the 6 pages. Everything seems
to work fine except for two problems:
First, most of the graphs have the X-axis using the "Date" field.
While the graph displays 3/1, 3/2/, 3/3, 3/4, etc for the month of
March, when I click on the "Date" box it only lists me the first 14
days of April-which were the first days available when I made the
original report to be used as a template. I tried pulling the date
field off the chart, then adding it back in but that didn't correct
this minor bug. Any help on this?
Second, one of the charts doesnt populate any information... I
have to recreate it.

If it helps, my source data is a single excel page that uses all rows
in columns A through U.

--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=533450

I have a data set from which I am making a pivot table. I wish the pivot
table to display the two separate columns from the database as "data" with
subtotals. Yet when I place the two columns into the data section of the
pivot table, I get an error: too many rows or columns in pivot table.

I can easily put one of the columns into the pivot table column section and
have the other as the data for the pivot table, but then I cannot (to my
knowledge) get a subtotal from the each of the two columns, but only from the
one listed explicitly as data.

Any ideas?

Here is a tricky one (at least to me).
I have a bunch of data that is being pulled in from an Oracle DB into Excel through an ODBC connection. This data then needs to be pulled in to a pivot table. Here is an example of the data:
Product Name | Sampled Date | Sample Name | Result | Min | Max
ABC | 03-09-03 | Moisture | 20 | 10 | 30
BCD | 03-09-03 | Ph Level | 7 | 8 | 10

This data needs to be displayed in the following format in the Pivot table:
Prod name and sampled date as rows, Sample name as columns and result, min max as the data.

I can get the above to work just fine. However, the users want to see the results in bold/red if they lie outside the min/max range (as in the BCD example). So, I can use conditional formatting, specify the conditions and everything works just fine. Unfortunately the formatting does not stay that way next time the pivot table is refreshed (for understandable reasons).

So I am trying to ‘invent’ a way the formatting can be accomplished on the fly. Unfortunately, there is an unknown number of products and sample names that can be present with each query, so designating certain columns as results, min, and max is not an option. However, I wanted to swing this be you to see if this is possible:

Get the total number of columns and starting at the column 3 (which is where the result of the first sample would be) format through use of VBA by comparing values in column 3 to 4 & 5. Loop through all of the cells in those columns and go on to column 6, etc. etc. etc. Does this sound reasonable? What about the time that it might take to process all of this?

If this does sound reasonable, can I get an idea on how I get started: get a handle on the range of cells in column 3 for example and loop through each cell?

Thanks!

I dunno if this was done already but,

One way i use pivot tables is often to layout some complex Data that i want
to VLOOKUP into afterwards. I know what i could use GetPivotData, but it
requires too much specific information at times to be an efficient way to
lookup my values.

I also often add some information from other data source in different sheets
(tables,queries,pivots,etc) in the columns following the pivot table columns.

The way the data is display is very efficient but accessing that data for
use in formulas often gets tricky.

Picture this display of pivot (not the real layout of pivot):

A | B | C |
------+-----------+-----------------+-------------------+
1 | region | salesperson | Sum of Sales |
------+-----------+----------------+--------------------+
2 | North | John | 100$ |
------+ +----------------+--------------------+
3 | | Paul | 50$ |
------+-----------+----------------+--------------------+

if in D2 and D3 i add the following formula, i get into problems

=VLOOKUP(A2&"-"&B2,MarginsPerRegionPerSalesPerson, 2, 0)

The proble comes on Row 3. Since A3 is blanked because it is grouped with
A2, A3's value will be empty.

Would it be possible that references to any cell in a grouped cell range to
return the value of the grouped cell, instead of the value of the looked up
cell ?

What i would love is for A3 to return "North" when i access it. A3 should
know it is grouped with A2 so the real value of A3 is really the same as A2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Here's the premise:

Vehicle crashes have stored with them many fields of data. Each row of my table contains (for example) location, crash type, speed, year occurred, vehicle type and details on up to four drivers/pedestrians involved (by a set of columns that repeat four times). Each driver's age is recorded in a column called AGE with values ranging from 0 to 900 or left blank depending on how many drivers were involced.

Here's a rudimentary example of what it looks like using commas as column breaks (you'll have to use your imagination):

YR,AGE,AGE,AGE,AGE
2005,18,22
2001,16,64,28,27
2008,78
1997,35,12

So I have four AGE columns displaying ages of drivers. In a pivot table, I want to SUM the COUNTS of each age within all of the four columns by the year the crash occurred. So the table has to be smart enough to know that for every crash that occurred in 2005, XX drivers (of up to four possible drivers) were XX years old.

As it stands so far, I can get a pivot table (and subsequent graph) to show me a multi-year breakdown per driver column by age (AGE, AGE2, AGE3, AGE4) or even by age groups that I determine (AGE5), but I can't get it to combine the counts from each AGE column into a singular column representing that particular age.

The icing on the cake would be to be able to define my own age groups as well using preset criteria (less than, greater than, etc.) because the age data can be so variable.

I hope I supplied enough info.


No luck finding an answer? You could always try Google.