How to Plot Logarithmic Functions in Excel
Microsoft Excel is used by many people to store information and create charts, but it can also be used to graph functions. Log functions are no exception. By using a combination of Excel's "LOG()" function and the regression tool you can create a smooth looking log graph. With a basic understanding of Excel the process only takes a few minutes.
Enter a series of values in column "A" that correspond to the x-axis markers you want to use. For example if your data runs from 0-100, you might choose to enter the "x" values 10, 20, 30, etc. Just make sure that your "x" values are spread out and not bunched closely together.
Enter "=LOG(A1,[base])" in cell B1, replacing [base] with the logarithmic base number your function uses. Most log functions use base 10. Alternatively, you can replace "LOG" with "LN" if you want to use the natural logarithm function.
Copy this formula to all of the remaining cells in the "B" column for which there are associated "x" values in the "A" column. You should now have a complete table of values. This table will serve as a guideline for Excel to calculate a smooth graph.
Click on the "Insert" tab and from under the "Charts" header choose "Scatter with Only Markers."
Click on the "Layout" tab, click on "Trendline" and select "More Trendline Options."
Select "Logarithmic" and click "Close." This creates a smooth graph that lines up to the table of data you created.
(Optional) Right-click on one of the marker points and choose "Format Data Series...".
(Optional) Click on "Marker Options" and choose "None." This hides the guide points that you used to create the trendline.
Tips & Warnings
- You can use these same basic steps to create other graph types such as polynomial or exponential using the other trendline options.