CODEWITHSUNDEEP
sql-serverexcelpivot-table
Jeffrey Bane
Jeffrey Bane

Reputation: 592

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?

Upvotes: 1

Views: 4698

Answers (5)

Chad Pruitt
Chad Pruitt

Reputation: 11

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.

Upvotes: 1

Cindy
Cindy

Reputation: 11

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.

Upvotes: 1

jdg
jdg

Reputation: 547

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.

Upvotes: 1

realcals
realcals

Reputation: 1702

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

Upvotes: 0

M.R.
M.R.

Reputation: 4827

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

Upvotes: 2

Related Questions