• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2010 Getting Rid of Accounting Format In Pivot Tables

S

ShelPrice

New Member
I have a large file (30 MB) with multiple worksheets, pivot tables and some VBA macros to update the pivot tables. I recently added some new worksheets and pivot tables. Went back to the file and found that somehow the number format of some columns and in most of the pivot tables had changed to Accounting and instead of showing a $ it shows a Cyrillic symbol (like a large C with a line through the middle). I was able to reformat my data tables, but it doesn't fix it in the pivot tables. I tried a lot of different things in an attempt to fix it...removed the cell styles from the workbook, reformatted all the data tables, reformatted the pivot tables, etc. I believe this happened to me once before and I had to recreate my entire file which I'm hoping to avoid as there's 20+ pivot tables, 10 or so worksheets with just data and a dashboard page that uses getpivotdata and it will take forever. I'm really hoping someone can offer another solution as I can't think of anything else to try (maybe brain damage from banging my head on my desk). I couldn't find anything online that would indicate a virus or of anyone having a similar issue. Please help me!
 
I'm not sure about the cyrillc symbol issue. but I have a little tip that may trim your file size significantly and speed up the processing a bit faster.


Very often, I have seen data sheet with, say, only 400+ lines and 50 columns. but for some reason the scroll bar on the right hand side of Excel is thinner than a fine hair. So I would go to the data sheet and delete all the blank lines below the data set. A complete "delete line" and not just "clear content" type of deletion. If you do this, a typical file at over 10Mb is down to 2-3 Mb or even less (depend on size of data set and number of pivot tables).


And I have trimmed an excel file once was over 25MB down to a 3-4 MB! and i risk less chance of excel crashing on my laptop/pc at work.
 
Thanks, yes, I've learned several tricks about reducing file size since I started working with these reports. The thing that helped the most was upgrading to Excel 2010.
 
I'll take a stab at this . . .


Is it possible that you have some conditional formatting applied in the Pivot table?


If you access the File menu and then Options > Language, do have any additional language packs installed?
 
You must log in or register to reply here.
Back
Top