Solved

Reverse sign - condtionally - on numbers in Excel pivot table

Posted on 2010-08-21
5
1,560 Views
Last Modified: 2012-05-10
How do you reverse the signage on a number in a pivot table - based upon some condition.

Crystal Reports has a simple reverse sign display, and you can create a condition for that formatting. I was hoping for something this simple.

Everything I have researched on the internet discusses multiplying by -1..... however that changes the integrity of the number - and would be overwritten if you were to paste the values.

In a pivot table, you need to add numbers based upon the actual signage..... however for financial statements, you would show expenses/COGS as positive numbers, even though they are subtracted in the calculation.
0
Comment
Question by:cpv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:msd1305
ID: 33494190
If you just want to display negative number without the minus sign then you can do as following.

Drag your field on the report.
Right Click -> Click on "Format Field"
Go to "Number" Tab
Click on "Customize"
Go to "Number" tab
Go to the option "Negatives"
Select "None" from the dropdown
0
 

Author Comment

by:cpv
ID: 33507600
What version of Excel are you referring to?  This is 2007..... when I right click on the field in question, I can go to 'Value Field Settings', > Number Format > There is a Custom or Special, but not Customize.
0
 
LVL 4

Expert Comment

by:msd1305
ID: 33507805
sorry.. I thought the question was about crystal report. Anyways, evenin excel you can achieve this.

Right click on cells-> Format Cells->select Nuber tab->select Number category-> now on right handside you will see a box for "Negative Numbers:" -> select 2nd option in this. which is 1234 in red color.

This way it will no display the sign but negative numbers will be displayed in red color.

will this serve your purpose??!!!
0
 

Accepted Solution

by:
cpv earned 0 total points
ID: 33507891
No actually, I found a different way... although cumbersome.....  I went into the Number tab, and custom, and created a created a mask that showed negative numbers first, and postive second..... I then was able to highlight the COGS and Expense sections in the P&L and formatted with that method..... it is kludgy, but worked.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question