Charts & Data Labels Using =NA()

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
Short version: is there a way to show data labels for everything with data, but not label =NA() values?

Long version & background:
There is a small table based on VLOOKUPs which populate the data for the chart. The data is currency ranging from -$10 to $30. These are vlookups coupled with IFERROR statements that display an NA() if there is no data. The NA is crucial as text is charted at 0 and I need the chart to not display anything for weeks without data.

Now the issue that comes up is that I want the #N/A's to be hidden or displayed as "" in the data labels, but i'm stuck.

The only semi related post I could find is as follows, but it doesn't work with Excel 2007:

http://www.mrexcel.com/forum/showthread.php?t=77006&highlight=#N/A+Data+label

My data label number is currently formatted as such:

$0.00;[Red]-$0.00;$0;""
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
NA() suppresses the plotting of points in line and XY charts, but not in bar charts. Use "" instead, and apply the number format that suppresses zero values.
 

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
Thanks for the reply, but that's part of the problem I'm having. I can set the data lables to the custom number format and have them show up as blank, and if the number was actually zero I could use $0.0000001 so it rounds to 0 but isn't so it will display.

THe problem with the "" is that text also plots at zero. It's normally not a problem, but since my data ranges from -$10, to $30, that means that zero's show up as if there is data, when in actuallity there may not.

I'm hoping there's a cleaner way to make this work as I wanted to avoid making hidden series.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Is it in fact a bar chart? Or a line chart? Since they behave differently in this situation, it would help to know. For a bar chart, "" is invariably the best choice, whereas for a line chart, NA() is better, since you don't even get a point where the NA() occurs. Given this, I can't tell from your discussion which chart type you have, and why it's causing a problem.

Another option would be to have two columns of data, one using NA() for the formula result, the other using "". Plot the NA(), and use something like Rob Bovey's Chart Labeler to use the second column for labels.
 

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701

ADVERTISEMENT

Ugh.... I thought I had discussed it but it seems I deleted that paragraph describing the charts.

It is indeed a bar chart (column actually) and now that I have been able to get away from work and revisit I think I can more clearly articulate it.

The "" wasn't working because I had set my axis to cross at the bottom of the chart, rather than at the 0 point which is why it kept showing a bar.

To accomplish what I needed I set up the normal series to chart NAs and then set up an additional series which I set to overlap and be transparent with the data labels.

Thanks for taking a look and sorry I missed the chart type.
 

msaadkamal

New Member
Joined
Aug 3, 2011
Messages
8
I am using bar charts.
I have made two data series for the same thing just to change color for negative and positve bars. I used if(a1>0,a1,"") for positive numbers and if(a1<0,a1,""). and then put the bars at 100% overlap. It worked fine.

BUT !!

When i insert the data labels. it shows 2 data labels for every same. I tried to work it out but can't and want your help.

What i want is that, it should show only labels for the numbers which are non zero, or which are from the series.

Please help me.
 

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701

ADVERTISEMENT

Assuming you have your table in a simple setup like A1:G2, then you can change your source to get the chart you want based on what we have below.

When you first chart it, you will have three bars on your chart. Look at your legend to see which corresponds with which. Select the series that has your data labels and set them to show.

Format your data labels as follows: #;[Red]#;"";""

On the Data Label data series, adjust it so that fill is "None" and line color is "None" and set overlap to 100% THen you can hide your legend, or remove some of the entries from the legend by clicking once to select the legend, clicking once more on the NAME of the series you want to remove and then pushing delete.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1-Jan</TD><TD style="TEXT-ALIGN: center">2-Jan</TD><TD style="TEXT-ALIGN: center">3-Jan</TD><TD style="TEXT-ALIGN: center">4-Jan</TD><TD style="TEXT-ALIGN: center">5-Jan</TD><TD style="TEXT-ALIGN: center">6-Jan</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Data</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">-5</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">-4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD style="TEXT-ALIGN: center">1-Jan</TD><TD style="TEXT-ALIGN: center">2-Jan</TD><TD style="TEXT-ALIGN: center">3-Jan</TD><TD style="TEXT-ALIGN: center">4-Jan</TD><TD style="TEXT-ALIGN: center">5-Jan</TD><TD style="TEXT-ALIGN: center">6-Jan</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Data Pos</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Data Neg</TD><TD style="TEXT-ALIGN: center">-3</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">-5</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">-4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Data Label</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">-5</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">-4</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B5</TD><TD>=IF(B2>0,B2,"")</TD></TR><TR><TD>C5</TD><TD>=IF(C2>0,C2,"")</TD></TR><TR><TD>D5</TD><TD>=IF(D2>0,D2,"")</TD></TR><TR><TD>E5</TD><TD>=IF(E2>0,E2,"")</TD></TR><TR><TD>F5</TD><TD>=IF(F2>0,F2,"")</TD></TR><TR><TD>G5</TD><TD>=IF(G2>0,G2,"")</TD></TR><TR><TD>B6</TD><TD>=IF(B2<0,B2,"")</TD></TR><TR><TD>C6</TD><TD>=IF(C2<0,C2,"")</TD></TR><TR><TD>D6</TD><TD>=IF(D2<0,D2,"")</TD></TR><TR><TD>E6</TD><TD>=IF(E2<0,E2,"")</TD></TR><TR><TD>F6</TD><TD>=IF(F2<0,F2,"")</TD></TR><TR><TD>G6</TD><TD>=IF(G2<0,G2,"")</TD></TR><TR><TD>B7</TD><TD>=B2</TD></TR><TR><TD>C7</TD><TD>=C2</TD></TR><TR><TD>D7</TD><TD>=D2</TD></TR><TR><TD>E7</TD><TD>=E2</TD></TR><TR><TD>F7</TD><TD>=F2</TD></TR><TR><TD>G7</TD><TD>=G2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You need to use custom number formats for your data labels, like

0;;; to show positive values in the positive series' labels
;-0;; to show negative values in the negative series' labels

Or you could use Invert if Negative formatting using only one series.
 

msaadkamal

New Member
Joined
Aug 3, 2011
Messages
8
Thank you so much CWats and Jon Peltier, I must accept you guys are the gurus.

I am able to do it. I have some further queries


I have data like 6.2% -3.1% 2.5% -1.2%

What custom format should i use if I want the numbers to show in percentage with one digit after decimal with green color for positive numbers and red color for negative numbers. Also if i want to put the positive and minus sign also, thanks in advance
 

Watch MrExcel Video

Forum statistics

Threads
1,122,753
Messages
5,597,925
Members
414,191
Latest member
debbhatta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top