Pivot table driving me nuts, turning numbers to dates

All, I have a pivot table that uses a SQL Server table as it's source. The numbers are all typed on SQL Server as INTEGER or MONEY. However the excel pivot table formats them as dates when drilling to detail or cutting and pasting from the pivot table. It's a开发者_开发知识库 royal pain. Anyone have this issue and a solution?

---

In the "Pivot Table" drop down menu, "Table Options", select/check "Preserve Formatting". This should maintain your formatting changes when you refresh data and drill down.

When you cut and paste, try using the 'paste' options: http://support.microsoft.com/kb/291358

---

Realize this is an old question - I have been having a similar issue and in the course of researching (on SO and elsewhere) found a working resolution in the following blog:

http://datapigtechnologies.com/blog/index.php/three-tips-for-making-your-pivot-table-formatting-stick/

Essentially, clicking "Preserve Formatting" is only part of the solution. The key is that formatting the cells is different than formatting the Pivot Table columns and rows. The action you took formatted the cells, but that format does not cascade up to the Pivot Table.

To fix:

Step 1 - turn off all filters on your table so you can format all of the data.

Step 2 - Hover your mouse at the boundary between your column heading and your first data row. The cursor will turn into an arrow, allowing you to select the entire data field.

Step 3 - Apply the desired format.

I tested by toggling off and on various filters and slicers and it is functioning correctly.

This was super frustrating for me and so I hope this explanation helps others avoid the same grief.

---

I have had the same problem when exporting data from SQL Server. I found that if I copied the data sheet and pasted special - values into a new blank sheet then inserted the pivot table the issue did not occur.

---

There were 3 things I had to do to keep my pivot table from changing formats when I changed the filter. The first 2 were in PivotTable Options - uncheck 'Autofit column widths on update' (self-explanatory) and check 'Preserve cell formatting on update'. The second item was to keep my column headings right-justified which I wanted because all the data was currency/%s which was right-justified.

The 3rd thing to keep my data holding the Currency formatting of including $ and , with 0 decimal points was more time-consuming. First, bring up the Field List. In the Values quadrant (bottom right) I had to click on each item's arrow to bring up Value Field Settings and go into Number Format. If I formatted there, it would hold. I had to do that for every column in my pivot table.

---

Check your source data for NULL values; if Excel finds nulls it treats the source field as text.