Dr. Mark C Gardener

Open University Associate Lecturer: S206/SXF206 Environmental Science

Mark's OU Students page
Gardeners Own Home

Using Open Office for Graphs

OpenOffice and its various derivatives are an office suite of programs designed to be a replacement for Microsoft Office. The Calc program is a direct equivalent for Excel. The look and layout of Calc is more like the older versions of Excel with icons in a fixed toolbar.

  • The range of functions is the same as for Excel
  • Graphical capabilities are similar but slightly more limited

Getting data

If you have OOo installed then most spreadsheet files will open automatically. OpenOffice can handle XLS and XLS files as well as CSV, that is comma separated variables. In most cases it is best to start Open Office first then open the file from the program. Use the File menu or the open icon on the toolbar. What happens next depends on the type of file:

  • If your file is XLS or XLSX it will open directly.
  • If your file is delimited by commas or tabs it will start the import wizard.
  • If your file is plain text it will probably open in Writer instead of Calc.

Import Wizard

If OOo recognises the file as being delimited by commas or tabs (or some other fixed delimiter) the Import wizard will begin.

The Import Wizard

You see a preview of the data and can choose the type of delimiter. Tick the various boxes until the preview “looks right”. In the example above there are three columns of data.

Plain Text

If OOo cannot recognise the file as a spreadsheet type with delimiters it will open in Writer and you'll have to use copy & paste.

Plain text opened in Writer

Move the text to Calc like so:

  • Click once anywhere in the text
  • Press Ctrl+C or use the Copy button/menu
  • Switch to an open Calc window
  • Click once in cell A1
  • Press Ctrl+V or use the paste button/menu

You will now have text in your spreadsheet but it will be in one chunk.

Text copied to Calc in one block

You need to convert the text to columns. You'll need the Text to Columns menu item. Proceed as follows:

  1. Click in the header of column A to select the entire column (assuming that's where you placed it).
  2. Click the menu Data > Text to Columns to open the Import Wizard.
  3. Alter the settings as required, probably this will mean Separated by Space and Merge Delimiters.

The Text to Columns menu is like the Import Wizard

When the operation is completed you may have to “tidy up” by deleting some of the original data as the command sometimes leaves the original data intact.


Open Office can create a wide range of graphs, which are broadly similar to those in Excel. However, the mechanism for making and editing charts is a bit different. It is actually more like the older versions of Excel with a chart wizard.

Starting a chart

The graphs are accessed via the Insert > Chart menu or the Chart button (which looks like a mini bar chart).

As with using Excel the best method to proceed is not to select any data! Make sure you click in a cell that is not in or adjacent to an occupied cell, then click the chart menu or button to start the Chart Wizard. You will begin with an empty chart, which you can choose how to build.

The chart wizard starts with a blank chart. Begin by selecting the type of chart you need

You now choose the chart type you want. In this example we are going for a scatter plot between maximum temperature (monthly average) and total monthly sunshine hours.

Select the type of chart (scatter for this example), there are additional options (different for each chart) choose what is appropriate and click Next >> to proceed.

The next part is about the data range. This is where most people go wrong! Forget this bit and simply click Next>> to go to the next section (Data Series).

Do not choose a data range! The program will inevitably get it wrong! Skip to the next phase.

In the next phase (3. Data Series) you can choose exactly what data to put where. You have control over which goes on the y-axis, which goes on the x-axis and so on.

In most cases you will need to click the Add button to make a data series. Here we will use only one data series (temp against sun) but you could have more than one and plot several on one chart.

Once you click the Add button you get to choose what to place where.

Click the Add button to start a new data series.

You can now click one of the options in the Data ranges box and choose the spreadsheet cells appropriately. Start with the y-values (the response or dependent variable). Click in the Range for... box and you'll be able to select the cells. However, you have to use the icon to the right if you want to use the mouse.

Click the icon to allow the mouse to select the cell range.

Once you click the icon (I have highlighted it in the figure) the menu “rolls-up” and you can use the mouse to select the cells you want. Usually you will return to the menu once you've done the selection but you can also click the icon to return (similar but the arrow points the other way).

In some chart types you will also want to choose labels – for bar charts and so on – in this case they are not required. Once you have the values you want you can click the Next>> button and move on to other chart elements. Note that you cannot simply type text as a name for the series, you either have to type a cell reference or point to a cell. If you want a name other than the column heading you'll have to type it in the spreadsheet before starting.

Data values for x and y values are now entered and you are ready for the next step.

The next step is where you get to add titles and so on. These can also be added afterwards but it is a good idea to put in something now, even if you edit later.

Selecting chart elements such as titles.

In this case a legend is not needed as there is only one data series but we'll leave it in anyhow as we can delete it later.

The final step is to click the Finish button. You will return to the spreadsheet and the chart will be completed. You can now set about editing it and moving or resizing as you need.

Once the chart wizard has finished you see the competed chart in the spreadsheet.

Editing a chart

When you first complete a chart from the wizard you'll see your chart prepared and in edit-mode. You can tell this because the border is “fat” and grey. You will also see the chart toolbar. The menu items are also “relevant” to charts.

Click in the spreadsheet in an area off the chart. You will see the chart border disappear. The chart is now deselected.

Click once on the chart. You should see the resize handles but not the fat grey border. The chart is selected for moving and resizing but not for editing. Click and drag the chart to a sensible spot and resize it as you like. In this selection mode you'll see the drawing toolbar and can add drawing elements. However, you should not do any of that until you have finalised your chart (and probably not at all).

Double-click the chart and you will see the fat grey border. The drawing toolbar will be replaced by the chart/graph toolbar.

Edit options

You can select the various chart elements in several ways:

  • Menu bar – via the Insert and Format options.
  • Directly select on the chart – point at the element you want (a popup note appears) and double-click.
  • Using the Chart toolbar – there is a limited range of buttons, hover over to see what they are.

There are too many options to list them all in this brief guide but as an example here is the y-axis edit menu:

The y-axis edit menu allows a range of options.

In this menu you can see that you can choose the max and min values for the axis, hence can rescale it (you do not always want to start from 0). Reversing the axis direction can also be useful for things like soil and ocean profiles where depth can be a positive value but you want it to mimic reality. If you do this then also check out the Positioning section so you can alter how the axes cross one another (you may have to select the x-axis and alter how it crosses the y-axis).

If you click a chart element and see the selection blocks you can probably also delete the item direct and move/resize it.


blogger counter

Mark's OU Students page
Tips & Tricks for Excel Gardeners Own Home