Numeric vs text in Excel

[Guest]

I often have tables in Excel that I use the Chart Wizard to graph in a few clicks. However, if the first column contains numeric values it is treated as a series by the wizard rather than as a data range. Eg. lets say I'm examining the quantities of a certain product in orders placed by customers

Widgets/Order Coun
0 9
1 2
2 2
3 5
4 1
5

Highlighting this range and running the wizard treats both columns as a series. I can manually tell Excel to treat the first column as a range rather than a series but it's a number of extra steps

I've found that if I preceed each of the first columns values with an apostrophie (') Excel treats these cells as text and the wizard will work the way I want by default. I can't find a way other than adding apostrophies to convince Excel that these cells should be treated as text though. The option to format numerics as text in Format Cells does not do this

Is there another way to tell Excel these cells should be treated as text rather than numbers? Ideally I'd like a cell property I can set programatically via OLE Automation

TIA

 

[Jon Peltier]

If you make an XY Scatter chart, the first column will be used for X
values. Doesn't help if you want a column chart.

However, if you leave the cell above the X value range blank, and have a
label above the Y value range(s), this tells Excel there's something
special about the first row (series names) and column (category names).
The data should look like this; select a single cell in the range, or
select the entire range including the top row with the blank cell.

Count
0 98
1 23
2 27
3 59
4 16
5 3

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______