OpenOffice.org Training, Tips, and Ideas

Combining and summing fields in OpenOffice.org database queries

Let's say you've got a bunch of customers with a few invoices each, and you just want to know how much each of them has spent. You take your one customer record and you add many invoice records to get the total they spent on all invoices.

You can also multiply items: let's say your invoice table has the item price, and number of items purchased on the invoice, but not the total per invoice. You can multiply item price by number of items to get the total.

Open the database.

Click on the Queries icon and choose to create a query in Design view.

You'll get a window where you can choose which table to use. In this example I'll use the invoices table. Click Add to add the table.

Then double-click a few fields to add them. I'm going to add a few here, then delete some later.

Now I've got the fields added.

If I click Run Query then I just get those fields.
 

Note I have the unit price and number of items but not the total per invoice. Here's how to do that. Just type as shown in the far right column. It is case sensitive.

Clicking Run Query gives the results.

Which look like this--you get the Alias you typed, not the multiplication. Note however that it is not formatted as currency in this example. If you want to change it you can right-click on the column heading, choose Column Format, and select a currency or number format.

And here's what that looks like.

Here's a note. You don't have to have the fields you're multiplying in the query. You can unmark the checkmarks as shown and they won't show up. Or you don't even have to have them at all; you can right-click on any column head and choose Delete to remove it from the query. (Do this in the bottom area of the query where you do the defining, not the top where you see the results.)

Now you're thinking about doing that sum thing, adding up all the totals for all the multiple invoice for each single customer. For this, you need to have just the customer ID (one) and the calculated field (many for each one customer). So I deleted everything but those two columns.

Then for the single field I selected Group in the function list, and I select Sum for the many field, as shown.

Run query, and here are the results. Note that now each customer is only listed once, and the total for their invoices is calculated.

 

OpenOffice 3.2.1 is out

Download it at http://www.openoffice.org.  Release notes are here. 

http://development.openoffice.org/releases/3.2.1.html

It's a bug fix release, and also features new branding from our new benevolent overlords. ;>

Adding clip art to the OpenOffice gallery

It's nice to be able to just pull clip art from within the program. Some users find it difficult to navigate a file system, which is what you need to do if you're using Insert > Picture > From File. And, frankly, dragging from a nice pane of graphics is easier.

To get a bunch of graphics, just google for free clip art and download it.  Here are a few options specifically for teachers.

Some clip art

http://clip-art.kaboose.com/index.html
http://school.discovery.com/clipart/new.html
http://www.teacherfiles.com/clip_art.htm  (BIG collection)

Free animations
http://register.freeze.com/download/index.aspx?sx=348413c5-d957-4f6c-bfe7-d91ca643ee6a

You might also consider the phenomenal Big Box of Art, vast amounts of clip art for not a lot of money.

So whether you want easier access to pictures or video, or good change management techniques, the gallery is a nice feature.

The locations of all the gallery graphics and other gallery info is stored in openoffice\share\gallery, in the .sdv files. Copy those files to other computers if you want them to have the same graphics, when you're done doing the configuration.

1. Click the Gallery icon or choose Tools > Gallery.

2. The gallery will appear.

3. Click New Theme.

4.  Click the General tab, and type the name of the theme that you want displayed in the Gallery.

5. Click the Files tab and click Find Files.

6. In the window that appears, go to the directory containing the graphics. The directory can contain subdirectories; all graphics in all subdirectories will be brought in for you to choose from. Click OK.

7. All the graphics in that directory and any subdirectories will be brought into the window.

8. To add only some, use Ctrl select or Shift select to select specific graphics. Select the Preview checkbox to see graphics. Then click Add.

To add all graphics, just click Add All.

9. A window will show the graphics being added.

10. Find more graphics and add them the same way, or just click OK. You'll see the graphics in the gallery. To add a graphic to a document, just drag it into the document.

If  you need to change the name or add files later, right-click on the theme and choose Properties. This will take you back to the same window.

---

---

How to select an image that you put in the background of an OpenOffice Writer document

It's easy to put an image in the background, behind text. Right-click on the image, choose Wrap > In Background.

But once it's back there......you can click all day long and not be able to select it to move it, make it lighter, *delete it*, whatever.

UNLESS.....you RIGHT CLICK on the image. Right-click on it and the green handles show up. Then you can left-click on it and drag it, etc.

Make your OpenOffice forms open with the focus in the first field

If you're using these tools to make your forms

then be sure to click this Automatic Control Focus icon

so that when users open the form, the cursor (the focus), is in the first field in the tab order.

Sometimes you just have to start over with a document (OpenOffice Writer)

I've been doing some consulting, putting together documents in a nice neat organized master document. However, some of the features weren't working--I was having trouble overriding the pagination of the headings at the beginning of each new subdocument, for instance.

Then I talked to my contact with the client and he said that the document had originally been created in the early 90s, in WordPerfect 5, on UNIX. It's nearly 20 years old and has been through at least three major document type conversions and three different operating systems. Not to mention all the different versions of WordPerfect.

This can take a toll on a document.

So I am taking the content, copying it, creating a new empty text document, and doing a special paste as unformatted text into the new document to nuke away all the vestigial formatting of the last two decades, and re-applying the formatting.

(Edit > Paste Special, and choose Unformatted Text.)

When you have an old document that's been through so much, it's not OpenOffice, it's the gunk that the document is dragging along with it. Once I did the paste special, all the formatting worked exactly as it's supposed to.

Making Cross-References Work All the Time

I’ll start this explanation with an example. Here’s a document. I set up the outline numbering as shown, with level 1 as SectionTitle and level 2 as the Numbered Heading. I set up level 3 as the Revised Date.

However, when I tried to insert the current Revised Date in the footer (by inserting the “level 3 chapter” using running headers) it didn’t work, It just showed the most recent content for the Numbered Heading style.

That’s because you have to set up the outline numbering in the same order the styles first appear in the document, for the running headers and footers to also work.

So this is how I ended up doing the outline numbering, even though that’s not really an accurate representation of the structure of the document.

Now, if you don’t want your table of contents to have to follow the order that your outline numbering does, that’s no problem. When you're setting up the TOC, in the main tab select Additional Styles and unmark Outline if you don't want the Outline headings in the TOC.  Then click the ... button next to the Additional Styles checkbox and pick the styles you want. You can also select any individual paragraph (Format > Paragraph, Outline tab, and choose what level in terms of outline numbering you want it to be on; then if you have the Outline checkbox marked for the TOC, that paragraph will show in the TOC.)

How to hide lines if a field is empty

When you do mail merges, especially those involving addresses, some people have apartments and some don't.

If you are using an Address2 field in the address block, you need to hide that line if it’s empty.

Here’s how to hide any line. Let's assume here that the database I'm using for the mail merge is called nameslist_newcustomers.odb, that the table I'm using is just called Sheet1, and the address 2 field is called, well, Address2.

Get the mail merge document ready, with all the fields brought in. Be sure that you have the Address2 field, or the field you want to hide, on a line of its own, since the whole line, i.e. everything in that paragraph, will be hidden. 

Click to the left of the Address2 field. (You can click anywhere on the line, but it’s simpler to always do it one way.)

Choose Insert > Fields > Other.

Click the Functions tab, then select Hidden Paragraph.

 

Type the following; the fieldname that might be empty is the fieldname you want to specify. This means everything on that line will be hidden if that field is empty:

Syntax:   ![databasename.tablename.fieldname]
Example:  ![nameslist_newcustomers.Sheet1.Address2]

Click Insert.

You won’t see anything appear; it will look like the picture below.

If you want to prove to yourself it’s there, click to the right of the Address2 field, press the left arrow once, and press space three times. You’ll see there are two fields, the hidden paragraph field and the regular Address2 field as shown on the right. Now press Backspace again three times so that you won’t have extra spaces in the document.

You’re all set. Now when you print  the line that the hidden paragraph field is on will show only if there is a value for that record for Address2.

The double-edged sword of the "Linked With" feature in heading styles, with tables of contents

When you create a table of contents, you specify which paragraph styles, applied to your headings, should show up in the table of contents.

 

No other styles except the ones you specify (in Outline Numbering, or in other ways) should be included.

So if I create a style called DontIncludeMe and apply it to some text, that text shouldn't end up in the table of contents (TOC), right?

 

You would think so. BUT here's the thing.

If other styles that you apply are Linked With styles that are set to appear in the TOC, then those styles WILL APPEAR IN THE TOC even though you didn't tell them too. Take a look at the Linked With field above, in the style formatting window.

Now, this isn't necessarily bad, just to my mind unexpected. Just be sure that you know this is how it works, then use the Linled With feature appropriately. You can choose None if you don't want the style you're creating or using to show up in the TOC unexpectedly.

Creating a hyperlinked table of contents

You can create a hyperlinked TOC in Writer that not only lets users click on an item to go to that page, but is still hyperlinked when you export the document to PDF.

1. Apply paragraph styles (Heading1, etc.) to your heading text and set up outline numbering (Tools > Outline Numbering) to specify which paragraph styles go with what level headings.

2. Choose Insert > Indexes and Tables > Indexes and Tables.

3. In the Index/table main tab, in the Evaluate Up To field, specify how many levels you want to see. Usually you want to see 2-3 levels.

4. Click the Entries tab. Follow the directions in the following illustration.

5. Click OK. You'll see the linked TOC.

Data type issues in spreadsheet-based mail merges in OpenOffice, or why your mail merge doesn't work with 3.2

Let's say you're putting together a mail merge listing the fees people in the county have to pay for getting their new cat license. You'd put together the spreadsheet data kind of like this, then create a database based on that spreadsheet.

Name    Fee

Bob      $12

Jean    $13

Your mail merge document, with the various prompts and the field names, would look kind of like this:

Name: <Name>

Fee for license: <Fee>

So far so good. But let's say that sometimes there's no fee, perhaps organizations don't need to pay for cat licenses. So your data might look like this:

Name                          Fee

Bob                             $12

Jean                            $13

Library                         N/A

BUT that will not work because mail merges as of 3.2 (possibly earlier) don't like you to mix your types of data in a column. You can have all text, like N/A, or all numbers like 12, but not both.

Here's an example of some test and screen shots I took.

Here's the spreadsheet.

Here's what is in the database, and therefore what a mail merge would have access to.

 

So: in the scenario I outlined you could:

just put in 0, or just blank, instead of N/A

OR do this, which is workable but a bit more complicated.

Name                          Fee        Applicable or Not

Bob                             $12      [leave blank] 

Jean                            $13      [leave blank] 

Library                  [leave blank]       N/A

Then for your mail merge put both the fee and the applicable or not fields next to the Fee prompt. If there's no data the field won't take up any room, and only the correct field will show.

Name: <Name>

Fee for license: <Fee><Applicable or Not>

Note: This change in 3.2 really is a proper approach to databases; having mixed types is not proper database structure. But it is probably causing a few headaches.

Formatting OpenOffice spreadsheet cells with DD/MM/YY

Things don't always work the way you expect. However, it's easy to make things work the right way with spreadsheet cells once you know how it works.

Let's say you want the day first, so that 12/1/10 is January 12th, not December 1st. You do this under Format > Cells. However, if you just change the format to DD/MM/YY manually, you might get some unexpected results. It's usually better to just switch the country/language for those cells.

Here's why. We'll start out "normal" with standard American English formatting, MM/DD/YY.

And now I switch the format code to DD/MM/YY manually...

But now when I type a new date into a cell I've formatted that way....

So switch the language to one that uses the date format you want. Then what you type stays in the same order.

New in OpenOffice 3.2: Notes in Drawings and Presentations

You can now insert notes in Drawings and Presentations, as you can in Writer. Just choose Insert > Comment.

Switching Between Absolute and Relative Cell References in OpenOffice.org Calc Spreadsheets

If you want to refer to a cell absolutely, you type $A$1 instead of A1. This means that if you drag down the cell or drag it over, that it doesn't change to A2 or B1.

Let's say, though, that you realize you need to switch from absolute references to relative, or vice versa. You could laboriously retype everything. However, there’s a quicker way. To switch from relative to absolute or vice versa, select the cells containing the formulas and press Shift F4. You’ll see in the illustrations how it cycles through.

Managing ANDs and ORs in the OpenOffice Calc Standard Filter

The Standard Filter is a nice tool. But when things get complicated, it's about the logic, not the software. Make sure you keep the logic solid as you build the filter.

Let's say you've got this list of people. We'll keep it short just to simplify the example.

 

You want to build a filter that will filter OUT everyone EXCEPT:

people from Kalispell whose names are also either Hanson or Miller.

Taking a look at the data, the highlighted names are the only ones who fulfill the full criteria.

There are other people named Hanson, and Miller (and Hansen) but these are the only ones with the right names who are also from Kalispell.

So select the data, choose Data > Filter > Standard Filter, and you might build this filter.

 

Hanson, OR Miller, AND Kalispell. 

But you get this data, because what the filter really says is

Hanson

OR Miller AND Kalispell

What you need to do is repeat one of the criteria, so you have two sets. Like this:

 

This says

Kalispell AND Miller

OR

Kalispell AND Hanson

So you get this correct result.

In OpenOffice 3.2 there are eight spaces for your filter criteria, so you do have the room to put in all the necessary logic.

Topics for Online Seminars?

I'm going to be starting a regularly scheduled series of seminars soon, some paid and some free. I'd like your ideas for what would be the topics you'd like me to teach, as well as times (day/evening, week/weekend) and preferred length. Thanks for your help!

E-mail Address: * What topics would you like to learn about in an online seminar? When would you like online seminars (day/weekend) Weekdays Weekends When would you like online seminars (time of day) 6-9 AM 9-12 AM 12-3 PM 3-6 PM 6-9 PM Other comments * Required Create Email Forms

Free Online Seminar Thursday February 25th: Mail Merge

Ever wanted to just be guided through how to do a basic mail merge? Here's your chance! I'm going to be testing out my new online seminar tool, and I'd like your help. I'll be hosting a 2-3 hour seminar on how to do mail merges, using a spreadsheet for your data source.

• Creating the data in the proper layout
• Creating a new database based on the spreadsheet
• Creating a letter using fields from the spreadsheet
• Creating labels using fields from the spreadsheet
• Sorting and selecting the records you want to print
• If time permits: envelopes

DimDim is the tool I'm using, and it purports to support Windows, Mac, and Linux.

I'll provide a short downloadable guide to the mail merge process, as well, during the seminar as a reference.

Click on the widget to register. I can have up to 20 participants, and it's first come first served. Please register only if you can attend. It's 10 AM mountain time on Thursday February 25th.

When you register you'll receive an email with the call-in number and URL. You'll need to call in from a phone in a quiet location. The phone call is not free but should be relatively low-cost.

Thanks for helping me out with this test, those who register, and I look forward to clarifying the mail merge process.

"Contains" and "Starts With", and more, now in OpenOffice Calc 3.2

You could do it before with regular expressions, but now you can have more control in filtering with:

• Contains
• Does Not Contain
• Begins With
• Does Not Begin With
• Ends With
• Does Not End With

From the spec: "The "Standard Filter" dialog now shows 4 instead of 3 conditions, and has a scroll bar which allows to access up to 8 conditions in total. It now supports the filter conditions 'Contains', 'Does not contain', 'Begins with', 'Does not begin with', 'Ends with' and 'Does not end with'."

So you can look for Hansen and Hanson but not, say, Senngard or Pensonner, by using "Ends with" and an OR.

(Note that as always you have to be careful....you would also find Manson with this, so you might want to just look for = Hanson or = Hansen.)

In my happy place: reading list of 3.2 upgrades

Download the official release for OpenOffice 3.2!

And here are the features.

http://www.openoffice.org/dev_docs/features/3.2/

All right. This will take a few posts but let's start talking. This isn't the complete list, see that above, but it's the stuff I'm excited about.

General

Faster startup times! Haven't tested but the graph looks great. 

Some file support stuff: MS Office 2007 passwording is supported. That's a nice reassuring feature. Also 2007 pivot tables are supported.

• Password protected Microsoft Office XML files (supported document types: MS Word 2007 documents (*.docx, *.docm); MS Word 2007 templates (*.dotx, *.dotm); MS Excel 2007 documents (*.xlsx, *.xlsm); MS Excel 2007 binary documents (*.xlsb); MS Excel 2007 templates (*.xltx, *.xltm); MS Powerpoint 2007 documents (*.pptx, *.pptm); MS Powerpoint 2007 templates (*.potx, *.potm)).
• OLE objects, form controls and pivot tables can now be read from MS Excel 2007 documents (*.xlsx, *.xlsb).

And fonts galore are now available. "There are many high quality commercial and free OpenType fonts that are based on Postscript outlines. They are now supported for formatting, printing, PDF-export and display."

Calc

Love this. You know how you have 10.001 and you start incrementing and it goes to 11.001? Hah! Not anymore. "If the starting cell for AutoFill contains a number at the start and the end, and there's no space after the first number, the number at the end is now incremented. This allows easier creation of things like lists of IP addresses (10.0.0.1 is followed by 10.0.0.2)." Note that 10.001 still goes to 11.001 as shown below.

And this is nice. The cell formats come with the numbers when you sort. "When a cell range is sorted using the "Sort Ascending" or "Sort Descending" toolbar button, cell formats are now sorted with the cells."

The info SAYS that you can copy and paste noncontiguous ranges of cells but I can't get it to work. "This feature extends Calc's current copy and paste functionality, to allow copying of a set of multiple non-contiguous ranges. When pasted, all copied data get consolidated into a single range either horizontally or vertically. There are some restrictions when ranges are being copied, in order to ensure that the pasted range become a rectangular range."

Drawing a straight line in GIMP

With the paintbrush or pen tool, you can draw a straight line, at any angle (not just 45/90/180) by clicking where you want the line to start, holding down Shift, then clicking where you want it to end.

Quick guide to convenient tools in GIMP

Here's a very quick callout to the tools I use most frequently in GIMP: clone stamp, bucket fill, color select, and so on.

Selecting a contiguous area of a color, or selecting all instances of a color, in GIMP

Take a look at the this image. Let's say you want to get rid of the sky, or change it to a different color, because you're going to just use the mountains in a separate image. You want to get rid of that shape of blue; however, there could be bits of blue in the shadows that you don't want to get rid of.

Now look at this; you want to do something similar, select a bunch of one color, but you want all the instances of that color.

 

For the first instance you use the Fuzzy Select tool; for the second you use the Color Select tool.

 

Using each of them is simple. Click on the tool, then set the tolerance threshold and any other options.

Click in the image; you'll see what's selected. If you're happy, press Delete or complete another task on the selected area. If not, click off and reset the threshold or other settings.

Doing red-eye reduction in GIMP

There's a pre-fab redeye reduction filter in GIMP that works pretty well. Here's how to use it.

1. Open the image with the red eye issue.

2. Click the rectangle select tool. You’re going to select the area of just the eyes. You don’t have to do this but if there is red anywhere else in the photo, you risk having that turn darker too unless you select just the eye portion.

Draw a box around the eyes.

3. Choose Filters > Enhance > Red Eye Removal.

In the window that appears, check to see in the preview area if the red is still visible. If not you're fine; if so you need to increase the tolerance, i.e. the amount of color similar to red that’s changed.

If the eyes are red, drag the slider bar to the right. Stop when you see that in the preview area the eyes are now dark.

4. Click OK.

GIMP: GNU Image Manipulation Program, or "free Photoshop"

In my enthusiasm to share GIMP tips, I forgot to back up and talk about it a bit.

It's an open source program for editing and creating raster images. So, Photoshop like. It's not Photoshop lite, though; as with Photoshop there are hundreds of features that I've never heard of the words for, much less know how to tackle. It's big and powerful. I am not a graphic artist so can't do a detailed evaluation or comparison, but my point is, it's got lots of stuff.

You can download and install it from here. http://www.gimp.org/

Here's what the GIMP folks say about it:

"GIMP is an acronym for GNU Image Manipulation Program. It is a freely distributed program for such tasks as photo retouching, image composition and image authoring.

It has many capabilities. It can be used as a simple paint program, an expert quality photo retouching program, an online batch processing system, a mass production image renderer, an image format converter, etc.

GIMP is expandable and extensible. It is designed to be augmented with plug-ins and extensions to do just about anything. The advanced scripting interface allows everything from the simplest task to the most complex image manipulation procedures to be easily scripted.

GIMP is written and developed under X11 on UNIX platforms. But basically the same code also runs on MS Windows and Mac OS X."

Here's what it looks like.

I definitely recommend downloading it and trying it out. You might have heard that it's hard to use. I wouldn't really say that. Some of the items aren't under the menu you'd expect them in, and the phrasing is a little weird, but overall, not too bad. Plus, I'm blogging about it here for a while, and there'll be a book coming soon.

Taking a screen shot using GIMP

You can get a screen shot of your whole screen at any time by pressing Print Screen. You can also use Snagit, which I personally love. However, if you'd like to do screen shots with GIMP, here's how. 

1. Get ready to take the shot, then choose File > Create > Screen Shot.

 

2. Choose to grab a single window. Also, if you need some time after grabbing the window to do stuff, specify a delay. For instance, you might want to click on a window, then start a video, or select some text, or something else that can't be done while clicking on the window you want. (Some quick keys or cursor capture stuff is hard to do without a delay.)

 

3. Click Grab.

4. Drag the actual little crosshairs to the window you want. You don't see it here but when you do drag the crosshairs, a darker black border will appear around the selected window.

5. A new untitled document will appear, containing a screen shot of the selected window.

Some posts on GIMP: Creating a new image from a copied image

I've been playing (and writing a book about) GIMP for the past month or so and if you've been thinking about getting started, I definitely recommend it. I'm not a graphic designer so the advanced stuff is beyond me in experience/application, but I use Photoshop and everything I do is doable in GIMP. Some of it seemed easier (some not) to my self-taught Photoshop mind.

Here's a simple but not obvious basic. When you copy an image and create a new empty document in Photoshop, it automatically reads the clipboard and creates on in the appropriate size. In GIMP, it proposes standard 600x400 or something static like that.

How do you get GIMP to give you the right size for your copied image automatically? Choose File > Create > From Clipboard instead of File > New.

Creating a presentation quickly from a directory of images in OpenOffice Impress

Once I installed this extension, it took me about six seconds to create a presentation based on a directory of graphics, one slide for each graphic.

http://extensions.services.openoffice.org/node/419

Download the extension, don't unzip it, then choose Tools > Extension Manager as always to add it.

Then choose Tools > Addons > Create Photo Album.(If that doesn't show up, restart OpenOffice.)

Point to the directory where your graphics are. That's it. You get a presentation.

Only thing is, the randomly chosen slide transitions are a little annoying, so click the Slide Transitions item, select No Transition (or something you like), then click Apply to All Slides. And you can use that pane also to change the amount of time on each slide, or to switch it to manually going from one slide to another.

 

Applying borders to nonconsecutive cells in Calc 3.2 spreadsheets

In OpenOffice.org 3.2 you can apply borders to nonconsecutive cells using the toolbar.

Currently if you try to select multiple cells that aren't together and apply borders on the toolbar, you get an error. (You CAN apply border formatting from the Format > Cells, Borders tab currently.)

In 3.2, though, there's no problem using the toolbar icon.

The somewhat variable helpfulness of the Function List in OpenOffice Calc, and how to get the full syntax

 
I'm a big fan of the function list palette, as a list of functions. Choose Insert > Function List and the palette of all functions will appear. Much easier to use than the wizard just as a reference. In the dropdown list at the top of the function list palette, select the category of functions you want to view.

Then just double-click the function you want, or click the little F(X) button in the palette, and the function and syntax will appear.

But look what appears, for instance, for =AVERAGE.

That's just not that helpful period; the average of one number is itself, plus who wants to find the average of a number?

If you look at the function wizard (click the F button next to the function entry field on the toolbar) or look at the help or do something else I'll show you in a second, this is the syntax. You could do a range or just list the numbers.

 

Likewise the syntax for =CONCATENATE from the function list is this

 

(not helpful, though not strictly wrong)

and the actual syntax that would be helpful is this.

 

Sometimes the function list does give you the full accurate helpful syntax as with HLOOKUP.

 

Here's what I recommend.

* Use the function list as a reference to jog your memory. Is it AVERAGE or AVG, that kind of thing.

* Then use the function wizard to get the real synta

* Or the help

* OR (fastest) type in the function as shown.

   * First you get a popup with the name and the basic syntax. 

   * Press Enter. Then you see the full syntax.

   * Don't press Enter, it will disappear. But you can use it as a guide to the syntax, and drag, click, or type to enter the appropriate cells/ranges/values.

Another approach is to use the function list, click to add it and its maybe-not-complete syntax to the cell where you want the formula, then click between the parentheses. The same little popup that you get with the previous approach will show then too.

The key point here is, the function list doesn't always give you the whole story on the syntax. Get the full story another way.

Exporting a particular graphic to a file in OpenOffice 3.2

In the upcoming (developer version available) version of OpenOffice.org 3.2, you can right-click on a graphic and save it to a file.

Copying multiple sheets at once in Calc spreadsheets

If you try to select several sheets in a spreadsheet, then right-click to copy, all but one come unselected. It's not possible to copy multiple sheets that way.

However, if you Ctrl Select or Shift Select the sheets by clicking on the sheet tabs, then choose Edit > Sheet > Move/Copy, then right-clicking doesn't disrupt the selection.

First, select the sheets you want to copy. Click on one, then hold down Shift and select the last one (select all sheet tabs between first and last) or hold down Ctrl and click on each specific sheet tab that you want. The sheet tabs will change color very very slightly, will be come slightly lighter, when they're selected.

Then choose Edit > Sheet > Move/Copy.

 

The option to Copy will already be marked and dimmed so you can't change it. Select the sheet after which you want the copies to appear.

 

Click OK and you'll see the copies.

How to output to files all the graphics that are pasted into a document in OpenOffice Writer

You've been there. You have a document full of graphics and you'd like to be able to save them all as individual separate graphics files. Mountain.jpg, picnic.jpg, diagram.jpg, etc. But it's a pain, and there are a LOT of them.

Here's a way to use the web wizard to make it a lot easier. I think this should work also in Calc and Impress, I just haven't tried it.

I just got an excellent comment on this -- you can also change the file extension to .zip, unzip it, open it up, find the graphics directory, and there are all your graphics without this process. Copy them out. Then zip up the file again and change the file extension back to .odt. If you're comfortable with that, that's the faster process.

So here's a document.

Choose File > Wizards > Web Wizard.Click Add to add the document with the graphics. Choose to export to HTML.

It doesn't particularly matter what options you choose; just pick some layout and export it to HTML.If you might ever use the exported version then pick the appropriate layout for the pages.

Pick the output directory.

The exported files will have a graphics and a content directory. You want the content directory. Go to the directory you exported to, open contents, and you'll see all the graphics.

How to do a holiday letter using mail merge, and print labels for the envelopes

I've done a lot of mail merge postings, but it's A) a powerful tool that B) isn't that obvious and C) is particularly useful at this time of year. (Me, I don't actually do holiday letters, not yet anyway, but I enjoy the ones I receive from friends.) So this is another one for this year. I've tried to make it as complete as possible without actually rising up out of the browser and knocking you over the head with painful details.

Here's how to create physical, printed-on-paper, snailmailable letters and labels, soup to nuts. 

If you want to try doing it via email, try this link instead.

http://openoffice.blogs.com/openoffice/2008/10/doing-an-email-mail-merge-without-the-overhead-of-the-openoffice-mail-merge-wizard.html

Step 1: Get the data in order.

Take a look at the data. Make sure you have the fields you need. Are you planning to address the letter to the <Lastname> family? You're probably fine then but if you want to address it to two people who live at the same address with different first and last names, you won't be able to do that without some data juggling. Consider in that case just sending two letters. Also if you want to use Mr., Ms., Miss, etc. in the letter or labels, make sure that info is in alllllll the address records. Otherwise you'll have an empty space in your label or salutation. Not the worst thing but a little off. In general, simplest is best.

If you have to assemble the address data from various bits and drabs of info on your computer, I suggest putting it in a spreadsheet. You must create one column for each separate piece of information, and you must (should, but I'm saying must) have a heading for each column, as shown.

Step 2: What format is the data in?

If the data is in your mail client address book, you can use that. If it's in a CSV text file, you can use that. Spreadsheets work too. So do Access databases. mySQL, Oracle, pretty much anything. Just make sure you know that the data is in an acceptable format, and you know what format it is. 

Step 3: Create the little database file that lets you suck the address data into a letter.

Follow the steps for the type of data you have.

YOU ONLY NEED TO DO THIS ONCE FOR EVERY SET OF DATA YOU HAVE. Once it's set up, it just sits there, ready for you to use in any mail merge document you want. (Letter, labels, another letter, invitations to a holiday party, birthday cards for everyone in your list with a January birthday, and so on.)

Text File Instructions

If your data is in text files, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Text as the format.

3. Click Next.

4. Specify the DIRECTORY where the text files are. Each text file in that directory will be a table in your database. Then select the item separating fields, i.e. a tab or comma or something else.

5. When all the settings look correct, click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.

You're done.

Make the Data Source: Spreadsheet Instructions

If your data is in a spreadsheet, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.
 

Address Book Instructions

Start off the same way as either of the other two, but specify the type of address book such as Thunderbird address book. Then click Next; the program will find it. All you have to do then is, as instructed in the other two approaches, save the database and name it as something you'll remember.

Step 4: Create the letter you want to write.

This has absolutely nothing to do with mail merge. Write the letter, put in graphics, go nuts with formatting. DON'T SAVE IT IN .DOC FORMAT; IT NEEDS TO BE IN NORMAL OPENOFFICE FORMAT. BE SURE THE FILE EXTENSION IS .ODT.   

Write in the name and address of a real person to help you figure out what fields you want.

Step 5. Put in the fields from the data.

Choose View > Data Sources. Everything you've created will be displayed. Click the + sign by the data source you want to use, then click + by Tables til you see the data you want to use.

Find one of the pieces of data you wrote, like "Bob" which is a first name. You're going to delete that, then drag in the FirstName field to the place where it was.

Click on the NAME OF THE FIELD, not the piece of data, that you want in the mail merge.

Drag it into the document and release. The field name will appear. If it goes to the wrong place, just cut and paste it to where you want it.

 

Continue with any other fields that need to replace any other pieces of individual data.

Step 6. Print the letter

I suggest printing to a file ("Individual" file), opening the file, checking it, then printing that file.

The following steps show how to do that. If you want more control, here's how to filter, sort, restrict, etc.

http://openoffice.blogs.com/openoffice/2009/08/queries-in-openoficeorg-mail-merge-how-to-print-only-documents-for-the-records-you-want-in-the-order.html

Choose File > Print. Click Yes when it asks if you want to do a form letter.

In the window, be sure that the correct database is selected, then select File and Individual.

 

Click OK. Name the file. This is the file that will contain a copy of the holiday letter, one for each person in your address book. It will say Dear Bob, Dear Tina, etc. It will be a normal Writer document, no fields in it or anything special.

 

Name it and click Save.

The mail merge process will run.

Now open that file, the merged output file, and check to be sure it's been created the way you want. If it isn't, go back to the mail merge letter (the one you dragged fields into) and make adjustments. Then print again, creating another output file.

Once the output file is how you want, just print it normally.

Step 7: Create the labels

Now that you've got the database set up, creating the labels is easy. Create the labels for whatever label type you're using, following these instructions.

Then print the same way you printed the letter; just be sure that when you're ready to really print on label paper, that you insert the label sheets in the way that works for your printer.

http://openoffice.blogs.com/openoffice/2006/07/mail_merge_labe.html

Step 8: Laboriously fold, stuff, label, and stamp the envelopes.

Step 9: Consider doing it with email next year.

http://openoffice.blogs.com/openoffice/2008/10/doing-an-email-mail-merge-without-the-overhead-of-the-openoffice-mail-merge-wizard.html

Modifying raster shape colors with the RGB and Brightness tools in OpenOffice.org Draw

If you want to change how a drawing shape looks, you can easily use the border and fill shapes. However, if the object is a bitmap, like a digital camera photo or a JPG you got off the internet, you need to do something different.

You don't have the capabilities of GIMP or Photoshop but you do have quite a bit to work with. Here are some examples. You just click on any JPG or other shape, and you'll see the Pictures toolbar. Click on the rainbox icon on the Pictures toolbar and you'll get the other toolbar. Change the brightness/contrast (the sun), the contrast, and the Red Blue and Green values. The range of possible values is 100% to -100%, so higher for more intense and less for less.

Use the tooltips to determine what each icon does. Move your mouse over each icon and you'll see a description.

Seeing an HTML preview of how your document will be rendered on the web

When you post OpenOffice documents on the Internet, people searching for it can view an HTML version.

If you'd like to see what that would look like, use the File > Preview in Web Browser feature for Writer, Calc, and Impress documents.

Here's a more detailed specification.

http://74.125.95.132/search?q=cache:0KdoLY33KG8J:specs.openoffice.org/appwide/web_preview/Web_Preview_Specification.odt+%22preview+in+web+browser%22+openoffice&cd=1&hl=en&ct=clnk&gl=us&client=firefox-a

Also note that you can convert any document to HTML or PDF using File > Wizards > Web Page, if you'd like to have more control. I blogged about that in more detail here.

No templates? No problem. Convert any MS Office template to use easily in OpenOffice.

File > Wizards > Document Converter.

Then choose Tools > Options > OpenOffice > Paths and update the Templates line to specify where the converted copies were created.

For more detail, see this post.

Conversion examples between OpenOffice.org and Microsoft Office

I wanted to post a quick example of some key formatting, and show that the converting works pretty well.

Here's a document in OpenOffice. The graphic on the first page is inserted three times: anchored to paragraph with wrapping, anchored to paragraph without wrapping, and anchored AS CHARACTER which means it just flows in the text flow like any other character. As you see, all come through fine. (For more information on anchoring, see http://openoffice.blogs.com/openoffice/2009/01/how-to-insert-graphics-in-openoffice-writer-that-are-reasonably-manageable.html. )

 

And the Word version.

 

Here's the second page of the OpenOffice document, with various lists.

Here's the second page now in Word. Note that the graphical bullets didn't come through. They don't come through when I close and reopen the .doc file in OpenOffice Writer either; Word file format can't handle this kind of bullet graphic.

However, due to the huge ASCII options available, you can have any character non-graphical bullet that you like. To do that, format as bullets, then choose Format > Bullets and Numbering, Options tab, and make the selections shown.

Follow me on twitter! (updated link to new ID)

I made the account ages ago, but I'm starting to tweet. And learn how as I do it.

http://twitter.com/getopenoffice

Searching and replacing paragraph returns (carriage returns), tabs, and other special characters in OpenOffice Writer

This is a repost, but it bears repeating since it's powerful, helpful, and  not obvious.

(Originally published December 2005)

John V and Gabor at the OpenOffice.org Forum helped me find this solution.  See also this blog on bringing text files into spreadsheets.

Call them carriage returns, line breaks, paragraph marks, whatever, sometimes you want fewer of them. Maybe you've brought in some ASCII text that had a line break or two after every paragraph and now with formatted text you don't need it. Or you're turning a spreadsheet or database into text or vice versa.

At any rate, it would be nice to use the Find and Replace window to quickly find'em and change them to whatever you want: nothing at all, or the phrase "el elegante" or whatever.

Note: If you're a macro kind of person, see this page on the ooo forum.

Searching and Replacing, Step by Step

In your OpenOffice.org document, choose Edit > Find and Replace or press Ctrl F. The Find and Replace window will appear.

In the Find and Replace window, enter the symbol for what you want to search for, in the Find field. Here's a quick reference to the symbols to enter for what you're looking for.

• Regular carriage returns  are $
• Soft returns inserted with a Shift Return, are \n
• Just an empty paragraph, i.e. a carriage return but with no text on that line, is ^$
• Tabs are \t

In the Replace field, you typically don't enter anything since you're probably just trying to get rid of whatever you're searching for.

• If you want to replace something with a carriage return, put \n in the Replace field.
• If you want to replace one carriage return with two, put \n\n in the Replace field.
• One thing--you can't replace something with soft returns. As you see, a \n in the Replace field turns into a normal hard return.
• Just use \t normally, in both the Search and the Replace fields, for a tab.

Once your Find and Replace fields contain what they should, click the More Options button. Select the Regular Expressions checkbox. This will make the program look for what those codes represent, rather than literally those characters.

If you're using a mix of regular expressions and normal characters, you might need to use a \ in front of anything you want evaluated normally. For instance, if you really are looking for the symbol $ but you want to replace it with a carriage return \n, then you need to actually search for \$ in the Search field and replace it with \n because $ is a special character.

This illustration shows you're looking for a carriage return (any carriage return), and you're going to replace it with nothing.

Click Find. The first instance (from where the cursor was) of the thing you're looking for will be highlighted.

Click Replace to do the replacing.

And so on. Keep going until you're done. Use Replace All only when you're absolutely positive you'll get the results you want.

---

Using Scenarios in OpenOffice Calc spreadsheets to figure out what to do

My friend Stephanie posted on Facebook that she was using a spreadsheet to figure out which car to buy. My mind immediately flew to scenarios. Here's an example of how you could use two scenarios to really get serious with pros and cons and the amount of money you'd be spending.

Let's say that you're not sure yet which job you'll take, a job with IBM or whether you'll continue to work at home. And you're also considering various cars, and what the cost of ownership will be depending on whether you work at home or have to drive to the IBM site and (in this case, just for fun) pay for parking. Typically, or at least some of the time, you only have one scenario. But I'm showing two in this situation that both affect that big bottom line calculation, just because it shows the power of using them together.

Here's the file Download Scenario_start as it is when I start the example, and here's the file Download Scenarios_done finished.

First thing you do, set up the spreadsheet with the data for one of the scenarios (a job scenario) and another scenario (a car scenario). So far you're just doing a normal spreadsheet. Put in the data, label it, then do a big complicated field that uses data from all over the spreadsheet to figure out how much it's going to cost, per year, to get the data for the car that's currently showing.

Here's how it looks in normal view.

 

Here's a closer look at the big complex Annual Cost of Use field. It brings in all the fields.

 

You've already typed in one version of each scenario but where it gets interesting is overlaying a different version of the data. You're going to do a different version of the job scenario.

So select the data (not the label) in one scenario, the job scenario here.

 

Choose Tools > Scenarios.

In the window, give the scenario the title that you want to be displayed in the spreadsheet, any notes, and select a color.

Click OK. You'll see the scenario, exactly the same data but with a dropdown list and the color you gave it. You've saved the first set of data as a set that you can go back to.

 

Now that that set of data is saved, retype a new set. You don't need to do a title here, just retype what the mileage to work would be, parking, and any other changed data, for a different situation: working at home.

 

Now, select that data again as you did last time.

Choose Tools  > Scenarios.

Give the scenario a name that you want displayed in the spreadsheet, plus notes optionally, and a different color.

Click OK. The new data is saved in this scenario, and of course it affects the bottom line.

Now you can switch between the scenario names, and the data you specified  for that scenario is displayed.

 

You can keep going as long as you need to--if you have five job offers with five different driving distances and parking costs, you would enter them all.

Typically, or at least some of the time, you only have one scenario. But I'm showing two in this situation that both affect that big bottom line calculation, just because it shows the power of using them together.

To create multiple scenarios for different cars, you do exactly the same thing. You've already got the first set of data, so select it.

 

Choose Tools > Scenarios and enter a name, notes, and a color, just like before.

 

Click OK. You see the saved scenario with the name you gave it in the window, and the color.

Now change the data for what it would be for another car.

 

Select that data, create another scenario like Subaru, Echo, Bike, etc.

 

Click OK when creating it, and you have two different ones to switch between.

Create additional car scenarios if necessary, then switch among car and job scenarios to see what the cost of annual ownership of the car would be in each permutation.

Note: Deleting the scenario isn't obvious. Here's how. Click the Navigator button or press F5 to bring it up. Click the blue-circled Scenarios button, then right-click on the scenario name and choose to delete.

---

---

How to make text wrap around a graphic (and resizing the graphic proportionately) in OpenOffice Writer

When you insert a graphic, it usually looks like this. You can't get the text on either side of it.



What do you do if you want it to look like this, though?




Or what do you do if you want to have the graphic there on the left, then you want to start typing over there on the right, to the right of the graphic?

Just follow these steps. Put your text in first, then insert the graphic. (Insert > Graphic > From File, or paste something from the Internet, or choose Tools > Gallery to drag in a graphic.)

Now.

Right-click on the graphic and choose Wrap > Page Wrap.




Now the text can wrap around it.



It doesn't look quite right, though. The words come right up to the edge of the picture. So give it some space. Right-click on the graphic and choose Picture. Click on the Wrap tab and give the picture a little space on all four sides.



Now there's more space.



Also, you might want to drag it to a different point in the document, so go ahead and do that. The wrapping will continue to work.



Finally, you might want to resize the graphic. Click on it so the green handles on the sides and corners show, then move your mouse over a corner handle and drag in to shrink it or out to enlarge it.





Hold down the Shift key while you do this, to make sure that you keep the picture proportionate and don't make it to squat or skinny.

---

---

Combining two or more CSV text files in the same sheet in OpenOffice Calc

Let's say you get a bunch of CSV files exported to you each week, or each month. They're all related so you want them all together in the same sheet so you can analyze them together.

And when you export a new version of CSV file, you'd like that exported data to automatically update the spreadsheet. So if the new amount for account AB-12 is 10,000 in January and 12,000 in February, you want the spreadsheet to show 12,000 when you export the data February 1st.

This would be great with Insert > Sheet From File but you can't combine those sheets. So just use either Paste Special or, preferably, basic Sheetname.cellname syntax, in a central "combo sheet" to refer to separate sheets linked to each CSV file.

Want more detail? See this PDF.

And because I like to be sure that key search terms come up as much as possible, I'm pasting the content in here too. I know the screen shots didn't come up but I want to get this posted so am acknowledging laziness. ;>  The screen shots are all in the PDF so just use that; this is just for extra searchability on this topic.

Using Two or More CSV Files on the Same Sheet of a Spreadsheet

Solveig Haugland, GetOpenOffice.org

There are three basic steps:

1. Determine the Name and Location of the CSV Files

2. Bring Each CSV File Into a Separate Sheet

3. Combine the Separate Sheets Together in One Sheet

Notes before beginning:

You must use the “link” checkbox when bringing in CSVs.

You can rename the individual sheets that you bring the individual CSV files into, once you create them, but don’t rename them after you create the combo sheet.

Determine the Name and Location of the CSV Files

The CSV files can be in different directories and can have any names, though I strongly recommend that there be no spaces in the names.

If you want to replace the CSV information that will be brought into the spreadsheet when you export a new CSV, then when you create new CSV files, make sure each new CSV file with updated information has the same file name and is put in the same directory.

Bring Each CSV File Into a Separate Sheet

In a new or existing spreadsheet, follow these steps.

1. Choose Insert > Sheet From File.

2. A window will appear, as well as another on top of it asking you to select a file. Pick the CSV file you want to insert.

3. In the next window, you’ll be asked to pick the separator that separates the columns in the CSV file. It’s usually comma but sometimes tab. Usually the program will pick the right one automatically. If it looks good in the bottom part, with the data in columns looking right, then just click OK. Otherwise select a different checkbox; for instance, uncheck Tab and check Comma.

4. Click OK.

5. In the window that is now shown, select Link. Nothing else matters since you can drag the sheets around where you want them once they’re created.

6. Click OK.
   

7. The data will appear in a new tab in your spreadsheet. If the data in the file changes, the data here changes.

8. To clarify for yourself exactly what data is in this sheet, right-click on the sheet name and choose Rename. Type a new name for the sheet and click OK. Make a note of the name you give the sheet, you will type it in the next step. In the examples, I rename this sheet january_accounts.

9. Repeat the steps in this section for each additional CSV file you want in the same spreadsheet.

Combine the Separate Sheets Together in One Sheet

Create or select a sheet to use to combine the sheets. I recommend you name it something distinctive to be sure it’s clear this is the combined sheet. This is different from the procedure in class but addresses the problems that we had with Paste Special.

1. Go to the first sheet that’s linked to a CSV file that you want to put in the combo sheet. Note the sheet name. This is the name you gave to it when you renamed it, after importing the CSV file.
   
   Also look at the data. How many columns across is it? Does it go to G or to HH? Then note how many rows it has; does it go down to 10 or to 400?

2. Go back to the combo sheet. Click where you want the data to start appearing.

3. Type the name of the sheet, then A.1. and press Enter. The syntax is =sheetname.A.1 and an example is january_accounts.A.1.

4. Once you’ve typed the text from the previous step and pressed Enter, you’ll see the first cell from the sheet you’re bringing in.

5. Copy that cell.

6. Now select as many cells as you could possibly need to show all the data from the sheet you’re bringing in. You noted this in the first step. Select from A1 to G100, if necessary, or from A1 to JJ459.
   
   To ensure that there is always enough space for the CSV data if there are more rows next time, select a few extra rows, perhaps 10 extra rows for a small sheet or 100 extra rows for an enormous one.

7. Paste: just do a normal paste. You’ll see something like this, with 0s showing where there is not (currently) any data in the sheet with the CSV file.

8. Now go down farther in the combo sheet to where you want the data from the next CSV file’s sheet to start, and repeat the steps in this section.

---

---

Keyboard shortcuts for keyboard navigation, in OpenOffice Writer and Calc

I'd like to suggest that everyone check out the keyboard shortcut features under Tools > Customize, Keyboard tab. Try for Writer and for Calc. Just select the Navigate item in the left hand side and scroll through all the possibilities.

You don't need to change the keyboard shortcuts. Just check out all the possibilities.

Printing a selected print range in OpenOffice Calc spreadsheets

When you have an enormous spreadsheet, you don't always want to print the whole thing every time. You want to print just a chunk. Also, when you have an enormous spreadsheet, you don't want to have to re-specify, every single time, exactly which range you want to print.

So you define named ranges. One called Just Totals, maybe, one called January through March--whatever. Whatever the logical subchunks are that you want to print.

How do you easily A) define those ranges with a meaningful name and B) specify which ONE of those you want to print?

Here's how.

How to name a range
Select the range.

Choose Insert > Names > Define.

Name the range.

Click Add.

Click OK.

Now it shows up in this list. Any additional ranges you define will also show up here.

How to choose to print a range
So you're going along, minding your own business, somewhere else in the spreadsheet, and you decide you want to print a range.

Select the range name from the list.

The range is automatically selected.

Choose File > Print.

Select the Selected Cells item.  Click Print. You'll only get the selected cells, aka only the ones in that named range.

---

---

Great new features in 3.2, including the ability to insert new columns in the middle of merged cells

Check out the features.
http://development.openoffice.org/releases/DEV300_m58_snapshot.html

Download and install. Note that this is still a developer build.

Things that struck me and made me yell "whoohoo!" (and there are more) are:

* Ability to insert new columns when there are merged cells in that area

* Pressing Shift - F4 lets you toggle between absolute and relative cell references and it applies to a group of cells, whatever you select

* Way more than three criteria for the standard filter now

* "Create new style" button in the Conditional Formatting window -- not as good as just defining the formatting without styles but very close to the same thing

Easily splitting one column into two or more columns at the separator(s) of your choice, in OpenOffice.org Calc spreadsheets

Let's say that you have some nine-digit zip codes, and you'd like to split them into two columns, the regular zip and the extension. The following illustration shows both. (And this post shows other ways to split them and/or deal with sorting them correctly.)

Select the column and choose Data > Text to Columns.

This is what the window looks like by default.

Now you need to specify at what character you want Calc to go "Hey, time for a new column!" In this case it's the dash. It's not listed so you just deselect Tab, select Other, and type the dash. Then scroll through the data and you'll see how it's going to look if you click OK now.

Click OK, and you get exactly what you want. (Note that you could split a column into two or more, splitting a social security number column would give you three columns because it has two dashes, 333-44-5555.)

If  you have phone numbers, you can use this to split the columns twice, once at ) and then once at -.  (You would use Edit > Find and Replace to get rid of the ( at the beginning of the area code.)

Phone number data:

Data > Text to Columns brings up the regular window where, the first time, you specify ) as the separator.

Click OK and you get this.

 

You could do it again on the column containing the phone number with the - and split that at the - the same way we split the zip codes earlier in this post.

Or just specify both separators, the ) and the -  at the same time, and one split will give you three columns.

---

---

You must read Randy Orwin

He knows all and explains all about open source, including the very technical details, and how it works in the real world with people, administrative details, and more.

http://k12opensourcehelp.com/blogs/randy-orwin

Switching to OpenOffice.org

I wrote this article last year for my colleague Russell; here's the link.

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.

---

---

New search field on this blog

Hi,

I've added a Search This Blog field to both sides of my blog. Apologies for the years of lame or no search functionality!