numbers in the legend - clikc by click

[mitja decman]

Hi,



I am using Excel for ages and I noticed that in the case, I have to put
numbers in the legend, I have to click and select each of them separately
every time. Is there a faster way?



This is an example of "Testing results" chart (I need years on X-axis and
test numbers in the Legend)



test no.\year 2006 2007 2008

213345 1231 1311 1366

213367 1235 1333 1335



In Excel 2007:

I select everything and insert a Column Chart. Excel puts test numbers
(and even years) as columns. Wrong!?

I select data only and create a Chart. On the chart I use right click,
"Select data" and for each "Legend Item - series name" I click and select
appropriate cell. Too much clicking (this is a simple example, we have more
than 10 test no. in the chart)!?



I know I could put special sign ( ' ) in front of every test number (Excel
then interprets it as a text) and it would work, but I don't want to,
because we use numbers for that. So how to put or insert numbers in the
legend of the chart as fast as possible?



Thanks

Mitja

 

[Andy Pope]

Hi,

Try removing the 'test no.\year' text from the top left corner of your data
set.
This will allow excel to better guess you data, even in xl2007. Where the
axis will be years and the series names (legend) will be part numbers.

Cheers
Andy

 

[mitja decman]

This works only if I make a chart from the whole table.
OK. But what if I want to create a chart just for last two years 2007 and
2008? Then removing the 'test no.\year' and selecting appropriate cells in
columns does not help.
What then?

Mitja

 

[Andy Pope]

If you format the test numbers as text then select the range A1:A3,C13 it
will work.

But what I have noticed that is even more worrying than the excessive clicks
required is the scaling of the value axis.
It sets the Minimum value to 1150. This is not good practice for column
charts.

Cheers
Andy

 

[Andy Pope]

Regarding my comment about the automatic axis scaling. Whilst valid this is
not something that was new in xl2007, it occurs in 2003 as well.

 

[Deskpilot]

I took you minor table, make a quick sheet.
I found that by making the date cells as 12/31/20xx and then custom
formating to show only YYYY, I got the desired results. Excell needs to What
the function of special numbers is (Date / time / phone etc)

I ommitted the Test / Yr cell and got an INSTANT, ready to read graph