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

In article <[email protected]>,
[email protected] says...
>
> 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
> ------------------------------------------------------------------------
> nima's Profile: http://www.excelforum.com/member.php...o&userid=30408
> View this thread: http://www.excelforum.com/showthread...hreadid=500770
>
>

[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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"nima" <[email protected]> wrote in message
news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> nima's Profile:
> http://www.excelforum.com/member.php...o&userid=30408
> View this thread: http://www.excelforum.com/showthread...hreadid=500770
>

[Jon Peltier]

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

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

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


"nima" <[email protected]> wrote in message
news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> nima's Profile:
> http://www.excelforum.com/member.php...o&userid=30408
> View this thread: http://www.excelforum.com/showthread...hreadid=500770
>

[nima]

Jon, I had came across your webpage earlier, but I haven't tried implementing it yet. I was unsure if it would work for a bar graph? Any thoughts? If it works, would there be any way to program a macro to expedite the process? It seems that data labels are a quicker yet less precise method. By the way, instead if individual data labels, I made all labels into one text box; its easier to copy/paste and change 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" <[email protected]> wrote in message
news:[email protected]...
>
> Jon, I had came across your webpage earlier, but I haven't tried
> implementing it yet. I was unsure if it would work for a bar graph? Any
> thoughts? If it works, would there be any way to program a macro to
> expedite the process? It seems that data labels are a quicker yet less
> precise method. By the way, instead if individual data labels, I made
> all labels into one text box; its easier to copy/paste and change
> superscripts that way.
> Thanks for all the help so far.
> Nima
>
> oh, btw-any thoughts on the dynamic charting issue?
>
>
> --
> nima

[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

[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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> nima
> ------------------------------------------------------------------------
> nima's Profile:
> http://www.excelforum.com/member.php...o&userid=30408
> View this thread: http://www.excelforum.com/showthread...hreadid=500770
>

[nima]

EDIT: I have found how to make the data labels work, but will it work with the scientific axes?
<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/
_______


"nima" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> nima
> ------------------------------------------------------------------------
> nima's Profile:
> http://www.excelforum.com/member.php...o&userid=30408
> View this thread: http://www.excelforum.com/showthread...hreadid=500770
>