Creating a Log/Log Chart

Excel is great at automatically creating a wide variety of charts, lickety split. For some types of data, you may want to create a chart that is not readily apparently. Such is the case with a log/log chart.

The answer to this conundrum is to change which type of chart you use for your data. It seems that Excel will not allow the X axis to use a logarithmic scale for many types of charts. To specify a chart where you can use logarithmic scales on both axes, follow these steps if you are using a version of Excel prior to Excel 2007:

1. Select the chart area (the actual chart or the spreadsheet area you want to chart).
2. Click the Chart Wizard button on the toolbar. (Click here to see a related figure.)
3. Select the XY (scatter) type of chart.
4. Select the sub-chart type you want to use.
5. Click on the Finish button.

If you are using Excel 2007, follow these steps:

1. Select the chart area.
2. Make sure the Design tab of the ribbon is visible. (This tab is only available if you select the chart area, as instructed in step 1.)
3. Click the Change Chart Type tool in the Type group. Excel displays a palette of available chart types.
4. Select the XY (scatter) type of chart.
5. Select the sub-chart type you want to use.
6. Click on the OK button.

If, for some strange reason, values along the X axis are still not represented in logarithmic scale, you can click on the X axis, choose to format it, and then specify a logarithmic scale.

You should note that this solution will work for many types of charts, but won't work for charts where you need to plot zero or negative values. (Those values don't have a LOG value.)

So why do you need to use a scatter chart for log/log data? Because it is the only type of chart that allows numeric values along the X axis. Other chart types use the X axis for categories of information, not numeric values.