Creating Spreadsheet Charts
In Excel, a chart associates numeric values on the y-axis with corresponding labels
on the x-axis. Excel charts are very flexible. There are default parameters
that make charting of data quite simple but some study is required to come
up with very specialized charts.
By default, spread sheet cells are outlined, grid lines, scale values, x axis and
y axis lines, are all provided, and automatically chosen to fit the data.
To create a chart, drag your mouse over the desired data, in rows or columns, while holding down
the control key to select it. Then move your cursor to the Insert drop down menu to select
Chart. A form then appears to allow you to choose the type of chart, (line, bar, pie,
....), its title, axis labels, legend, ....., and whether it should be presented
on the current sheet or on a new sheet.
Customizing a chart is the main
focus of this topic.
The next chart is a screen print of Excel showing outlined cells containing labels
and data as selected
for charting. A considerable variety of text styles, colours and sizes can be
selected. In this case cell widths and heights were expanded automatically
to accommodate the text size. Use your mouse to tug at column borders,
(A, B, C, D, .....) to change their widths as has been done for Column A, or at row
borders, (1, 2, 3, 4, ....) to change their height as desired.
Next, the Insert drop-down menu is shown:
When Chart is clicked, the chart selection form appears as seen next.
The type Line and a Subtype are then selected. Clicking Next > provides
a default chart for inspection.
Here we see that the desired range of data and labels has been plotted and
we click Next >. Now we see a form that allows us to show a legend or not,
make titles, alter the grid lines and more.
As a legend is not required, we click the Legend tab and deselect
that the grid lines can obscure horizontal and vertical lines of our drawing and that
they are not very valuable for viewing shapes, we click the Gridlines tab and deselect
all gridlines and click
Next >. The following form appears.
We select "As object in:" , then Finish,
and the chart appears on our spreadsheet:
The chart can be sized in both x and y directions by dragging on the small black
squares with the mouse. It can be selected, indicated by the dotted outline, and
moved around on the sheet with the mouse. Here it has been moved close to
For some purposes the chart could be satisfactory as it stands. For our purpose we would prefer
a black drawing on a white background. To accomplish this we right click
area to access a drop down menu:
For improved clarity, the chart was first positioned closer to the x,y table. (The screen print was cropped so as to show only the cells of interest). From the
menu options Format Plot Area is selected with the result:
With the foregoing form one could choose not to border the plot region or to border it with
a selected line weight and colour. A color or none
can be chosen for the Plot
None and None for each of these with the result:
Lines that we may wish to draw with their or x coordinate or y coordinate at value
zero may be obscured
by axes lines. To eliminate these, right click
in the label region
below the x-axis as now seen, or left of the y-axis as has already
been done, to obtain a small menu.
Click Format Axis and get:
On the Patterns tab set Lines to None. The result is:
The labels First and Fourth were there to demonstrate
the nature of labels.
In our case a number representing distance would be more appropriate, so type 0 and 3 in those cells. In the value column replace the 1s
with .01s so as to plot a line segment
close to the former location
of the x-axis line. (Excel graphics will not show a chosen
line weight quite correctly along y=0 but will
Next, delete the cell outlines to obtain a better
chart appearance. To do this, use the mouse to select a rectangular region
that includes the chart, then select Cells from the Format
menu to get the form:
On this form choose the Patterns tab select the
Color:, the small white square and then OK
for the uncluttered chart and table
One can select the y-axis, and then the Format menu to choose Selected Axis,
unselect automatic scaling and choose their own scale and interval, and also change
the font type, colour, size, orientation and weight, and more as is seen next.
Excel's drawing method is to draw a nearly straight line between adjacent labels;
actually the line consists of a barely noticeable sequence of small steps. To draw
smooth curves the points need to be close together. To draw a near vertical line
the points have to be very close together.
With many points, cell values are usually calculated rather than being typed. The
drawing of curves using calculated values will
be the next topic.
For now there is an opportunity to choose from a few sets of y values to draw some
y coordinates: left to right = top to bottom: hover to view plot
.1, .1, .01, .01
2, -50, -50, 2
25, 0, 25, 0
1000, .01, .01, 1000
1000, 2, 2, 1000
As an exercise, you should attempt a table on your own spreadsheet that will
draw a rectangle similar to the rectangle that is available forgoing.
The viewer will see how to calculate values for use in charts.