Wednesday, October 15, 2014

Excel: How to Adjust the Scale of the Axis in a Scatter Chart

Routinely, we have been using TextPad and Excel to analyze Garbage Collection (GC) events gathered in GC log files.

In this article, we will demonstrate how to use these data to create a Scatter Chart and also adjust the scale of the horizontal axis in the chart as shown below:

Before Scale Change


After Scale Change


The Data


After cleaning the data, we have two columns holding data as shown below:
  • GC time stamp
  • Mixed GC pause time
Using shift key, you can select both A1 and B1 cells as shown below:

Then you can select entire columns by pressing:[1]
CTRL+SHIFT+DOWN ARROW key


Create a Scatter Chart from Spreadsheet Data


A scatter chart has two value axes, showing one set of numerical data along the x-axis and another along the y-axis. It combines these values into single data points and displays them in uneven intervals, or clusters.

To create a scatter chart, you do:[2]
  1. Arrange your data so that the x-values are in the first column of your worksheet, and the y-values are located in adjacent columns.
  2. Select the range of x- and y-values that you want to plot in the chart.
  3. Click Chart on the Insert menu to start the Chart Wizard.
  4. In the Chart type box, select Scatter .
  5. Under Chart sub-type, click the chart sub-type you want to use.

This will create a scatter chart as shown in diagram 1.  But, you can see the scale of X-axis is not appropriate.

Change the Scale of Horizontal Axis


By default, Microsoft Office Excel determines the minimum and maximum scale values of the axis when you create a chart. However, you can customize the scale to better meet your needs.  To customize the scale of horizontal axis, you do:
  1. Change the label/title of the series to "Mixed GC"[4]
  2. In the chart, click the horizontal (value) axis that you want to change.
  3. This displays the Chart Tools, adding the Design, Layout, and Format tabs.
  4. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click Horizontal (Value) Axis.
  5. In the Current Selection group, click Format Selection.
  6. In the Format dialog box, change maximum value of X-axis from 40000.0 to 30000.0.
Voila, you have created a scatter chart of the Mixed GC diagram with a good scale at horizontal axis (see diagram 2 above).

No comments: