Data Labels (How to Merge two labels into one)
- Thread starter Serene
- Start date
AbdalKarim
New Member
Hi Serene, Disclaimer: There could be easier ways of doing this.
Lets say you are collecting funds for your pet project. Your goal is to raise 40 dollars.
1) So put 40 dollars in a cell, say, A2.
2) Put all your dollars given by each contributor in a column, say, A2:A16
3) Lets say you want to show the corresponding % for each contributor in col B2:B16
4) Go to B2 and write this formula =(100*A2)/A$2 and hit enter
5) Drag it down till B16.
bingo you have your dollars values in A3:A16 and your percentages in B2:B16. There are simpler ways of doing it, like using % in the formula, but, I think this is most easy per my opinion...
Lets say you are collecting funds for your pet project. Your goal is to raise 40 dollars.
1) So put 40 dollars in a cell, say, A2.
2) Put all your dollars given by each contributor in a column, say, A2:A16
3) Lets say you want to show the corresponding % for each contributor in col B2:B16
4) Go to B2 and write this formula =(100*A2)/A$2 and hit enter
5) Drag it down till B16.
bingo you have your dollars values in A3:A16 and your percentages in B2:B16. There are simpler ways of doing it, like using % in the formula, but, I think this is most easy per my opinion...
Hi Karim
Thanks for your response.
I realised my question might not have been clear.
Yes, I already have the two columns. But I would like to show these two column values into one label. However, the label could only take the value or the percentage but not both.
I think I have to create a third column to merge the first 2. Then I can use this 3rd column as a label. How do I merge these 2 columns so it could be shown in the following format. For eg, $10 (10%)
Thanks again
Thanks for your response.
I realised my question might not have been clear.
Yes, I already have the two columns. But I would like to show these two column values into one label. However, the label could only take the value or the percentage but not both.
I think I have to create a third column to merge the first 2. Then I can use this 3rd column as a label. How do I merge these 2 columns so it could be shown in the following format. For eg, $10 (10%)
Thanks again
Would anybody advise whether it is possible to amend this formula:
=Concatenate(text(B2,"$##"),text(C2," (##.#%)"))
so that my zero whole numbers are displayed as "0"?
e.g. using the above formula my 0.7% are displayed as .7% (in the cell and in the label), but I want it to be displayed as 0.7% (with ZERO in front of the decimal mark).
Thank you!
=Concatenate(text(B2,"$##"),text(C2," (##.#%)"))
so that my zero whole numbers are displayed as "0"?
e.g. using the above formula my 0.7% are displayed as .7% (in the cell and in the label), but I want it to be displayed as 0.7% (with ZERO in front of the decimal mark).
Thank you!
NARAYANK991
Excel Ninja
Hi Yulia ,
The format string used above ( "$##" or " (##.#%)" ) is the same as what you would use in Excel to format a cell or a range. When you enter .7 in any cell , right click on that cell , and select Format Cells -> Custom , you can choose all sorts of formats , and see for yourself how the display of the cell contents will be.
The format #.## will display a number like 0.7 as .7
Changing the format to 0.## will display the same number .7 as 0.7
Changing the format to 0.#0 will display .7 as 0.70
Play around with the formats and see for yourself.
Narayan
The format string used above ( "$##" or " (##.#%)" ) is the same as what you would use in Excel to format a cell or a range. When you enter .7 in any cell , right click on that cell , and select Format Cells -> Custom , you can choose all sorts of formats , and see for yourself how the display of the cell contents will be.
The format #.## will display a number like 0.7 as .7
Changing the format to 0.## will display the same number .7 as 0.7
Changing the format to 0.#0 will display .7 as 0.70
Play around with the formats and see for yourself.
Narayan