Bubble.ro - because there is always something new to learn about

Logarithmic scale graphs in Excel

 

Category: Office tips'n'tricks

You probably already know how to do standard graphs in Excel. Sometimes, you'll need to take it up another level and do logarithmical or semi-logarithmical graphs. Properly made graphs can give a concise and compact form of representation, while graphs made poorly can be misleading and very confusing.

Why use logarithmical graphs?

If you find yourself with data spread on an extremely large area, the graph will be very compact and you may miss some sharp drops in values because of this.

A semi-logarithmical graph is has one of the two axes (usually the abscissa) in a logarithmical form. The logarithmical base is usually 10, but can also be “e” or any other base.

Well let's suppose you have the following table:

X 1 2 5 10 20 50 100 200 500 1000 2000
Y 1 2 4 16 256 65536 4.29E+09 1.84E+19 3.4E+38 1.16E+77 1.3E+154

The corresponding Excel scatter chart is this:

Linear Excel Graph


On the other hand, if you extract the logarithm of both X and Y rows you get this table:

log_x 0 0.3 0.7 1 1.3 1.7 2 2.3 2.7 3 3.3
log_y 0 0.3 0.6 1.2 2.41 4.82 9.63 19.27 38.53 77.06 154.13


And its corresponding graph:

Logarithmic Excel Graph


Does it make sense now? I thought so

How to obtain the logarithmic graph

Double click the axis that you want to be on a logarithmical scale. In the "Scale" tab, select the "Logarithmical Scale" option and click ok.

Scale Settings

Then, go to the Chart Options (in the Chart menu) and in the "Gridlines" tab check the "Minor gridlines" on the axes you want depending of the desired graph type: semi-logarithmical or logarithmical.

Minor Gridlines


That's it ! Hope this helps !


Posted by: Nitro on March 18, 2006 at 14:51.
 

» Comments

Har har
Very interesting. I never thought logarithmic scales would lighten up my life ;)...

Posted by SunBeam on March 20, 2006 at 05:19 PM.

Tip
Just remember to use the normal values when you select source for the graphic, Excel does the log operation for you.

Posted by Mika on March 21, 2006 at 01:18 AM.

Hello, sort anoob here
hello ladies and gents!
I've been kind of lurking around.
I love this site! thanks for having me :)






Posted by Nanbe1st on October 16, 2006 at 06:49 AM.

thanks
you just made my day
thanks so much for your help
i was able to find this site on the 1st page of google

assignment done!
:D

Posted by mr.mackey on January 24, 2007 at 02:17 AM.

Y crossing point
How can I have the Y axis cross the X axis at its MINIMAL value (X axis being logarithmic)?

Thanks in advance

Posted by anne on March 5, 2007 at 11:06 AM.

Limitations of logarithmic graphs in Excel
Unfortunately Excel is only able to use powers of ten as minimum and maximum values of the logarithmic axes.

But logarithmic scaling is not only useful for a wide data range. It is very convenient if data graphs become descriptive, i.e. if exponential data becomes a straight line. If this data is spread over less than one, or two decades, the limitation to powers of ten reduces the perceptibility of the graph.

Erich



Posted by Erich on March 21, 2007 at 05:21 AM.

Double logarithmic scales
I've wondered how to make double logarithmic scales?!

Posted by Jeppe on March 23, 2007 at 11:12 AM.

Graphing Dilemna
I'm trying to graph values in log scale, and when I check the box to do so, the dates on the x-axis move up to the top of the chart, and I can't find the option to have them appear on the bottom. Any help?


Posted by Derek on July 13, 2007 at 10:26 AM.

Hello There
2002-03 2003-04 2004-05 2005-06 2006-07
MOT 371,71 408,59 514,97 582,54 700,97
DBT 38,40 45,79 57,99 70,02 92,97
HSS 10,35 10,92 10,37 10,38 11,29
ICPR 0,75 0,83 0,72 0,75 0,78
DIF 88,30 93,61 120,65 160,98 197,72

Whenever I make graph from above mentioned data and adjust log scaling as 0, 1000, 10, 10, 0 and check logarithmic scale, it automatically changes into 0.1, 1000, 10, 10, 1 and check logarithmic scale. Could you please help me. Thanks & regards, LA


Posted by LUX on November 1, 2007 at 06:28 AM.

Possible solution
You can try to use point (.) instead of (,) when you write numbers, it might consider those are different numbers.

Posted by Indy on November 3, 2007 at 10:48 AM.

ECE
LIFE sucks!!!

Posted by Enkei Tancado on November 12, 2007 at 10:58 PM.

values on x-axis
hallo all,

How can i show values on the x-axis which has a logarithmic scale? values such as...0.001,0.1,1,10,100,... are shown by the program. What can i do to show x-Values such as 34, 63 or 0.0063.

Thank you in advance.



Posted by simeneh on November 14, 2007 at 08:11 AM.

TY for the post
Great post. this has stumped me for some time. I used to do the log values myself and then graph those.

Once again, thank you. Your insight was much appreciated as it was clear and concise.

Posted by ER sdfghjk on November 27, 2007 at 11:00 PM.

Another Comment
Another thing I noticed is that it helps if you choose to graph it in a scatter graph, it allows for double logarithmic scales (if you need it).

Posted by Betty on December 3, 2007 at 01:40 PM.

scaling
How did you do this? When I try to do it like yours, Excel 2003 complains logarithmic scale must be greater than 10. It won't let me do 0.1 like yours.

Posted by logarithmic on March 16, 2008 at 07:13 AM.

Negative values
Anyone have a trick in order to insert negative values in a log10 scale? I have a bunch of sin funtions (ranging from .05 to 6e18 in amplitude).

Thanks,

Posted by Alex on March 16, 2008 at 01:54 PM.

interesting
Thank u very much
that is what i'm looking for

Posted by mohammed saad on March 30, 2008 at 08:15 AM.

THANK YOU!!! LIFE SAVER
i was stuck on my lab. You r such an life saver!!!
THANK YOU!!!!!

Posted by stuckonlab on March 31, 2008 at 12:18 PM.

semilog graph
hi. how to plot a semilog graph? i meant a lin-log graph with only x-axis in log scale? pls help. urgent. thanks.

Posted by carmen on April 11, 2008 at 11:55 PM.

semilog scale
For semilog scale, change only x scaling to logarithmic.

Would give a straight line with bubble's data - try it!

Posted by john on April 30, 2008 at 05:38 AM.

graphs and scales
how can i know about scales and the importance of graphs or where cn i find it plus it has to be more than 300 words and also has to be submitted on monday

Posted by oyinlola on May 24, 2008 at 05:34 AM.

log and exp
I logged all of my data and plotted it but now want my legend to show the orignal non-logged data. AKA exponetiate the values currently in the legend. Is this possible?

Thanks!!

Posted by exponentiate me on June 18, 2008 at 04:13 PM.

add standard deviation
Hi All,

Anyone has an idea on how to add error bar on each of the data. how do i arrange the data set before i begin?
Thanks

Posted by sukor on July 4, 2008 at 12:38 AM.

Operations Research Analyst
Hello:

I have no idea how to convert data such as 100,96,84 into log using excel. Can anyone help me? I am using the learning curve to predict cost and I am unsure of how to change my values to logarithm to get make my model linear.

Posted by monica on August 7, 2008 at 01:33 PM.

Help urgent
i am working in vista and am totally at a loss on how to display the values of minor grid lines when using semi log scale on x axis, plz help.....

Posted by kashif hasnat on August 9, 2008 at 01:03 AM.

Excel y axis log plots before the current release: Bad for stock plotting
The log plotting limitation of the minimum and maximum to powers of 10 really limits the candlestick plot's utility. Other than upgrading to 2007 version, anyone have any thoughts on how to fudge this/ For example, if I spreadsheeted the logs of the open-high-low-clase and used linear scale, that would get the plot correct, but how do I fudge the values displayed on the Y axis to the original values?

To the question regarding negative values on a log plot: There aren't any such.

Posted by NeverLift on September 24, 2008 at 01:53 AM.

Mr.
I can not find the option for Logarithmic scale in the category axis options

Posted by Gnanchandra.s on March 31, 2009 at 02:26 PM.

booyah
nice one
totally forgot how to do this
one search and right on the money

BIZNASS!!!!!

Posted by popeye on May 1, 2009 at 05:51 AM.

SuzukiPilot
Hi i am trying to get a Log scale of 10 - 2000 on the x axis. I am only able to get either 1000 or 10,000. Does anybody have a suggestion how i can get the scale down to 2000 please?

Posted by Steve on June 10, 2009 at 07:54 AM.

Adding a trendline to log scale
I've managed to graph my data using a double log scale (that is both the x and y axis are in log scale) but I'm not able to add a linear trendline. Well, technically, I'm able to add the trendline, its just not linear. It's quite curved... but when I go to options to format the trendline, its definitely the linear option. Any ideas?

Posted by Kara on July 2, 2009 at 06:20 PM.

Adding a trendline to log / log plot
Remember that a straight line on a log/log plot is actually a power law. Something like y = x^z.

You can add a straight line to log/log plot by fitting a "power" trendline.

Posted by gerry on August 6, 2009 at 11:18 AM.

GOOD JOB BRO
thank yau bro...vary good info...you safe me bro...

Posted by RIZAL on September 10, 2009 at 04:10 AM.

minor gridlines
I've been using log plots a long time, but somehow never clicked "show minor gridlines." Now I feel like an idiot, but at least I can show resolution tighter than a decade!

thanks

Posted by Brent on November 25, 2009 at 10:50 AM.

Tush
Excelent article, you are a lifesaver, thank you

Posted by Tush on December 8, 2009 at 02:03 AM.

Negative values
Yes, there are such a thing as negative values.

For example, if you want to display a set of power levels recorded in dBm (decibels relative to milliwatt). Any values below 1 mW will be negative, but they will be log scale. How do you get Excel to display these negative values in log scale?

Posted by Matt on December 8, 2009 at 10:55 AM.

Finding points
After I have my points setup and I have my graph... how can I find specific points on the line?? Like how do I find the x-value when I know the y-value??

Posted by Rocky on January 21, 2010 at 11:19 AM.

how to draw linear trend line for log vs log plot
dear friends
i am drawing a plot log vs log and want to draw linear trend line but it is coming in parabolic, so help how to draw
thx in advance

Posted by anything on January 23, 2010 at 04:37 AM.

RE: linear trend line
Do a log transformation of the data and then you can plot a straight trend line to it easily

Posted by Brian P on February 3, 2010 at 07:07 AM.

no zero allowed and grid problem
Remember that zero (0) does not exist on a log scale.

I have an issue with showing a decimal log scale that does not start at an integer power of 10. I want x= 30 to 1000 but the best I can do is x= 10 to 1000. If I set the min to 30, the grid is based on 30, not 10.

Also, in VBA, the seriescollection line width is "border"???
.SeriesCollection(1).Border.Weight = xlHairline
Go figure?!

Posted by Steve on April 26, 2010 at 07:17 PM.

THANK YOU
THANK YOU VERY MUCH

Posted by JIGNASHA PANCHAL on May 11, 2010 at 01:31 AM.

2007 dramas
I understand the instructions you state but excel2007 seems to be a little different. I can get the log. scale on the y axis but not the x axis. can you help with this???

Posted by Ewen on May 26, 2010 at 12:49 AM.

Eng
Qustion
Find me Slope(m),Y-intercept(b),X-Intercept
X1=10 Logarthmic scale,X2=100 logarthmic scale
Y1= 0.6086271206 Linear scale, Y2=0.9744801206
Thnk You

Posted by mustafa on June 3, 2010 at 01:51 PM.

pls answer this
my prof is asking us to "prove that logarithms never appear in a log scale"... pls help me find the answer

Posted by Lee on August 31, 2010 at 11:45 PM.

Add trendline to semi-log scale ?
Hi

I have a semi-log scale with a series of 15 datapoints and want to add a logarithmic trendline to the graph. If I click on one datapoint in the graph and click "add trendline" and then select "logarithmic scale" it doesnt do anything. Potentially because my data is organised in different series (which I cannot change)?!

Any idea, anyone?

thanks a lot in advance!

Posted by Jenny on September 28, 2010 at 10:16 PM.

Log Scale y-Axis Conversion


Posted by Joe on October 7, 2010 at 01:17 PM.

excel 2007
on excel 2007 can only make x axis a log scale if the graph is a scatter graph, not line graph

Posted by Simon on October 14, 2010 at 10:45 PM.

Great job!
Thank you very much! You helped me a lot! Now I know how it works!

Posted by Yilmaz on December 11, 2010 at 01:28 PM.

Potentially a really stupid question...
What is the difference between the logarithmic and semi-logarithmic scales? I'm in my first year of uni and I never ever understood logarithms :(

Posted by Amber on January 20, 2011 at 06:27 AM.

LOG
LOOOOOOOOOOOOOOOOOOOG!!!!!!

Posted by LOGan on January 20, 2011 at 06:50 AM.

probit scale graph
Is it possible to make a probit scale graph on excel????????????

Posted by caz on April 10, 2011 at 01:00 AM.

tnx
thanks alot:)

Posted by shima on May 16, 2011 at 02:12 PM.

Which trendline for a semi log base 10 graph?
Which trendline choice, if any available, should I use for an Excel scatter plot that has the X-axis in log 10 and the y-axis arithmatic? It seems that what is available is ln, not log 10.

Posted by HKS on September 25, 2011 at 04:32 PM.

thanks!
thank you very much! u saved my day!

Posted by t3z on October 11, 2011 at 02:30 AM.

maziar
thank you very very very ... much!!!!!!!!!!!

Posted by maziar on November 5, 2011 at 03:20 AM.

Thanks
Thanks for sharing

Posted by Prash on November 14, 2011 at 11:58 AM.

LOGS ON EXCEL
Hi i was wondering how to plot the value of zero on excel. if use semi-log on excel it only starts from 1 any help would be amazing


Posted by Khush Shah on November 25, 2011 at 06:48 AM.

Miss
Hey for my lab i got a total cfu/ml as 0 when showin the effect of disinfectant, how do i plot this?? :(

Posted by Lauren on December 2, 2011 at 05:15 PM.

Miss
Hey for my lab i got a total cfu/ml as 0 when showin the effect of disinfectant, how do i plot this?? :(

Posted by Lauren on December 2, 2011 at 05:18 PM.

Log scale in secondary axes
I am plotting data on primary and secondary axes, however when I am trying to convert the x-axis to log scale, my data in secondary axes goes crazy and doesn't follow the x-axis.. :S someone knows what's happening?? or it is just an excel bug??

Posted by ClauMa on January 30, 2012 at 10:40 AM.

Thanks!
Great, Thanks

Posted by Emad on February 11, 2012 at 02:37 PM.

To all of you that didn't find the setting in Excel 2003. It is there.
To all of you that didn't find minor grid the "Minor gridlines" settings. Read the instructions carefully. Go exacly to where he says. It is right there, in the Chart Options. Not in the "Format axis" dialog. Not on any tab there. But on in the Chart Options. CHART OPTIONS. It is right there. Thanks for the article.

Posted by Lennart on April 23, 2012 at 07:17 AM.

Y-scale semi-log plots
I am trying to plot a semi-log graph using Excel 2010 with Y-axis on the log scale. However, when I tried to insert the Y-error bars some of datapoints only low the upper part of the bar and not the lower bar. I tried changing the Y-axis scale (from auto to fixed) but it did not help. Any suggestions on how to resolve this error bar issue?
Thx in advance

Posted by BRS on May 16, 2012 at 10:57 AM.

Log graph
Thanks ! Exactly what I was looking for.

Posted by HL Chew on May 21, 2012 at 03:56 AM.

Scale numbering display
I would like to have the log X axis display numers other than factors of 10.
I am making a graph based on frequency Hz for 1/3 octave bands and would like the x axis to disply evey whole octave in Hz.

Any way to do this in excel?

N.B.

Posted by N.B. on July 5, 2012 at 08:29 PM.

Log scales limited
Excel 2007+ will not show all grid lines on data ranges exceeding 10^8 or 10^-8. Open Office Calc will. Workaround is to use OOo or hand draw. My work involves ranges 10^+/-24.

Posted by cyberchem on August 28, 2012 at 11:40 AM.

Is it possible to make a log-probit scale graph in Excel ?
Is it possible to make a log-probit scale graph in Excel ?


Posted by Andrew on January 24, 2013 at 03:42 AM.

Random Article


How can I turn off system beeps?

System beeps are those sounds you hear when an error message or an information/warning message appears. They can be very annoying especially if your speaker volume is set to a high level (If you listen to rock music for example). To turn them off you will have to modify the system registry. ...

Search


Feeds


Bubble.ro RSS Feed

All Categories


Articles


Aetolia - The Midnight Age
How to create the histogram of an image using PHP
How to convert an image to grayscale using PHP
How to check if an image is grayscale in PHP
Interchanging 2 variables without the use of a third
Error launching browser window:no XBL binding for browser
Convert the AOL user session collection to a MySQL database
Introduction to Matlab
Creating a customized session handling system in PHP (part II)
Creating a customized session handling system in PHP (part I)
Firefox crashing with Yahoo! Messenger
ADL Search for oDC
Video codecs explained
Browsershots
How to use Auto-Away Message with oDC
Create complete Windows XP disk with SP2 and all updates
Data Execution Prevention error message in Windows XP
Google Mars
Logarithmic scale graphs in Excel
Urban Dictionary (or wtf does l33t mean?)
Learn more about BIOS
Backup your Firefox and Thunderbird settings
Syndicate your Yahoo 360 profile
What is Google PageRank?
'Cannot Open the File: Mk:@MSITStore' Error Message
Get your Gmail with Mozilla Thunderbird
E-Books links
Change the size of your Explorer thumbnails
Remove previews from Windows Explorer
How can I turn off system beeps?
How do I disable Internet Explorer?
What are proxies or how do I protect my anonymity?
How to set aliases triggers or macros in MushClient
What is RSS?
Palm Zire 31 fast review
oDC Installation and Basic Configuration
How I built a 2x80W amplifier (using power modules)
Leech/HotLink Protection
How to block referrer detection?
How to find out your IP address
Getting started with Mushclient
What is spyware and how do I protect my PC from it?
Stumble Upon - random surfing around the web
Automatic file backup for Windows users
How can I read foreign language sites?
Protect your web surfing privacy!
What is BitTorrent?
No more ads! Adblock for Firefox
Why use Firefox instead of Internet Explorer?
How do I create my own Yahoo ID?
© Copyright 2006-2014 Bubble. All rights reserved. Sitemap - Contact