LibreOffice Calc: More on Chart Editing

Last time we looked at how we can create a Chart in LibreOffice Calc. Now it is time to turn to the editing options. These are similar in some cases to what we had at the creation of the Chart. I will start by opening the spreadsheet from the last tutorial, which is here.

To begin, click on the Chart to select it. You know you have done this when the eight “handles” appear. These are the little squares that appear on each corner and in the middle of each side, that you can use to resize the object. Then right-click, and select Edit, which is at the bottom of the pop-up menu. When you do this, you will see a thick grey border around the chart, and your data series will be highlighted. Now you can right-click again and get a menu of things you can do in the Edit mode;

  • Format Chart Area This has 3 tabs. the first, Border, lets you put a border around your chart. The default is to have none, but you can add one. You need to select a style, a color, and a thickness before anything appears. If you want it to be somewhat less stark, add some transparency to tone it down. The next tab, Area, lets you fill in the Chart with a background color. The default is White, but you can choose any other color. And you can reduce the color effect my adding Transparency on the third tab, and even add a gradient.
  • Insert TitlesWe put in Titles, and labels for our Axes, when the Chart was created. Here we can edit those. And we can add Secondary Axis labels if needed. This is useful for when you have two different series on one graph, and they have a different range of numbers
  • Insert/Delete AxesThis goes with the Secondary Axis labels. IF you want to add a Secondary axis, you can do it here.
  • Delete LegendThis is a toggle. We created a graph with a legend, so the option is to Delete it. If we delete it, the option changes to Insert Legend.
  • Chart TypeThis option allows you to change the type of chart. Possibly you selected a line graph without connecting the dots, or it should have been a Scatter plot. Here you can make these kinds of changes.
  • Data RangesHere you can make changes to your Data Ranges and the data series you select.

So, you can see you have some fairly broad capabilities to edit your chart. Let’s see how this can work with an example

Example: A Multiple Axis Chart

Sometimes you want to put several different series on a Chart, and the ranges are significantly different. To see how this works, Let’s use the Editing option to go back into our Line Chart and add the series for Interest Earned. So we right-click on the chart, select Edit, then right-click again and select Data Ranges. Go to the Data Series tab, click Add, and we get “Unnamed Series” with no definition. This series has two items in the Data Ranges field on the right: Name, and Y-Values. Click on Name, then go down and click the “roll-up” button to the right of the Range for Name field, and click on the cell C1, which contains the column header “Interest Earned”. You will see the field fill in with something like this definition: $’High Return’,$C$1. Then go back to the Data Ranges box above this, and click on Y-Values. The field below will now change to read Range for Y-Values. As before, click the roll-up button on the right, then click and drag to select cells C2 through C41 to grab the actual values. You should now see this second series on the Chart, and an added Legend for Interest Earned on the graph. So far so good.

Now, right-click on the graph again, and this time select Insert/Delete Axes. Add a secondary Y-axis. When you do, it will simply be a mirror of the existing Y-Axis. Now, select Insert Titles and add a secondary Y-Axis called Interest Earned. This will now appear on the right-side of the graph. But the dollars are still the same on both axes. To get the effect we want, we need to move our cursor over the secondary Y-axis to where the tool tip says “Secondary Y Axis”. Right-click, and select Format Axis. This brings up a window with multiple tabs, and the first one is Scale. This is what is controlling the numbers here. If you look at it you can see that the fields all have “Automatic” checked, but if you remove the check marks, you can enter the values you want for the secondary axis. A Minimum of 0 still makes sense, so leave that alone. But for the Maximum, I can look at my numbers and see that by the end I get just over $40,000. So I will make my maximum 50,000, because you don’t want your scale to be smaller than the actual data. For the Major interval I selected 10,000, and for the Minor interval 5,000. I now have a scale that makes sense, but my series is not lining up with it. So there is one last step to take. While in Edit mode, right-click on the Interest Earned series on the graph. In the menu that pops up, select Format Data Series. Then go to the Options tab, and set it to Align Date Series to the Secondary Axis, and click OK.

You should now have a graph with two data series, each aligned to a different Y-Axis. The two series happen to lie one on top of the other because of the way the series were created. But if you want to make them more separate just go back and format the Secondary Axis to make the Maximum something like 100,000 in stead of 50,000.

Other Editing Options

Way back when we first started looking at LibreOffice I mentioned the significance of objects, and pointed out the LibreOffice is an example of an object-oriented program. The significance of this is that a spreadsheet is an object, and it contains individual sheets that are objects, and each sheet contains other objects, such as cells and graphs. And each graph is an object that contains further objects, and each of those objects has its own properties. In fact, we used this insight in our multiple axis example when we set properties for the Secondary Axis and for the Interest Earned series. Pretty much every object has a properties menu that can be accessed by right-clicking on it. The properties that you can set are one appropriate to the object. If it is a piece of text, you should see text properties, such as the font and the alignment. If it is a data series, you get a different set of properties. Running through all of the options in detail would turn this into an even longer and more tedious tutorial, so I will just briefly hit the highlights.

  • Title, Sub-title, Axis labels – If you right-click on these you get the usual font options, position, and size
  • Axes – Right-click and you can select position, labels, font effects, etc.
  • Legends – Right-click on these and you can set fonts, border, transparency, and position
  • Data Series – Right-click on these (in the graph) and you can set the alignment, the color, the symbol, etc.

So the bottom line is that you can pretty much edit anything on a Graph, but sometimes you need to drill down to the specific object to do it.

Our yet newer version of the Simple Savings Model incorporating the edited graph can be found here.

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

CC BY-SA 4.0 LibreOffice Calc: More on Chart Editing by Kevin O'Brien is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.