(Visually Appealing) Scientific Notation on the Y-Axis

[nima]

Hi, I am new to these forums. I have really come to tap into the power
of excel during the past 1-2 years. As a research scientist, my latest
achievement is a spreadsheet that takes raw data and calculates
everything I need from simple mathimatical manipulation to statistical
analysis-and now, graphing. But, I am having a few problems:
the graphs I make are used in published results and large poster
displays-and, they're logarithmic along the Y-axis. I have a fairly
good understanding of number-formatting, but I can't figure out how (if
it is even possible) for me to make the y-axis numbers appear as
10<superscript>6</superscript> instead of '1.00 E+06'. I've tinkered
around with it, and the best i've gotten is '1 e6' using a custom
number format. Even if I have to do this manually per graph, fine-but I
can't even BEGIN to get this to work! BTW I'm using Bar Graphs. Also, I
have excel auto-graph a group of series that fill up as I assign them,
but is there a way to make excel exclude empty series?

 

[nima]

So does anyone have any suggestions, I've lurked through man
excel tutorials but I cant find this covered anywhere! :

 

[Tushar Mehta]

Unfortunately, XL doesn't do numbers in superscript scientific format.
The best you can do, if you want, is to add a text box to the chart. To
do so, select the box and enter the desired number, say, 106 for 10 to
the power of 6.

Then, select just the 6 then Format | Text Box... | and in the Font tab
check Superscript. Duplicate this box and replace the 6 with other
powers of your choice. Position each box appropriately.

To remove the default numbers along the y axis, double-click the axis.
In the resulting dialog box, in the Patterns tab set the 'Tick mark
labels' to None.

Not quite sure I understand your question/concern about empty series.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[nima]

thanks for help, I discovered that all of five minutes ago I guess
that will have to suffice for now.

As for the empty series, I looked at your example among others, for
guidance on dynamic charting and such, but I dont know if it can be
applied to dynamically import series (if that makes sense).
The template file I have made automatically sets up a bar (column)
graph and retrieves means and standard errors that are auto-calculated
from raw data. Unfortunately, experiment to experiment, the number of
means varies. Here's where the problem comes in. Each column in the
graph corresponds to a different average, so in the legend, I want it
to have all the different columns labeled accordingly. I managed this
by making a bar graph using that automatically retrieves the averages
(and names, and error bars) from another worksheet by making each
column its own series. The problem is that sometimes i dont utilize all
the series possible. So the legend still includes these labels. I know i
can delete them one by one, but I spit out a lot of graphs and I would
like to automate this if possible. Especially because I want this
template to be convenient for other workers. Much thanks!

 

[Bernard Liengme]

I have a solution (not yet on my website) that uses a free font which has
all digits in subscript and superscript
Send me a message (my private email not the newsgroup) so I can send you a
file

 

[Jon Peltier]

I have a technique on my web site to handle this task:

http://peltiertech.com/Excel/Charts/ScientificNotation.html

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

 

[nima]

Jon, I had came across your webpage earlier, but I haven't trie
implementing it yet. I was unsure if it would work for a bar graph? An
thoughts? If it works, would there be any way to program a macro t
expedite the process? It seems that data labels are a quicker yet les
precise method. By the way, instead if individual data labels, I mad
all labels into one text box; its easier to copy/paste and chang
superscripts that way.
Thanks for all the help so far.
Nima

oh, btw-any thoughts on the dynamic charting issue

 

[Jon Peltier]

1. This technique works for any axis type, almost any chart type. You just
need to define your X and Y values appropriately.

2. This can be done programmatically with a little work.

3. Using a single textbox for all labels (including lots of spaces, right)
must be incredibly tedious to adjust when the axis scale changes.

4. I don't know what you mean about the dynamic chart issue.

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

 

[nima]

Regarding the dynamic graphing: I have upto 6 series of data. I plot a
bar graph using the mean of each group, and use the calculated standard
error as the error. The way I am doing it, each bar/column is its own
series. I did it this way to make the legend show what each column
represents. My problem is that I don't always utilize all the series,
so my legend shows entries identifying some series that are not
actually being graphed, see picture.

I want it to automatically graph the columns that have data. I
understand that this may be possible I graph it as one series, but can
I still have each column associated with a name in the legend?

Nima


+-------------------------------------------------------------------+
|Filename: graph.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4225 |
+-------------------------------------------------------------------+

 

[Jon Peltier]

You should put different topics in different threads.

Make your chart with a single series, then double click the series, and on
the Options tab, check the Vary Colors by Point box. This format each point
individually, with its own legend entry. This only works in charts with one
series.

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

 

[nima]

I dont see how I can label each point with a cell reference anymore. I
varied the colors by points as you said (and changed overlap to 0 to
make it look better). I have 1 series with the 6 data points. I named
the series as a 6 cell range that corresponds to the 6 data points. In
the legend it labels the colors '1, 2, 3, 4, 5, 6'. I guess I still
dont understand how I get the names for each data point into the
legend.
<quote>This only works in charts with one
series.</quote>
If I follow your tutorial for making scientific annotated axes, then I
have to add another series, so wouldn't it remove the legend entries?

thank you for your patience.
Sorry for not posting in a new thread-i am new to this.

 

[Jon Peltier]

What shows up in the legend are the category labels. So if the labels were
A, B, C, etc, the legend would show these. If no category labels are
specified, Excel uses the counting numbers.

I realize that this technique is incompatible with adding a dummy series for
the axis, so I guess your new question is intricately linked to the older
one in the thread.

Rather than relying on the legend for a label, you could apply data labels
to the column series, so that each point has a label. Excel lets you use
category labels or values, and with the help of a third party Excel add-in,
you could use any cell contents you wish. Here are a couple good, free
labeling utilities:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com/ss

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