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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.