Adding percentage columns and deleting its Total but not Sum Total

**Danak** · 10-30-2020, 03:40 PM

Hello, TY in advance for help and suggestions. I have a pivot table with the number of successful, canceled, and failed projects as columns. The rows are projects grouped by the $ Goal Amt. I have manually grouped because the AutoGroup groups like 0-1000, 1000-4999, 4999-9999...the next group doesn't add one to get 0-1000, 1001-4999, etc

I've elected to have Total Projects as a column. I'd like to add three columns to the right of Total Projects as a % of row. I can actually do this by duplicating the field in the Value box but there's a couple of issues I can't resolve.
1) I cannot rename the new three columns "% Successful", "% Canceled" and "% Failed". It wants them all named the same.
2) I don't want the Total column for percentage but want to keep the Total column for sums.
3) I don't want the % column next to the value its based on. I want all three % columns to the right of "Total projects" column. Any ideas?

**Mrrrr** · 10-30-2020, 05:24 PM

See attached.

1) To rename the 3 columns from pivot to % Canceled, % Failed and % Successful you need to select each of them, then go and type the desired name in their formula bar - instead of # put % (not in Field Settings).
2) The only thing I can think of in order to remove just the 2nd total column (the % one) is to color the text and cells background in white. Cell borders also need a color similar to the native excel cell delimiting lines.
3) I just selected the cell right above Count of Status2 column label (cell C4), right clicked it and selected Move, then the last "Move % canceled to End".

**Danak** · 10-30-2020, 06:14 PM

Hi! The shading did work for "removing" column. sadly, using formula bar to rename did not work...hopefully someone will have a suggestion. Thank you for solving one of my conundrums, much appreciated!

**Mrrrr** · 10-31-2020, 04:31 AM

Have you tried refreshing the table after trying that column rename?

See how this works for me:

\1

There is another rather unorthodox way:
- change file extension from .xlsx to .zip (if you don't see file extensions by default - go to Windows Explorer - View - Folder Options - View - uncheck "Hide extensions for known file types" - OK)
- open the zip file and navigate to xl - pivotTables
- extract to Desktop file pivotTable1.xml (drag and drop to desktop would suffice)
- don't close the archive just yet
- on desktop right click on pivotTable1.xml and open with Notepad
- Edit - Find - #
- replace # with %
- save the file and close Notepad
- drag pivotTable1.xml from desktop in the zip archive to replace the one there
- close the archive and change extension back to .xlsx
- open the Excel file and refresh the pivot table

This also did the trick for me.

If this solves your issue, please mark this thread as solved - go to the top right side of your first post - Thread Tools - Mark as solved.
If it does not, then I am clueless and hopefully someone else will help out