Excel - Add 2nd set of labels

Screen shot of chart with data.  Rather than the points at 0 for the state, I would like the names of states updated that month.KM-xlsx.xlsx
I am trying to add state names to a chart as to when they were updated. The chart has 2 sets of numeric data and the last column shows the states that were updated that month. I have tried doing a second set of data to no avail.  I feel I am just missing one thing here and I cannot find it.  Any ideas?
pmpataneAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
OK, manually, the steps are:

1 - Create the Labels column as in the screenshot above.
2 - Leave the State series markers visible for this exercise
3 - add data labels to the State series, positioned above the markers, using the value, which will show as 0
4 - click a data label to select all labels
5 - click one data label and with the selected label
-- click into the formula bar
-- enter a = sign
-- click the cell in column E that corresponds to that position, i.e. the first label for the Sep-13 data point would link to cell E4, link the next one to E5 and so on.
6 -  Repeat step 5 for all labels.

That is exactly what the add-in does, only faster. When a linked cell in column E is blank, then the label will be blank.

Format the State series to have no line and no markers.

Let me know if that works for you.

cheers, teylyn
0
 
Katie PierceCommented:
Looks like you need to graph the "State" line on a Secondary axis:

In the chart ribbon, select "Change Chart Type", then select the series and you can select "Secondary Axis" for it.

Make the secondary axis read the names of the States.  Then the points will plot on state vertically, and keep the chronology horizontally to coincide with the other data.
0
 
pmpataneAuthor Commented:
Hi Katie, thank you for your quick response and forgive my lack thereof. I click the State series and can get a secondary axis, but I do not understand how to get the State names to appear.  Sorry for my denseness
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Katie PierceCommented:
You know what? I've been looking at it now, and I don't think it's actually possible.  The graph wants a numerical vertical axis, so you can't list the states on the right.

I'll keep thinking, but so far I'm stumped.
0
 
pmpataneAuthor Commented:
Excel is my baby, glad I am not the only "stumped" expert
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you can add the text as labels to the chart. The easiest way to do that is to use the free add-in "XY Chart Labeler" which can be downloaded here: http://www.appspro.com/Utilities/ChartLabeler.htmhttp://www.appspro.com/Utilities/ChartLabeler.htm.

After you've installed the add-in, create a new column for the labels. In cell E4 enter the formula

=IF(SUM(B4:C4)>0,D4,"")

Copy down to cell E31.

Now select the State series, click the XY Chart Labels ribbon > Add Labels. Select E4 to E31 as the label range and specify the position of the labels. Hit OK.

The add-in will run a macro that creates labels for the series and then uses a formula in each label that refers to the respective cell in column E. You could also do that manually, but the add-in is much faster.

As a last step, format the data markers for the State series to "none" so they become invisible.

screenshot
See attached.

cheers, teylyn
KM-xlsx.xlsx
0
 
pmpataneAuthor Commented:
My apologies, teylyn, for a very delayed response.  I have been trying to convince my employer to allow a 3rd-party app in our environment, no luck.  So I have been trying to find the way you mentioned to make this happen manually.
0
 
pmpataneAuthor Commented:
Thanks for both answers!  At home I will use the app, but at work I will need to use the long way!  Sorry for my delay again, I am buried with projects and multi-tasking not my greatest of strengths.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.