LibreOffice Calc: Creating Charts

In the last tutorial we introduced Charts and Graphs and discussed how each one is used. But how do we implement this? In LibreOffice Calc we can go to the Insert menu and select Chart, or there is generally a button for it on the Standard toolbar. It can be a bit hard to make out, but it shows a pie chart in red with a percent sign in gray on top of it. If you move your cursor over it the ToolTip should say Chart. Clicking this button opens the Chart Wizard.

Chart Type

Step one is to choose the chart type. These are the types we discussed in the previous tutorial, so you should be ready for this step, but you may notice that for each type you also have several options. They are usually small variations on the main theme of that chart type.

• Column – You can have a Column Chart with more than one variable. This is usually handled by having multiple columns, identified by color, for each “bucket”. As an example, suppose you wanted to compare religious preferences in 3 states. You could have a blue bar for Catholics, a red bar for Protestants, a yellow bar for Jews, and a green bar for Muslims. For each state you would have these four color-coded bars. That is your first option. Your second option is called Stacked. In this case the bars are placed one on top of the other, the height of the resulting bar would be different for each state depending on how many people you counted. The third option is Percent Stacked, and in this case the height of the bars is the same (it is always 100%) but the height of each section represents its percentage.
• Bar – This has the same three options as Column.
• Pie – Your first option is the Normal option, which is the usual pie chart. The second one is called Exploded, and all it does is separate the pieces of the pie. It has no added significance, and is purely an aesthetic choice. The third option, Donut, allows you to display two variables on single pie chart. And the fourth option is an exploded donut chart, so it combines the exploded feature with multiple variables.
• Area – The first option is several data series superimposed on each each other, the second option is to Stack the data series, and the third is Percent Stacked. This works much the same as with Column and Bar charts, but is normally done with quantitative data.
• Line – Option one is points only, which is an odd choice for a line graph. Option two is to display the points and line connecting them. Option three is to display the line without the points, and option four turns the line into a 3-D surface. These differences have no significance for the actual data analysis, and are purely choices of how the graph is to be displayed.
• XY(Scatter) – The odd thing here is that you have options to connect the dots. The idea of a scatter plot is that you have no presumption of a relationship that would even make sense of connecting the dots. But the options are very similar to what you have for the Line graphs.
• Bubble – This only has one variation.
• Net (aka Radar) – Option one plots the points on each spoke, option two has both points and the lines connecting them, option three is the lines only without explicitly adding the points, and option four fills in each irregular polygon with a color. I think option one is not a preferred choice here. One of the main virtues of this type of chart is seeing the polygon shape.
• Stock – Basically, the choices here have to do with whether you draw in the columns, or just let the main points exist in space.
• Column and Line – Choices here are having the columns side-by-side, or stacked.

So, the Chart Type choice really comes down a little thought about the clearest way to present your data, and nothing more deeply significant is really involved.

Data Range

Here you need to specify the place where the data comes from. The data can be found on a specific sheet, for starters. Your spreadsheet file can contain multiple sheets (i.e. pages) within it. By default, they come labeled “Sheet 1”, “Sheet 2”, etc., but you can rename them to something. Note that when you open this up it gives the range as being a sheet, column, row. What you need to do is click the button to the right of the Data Range, which will cause the Chart Wizard to roll up to a single bar. Using your cursor, click-and-drag to to take in the whole range, than click the button on the right of your rolled-up bar, which will read Expand in the tool tip when you cursor over it. This will bring back your Chart Wizard, but now you will see your range of data listed in the Data range field. You might notice that the addresses are all absolute cell references, i.e., they all have dollar signs. This is good, since you don’t want your references moving around when you are making a graph.

The next choice is whether your data series are in columns or rows. Columns is the default as this is most common, but you can flip it around if desired. Why are you being asked this, and why does it matter? I will illustrate using our simple savings model from an earlier tutorial. If you have that spreadsheet saved, you can open it, and select a range from the cell that says Year in the upper left, and going down and to right of the last cell for End Amount. If you have your data series as columns, it would look like this:

 

 

 

 

 

 

By putting the data series in columns, and selecting a line graph, each of the columns Beginning Amount, Interest Earned , Annual Addition, and Ending Amount becomes its own line on the graph, which makes sense. If we instead selected that the data series was in rows, it would try to treat each row as its own series, and the result makes no sense at all:

 

 

 

 

 

 

The other thing on the Data Range section is to select the first row and/or first column as being labels. As you can see in the graph, done properly this makes the graph very readable. Since the data series is in columns, each value in the first column becomes a value for the X-axis, and each label in the first row becomes a different line on the graph with its own symbol and legend. This makes the whole graph much easier to read.

Data Series

Here we can narrow down what we want to show in the graph. In our Savings Model we have four series, but we might not want to display all four on one graph. Suppose we only really wanted to show the Ending Amount on the line graph? Then we could select the other columns and click “Remove”. If we later changed our minds and wanted to add back a column (for example, Interest Earned), we could come back here and click the Add button. The other fields who what Calc is picking up for the Series name (it is reading each column name from the cell in row 1), and it is getting its values for the X-axis (here called Categories, though that is more appropriate for bar graphs than for line graphs) from the values in Column A. You can manually change these, or click the button to the right of each field and use your mouse to click on what you want.

Chart Elements

On this tab you can add elements to the chart. First is a title. For our example, let’s use Savings over Time. When you type this in it will appear on the top of the Chart and be centered Then you can add a Subtitle. In many cases you won’t want to do this, but to illustrate how it works we can add A Simple Model. This will appear right under the Title, but in a smaller font. We can then label each of our axes if that is not clear. For the X-Axis we could use Years of Saving. This will appear under the graph in even smaller type. For the Y-axis we will use Dollars Saved. This appears in the same font as the X-Axis label, but rotated to match the vertical axis.

Legend matches up a symbol used for the data points to the name of the series. In the sample I created, for instance, I have a series of green triangles that represent “Ending Amount”. By default this is placed on the right of the graph, but you could move it around. Finally, the grids create grid lines on the graph. If you select X-Axis you will get vertical grid lines, and Y-Axis gives you horizontal grid lines. Y-axis is the default, and it usually makes the most sense.

Once you have made your choices click Finish. The finished graph will look something like this:

 

 

 

 

 

 

Note: The revised version of the Savings Model Spreadsheet with the graph included can be downloaded from here.

Listen to the audio version of this post on Hacker Public Radio!