Text as Pivot Data Field

Excel defines pivot a data field as "usually contains 
numeric data, such as statistics or sales amounts, but it 
can also contain text. Data from a data field is 
summarized in the data area of a PivotTable or PivotChart 
report."

I have a Pivot Table containing:

1 Row Field called: Name
1 Column Field called: Date
3 Data Field (2 numeric, 1 Text)

Everything comes in great except the text field is zero.  
I'm guessing the reason is because the Pivot is forcing me 
to perform a "Sumarize As" function like count, sum, ...

How can i get the text in too?  Is there a way to change 
the field settings or add a calculated field to do this

Thanks,
Mark

0
anonymous (74722)
12/17/2003 7:05:29 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
522 Views

Similar Articles

[PageSpeed] 58

Text can't be displayed in the data area of a pivot table. You can 
include text fields, but only as a numerical summary of the field.

If you have a copy of Microsoft Access, you could create a crosstab 
query to summarize the data, and use the First or Last function to show 
text.


LSMark wrote:
> Excel defines pivot a data field as "usually contains 
> numeric data, such as statistics or sales amounts, but it 
> can also contain text. Data from a data field is 
> summarized in the data area of a PivotTable or PivotChart 
> report."
> 
> I have a Pivot Table containing:
> 
> 1 Row Field called: Name
> 1 Column Field called: Date
> 3 Data Field (2 numeric, 1 Text)
> 
> Everything comes in great except the text field is zero.  
> I'm guessing the reason is because the Pivot is forcing me 
> to perform a "Sumarize As" function like count, sum, ...
> 
> How can i get the text in too?  Is there a way to change 
> the field settings or add a calculated field to do this


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/17/2003 10:04:26 PM
Reply:

Similar Artilces:

Linking Combo box to pivot table?
Hi All, I have a pivot table which has teams on the left, months on the top and a count of the calls in the data area. What I would love is that if I could have a combo box on a worksheet which when I select a team from its list it only shows me that teams data in the pivot table. Is this possible please?? -- Adam ----------- Windows 98 + Office Pro 97 You could move Team to the page area of the pivot table. Select a team, and the pivot table will only show its data. Adam wrote: > Hi All, > > I have a pivot table which has teams on the left, months on the top and a > c...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Pivot Tables & changing data
Hi - I have never used pivot tables in Excel before so hopefully what I am going to ask is possible and not too complicated for me.... :o) I am working with Excel 2003. I have a pivot table already set up and the information is pulling data from a row titled "sum of Subscriber". I added new data in a new column from the main spreadsheet and I would like to pull the data from there. Its titled "Adj Subscriber". Is it possible to switch it? If so, how? Thanks, Anna Marie Anna wrote: > Hi - I have never used pivot tables in Excel before so hopefully what I am &...

how to edit my x-axis data on a line graph
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel my x axis data on my graph is just showing numbers 1 2 3 4 5 6 7 8 9 10 11.... and it is suppose to show the years. I can't figure out how to change these values ...

Viewing an Excel sheet w/out all the empty fields...
How do you create a spreadsheet that only shows the fileds with data in them? -How do you get rid of all the empty rows/columns, to ease viewing for those who are easily confused by excel spreadsheets? (I don't know how else to emaplin my question... I just don't want the extra columns & rows there, if that's possible...) Please help... Hi! >I just don't want the extra columns & rows there, if that's possible... Just hide them! Suppose the last column in your sheet that contains data is column H. You can hide columns I:IV so that after column H all you ...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

Validation Rule for field values
I have a database that includes a phone number field. I want to prevent a user from creating a new record that contains a phone number that is currently in a record that exists in the database.I am using a form for data entry into the table. Somehow I'd like for there to be a check to see if the phone number typed into the phone number field to be checked against existing phone numbers in the database.I've researched various sources but haven't been able to find the solution. I'm not familiar with VB so any replies that suggest using that will probably not work for me. Sorry, j...

Pivot table fields missing on all computers
I have Excel 2007 installed on two computers. I also have Excel 2010 beta installed on one of the computers. I am unable to create a pivot table on ANY of them. None will show the field list. All copies of Excel work fine in all other aspects. I have searched the net for solutions and found many others have the same problem but few suggestions to fix it. Many questions never got any response at all. I did find a reference to try removing the .xlb file from the XLStart folder. I did, with no results. I have done all the proper show/hide clicks. I even tried using the ...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

text box on exit
I have validate code on text box and want to cursor get back text box. I use Screen.ActiveControl.SetFocus, but the cusor goes to where my mouse click. Are there any way to let cursor stay in the text box until user enter valide data? Your help is great appreciated, On Wed, 12 Mar 2008 11:43:02 -0700, Souris wrote: > I have validate code on text box and want to cursor get back text box. > > I use Screen.ActiveControl.SetFocus, but the cusor goes to where my mouse > click. > > Are there any way to let cursor stay in the text box until user enter valide > data? &...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

Full Text Index PF
We are running 3 Exchange servers in 3 separate sites. Two of which (including our main mail relay) are running Exchange 2003 while the third is still on Ex2000. Our public folder store size is roughly 3.5 GB. I am considering setting up FTI on my public folder store. Our only desire is to speed up the search results on a public contacts folder that is replicated across all 3 servers. Is FTI our best option and how much should I expect it to speed things up? We are seeing about 15-20 second searches (and sometimes longer) currently. Not horrible, but the execs want it faster. Thanks, M...

Combine part of text from one col to another
I'm trying to combine only part of a string from one column with all th text of another column. For instance I want to create usernames to log into a network. If col has Bill and col2 has Smith I want to get just the B from col1 an combine it with col2 to make smithb in a new column. Thanks in advance -- Message posted from http://www.ExcelForum.com Try this: -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== =B1&LEFT(A1) "epac135 >" <...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

Getting an UnBound control value into a Table field??????
I have a form that calculates a production rate in "parts per hour". This form needs to be able to differentiate between Line work, Cutter work, Side work, and Blister work. I created a drop down list for selecting the "Study Type" as listed above. I then created a text box for each "study type". When I select the "study type" each text box checks to see if the selection applies to it and makes the calculation if it does apply. The code for the "Line" study is as follows: =IIf([StudyType]="Line",2700?/[SecondsPerPart])...

Retreiving data
We move mailbox on a new server but somethimes we receive the following message: "outlook is retrieving data from the OLD exchange server" Can anyone help me -- Dambo On Thu, 1 Feb 2007 01:21:01 -0800, Damb0 <Damb0@discussions.microsoft.com> wrote: >We move mailbox on a new server but somethimes we receive the following >message: "outlook is retrieving data from the OLD exchange server" > >Can anyone help me What steps did you do to decommission the old server? If you give us an idea of the version of the old and new ones we can post the right help ...

On startup text to speech installation keeps appearing
Dear All When I load up Excel the dialog box comes up before excel continues to load saying: "Text to Speech - This feature is not currently installed. Would you like to install it now?" I dont want to install it but this KEEPS coming up, any ideas? Thanks Adam Someone answer please !!!!! >-----Original Message----- >Dear All > >When I load up Excel the dialog box comes up before excel >continues to load saying: > >"Text to Speech - This feature is not currently >installed. Would you like to install it now?" > >I dont want to inst...

Jscript to hide unpopulated fields
Hi We have created two custom entities named suppliers and business partners. We would prefer not to create account records and use the relationship type field so we created these custom entities. We now have on the Contact form three seperate fields that a contact could be related to. These fields are parent customer, parent supplier and parent business partner. A Contact should only be associated with one parent record. We would like to write Jscript which hides the remaining fields if one of the fields is populated. If a user associates a Contact with a parent customer then the ...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

Change database source for pivot table
Hi I've built a pivot table based on an Oracle database. however I want to change the source database (from test to live) but can't figure out how to change the source database. Any ideas? Cheers Neil Just answered my own question, in the query go into table definition and change the database "Neil" <neil@funkcity.demon.co.uk> wrote in message news:%23yauyKKpFHA.620@TK2MSFTNGP15.phx.gbl... > Hi > > I've built a pivot table based on an Oracle database. > > however I want to change the source database (from test to live) but can't > figu...

Remove Data Format Change
I have a program that puts a database's data into an excel spreadsheet. The problem is that Excel is constantly changing the numbers to dates, which corrupts the data. I know I can switch the column data type to Text and that will fix it, but the problem is that when the data is written to the spreadsheet it overwrites that column data type and Excel does the formatting that changes my data. How can I turn this off so Excel stops trying to recognize data types and change their format? Please help because this is causing major problems for us. Thanks, ACFalcon >>I have a ...

Data
I can not see the entires I made for the past month. What can I do? In microsoft.public.money, Magnus wrote: >I can not see the entires I made for the past month. What >can I do? I don't know what would cause that. http://support.microsoft.com/default.aspx?scid=kb;en-us;182608 describes using -s switch. That is a simple safe thing to try. http://support.microsoft.com/default.aspx?scid=kb;en-us;274584 describes salv.exe. These may not solve your problem, but they are what I would do. I would also review my backup file situation. ...

External data changes not reflected
I am frustrated by something that is probably simple and due to my lac of knowledge... I had some 10 rows of external data (a named range in anothe spreadsheet) and I was copying it in just fine. Then I extended the source to have 20 rows and changed the length o the named range now when I refresh the data in the destination it onl brings in the first 10 rows. Even If I create a NEW desination workbook file it still doesn't pic up my new BIGGER named range definition(so it isn't something local t the WKS file) I presume it muct be keeping some definition around in cache or in ...

Pivot table problem #13
Hi, I've got a pivot table that has dates on the left, and items tested in the other columns, however not every item is tested every day leaving blank cells, how do I continue on the last test number until a new test result is provided? Thanks Ross I'm not following you. You lost me when you said "how do I continue on the last test number until a new test result is provided?" -- cs02000 ...

Data Migration Import
I am setting up the migration to only import the "Account" entity. I load the cdf table with all the required fields. When I run the migration it says everything was successful but no data is imported. What am I missing? ...