Chapter 1

 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 Legend.  Noting 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 the table.

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 the gray 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 area.  Choose 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 near 0.)

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 below:


One can select the y-axis, and then the Format menu to choose Selected Axis, to 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 straight-line shapes.

y coordinates: left to right = top to bottom: hover to view plot
.1, .1, .01, .01 1,1,1,1 2,0,2,0 2, -50, -50, 2
25, 0, 25, 0 1000, .01, .01, 1000 1000, 2, 2, 1000 Rectangle


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.


Top Next Topic Topics