OpenOffice.org Training, Tips, and Ideas

How to tell what data your chart is based on, and update it with different data, in OpenOffice Calc spreadsheets

If you create your own chart, you can usually remember what data it came from. But what if there's a whole bunch of different data sets in the sheet; if there are many sheets in the spreadsheet; or if you just got a spreadsheet from someone who left the company to go teach yoga in Aruba and you have no idea what the spreadsheet is about?

Here's your spreadsheet and a chart. The person who created it didn't have the courtesy to even use the headings of the data in the chart so you've got no info about what the info is.

Double-click the chart and you'll see, highlighted, the cells the chart is from.

If you don't see anything highlighted you can right-click on the chart and choose Data Ranges.

Then the range will appear. You can just look at it, or change it and click OK.

If you just want to change the data a chart is based on, you can drag. Select the cells and drag them onto the chart.

You'll be prompted to say whether the first row and column are labels, i.e. should they be used as titles/headings/labels in the chart or are they actual data. Make the appropriate settings and click OK.

---

---

More Chart Options for OpenOffice.org 3.1: Placing the axes

You can now control where the axes intersect each other. Let's say you've got this data. Inserting a normal bar chart you get the data below.

 

You can modify where the axes intersect and where the labels are. Double-click the chart, then click on an axis, I'm showing the Y axis first. Be sure the axis is selected, then right-click on the axis and choose Object Properties.

Now you have various settings in the Positioning tab. You can have the axis cross at the start, the end, or a particular category. Here's what it looks like (in the next screen shot) with End selected.

The Y axis crosses the X at the far right, aka at the end.

Here's another setting and the result. You can place labels at the outside start, and other options in that list. This can be useful especially for the X axis since the bars overrun the X axis.

 

Here's what that setting looks like--outside start , or left, is the default.

 

If you want to modify the X axis, click on that and choose Object Properties.

 

Heres's what another setting looks like. You can have the X intersect the Y at a value other than 0.

 

And here's the result.

 

And another setting. You can position the labels at the outside end.

And the result, the labels are on the top instead of overrun by the bars.

OpenOffice.org 3.1 Chart Feature: What to do with zero values

If you're missing data, you have three options now: skip the data, assume zero, or compute a logical continuation.

Let's say you've got this data, with this line chart. The default is to leave the missing data out completely.

If you don't want that, though, you can double-click the chart, then click on one of the data lines, and choose Object Properties.

Select another option, such as the one setting all missing values to 0.

That might or might not be what you want.

The Continue Line option gives the best-looking, if maybe not the most accurate, results.

How the New OpenOffice Chart Tool Works When You Don't Specify a Data Range

I've talked about the new chart tool in previous blogs here and here.

In both of those, though, I assumed that you would be selecting the data range in the Calc spreadsheet or Writer table before inserting the chart. Which you normally would be.

 

If you choose Insert > Chart in Calc, in the new tool, without a range selected, it's not much use. You get a big blank chart.

In Writer, however, at least in this stage of development of the tool, you get something different when you choose Insert > Object > Chart with no data range selected. Thanks to Linda from Largo for pointing this out.

It's not much good like that, of course, so right-click on it and choose Chart Data Table to input some data.

You get this window where you can not only fill in the names on the X and Y axes and the data, but you can add and remove rows and columns of data, and move rows and columns around.

This is nice if you have the data written down somewhere but not in a chart or table, and it's just quicker to slam in the data manually than to base it on a chart or table. It took about twenty seconds to make this chart. (It would have taken more if I'd had any actual data on penguins and marmots as pets. ;>  )

That was the chart data table, and then I just clicked the red X in the upper right corner to close and update the chart. Here's what it looks like.

This chart approach does lack titles, and X and Y axis titles. However, you can just make them with text boxes. Choose View > Toolbars > Drawing (deselect the chart first) and use the Text tool to draw a text box outside of the chart area. Type what you want in the text box, then drag it into the chart area.

In this example, I also clicked on and shrank the inner data part of the chart to leave room for the title I created with the text tool.

---

---

PDF Reference to the New OpenOffice.org Chart Tool

I've done an overview/walkthrough of the new OpenOffice.org charting tool that will be released with version 2.3. It's a procedural approach, so to supplement it here's a PDF reference to some of the more complicated windows. I'll be including it and similar information in an update of my workbooks and Guidebook as soon as 2.3 has been released, and I'll provide the update to the book in PDF form, as well, so those of you who've already purchased the Guidebook can have the new chart info.

A note on the new chart tool -- in this window, you can specify multiple noncontiguous ranges. However, sometimes especially when they're in another sheet, they don't seem to take. Might just be a bug not worked out yet.

 

So for now, to more reliably add noncontiguous sections, just skip that window in the wizard, click next, and use this window. Click Add, then you'll get an item called Unnamed Series for which you can set the range of cells for the name (label/heading), the categories (must be the same as all other data series) and the Y values.

---

---

The new OpenOffice.org Charting Tool Coming in OpenOffice.org 2.3

For anyone who has grown weary of the current OpenOffice chart tool, or for anyone who has attended my classes and grown alarmed at hearing that charting is as much art as science, there is some relief in sight.

The chart tool has been improved significantly, and it's going to be included in OpenOffice.org 2.3, to be released roughly sometime this fall. If you're curious, you can download the developer version containing the chart tool here.

Read more
Download

Here's a comment from Pete who appears to be involved in the chart development.

Thanks for the nice overview. 
Please mentions also that many limitions will be overcome with the next
release. It's not only about a new (and shiny :) ) wizard. For example
multiple data series will be possible. Error bar for most kind of
charts will be possible. Regression lines with the function and R^2 are
implemented too. The performance regarding large data sets is improved.
EXCEL im- and export was improved considerably. Many of these features
are essential for engineers, students and scientists in their daily
job.
Worth to read:
http://graphics.openoffice.org/chart/whatsnewinchart2.html

However, there is still a long list of existing issues though OOo is
close to perfection. Any Volunteers will to finish the few remaining
items?
http://www.openoffice.org/issues/buglist.cgi?issue_type=DEFECT;issue_type=TASK;issue_type=PATCH;issue_status=UNCONFIRMED;issue_status=NEW;issue_status=STARTED;issue_status=REOPENED;email1=;emailtype1=exact;emailassigned_to1=1;email2=;emailtype2=exact;emailreporter2=1;issueidtype=include;issue_id=;changedin=;votes=;chfieldfrom=;chfieldto=Now;chfieldvalue=;short_desc=;short_desc_type=substring;long_desc=;long_desc_type=substring;issue_file_loc=;issue_file_loc_type=substring;status_whiteboard=;status_whiteboard_type=substring;keywords=;keywords_type=anytokens;field0-0-0=component;type0-0-0=equals;value0-0-0=Chart;field0-0-1=short_desc;type0-0-1=substring;value0-0-1=chart;field0-1-0=short_desc;Submit%20query=Submit%20query&order=issues.issue_id

Here's a tour of it. I'm getting to know the details myself right now, but this is the gist. OK, a fairly detailed gist.

Here's the basic data we're working with.

I like to select the data since it means less typing, or just less dragging, later. Select the data and choose Insert > Chart. You get this. Click this and any other illustrations to see them bigger.

Window 1: Chart Type
Note that you get a preview of the chart as you go along, in the document. This preview of course disappears when you click Cancel without creating a chart.

There are more up front variations, it seems. Here are a few.

Who doesn't love an exploded donut?

Or a nice XY Scatter.

Or a Net.

A Column and Line with options for how many lines.

And here are the 3D options.

In this window you just pick a type, a variation, any associated options, and you click Next.

Window 2: Data Range

Here you get the choices for what data is involved. Normally in a simple chart you could just have A1 to D5 in a contiguous block. But when you don't have that, you get to specify that ahead of time. Not that this is new but it's more obvious and easier.

You could change the range just by typing something different in the Data Range field. In this, I skip the D column entirely. Use a semicolon to separate non-contiguous ranges of data.

You also have the option, again not new, to have the data in rows or in columns.

Rows

Columns

And you also get the options at the bottom of the window, First Row as Label and First Column as Label. This is the same kind of thing you specify when sorting; you're saying, if you check the box, that the first row is identifying data, not data to be charted, and ditto with the first column. You usually want to mark both. Or at least I do.

Here's what the chart looks like with both boxes marked.

And the same chart with both unmarked.

Click Next.

Window 3: Data Series

Here, you get even more control over exactly what cells are used for the labels (Names) and for the data (Y-Values). For instance, if you wanted the label for Sales to not be Sales but to be some other term off in cell J14, you could enter $J$14 instead in the Range for Name field shown.

Select Names, then select each of the items in the Data Series list and make changes if necessary for them in the Range for Name and Categories fields.

Then select Y-Values, and do the same thing for each item in the Data Series list.

If you want to add an entirely new set of data to the chart, just click Add. You'll get an Unnamed Series. Then set the Name and YValues for that one, too. Or click Remove to remove any chunk of data in the Data Series window from the chart.

Note that the Categories will be the same for all data series. If you change them for one item in the Data Series list, they'll change for all.

When done, click Next.

Window 4: Chart Elements
You're nearly there. This is the simple stuff.

Then click Finish. And you've got your chart.

Modifying the Chart Once It's Created
Modifying after creation is a tish less twitchy though not that fundamentally different.

One thing: used to be, you could just select the chart by clicking on it once, right-click, and choose to change the data series. Now you just need to double-click it, then right-click and choose Data Ranges.

Essentially,  you double-click the chart, then either rightclick, use the Format menu, or use the Format toolbar. You can also double-click on an item in the chart.

The menu and toolbar have a fewer options, which will take getting used to but is a little simpler.

Here's the menu, and the toolbar after it.

To make a change to a particular bar in this chart, you do essentially the same thing as you used to. Double-click the chart, click on one of the bars to get the data series, then click on the specific bar to modify.

- Third click

- Fourth click

- Options under Object Properties (right-click, double-click, or use menu or toolbar)

- And results

To change titles, just double-click on them and type (after the requisite click, then double-click). This is the same as before.

Overall, not a makeover top to bottom. But nicer. I'm not a frequent chart user, so please give me your comments on what is the most useful.

---

---