OpenOffice.org Training, Tips, and Ideas

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!

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

http://twitter.com/solveighaugland

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