How can I assign an asterisk to a cell which has been formatted as 'Currency' and is also used as raw data for a Pivot Table?

microsoft-excel worksheet-function pivot-table

10,121

When you add the asterisk to the cell, you are changing it from a number to a string (i.e., 'text'). What you need to do is change the formatting in both places. You can go into custom formatting and set it to £#\*,##0.00\* or something similar. The \ before the * says you are interested in the character * and not the command.

The docs can help explain more on formatting codes so you can make your own.

10,121

Craig

Whilst I have dabbled with WordPress theme templates for some time now, I am fairly new to php coding myself. Excuse me if any of my questions are scattergun but I am keen on learning and believe the best way to learn is through doing. Once I have got to grips with php and more confident with how it all works, I will hopefully be able to help others here who are looking to become competent in using php.

Updated on September 18, 2022

Comments

Craig over 1 year

I have a spreadsheet full of raw data, where I have created a Pivot Table to help organise and manage said raw data.

For the purposes of this question, I would like to use the below image to help illustrate what I am looking to achieve ...

In the 'Bill Amount' column, I have formatted the cells to display 'Currency'. I have formatted the Pivot Table to also display 'Currency'.

What I would like to do now is append an asterisk (*) to certain cells within the 'Bill Amount' column, which would also be presented within the Pivot Table without affecting the maths.

Failed Function Option

I went to an empty cell and inserted =C2&"*". The idea being to call the C2 cell entry (£10.00 in the case of the above illustration) and append an asterisk to the entry. Whilst this worked, it removed the 'Currency' format and thus the '£' from both the cell and Pivot Table.

I also tried the =CONCATENATE(C2,"*") approach, which resulted in the same outcome as above.

Is anyone aware on how I would be able to append an asterisk to a cell entry which would also be presented within the Pivot Table without affecting any of the maths?
- Rajesh Sinha over 5 years
  
  Are you looking to apply * as cheque printing symbol? Like $*1000.00 or $**1000.00
- Craig over 5 years
  
  I'm looking up achieve, for example, '£1000.00*'.
- Rajesh Sinha over 5 years
  
  use this £#,##0.00\* as custom format.
Craig over 5 years

Excellent. This allows me to use the Asterix, whilst maintaining the 'Currency' format. Is there anyway I can get this Astertix to appear within the Pivot Table itself?
BobtheMagicMoose over 5 years

Just apply the number formatting to the pivot table like you did for the other cells.
Craig over 5 years

Oh yes! Stupid moment ... Tried editing the Pivot Table Cell rather than going through the 'Format Cells' option. Thanks for your answer, works great!
BobtheMagicMoose over 5 years

Welcome! :D Formatting can do a lot of fun stuff