Dr. Mark C Gardener
Open University Associate Lecturer: S206/SXF206 Environmental Science
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.
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 OOo recognises the file as being delimited by commas or tabs (or some other fixed delimiter) the Import wizard will begin.
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.
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.
Move the text to Calc like so:
You will now have text in your spreadsheet but it will be in one chunk.
You need to convert the text to columns. You'll need the Text to Columns menu item. Proceed as follows:
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.
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).
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.
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.
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.
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.
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.
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.
You can select the various chart elements in several ways:
There are too many options to list them all in this brief guide but as an example here is the y-axis edit menu:
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.
|Tips & Tricks for Excel||Gardeners Own Home|