Why do pivot tables not read numbers that are copied and pasted

I am working with a Pivot Table whose information comes from a two columns of 
data that has been assembled from data copied and pasted from other sheets.  
Unless I retype all the data, it only shows zeros when sum is selected from 
field settings.

I need a way to get it to sum without retyping or at least to know why it 
will not sum the numbers.

Thanks
0
B_4j (1)
6/23/2005 9:02:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
432 Views

Similar Articles

[PageSpeed] 30

Excel is seeing the numbers as text. There are some techniques here that 
you can use to convert the text to numbers, so they can be summed in the 
pivot table:

   http://www.contextures.com/xlDataEntry03.html

B_4j wrote:
> I am working with a Pivot Table whose information comes from a two columns of 
> data that has been assembled from data copied and pasted from other sheets.  
> Unless I retype all the data, it only shows zeros when sum is selected from 
> field settings.
> 
> I need a way to get it to sum without retyping or at least to know why it 
> will not sum the numbers.
> 
> Thanks


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

0
dsd1 (5911)
6/23/2005 10:10:51 PM
Reply:

Similar Artilces:

Query on Group field in Pivot Table
I have a large database wherein under Date column, data is in the format "Thu Oct 1 12:00 AM". When I use Pivot Table Under OPTIONS->GROUP-> Group Field is disabled. I used this to group Date field to either present data by Month, quarter at one click. Can anybody guide me why this seems to be disabled here. I want to present my data only by Monthwithout changing the format ""Thu Oct 1 12:00 AM". Thanks Hi Shewta Check you Data Source of your Pivot. it should come up like "Sheet6!$G$1:$I$821" and not like "'\Documents and...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

Can I set my custom views on Pivot table
I want to get Pivot table data on my invoice, is it possible to set custom views so that I can get Pivot table results on invoice format, I've tried views that offer excel but it doesn't offer custom views please help Thanks ...

pivot
How to take a copy of data on the web to a pivot tabel? ex. take a copy of ford and fiat cars in bilbasen.dk... HOW!? Import the data onto a worksheet (Data>Import External Data> New Web Query) Then, create a pivot table from the imported data Louise wrote: > How to take a copy of data on the web to a pivot tabel? ex. take a copy of > ford and fiat cars in bilbasen.dk... HOW!? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi. thanks.. but I already tried it. when I click the import button, to my selected "zone&qu...

From temporary table to master table and then join this in junction table
Hello, I have imported two spreadsheets from excel to two temporary tables called "import_tbl_contract" and "import_tbl_products". I do this on daily basis and import many spreadsheets. I end up with these data: [import_tbl_contract] contract_number contract_title start_date end_date [import_tbl_products] product_number product_text price I now append these data to the following two tables: [tbl_contract] contract_id (PK) contract_title start_date end_date e.g. 1; Syringes and needles; 01-01-2010; 31-12-2010 [tbl_products] product_id ...

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Volume shadow copy -- OK to disable?
I was doing a defrag and it kept stopping because of a conflict with the Volume Shadow Copy service. A quick Internet search turned up a site that recommended disabling the Volume Shadow Copy while defrag is running. http://forum.soft32.com/win4/Defragmenting-pauses-volume-shadow-copy-ftopict212207.htm I did and that fixed the problem. The site said to leave it disabled if I don't need it. It didn't say how I could tell if I need it. I left it disabled and everything seems to be working OK. How can I tell if I can leave it disabled or if I need to reenable it? htt...

help with preview pane and "read receipts"
Hi, I'm new to Outlook administration. We run Outlook on a server wit Exchange. Is it possible to setup Outlook on client PC's so that th preview pane cannot be activated by individual users? Also, can the blocking of "read receipts" by individual PC users b prohibited within Outlook, at the server level? If not, is there an other way to do it? My reasoning for wanting to do the above two things is to make th "read receipts" function work more effectively. Thanks for your suggestions. : ----------------------------------------------- ~~ Message posted from h...

Outlook2003 : keeping a copy of PST on server
I can't seem find the place to make the change on the client's outlook to keep a copy of the pst file on exchange server. When I configured outlook I got the message to go into tools and select the option to keep a copy on server. I couldn't find that option and all mail was delivered to the client. When you go through owa all you will see is the new mail not delivered to the client. I'm going to hack at it and when I find something I will post my findings. But if someone already knows where to look I would appreciate that Thanks Norm found this article http://www...

page numbering
I want to change the way the date appears in the footer and header. Currently the date is day/month/year. I want month/day/year. setting the date to a particular format is easy in a cell. How do I set the date format in the header and footer? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ jamessem, you could use a macro like this to do it Sub test() With ActiveSheet.PageSetup .LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub ...

Prevent Hidden Column data from being copied/pasted?
A student came up with a question that I haven't been able to figure out yet in a recent Excel class. They are hiding a column and protecting the worksheet in the correct manner. They want to allow some users to access and enter information in some cells. They do not want the users to be able to copy and paste the information from the hidden column. The question is how can this be prevented? For example, Column B is hidden. When they copy a range such as A1:C10 and paste it to another worksheet, they are getting the "hidden" data in Column B in B1:B10. Any suggestions wou...

Numbering Recurring Items #2
Hey all, Is it possible to number recurring items such as tasks and appointments? I have several recurring appointments for lectures, and would like to show them as "Lecture 1" in week/occurence 01, "Lecture 2" in week/occurance 2, etc. Thanks, Murdoc -- Murdoc ## ------------------------ "The journey of 1000 miles begins with a broken fan belt and a leaky tyre." "Duct tape is like The Force. It has a light side and a dark side, and it holds the universe together." "If the facts don't fit the theory, change the facts." -Albert Einstein &...

Changing default Print copies
Hi, I have a workbook that has a worksheet with default printing copies of 3. Even when I go to page setup and change the number of copies to print to 1 it will automatically revert to 3 the next time I bring up the print dialog. I have also looked through the VBA editor at the worksheet for any oddities and have found none. Does anyone know how to change this permanently? Thanks in advance. Pach >Even when I go to page setup and change the number of copies to > print to 1 it will automatically revert to 3 the next time I bring up > the print dialog. Look at the defaults for you...

Copy/Paste formulas
How do I keep certain cells (those I want to point to a specific 'constant') from incrementing while the remaing cells in my formulas increment as expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains a set value that I want to be placed in the result as I step down the incremental (I) rows when the character "V" is found in the particular (I) row. When I do my copy and paste, the (E) row increments as the (I) rows increment. $E$65 "BobG" wrote: > How do I keep certain cells (those I want to point to a specific...

HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 and e1 the cells d1 and e1 have the following formula : d1=c1-b1 e1=d1-c1 but i want the following d1 should be b2-a2 and e2 should be b3-a3 how do i do this? One way... In C1 enter & copy across: =INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)-INDEX($B:$B,COLUMN()-COLUMN($C$1)+1) anantth wrote: > My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 > and e1 the cells d1 and e1 have the following formula : > d1=c1-b1 > e1=d1-c1 > > but i want the following > d1 should be b2-...

Copy data from one record to a new record on a sub form
I have a form showing patient details with a sub form displaying all associated referral details for the patient. One patient can have more than one referral and I would like to be able to copy data from some of the fields on the current referral when adding a new referral for the same patient. Is this possible? Thank you for your help I am fairly new to Access code and I am having some problem following your suggestion could you please simplify. Also some of the fields on the referral records are selected via a combo box will these fields still copy? "Klatuu" wrote: > ...

Right Click Safari 404 and paste into Word 2008 only showing address
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Example. I open gmail.com. Right click on the Gmail logo to "copy image" not the "copy image address" option. Then, I open Word 2008 and paste. All I get is the address, not the logo image. If I repeat and use Text Edit (Apple) instead, it works as expected. Is this a bug with Word? Or am I missing a preference setting here set up by default that needs adjusted? Apparently it has to do with how Safari populates the Clipboard � I have no problem pasting the logo with F...

Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a strai...

Supress col/row increment with copy
Question with (hopefully) an easy answer.... When you copy a cell that uses a formula referring to another cell, for simple illustration say "=C4" it will increment based on where you paste it. So, it will increment up to "=C5" if you paste it in the next cell, or "=D4" if you paste below. How can I suppress that, so that when I paste the new cell receives "=C4" as well. (I know I can simply copy the text and paste that in, but I want to copy a whole row of formulas to be the same. I thought Paste>Special>Formulas would work, but it seems to ...

IF formula needs to read only values, not formulas
I have an IF formula down column F, and am trying to read the results with another IF formula in column G: =IF(F5 > 1/32, "Yes", "") But the formula in column G gives "Yes" when the cell in column F is filled only with the formula, not a result. How can I make this work? Ed Hi Ed, Please tell us what the exact formulas in F and G are; that is not clear from your post. What are the values involved? Are any of the columns formatted as Text before you entered the formulas? -- Kind regards, Niek Otten Microsoft MVP - Excel "Ed" <ed_millis@...

Vendor Address ID should always be stored with Voucher Number
The Vendor Address ID should be stored in more places in the PM tables. This will make it easier to pull the correct address for reporting purposes. You should also be able to click into the Address ID field from the Payables Inquiry rather than having to go through the Vendor Inquiry. This will make it easier to look up what invoice was sent to what address. ---------------- 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...

Link Drop Down with Pivot Table
Hi I'm using MS Excel 2003 and need a solution regarding link my drop down list with my pivot table. Query : I have created a drop down list in which month can be selected and wanted to link with pivot table in which month wise team count is displayed for a selected month. Presently I'm drag n drop the month in pivot table and its shows the count. But this exercise I have to do every time when I need the report. Is it possible that I have select the month from my drop down list (created in same sheet) and pivot table shows the data related to that month. Please h...

Number of selected shortcut files
Does anyone know of any function that can tell us number of shorcut files selected by a user on Windows Explorer. Hi, Based on my experience, there is no direct method. I suggest that you can perform the following steps to implement this functionality: 1. Use FindWindow to get the handle to Windows Explorer. 2. Call EnumChildWindows() to find the handle to ListView in the Windows Explorer. 3. Get CListCtrl from CListView. 4. Retrieve the selected listview items, and count the items whose type is Shortcut. Please feel free to let me know if you have any problems or concerns. Have a ni...

Inserting a value in a table when a connector is added between two shapes
Hi everybody.. I have a Visio drawing containing six different shapes in my stencil. When adding a connector between two shapes, is there a way other than by VBA to make Visio insert the shape IDs of the two connected shapes in a relationship table between the two shapes.. An example.. if I have the two tables Course and Objective, and I want to add an objective to a course, I would create a table between the two tables, say RelationCourseObj where the primary key would be the two primary keys from the two tables... Now is there a way with the database wizard to make Visio automatically...