OpenOffice.org Training, Tips, and Ideas

Getting Data Back Out of OpenOffice Base

Click here.

Note: Having clearly labeled import and export features for Base has been proposed and you can vote for it by clicking one of the following links. Here's how to make things work until the features are implemented and put into the next build.

Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export

---

Here's the related article on the cleverly disguised import function, for getting data from a spreadsheet, Access, or another database into an OpenOffice Base database.
http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1222186,00.html

---

Turning data into information with functions in OpenOffice Base

I've written an article for TechTarget on how to use the functions such as Sum and Max in OpenOffice.org Base queries and views.

It took me a while to grok how to use these -- the difficulty is not in applying the functions, but in the kind of data you can use the functions with, and the Group function that needs to be applied along with Sum, Max, or whatever you're using.

 

---

More on the Data Pilot: Analyzing a Data Source/Database

 

It's time for more data pilot adventures.

I blogged here about the data pilot. The example I used there was for analyzing data in your spreadsheet. You also, of course, have the option of analyzing, using the data pilot, any of your data sources. You'd want to analyze a data source rather than a spreadsheet for many reasons-- perhaps the data is in Access and not in a spreadsheet. Or the spreadsheet is just realllllly big and you don't want to have to open it every time you want to run data pilot on it.

 

Here's my sample data for this blog. Just a nice basic set of data for a week. It's similar but different to what I used in the previous blog and more suitable for demoing the different options. Click to see it bigger.

The first step, of course, is to make sure you have a data source. (Or database—it's all kind of the same thing.) If you already have your data in Access or another database, you'd skip this, of course. However, let's say that this spreadsheet is extremely large and I need to make a database out of it to save time.

Here's what you do to create a database from a spreadsheet; using a different type of data is pretty similar.

1. Choose File > New > Database.

2. In the window that appears, select the last radio button, and Spreadsheet as the type. Click Next.

3. In the next window, just specify where the spreadsheet is that you want to turn into a database. Click Next.

4. Unmark the Open for Editing option and click Finish.

5. Save the database with an obvious name. This is the name you'll be selecting from dropdown lists in OpenOffice.org.

Now you've got a database.

 

To analyze a database, start the same way as usual.

1. Open a new empty spreadsheet and choose Data > Data Pilot > Start.

2. Choose to use a data source already registered with OpenOffice.org. Click OK.

3. Select the data source name, then the sheet name (typically Sheet1) where your data is, and specify Sheet as the type of data. Click OK.

4. Now you're ready to drag fields into the layout area and set options as you have with other analyses. This time, I'm using the Date fields as the quick filter field, so I put it in the Page area. The rest is as pictured.

• I double-clicked the Units Sold field and selected Average. In the results, we'll see the average for each book and store, not the total.
• For options, I'm choosing to identify categories, and to sum rows and columns.

<>

 

Click OK.

 

Here are the results of the setup shown above.

---

Data Pilots (Pivot Tables) in OpenOffice Calc

See also this blog post.

Data Pilots are the OpenOffice Calc equivalent of pivot tables.

How do you use them? Very carefully. If you haven't used them before, it's just a bit of a mind-bender to get started--it's all about the data that you start off with. You start off with raw, plain old data, not data that you've already summed up. 

I'm including a lesson here on how to use the data pilot. Download this file, datapilotspreadsheet, if you want to use the same spreadsheet I've used. (Don't worry if your numbers are a little off from the screen shots--I tweaked it a little along the way.)

1. Open a spreadsheet.

2. Click in the upper left corner of the data. Don't select the data; you don't need to.

3. Choose Data > Data Pilot > Start.

4. Choose to use the current selection, then click OK.

5. Here's the window you'll be working with. Just drag the fields where you want them, and click More to have more options. When you have things the way you want them, just click OK.

I'll show you a few examples: the setup, and what the data results look like.

NOTE: Click any of these images to see a bigger version.

Example 1

Here's what the setup looks like.

Click OK, and you get this data.

Click the Filter button and you can filter things out more -- say, just view invoices that are more than $5000,  or whatever you like.

Example 2

Here's the setup. The Store window in the Page Fields area will give you a dropdown list so you can view all values, or one value at a time for the category in the Page Fields area.

Click OK and you get these results, with the option to use the dropdown list.

Select a different value in the dropdown list and you get different results.

Example 3

Here's the setup. As you can see the More section is displayed with different options.

Here's a closer look at those options. I specified that no total for rows or columns should be included, and that the results be put in Sheet2 in the upper left corner.

Here are the results you get with that setup.

Example 4
Here,  I double-clicked the Sum-Amount field to get other function options. I'm going to choose Average for this example

Here are the results.

Double-Clicking a Field
You can see the detail rather than the sum or average if you double-click the name of a field. The following window appears.

Click OK in that window after making a choice and you get the detail. Double-click a detail field to go back to the regular view.

---

A Design Contest! Enhancing the OpenOffice.org Template Coffers

The deadline is October 31st! Still plenty of time to enter!

It's another contest! Anyone with OpenOffice.org and a dream (that's all of us, potentially) can enter.

It's the Worldlabel-sponsored OpenOffice.org Design Competition.

" The OpenOffice.org documentation project has announced a Template & Clipart Contest.

The total prize money is to the tune of $5000 sponsored by WorldLabel, which has long been a strong champion of OpenOffice.org.

The goal is to increase the trove of templates and clipart. The current collection of template samples leaves a huge gap that needs to be filled. Step up and fill that gap. Winners will also have the option of including their winning entries in the OpenOffice.org installation sets available from the site.

If you are interested, visit the Documentation Project for more information. Everyone is eligible and everyone is encouraged to participate. The contest officially starts this week and ends 31
October 2006. Winning entries will be judged by a panel of three and winners will be announced."

Enter! I'm definitely going to do it. It's fun, plus with more templates, OpenOffice.org will be even more appealing for those still with Microsoft Office.

---

Another Mystery Solved: Getting Data Into an OpenOffice.org Base Database

---------
Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export
---------

I swear, my shift and matching pumps are getting a little worn out with all this detective work.

I've written an article for TechTarget about how to get data out of one database, such as Access, into an OpenOffice database table. Not exactly obvious, but easy.  You just need to know what to do. (And I'm pleased to say that my mantra, "When in doubt, right-click," does apply here.  ;>  )

(You can of course just type from scratch but when you've got 50,000 records of census data, for instance, that's kind of impractical.)

---

Using the Detective Feature in OpenOffice Calc

I do enjoy having software do work for me.

And a feature that will do that, but which I rarely hear anyone talking about, is the Detective under the Tools menu.

The Detective has many features; I'll go over them in a few different posts. Here's how to get started: tracing the precedents (the ingredients that go into a cell) and the dependents (the cells that look back to a cell as the basis for the calculations).

The Sample Spreadsheet
In this spreadsheet, the expenses for the spring and summer are totaled, and the fall and winter are totaled. We also have a total for the whole year. So the spring and summer depend on the original numbers in the columns, as do the fall and winter totals, and of course the totals at the bottom of the columns.

In addition, there are two calculations that figure out the percentage, which go back two levels.

Click the following picture to see a bigger version; this is definitely too small to see reasonably.

Precedents
I want to see what cells are involved in the calculation for the Spring and Summer percentage. So I click on that cell, and choose Tools > Detective > Trace Precedents. The blue arrows show me the two cells that are referenced in the cell.

Dependents
Let's say I'm thinking of changing the total for the Fall and Winter Expenses but I don't want to screw up other calculations. I can see a cell's dependents by selecting the cell and choosing Tools > Detective > Trace Dependents.

Those are two of the most useful basics for the Detective. I'll talk more about the other features in future blogs.

---

A Few Helpful Calc Display and Usage Tips (in a fresh all-new post!)

I'm back! And not with a vengeance, just with a couple Calc display tips that you might find useful.

It's all about investigating what's in there when you choose Tools > Options and start rooting around.

Under Tools > Options > Calc, there's the General category.

There's the Press Enter to Move Selection option, with the corresponding dropdown list. Normally when you press Enter, you move down a cell. However, if you want to move in a different direction, just select it.

Just below that option, there's another option, Press Enter to Switch to Edit Mode.  This is good for non-mouse-types. Normally if you want to type in, rather than select, a cell, you need to double-click in the cell. (Or click in the entry field in the toolbar, of course.) With this option, you just press Enter and you're in edit mode with the cursor, as shown here.

Once you select your options, click OK to save changes and close the window.

Under Tools > Options > Calc, there's also the Edit option.

In the upper right corner, you have the option to show Formulas, rather than the result of the formulas. Mark that option and your spreadsheets will look more like this.

Another option is Value Highlighting. In this case the text is shown in black, numbers in blue and formulas (the results, not the formula) in green.

A third option farther down is Text Overflow. This is on by default, and I like it, but I want to point it out. It's what makes the red triangle show up when you've got too much unwrapped text in a cell, with text in the cell to its right. (If there's nothing in the cell to the right, then the text just flows all the way through. Another approach is to choose Format > Cells, click the Alignment tab, and choose to wrap the text.)

Once you select your options, click OK to save changes and close the window.

Go ahead and explore the other options in these windows, as well; some are pretty obvious and on by default. But some aren't -- and you might find just the option you want to make things a bit easier every day.

 

Making OpenOffice.org Work the Way You Want It (Repost)

 

(Originally posted in October 2005.)

Note: Here's a related article I wrote for TechTarget.com.

What's better, software that does what it thinks you want, or software that does what you tell it to?

If it's rocket-launching software, and I don't know much about rocket-launching, I guess I would let the software do what it wants. However, I know what I darn well want my office suite software to do. I know how I want it to behave. I know what features I want and I know, for instance, that I do not want it suggesting to me what word I am typing and offering to help me finish the arduous task of typing it.

OpenOffice.org has its default behavior and default settings just like any other software but is very cooperative in letting you customize those behavior and settings the way you want them. Which is refreshing. You just have to tell it how to behave.

So I've put together a list of the top  customizations I think are the most helpful and/or powerful. I make sure that everyone in my classes learns these by lunchtime, and review them afterwards. When the software behaves the way you want, that makes everything better.

(By the way, this is a little bit off topic, but I wanted to mention that the scroll graphic at the top of this blog is a drawing shape in OOo 2.0.  I'm a bit of a giggly schoolgirl when it comes to the OOo drawing tools, especially the new 2.0 features.)

1. Turn off the word completion.

I hate word completion, and it’s really easy to turn off. Choose Tools > AutoCorrect and click on the Word Completion tab. Make sure the Enable Word Completion option is unmarked, and click OK.

2. Turn off any automatic formatting that you don’t want.

Would you let people live in your house who you didn’t know? Then you don’t want automatic formatting going on that you don’t understand. Choose Tools > AutoCorrect and click on the Options tab. Unmark everything except the top option, Use Replacement Table. Then go back through and see if you really want anything.

(You can unmark the Use Replacement Table option too....but that table is handy, as you'll see in the next item.)

3. Use the automatic formatting to create handy shortcuts.

The same tab where you turned off word completion has a really great feature for creating shortcuts. Let’s say you type the word supercalfragilisticexpealidocious a zillion times a day, or your name and title, or anything kinda long. You can set up a shortcut for it. It's a much more reliable approach than word completion.

To do this: Choose Tools > AutoCorrect and click on the Replace tab.

A. In the left-hand field type your shortcut like sig and in the right-hand field, type the word you’re tired of typing all the time.

B. Click New, then click OK.

C. Click the Options tab and be sure that both checkboxes for the top item, Use Replacement Table, are marked. That just means "use the stuff in the Replace tab."

D.  In your document, type the shortcut, followed by a space, and your word will appear.

Note: You can also delete anything in the Replace tab that you don't want.

4. Display the icons that you want.

There are a zillion icons in OOo as with any software and you probably don’t use all of them. There’s also that dandy little result of having to click on the black arrow to get to the icons you want, while the ones you do want sit there taking up space and, quite frankly, smiling a bit smugly. So take off the ones you don’t want, leave room for the ones you do want, and add some other ones.

First step is to take off the ones you don’t want. Click on the dropdown arrow and choose Visible Buttons. Find the icons you don’t want, like double spacing, and select them. That’ll remove the checkbox by them, and that removes them from the toolbar.

Now add the icons you want. The first thing to try is to click the dropdown arrow again and choose Visible Buttons. If the icon you want is there, select it and it’ll appear.

If the icon you want isn’t there, click on the dropdown arrow again and instead of Visible Buttons, select Customize Toolbar. Find the toolbar you want to add icons to. Click Add, and in the window that appears just keep looking through the categories on the left til you find the feature you want in the list on the right. Select it and click Add.

 

Back in the customization window, you can leave the icon as is and just click OK, or change the icon by clicking and holding down on the Modify button and choosing Icon.

5. Get to know the choices under Tools > Options.

Choose Tools > Options, and you’ll see the big fat configuration window. Just as the items under Tools > Autocorrect were about default behavior, Tools > Options is about default settings, default values. Anything about the program, from icon size to language settings to where the program looks when you choose File > Open, is set here.

I suggest that you open the OpenOffice.org (or StarOffice as in this illutration) item at the top, then select Paths, and change the values for any paths you use a lot. Change the My Documents item, for instance, to change the default for where OOo tries to save documents. You’ll save a lot of time scrolling around in your Save windows.

To change the path, select it in the window, click Edit, and just point to the new location.

You might also want to expand the StarOffice Write item, select Default Fonts, and choose the ones you prefer.

When you’re done, just click OK.

You can turn off the Save for Autorecovery feature if you want--or increase the save interval
I find this feature entirely un-annoying, but you can turn it off easily if you want. Autorecovery means if OpenOffice.org crashes or you have to coldboot your machine, at least you'll have something recent to return to. If you're hypervigilant, increase the interval to every minute or 5; if you don't care about it, set it to every hour or just turn it off.

Choose Tools > Options > OpenOffice.org > Load/Save > General. You're looking for the Save AutoRecovery Information Every  option. Unmark it, or change the interval.

Click the thumbnail below to see a bigger image of the window.

Now OOo is more like a well-behaved pet and less likely to jump up at you, licking and biting inappropriately.

Those are not all the configuration steps you can do, by a long shot. But I like them, and students seem to like them. And they're an important set of steps in the general process of showing that OpenOffice.org does what you tell it to do.

---

Times Tables Quiz Sheet in OpenOffice Calc: The File, and How to Do It (Repost)

(First posted February 2006)

For one of the exercises in my training materials, I put together an exercise using the random number function. It's a times table document that regenerates itself every time you open it.

Click this image to see a larger version. The first number in each multiplication is fixed, and the second is random.

Here's the file if you just want to download it.

I did it by entering 1 - 12 in three columns. I just typed 1 in the top cell, then dragged the small lower-right-corner of the cell down to create a series of 1-12.

I did the same with the x for the multiplication sign.

And I put this formula in each cell where I wanted a random number between 1 and 12 to come up.
=RANDBETWEEN(1;12)

The document regenerates with different multiplication questions in different order each time you open it, so students can't remember which answers were where. Print the document to PDF if you want to preserve it in a particular way.

For other ideas, see Plugged In Home School for a tip on making a word find puzzle.

---

---

Please Step Back Five Feet: Controlling What People Put in Spreadsheets With OpenOffice.org Calc Help and Error Message Tools (Repost)

First published March 2006

I don't know if you remember in the 90s, the thing on the Mac that let you define all sorts of wacky error messages on your friends' computers. “The radiation shield on this Macintosh has failed. Please step back five feet” was the default, I believe.

Well, it might not have the same impact, but you can do the same kind of thing in OpenOffice.org Calc spreadsheets.

Pranks of course are great but you can actually use this Calc feature for good, useful, pure and high-minded purposes as well. You can do online help, error messages, data entry lists, restrict the maximum characters in the field—and so much more.

Let's say you're in charge of creating all the forms people fill in. Obviously, sometimes they fill them in wrong. You can build in business logic, i.e. you can set up the spreadsheet so they can't fill it in wrong. Or you can at least make it a lot harder.

Window Walkthrough

Select one of the cells in your spreadsheet and choose Tools > Data Validity ( in 2.0.1 and earlier) or Data > Validity (in 2.0.2 and later). Take a look at the first tab, Critera. You can restrict entry in that cell to whatever is listed here.

Then to enforce what you specify here, go to the Error Alert tab and set up your error message.

If instead of controlling what's entered, you just want to provide guidance, then it's time for input help.  Select the cell or cells, choose Tools > Data Validity ( in 2.0.1 and earlier) or Data > Validity (in 2.0.2 and later),  then go to the middle tab, Input Help, and enter some information help that's displayed when users select the cell.

Sample Procedure Using Criteria, Input Help, and Error Messages

Here's what I did with a sample spreadsheet.

In this sample, customer service representatives have a simple form they need to use to enter customer service Incidents.

In the date field, I've observed that people enter the current date rather than the date when the customer service indicident occurred. So I'm going to offer a little gentle help for what to put in the date field. I selected the Event Date field where the date goes, chose Tools > Data Validity ( in 2.0.1 and earlier) or Data > Validity (in 2.0.2 and later), and clicked the Input Help tab. I typed in some information for users to see, and clicked OK.

Here's what that help looks like when someone selects the field.

The next field, Customer Type, is easy since there are only three types of customers. The customer service reps don't always remember this correctly, though, or they like to make up their own types. Therefore I decided to create a data entry list. I selected the field where the customer type goes, chose Tools > Data Validity ( in 2.0.1 and earlier) or Data > Validity (in 2.0.2 and later), and clicked the first tab, Criteria. I selected List, then just typed the items in the list, pressing Return after each. Then I just clicked OK.

Here's what the list looks like in the spreadsheet (sorted alphabetically).

The Description field, which is several cells merged together, is of course for the description. I want a short description, though, and the customer service reps go ON and ON and ON..... So I'm limiting the description to 100 characters. I selected the field where the description goes, chose Tools > Data Validity ( in 2.0.1 and earlier) or Data > Validity (in 2.0.2 and later), and clicked the first tab, Criteria. I made the indicated selections to accept a maximum number of 100 characters.

Then I clicked the third tab, Error Alert, to enforce what I've just done, and created a Stop error message. I typed the title and the message, and clicked OK.

Here's what the error message looks like when people type in too much text. It appears after users tab off the cell.

Here's what the whole spreadsheet looks like now.

That's about it. This is a pretty powerful and flexible feature; I haven't shown you much of the Criteria tab at all, so go ahead and take a look at that a little more if you find this feature interesting. With some imagination you can come up with a lot more applications for it than I have here.

---

Things That Are Hard to Figure Out in OpenOffice: Page Numbers, Different Page Orientations, Watermarks, New Document Formats, and Dragging Cells (Repost)

Note: Here's a related article I wrote for TechTarget.com. First published November 2005.

I get a lot of questions when I train, and just in emails. A lot of them are about things that aren't actually hard to do but they're hard to figure out how to do in the first place. Here's how to do some of them.

Adding a Page Number
Note: See a related post on starting a document with no page number on the first page and page 1 on the second page.

You can do this a number of ways, but this is the quickest.

1. First, make a footer for the page number to appear in. Choose Insert > Footer > Default.
2. Scroll to the bottom of the document; you'll see the footer.
3. Click in the footer. Press Tab if you want the page number in the middle, press again if you want it at the right.
4. Type the word page if you want, followed by a space.
5. Choose Insert > Fields > Page Number.
6. Format the text in the footer the way you want it.

If you want to have no page number on the first page and start with 1 or 2 on the second page, that's a bit more advanced. Stay tuned for the blog on page styles.

(You can do the footer turn-on by choose Format > Page > Footers, too.)

Putting a Portrait Page and a Landscape Page in the Same Document

You absolutely can do this. It just takes a little while. You set up a page style that's horizontal, and one that's vertical, and then you just switch.

Here's a 2.0 document (twopagestyles.odt) that has a vertical page style and a horizontal page style. (You can set them up yourself using styles—Format > Styles and Formatting, then use the help.)

1. Type your content.
2. Choose Format > Styles and Formatting.
3. Click the Page Styles icon at the top of the Styles and Formatting window.
4. Double-click the first page style you want to use, the vertical or the horizontal. (It should probably be vertical, since this page style will apply to everything above your cursor in the document.)
5. Click at the bottom of the page using that page style.
6. Choose Insert > Manual Break.
7. In the Page Style list, select the other page style, such as Horizontal.
8. Click OK.
9. Put the content on that page that you want.
10. Click at the bottom of that page.
11. Choose Insert > Manual Break.
12. In the Page Style list, select the first page style, such as Vertical.
13. Click OK. Now you're back to where you started.

Stay tuned for the blog on page styles, to create page styles yourself from scratch, and to do some gnarly pagination control.

Getting exactly what you want when you choose File > New ____ Document.

The default empty blank text document and spreadsheet are fine but you'd like the margins to be wider, or the font to be different, or for it to have certain styles. It's easy to switch out what comes up under File > New > ____ Document.

1. First, make a new document or spreadsheet. Set up the page the way you want, create or import styles, add footers and page numbers, etc.
2. Then choose File > Templates > Save.
3. Name the document, leave the category My Templates selected, and click OK.
4. Choose File > Templates > Organize.
5. Double-click the My Templates category.
6. Right-click on your template you created previously and choose Set as Default Template.
7. Click OK.

Now that document will come up when you choose File > New > _____ Document (text or spreadsheet).

To go back to the original, just repeat those steps but instead of choosing Set as Default Template, choose Reset Default Template > Text Document or Reset Default Template > Spreadsheet.

Making a Watermark

If you want a graphic or piece of text behind the content of your page, you can approach it a few different ways.

If you want a text-based watermark for your document, like CONFIDENTIAL, behind just a few pages, follow these steps. You'll  need to place the text box on every page where you want it to appear.

1. Click the “T” text icon, or if you don't see one, choose View > Toolbars > Drawing to make it appear.

2. Draw a box with the tool and type what you want inside, like CONFIDENTIAL.

3. Select the text and make it really big, maybe 66 points. You can use the font size dropdown list on the object bar for this.

4. Make the text gray if you want it lighter. Use the Font Color icon on the object bar.

5. Click somewhere else in your document, like a blank spot or some regular text.

6. If you want the text vertical or diagonal, click on the text box, right-click and choose Position and Size, click the Rotation tab, and in the Rotation field type the number of degrees. (You can also click on a point in the Default Settings region.) 55 degrees is good for a diagonal watermark.

7. Click on the text box you just drew, right-click, and choose Wrap > In Background.

8. Drag the text box to reposition it if it's not where you want, make the text larger or smaller, rotate it more or less, and make any other adjustments.

Here's an OpenOffice.org 2.0 document (watermarktemplate.odt  ) you can use with a watermark.

If you want a graphic-based watermark for just a few pages of your document, paste it into your document on each page where you want the graphic, or choose Insert  > Picture > From File. Then  do step 7 from the previous step to wrap the graphic in the background.

To make the graphic lighter, select it. The Picture toolbar should appear but if it doesn't, choose View > Toolbars > Picture. Use the Lightness icon to make the graphic lighter.

To put a graphic in the background of every page of your document (every page with the Default page style, that is, or every page with the page style you modify if you know styles), follow these steps.

1. Be sure you have the graphic, that it's light enough, and that you know where the graphic is.
2. Choose Format > Page.
3. Click the Background tab.
4. From the As dropdown list, select graphic.
5. In the Type area, be sure Position is selected.
6. Click Browse and find the graphic.
7. Click Open.

Dragging a Cell in a Spreadsheet

I get a kick out of how obscure this is. You can select two or more cells and drag them, but you can't drag just one.

Unless you do this.

1. Select the cell.
2. Click and hold down, drag the mouse down one cell, then back up one cell, and release.

Now you can drag the cell wherever you want.

---

Doing Calculations in Tables, in OpenOffice Writer (Repost)

(First posted February 2006)

Doing calculations in tables is a nice feature of OpenOffice.org Writer. You can essentially treat a table like a spreadsheet. With this example, for instance, I can just use spreadsheet-like calculations in the table to get the totals and differences.

The process is a bit different in 2.0 so I'm blogging it here; plus anyone who hasn't used the feature before can see if they like it.

Note: I personally prefer to use spreadsheets, so I would do the calculations in a spreadsheet and then just copy and paste. But for those who like tables, here's how you do it.

This process focuses on summing, with some other options.

First, of course, create the table the way you want it.

Then click in the empty cell where you want a calculation. Choose Table > Formula or press F2 and you'll get the spreadsheet-like toolbar with the formula entry field.

If you know what you want and just want to go right ahead and do it, just type what you want using <> around each cell reference. <F5>, <A1>, etc.

If you want some help from the system, do it this way. Select the cells you want to calculate; you'll get an addition formula by default. This will sum the contents of the selected cell range.

If it's what you want, press Enter or click the green arrow by the formula field. The total will appear.

If it isn't what you want, just retype the formula in the formula field. Be sure to preserve the syntax with the <> around each cell reference. Cell references are the same as Calc, with A1 being the upper left cell of a table and counting across to B and down to 2, and so on. A correct subtraction formula for instance is =<b2>-<c2>

In the following example, I of course want to find the difference, not the sum, of the expenses and income, so I changed the formula.

If you want something more complicated than addition, subtraction, multiplication, and division, click on the formula dropdown list.

To format the cells automatically with dollar signs, etc.. select the cells, right click, and choose Number Format. In the window that appears, select the format you want. Click the following image to see a larger image of the window, if you want.

 

Overall, the table formulas work but they're just a bit twitchy. If you've got something beyond simple math, I suggest doing it in a spreadsheet, then copying and pasting the spreadsheet into Writer.

---

---

Tags: OpenOffice.org  openoffice  OpenOffice.org training  openoffice training  open source  open source training  StarOffice  Linux   tagtest   education  Google  Sun 

Spreadsheet Printing Tips: Hiding, Squishing, Printing Headers, and More (Repost)

(First posted November 2005)

Calc spreadsheet printing can be tricky. Which is too bad, because there are some fairly powerful features. Here are some questions people used to ask me a lot at when I worked at Sun. Between horrified peeks at my under-water stock options, this is what I told them.

My spreadsheet won't print the way I want. What should I do?

There is a powerful  and quite easily accessible tab that lets you do a bunch of stuff with printing. Choose Format > Page and click the Sheet tab. There, you will find many useful settings such as what direction to print in (all the way down then start again, or all the way across then start again), and which items to print (grid, formulas and much more).  Possibly the most useful, however, are the scaling options at the bottom which let you make the whole thing fit onto a specified number of pages or let you scale the whole thing up or down a bit.

 

The Page tab of that window can be useful too with more everyday settings—margins, page size, and centering the cells left-right and top-bottom within the page. Use the Table Alignment option for that last feature.

I really don't want to show McNealy the figures in column G. How do I hide that?

It's not tricky—not this approach to hiding, at any right. Select the whole column by clicking on the letter, then right-click and choose Hide. To show again, select the two columns on both sides, right-click, and choose Show.

No, I mean I want to show them in the spreadsheet while I'm working with them, I just don't want that column to print.

Oh, why didn't you say so? This is simple too. Select whatever cells shouldn't print, and choose Format > Cells. Select the Cell Protection tab. Mark the Hide When Printing option and click OK. (Remember to turn it back on again when you want them to be printed.) However, note that this won't make the cell spaces themselves go away. So hide the heading that goes with the data, and be prepared to answer questions about why that space is empty.

How do I print just specific rows or columns of my spreadsheet?

It's quick and easy, just not obvious.

1. Select the rows or columns that you want printed. It has to be rows or columns, the shape must be rectangular.
2. Then choose Format > Print Ranges > Define.
3. Choose File > Page Preview to be sure you got what you want, if you want to check first.
4. Then just print; only items defined in print range will be printed.

If you made a mistake defining the range, repeat the step with the right range and the new range will replace the old range. Or just select the old range and choose Format > Print Ranges > Remove and start over.

If you need multiple print ranges, it might be best to consider using one of the hiding features.

I need the headings across the top, Budget and Forecast and all that, to print on every page. How do I do that?

In the previous task, the window had fields where you could pick rows to repeat. You just use those fields.

1. Choose Format > Print Ranges > Edit.
2. Click in the Rows to Repeat (or Columns to Repeat field for columns).
3. Just use your mouse to select the rows to repeat.
4. Repeat with the other field if you want both columns and rows to repeat.
5. Click OK.

Here's a picture of the key point. Click to get a bigger graphic.

 

Choose File > Page Preview to check if you want to verify before printing.

---

---

Tags

OpenOffice.org open office OpenOffice.org training open office training open source open source training Office 12 Linux Ubuntu education Microsoft Office migration spreadsheets printing 

---

The Immense Potential of the IF Function in OpenOffice Calc

The word IF has so much potential.

If I win the lottery...if I lose 20 pounds by next Friday...if only I had gone to France that summer...if blind monkeys from hell fly out of my butt, I will certainly attend that concert with you...

IF is a powerful word.

It's also a powerful function in OpenOffice.org. You can use IF to control the content of a cell depending on the content of another cell, on the result of a calculation—and that means the possibilities are limitless.

Syntax

First, what's the syntax?

 

So you could have this, if you needed to have different values depending on whether the value in cell B5 is bigger than the value in cell B6. Note that the test and both results can all be plain values, can be cell references, or can be calculations.

 

Or you could do this, in case you just want to print something in a cell depending on whether the contents of cell B5 are over a particular value.

How to Use IF

Click in the cell where you want the variable results to show up.

 

Then you can just type the formula.

IF (B5 > B6;G10;G10/G2)

Or you can click the Formula icon.

 

Type I in the list to go down to the IF function, double-click IF, and type the values you want in the window. You'll see the result in the window. When you're done, click OK.

Example

Here's an example. I've got this spreadsheet. I want to always have the right recommendation about what to do in the Recommendation field, and the calculations can change anytime. Also, the calculations might bring in cells from other sheets as well, so it might not always be as simple.

1. Click in the cell where the results should show up.

 

2. Click the Function icon.

 

3. Find IF (they're in alphabetical order).

 

4. Double-click IF.

 

5. Type the test (whether the consulting revenue is bigger than writing revenue)

 

6. Type what I want to have in this cell if that's true, with quotes since it's text

 

7. Type what I want to have in this cell if that's false, with quotes since it's text. Click this image to see it bigger.

 

8. Click OK.

 

And here are the results.

 

When I double-click the cell I can see the formula bigger. (Click this image to see it full size.)

 

I've shown some simple examples, but when you think about it, IF can be used effectively in far more powerful situations....IF you think about how to apply it the next time you bring up a spreadsheet.

 

---

Pasting OpenOffice Calc Spreadsheets Into Writer, as Tables

Alexander Kjerulf (http://www.positivesharing.com ) wrote to me with an excellent question.

How do you get a spreadsheet into a Writer document?

The question is interesting, since it's not really that it's hard—there are just so many ways and many possible results. It's kind of like asking “How do you make a really great vinaigrette?” or “What is the best episode of Buffy the Vampire Slayer?” There is no one right answer

Here are some answers, then. My birthday is coming up, so let's say that I'm planning my ideal birthday weekend and I did it in a spreadsheet first.

However, let's say that I've had the brilliant thought that I should leave it lying around where my boyfriend is sure to see it, so that he can help facilitate some of these items. In this case, I would like it to be in a regular document, with a nice heading across the top.

In short, I want to take some content from a spreadsheet, and turn it into a table.  I'm going to paste.  But which of the many pasting approaches is best?

Just Pasting the Spreadsheet Normally

Copy the cells from the spreadsheet that you want, switch to a Writer document, and paste.

You get a thingamajig that is kind of a table. Note the table icons in the toolbar. However, you can't really use them except to do things like wrap text around the table or apply other properties to the table object.

Pasting normally gives you kind of a table object with a table look

Double-click in any of the cells so that you can select the text and the cells and basically do normal things to it. But when you do this, iit magiliciously turns into a spreadsheet, with spreadsheet icons. You don't have the table icons anymore.

Double-clicking a pasted spreadsheet gives you spreadsheet tools and a temporary spreadsheet look

This could be quite useful if the spreadsheet were chock full of numbers. However, since it's just full of Wonderful Birthday Ideas, I don't really need it to act like a spreadsheet.

The quest for the perfect way to paste this into a Writer document continues.

Exploring the Paste Special Options

When you choose Edit > Paste Special, or click and hold down on the Paste icon in the main toolbar, you get extra pasting options.

You probably won't use the metafile or bitmap that much, since they create graphics. And the calc8 option just does exactly the same thing as normal pasting. However, the rest are quite useful.

Using the HTML (HyperText Markup Language) Paste Special Option

As you might guess, using this option pastes an HTML table version of your spreadsheet. This is pretty slick if you are, for instance, creating a web page. And it's a table, so of course you get the table tools. Not just when you click on the table but when you click in the cells or select the text.

Using the DDE Link Paste Special Option

This is a very slick pasting option—it's one of the two I recommend. You get a Writer table version of the spreadsheet content, but it's linked to the original spreadsheet. When the original spreadsheet changes, this copy of it changes, too. Of course, you have full use of the table icons.

Consider this option even if you don't want the link because you retain any border formatting or background shading.

This is also nice since, as you might have noticed, the alignment was corrected. In the spreadsheet the alignment by default was at the bottom of the cell. Now, it's at the top. If this isn't what you want then it's a disadvantage, but it's what I want in this instance.

Using the Unformatted Text Paste Special Option

When it says unformatted, it means unformatted.

However, what you can do with this, if you want, is to re-convert it to a table using the instructions here.

http://openoffice.blogs.com/openoffice/2006/06/converting_text.html

It's a bit of extra work since you can also use the DDE Link or Formatted Text options to get a regular table.

Using the Formatted Text (RTF) Paste Special Option

This is the other one I recommend. You get a plain old table, in normal Writer format, that's not linked to the original spreadsheet. With the table icons. However, you do lose any formatting associated with the borders or background shading that you might have had in the spreadsheet.

---

TechTarget Article: Google Spreadsheets Review

I've written an article for TechTarget on the new Google spreadsheets. Here's part of it.

"My first reaction was, "Oh, cute," and "Looks like a good design." It seems like the GUI designers stood up inside the box and looked at what was outside. The design is not revolutionary, but it avoids, at the least, a retread of the client-based spreadsheet GUI. The designers used different types of widgets to provide the navigation and options. There are three tabs for different types of functions: drop down buttons for key features like Saving, buttons for standard toolbar features (Cut, Copy and Paste) and a few plain old links for common features like New and Open."

Essentially, I think it's great for some, and others will never take a second look. Click here to read the whole review.

---

AutoFormats in OpenOffice Writer (Also Works in OpenOffice Calc Spreadsheets)

It's time for autoformats. Autoformats are going to save you SO much time, if you do tables with even vaguely complex formatting. Autoformats are like styles for tables -- they capture all the complicated border and shade formatting, "freeze" it under a name like Gray and Red Table, and can be applied easily to new tables.

This is part 3 of the Table series ( here's the first post and here's the second post). Last time, we talked about how, while there are many formatting options for tables and a lot of control, it's a lot of work to do that formatting. And in a large document with 20 or even 2000 tables, that turns into a ginormous amount of work.

Let's say you've got this table, and you need it formatted this way. And you need the other 147 tables in the document formatted this way, too.

All you have to do is:

1. Get the table formatted how you want—including fonts, number formatting (right-click on a number in a cell and choose Number Format), etc. AutoFormats preserve not just formatting characteristics but also fonts.

2. Turn it into an Autoformat

3. Apply that Autoformat to other tables

I've already done step 1, formatting the table.

Let's do step 2. Here's how to make an Autoformat.

Select the formatted table and choose Table > Autoformat.

In the window that appears, click Add. (You might see more autoformats than this.)

In that window, type the name of the autoformat, as descriptively as possible. Click OK.

The autoformat appears. Click OK.

Now we're on step 3.  You have another table that you need to apply the formatting to.

Select it and choose Table > Autoformat.

Select the autoformat you want.

Click More – you can choose whether to include the other formatting shown, when you apply the autoformat.

Click OK.

You'll see the autoformat applied—I'm showing the original and the newly autoformatted tables together. Click to see the illustration larger.

Note that the outer border format is applied to the inside of one column here, since this table has one more column than the table the autoformat was based on. It's a good idea to test and tweak a little bit. If you want to do outer and inner borders differently, make your first table with three or more columns, so that the formatting applies correctly to all tables.

See how simple that was, though, overall? Reapplying the formatting is SO much faster with Autoformats. You can spend vast amounts of time applying formatting to tables manually; autoformats get rid of all that work. Also, unlike styles, autoformats aren't just available by default in the document where you created them. They're available in any document you create. 

---

Table Formatting in OpenOffice Writer (Works for Calc Spreadsheet Cells, Too)

 

This is part 2 of the Table series (here's the first post). In this post, I'll show you how to make your tables look exactly the way you want them with border colors, border styles, border placement, and shading. (The first post covered how to control column width; other posts will cover more complex items like vertical spacing, headings, captions, and other advanced table stuff.)

Let's say you've got this fabulous table full of very important information.

It's nice content, but this is, after all, for a glossy brochure on the candidates for mayor, so you want to make it look a little fancier. The candidates, especially that snob Stephanie, would be insulted if you sent this out as is.

So you'll need to make the borders and shading fancier. Bring up the Tables toolbar (View > Toolbars > Table). Dock it and drag it to the top of the work area to dock it.

Click and hold down on the downward-facing black triangle at the far right end of the toolbar and make sure you've got all four of the formatting icons: Line style, line color, borders, and backgrounds. If no check mark appears next to one of them, select it and it'll be added to the toolbar.

The first thing you do is to specify where the borders should be. You can put the borders on just horizontal lines, on horizontal and vertical (as is), on just the top and bottom of the table, etc. You do this first because if you do the formatting, then change where the borders go, all your previous formatting might go away. (Later I'll talk about AutoFormats which will solve that problem, but that's a topic for another blog.)

So select the table. Let's say that you want to put the borders only on the horizontal lines, not vertically You'd think that you'd click and hold down on the Borders icon and choose horizontal, as shown.

That's logical but that's not quite the way it works. You need to clear the border positions first by saying you don't want any borders anywhere. You can't just switch from one to another directly. So first, click on the No Borders At All icon in the upper right corner, as shown.

Now select the horizontal-only borders icon, or whatever icon you like in order to apply the border placement you want. The formatting will be applied.

Now, format up a storm. In addition to border placement, you can change:

• Border style -- Double lines, thin lines ,thick lines, etc.
• Border color -- And keep in mind that if you reallllly want to go nuts, you can make your own colors.
• Background shading -- A background color for the heading row is one nice effect. Alternating shading can be very effective for making long tables easier to read. Be sure that you change the font color as necessary to make sure the table is legible.

 

Border Style
Select the table, select the border style icon, and pick something.

Border Color
Select the table, select the border color icon, and pick something.

Background Shading
Select the table, select the background shading icon, and pick something.

Removing a Color
To remove border color or background shading formatting, choose the No Fill option.

Here's your table. (It's pretty horrible in color, of course, but it shows some of the effects.)

 

Now, you might also want to just apply formatting to one row. For instance, you can select the heading row, apply a heavier border style and different border color and shading color. Maybe you just have shading on the heading row and nowwhere else. The basic message here is, select the column or row to apply formatting to; you don't have to apply the same formatting to the entire table.

Here's the big cahuna, which I'm just going to touch on lightly since it's really just about whatever you want to do with it. Let's say you want some really specific formatting in different parts of the table. Select the table, choose Table > Table Properties, and click the Borders tab.

As when you used the toolbar icons, you first select WHERE you want the formatting. Use the prefab icons or click on a line in the User-Defined area. To deselect a line, click on it again or click somewhere else.

Then specify the formatting such as line style, color, etc.

Set other options such as distance from text, shadow, etc.

Click OK.

All That Work to Format Just One Table?????

This was a lot of work, wasn't it?

Doing it once is a lot of work. Doing it 40 times is hard labor.

Wouldn't it be nice if there were something like styles, but for tables, that would preserve the formatting for a table and allow you to reapply it to any other table?

Hmmm.... ;>

Tune in next time.

---

TechTarget Article: Using OpenOffice Calc Spreadsheet Scenarios With Different Variables

I've written an article on how to use the Scenario feature in OpenOffice.org or StarOffice Calc.

http://searchopensource.techtarget.com/tip/1,289483,sid39_gci1191742,00.html

This is what scenarios look like.

Here's the intro, i.e. what scenarios are and why you'd use them. They're pretty slick.

I've been there. We've all been there. You're sitting there in the third hour of a boring meeting, nibbling on the last doughnut and trying to figure out what your income and expenses would have to be to move to a small island in British Columbia and support yourself with your macrame skills. Or you're giving a presentation to the director and a couple veeps and one of them just asked you, "OK, I like those numbers, but what if we sold the routers for $20,000 and outsourced all the work to Budapest?"

The common thread here is multiple scenarios. You want to see the effect of different values for the items that affect your bottom line: gross income, expenses, macrame yarn costs (buying in bulk saves you a lot of money), etc.

You can copy and paste, retyping the values each time, or you can tell your director that you'll, um, get back to her this afternoon. But the clever, slick, timesaving approach is to use the OpenOffice.org Calc scenarios feature.

Using the OpenOffice Calc Subtotal Feature

You have many ways to add up figures in spreadsheets. The Quick Sum feature on the toolbar, for instance. Click below or to the right of a column or row of numbers, and click the Quick Sum icon.

Then the proposed total appears, and you can just press Enter or click the green checkmark icon if you want it.

However, sometimes things are a little more complicated? What if you had something like this, with groups of figures you want to subtotal? You'd want subtotals as well as a total. If you put in the subtotals with the Quick Sum feature, then you'd have to make sure that your Total figure included only the original cells, not the subtotal amounts, as well.

A situation like this is when you'd use the Subtotal feature.

Select the data and the headings; selecting the headings makes it easier to choose the data to work with.

Choose Data > Subtotals.

In the Subtotals window, in the first tab, select the columns you want to add up, and be sure that Sum is marked as shown.

If you don't want to add, but do averages or something, you can do that, as well.  Select the function you want in the Use Function list.

Click the Options button and be sure that the Pre-Sort Area According to Groups option is unmarked. Marking it will reorganize the data, and not in a good way.

Click OK.

The subtotals will appear; here are the results with Sum as the function.

Here's an example of the results with Average chosen as the function instead of Sum.

Look at the left-hand border of the spreadsheet; you'll see little + signs and brackets. Click each to determine which of the data you see; you can display everything for each group of data, or just the total.

Two More OpenOffice 2.0 Book Chapters: Calculations and Master Pages

Hi all,

I've had a little more time due to publisher-related stuff, so I'm still refining the book chapters. Here are two, one from Calc and one from Impress. Feel free to read them, use them, or comment on them. As always, when you download  the chapters you agree to not republish it under your own name, not to reuse the content in another context, to credit the book/site if you link to it, not to use the pages in a bizarre sculpture for your MFA final project, etc. ;>

• The Calc calculations chapter
• The Impress master pages chapter

Notes:

The book itself will be out "soon", I promise, from Amazon and other fine booksellers.
I started slow and easy with the calculations chapter, based on what I've seen in some classes. Anyone who already knows how to write a calculation can skip that section.

I'm also looking for a few additional cool formulas to teach in the chapter.  I've got some ideas from www.openofficetips.com but would like input, as well.

Regarding master pages, they're being kind of twitchy in the current release. I have found them to be more reliable in the 2.0 version and am hoping for a return to reliability in 2.0.3.

If you have any feedback or suggestions for other topics, please feel free to post.

Thanks!

TechTarget Article: Using Customization Tools to Ease Migration Problems, and Just Make OpenOffice Easier to Use

I've written an article for TechTarget on the general theme of using the customization tools in OpenOffice.org to make life better for users. Whether they're just using OpenOffice (or StarOffice), or switching from Microsoft Office, you can customize toolbars, menus, and keyboard shortcuts to make life easier.

Here's how it starts--thanks to Louis for the story and to Scott for his pivotal role.

"One of my blog readers reports that, when he migrated his organization to OpenOffice.org, he didn't even tell the users that they were switching to a different office suite. He just said that there was going to be a big upgrade. Then, he and his migration cohorts modified the OpenOffice.org menus and toolbars to resemble the Microsoft Office layout and phrasing as much as possible (without violating copyright, of course) and gave that configured version to the users. He reports very few problems with the migration.

"I love that story. Is the biggest problem with change simply that it is change?"

~~~~~~~~~~~~~~~~~~~~~~

OpenOffice.org openoffice training open source open source training web publishing education Microsoft Office Office 2007 open source burning software  free software 

Two More OpenOffice 2.0 Book Chapters: Creating Good Spreadsheets and Good Presentations

Hi all,

I've had a little more time due to publisher-related stuff, so I'm still refining the book chapters. I've posted a couple today that I'd like additional input on: how to do good spreadsheets, and good presentations.

• Good spreadsheets
• Good presentations

These aren't really OpenOffice.org-specific of course, but I think it's a good idea. Their cohort, the Good Text Document chapter, definitely brings out some things that make text document creation much easier.

So if you have time this week or into the next, let me know if you have additional tips for how to make good spreadsheets and good presentations. I'd like to lean toward the technical side, things specifically related to what you do in the software, but if it's important, let me know regardless.

As always, when you read  the chapters you agree to not republish it under your own name, not to reuse the content in another presentation, to credit the book/site if you distribute it, not to use the pages in your MFA final project, etc. ;>

Thanks!

A Couple Convenient Toolbar Tips for OpenOffice Calc Number Formatting

I tell people in class that the best thing to do is to use the tooltips to figure out what icons are for. Nonetheless, it took me a while to start using a couple nice, simple little icons on the calc formatting toolbar:

• Add Decimal Place
• Delete Decimal Place

Here's the toolbar for number formatting in cells.

The currency, date, and percentage formatting pop out at you relatively easily. The defaults for your language are used: for US English it's $X,XXX.XX, X,XXX.XX%, and MM/DD/YY.

I also like the Standard Number Format icon, which lets you remove the fancier numbering formatting and just say “this is a plain old number.”

Here's where the decimal place icons come in. Let's say you've got some numbers like this.

The number of decimal places for the first column, Items, is uneven, so you'd like to add decimal places to the two middle ones. Just select the cells, and click Add Decimal Place.

 

The cells now have two decimal places.

 

Or look at the percentage numbers—maybe you just want to have no decimal places at all for the percentage points. Select the cells and click Delete Decimal Place.

The decimal places are deleted.

If you change your mind, you can always add again, and the same numbers come back. The names should be more accurately Hide and Show since no deleting is done.

Also note that the totals stay the same, so no rounding is done.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you don't have all the icons pictured in the toolbars, do this. Click and hold down on the black downward-facing arrow at the right side of the Calc formatting toolbar. Choose Visible buttons. A list of items will appear. Anything without a checkmark isn't displayed on the toolbar, but can be. Select any item you want; if it's not showing it will be displayed, and if it is currently on the toolbar (with a checkmark) it will be hidden. You can change the icons back and forth; nothing is deleted or added, just hidden or displayed.

A Few Small but Useful Spreadsheet Printing Tips for OpenOffice Calc

I was fiddling with a very large and ungainly spreadsheet this morning. Just bad setup, period. I was trying to figure out how in the world to keep track of what was supposed to show up in print preview, and what was actually showing up. Here's what helped.

Use the Print Column and Row Headers feature.
Choose Format > Page, click the Sheet tab, and under Print, select Print Column and Row Headers. Here's what the window looks like; click this and any other image if you want to see a bigger version.

Here's what the spreadsheet looks like in Page Preview (File > Page Preview) with that option marked.

Print the Grid for Readability
Another handy option for readability is to print the grid. The option is in the same window; choose Format > Page, click the Sheet tab, and in the Print area, mark the Grid option.

Repeat Column and Row Headings

I've mentioned this before, but it bears repeating. You can repeat columns and/or rows on each page by using the print range feature. It's very handy to have that data repeat. Here's what a spreadsheet looks like without the feature, and with it.

Without repeated columns and rows: page 6 of spreadsheet

With repeated columns and rows: page 6 of spreadsheet

Choose Format > Print Ranges > Edit. Click in the dropdown by the Rows to Repeat and select User-Defined, then click in the field next to it. Then, in the spreadsheet itself,  click on the cells of the rows you want to repeat. Click in A1 to repeat just the first row, for instance; select A1 and A2 to repeat two rows on every page.

Do the same for columns. Click in A1 to repeat one column; select A1 and B1 to repeat two columns, and so on.

Here's what the window would look like if you wanted to repeat two rows and one column on every page.  You can also type, as you see:  $1 for row one; type $1:$2 for rows 1 and 2.  Type $A for column A; type $A:$B for columns A and B, type $A:$D to repeat columns A through D; and so on.

Use the Page Break Preview

This really helped me this morning with the unwieldy spreadsheet. Choose View > Page Break Preview.

This is what a big view of the spreadsheet looks like in this view. Click it to see the screen shot full size.

Here's a more focused view.

If you have blank areas of the spreadsheet displayed, the page numbers might not match what's in page preview or what prints. However, the page breaks will be correct.

When you want to go back to normal view, choose View > Normal.

If you want to insert the page breaks manually, click to insert the break, and choose Insert > Manual Break > Row Break, or Insert > Manual Break > Column Break.

Print Across and Down, or Down Then Across
Remember that with big spreadsheets, you can choose to print two ways:

• Print the first chunk of  rows in their entirety, across all the columns, and then come back to print the next chunk of rows

• Print the first chunk of  columns  in their entirety, across all the rows and then come back to print the next chunk of columns

You can set this by choosing Format > Page, clicking the Sheet tab, and making the appropriate selection at the top of the window.

And Finally: Use the Shortcut Between Page Preview and Page Setup
You will probably be in Page Preview mode a lot (choose File > Page Preview). You'll also spend time in the Page styles window setting various options in the sheet tab. You might think you need to close the page preview mode, then go to the page setup window by choosing Format > Page. You can, but it's much quicker to just click the Page button while you're in Page Preview. That'll take you to the page setup window. Change your options there, then click OK and you'll pop back to the  page preview window, with the changes you just made reflected in the view.

So: Choose File > Page Preview.
And click the Page button below  to go to the page setup window.

Doing Equations, Formulas, Pi, Etc. in OpenOffice Writer and Calc -- And Impress, Updated February 2007

Sooner or later, no matter who you are, you're going to have to talk about pi. Or you're going to need to talk about squaring lambda. (Mmm....squaring lambda.....) Or you'll need to have a+b+c divided by 2.

How do you do that in OpenOffice?

The first step is to just go to the old reliable Insert menu. Anything out of the realm of plain text, just go to the Insert menu.

Just Using the Special Characters Window

Now, if you just want a Special Character, pi or lambda or something, you can choose Insert > Special Character.

Find the one you want. If you select several you'll see them all displayed at the right side of the window and they'll all be inserted.

Then just click Insert. The character will show up.

It's a pain to scroll through all that again and again so make an AutoText entry for it. See

http://openoffice.blogs.com/openoffice/2006/03/automatic_text_.html

Using the Formulas Features

If you need something more complex, then instead, choose Insert > Object > Formula.

You get an editing window at the bottom, a box for the equation in the document, and a little shortcut window floating off to the side.

Now,  you can use the little shorcut window. Click an item above the line, then click an item below the line and that inserts some placeholder stuff for you in the editing window.

   

But frankly I find it not that helpful since just writing the formulas is reasonably easy once you memorize a few tips.

• Use the Formula Reference Tables online help list to see how to enter formulas. Basically, do it how you think it would work. Use the OpenOffice.org Math Examples online  help list to see examples. These are really good. Just press F1 while you're in the editing window; you can type the titles of these topics into the Find or Index window.

• Use ^ for exponents, as in 3^2  which would be three, squared.
• Use sqrt for square root
• Use % in front of the written version of a symbol, as in %pi

So here are a few formulas. They're pretty easy to figure out. Click each image to see a slightly larger version, if you like.

a + b / $pi

a + b over $pi

(a + b) over $pi

If You Don't Know How to Write Out a Character Like %pi or %rho

Click the Sigma icon at the top to add a special character.

You can just scroll  through and select something from the list, and insert it.

Or you can add something yourself if you don't see what you need.

Optional: Add your own symbol   

To add something you don't see, click Edit.

Find what you want, by scrolling and manipulating all the dropdowns. Then name it, and click Add, not Edit.

The new symbol will show up in the symbol list.

Click in the document to stop editing the formula.

If you want to get back into the formula to change it, double-click the box the formula is in.

Formatting the Formula

All right. You've got a great formula. But it's really small. Or you'd like a different font. You change these by selecting the formula in the editing window and click on the Format menu.

Choosing Fonts gives you this window. You get to choose the font by the type: variables, etc. Click and hold down on the Modify button to change any font.

Select the font in this window, then click OK all the way back out of the windows.

That's About It

Insert > Object > Formula. Type what you want and use the online help and the brief tips I gave you.

Click in the document when you're done, and double-click the equation box to start editing again.

To format, select the text in the editing box and find the Format menu.

To add a symbol you don't know, click the Epsilon icon at the top of the window and select one--or click Edit to create your own.

Doing All This in Impress

I'm using 2.1 in February 2007 and it works just fine.

Click in a bulleted item and choose Insert > Object > Formula.

You can also paste the formula object from Writer to Impress. Don't paste it into a bullet in this case; just paste it into a layout with no bullets.

Double-click to edit, as usual.

Double-click the formula object to get into edit mode. Use the Format menu or make other changes.

---

OpenOffice Pan-Galactic Post: Posts on Charts, PDF, Spreadsheets, Templates, Drawings, OpenOffice Training, Toolbars, and Much More

I've been having a problem that some of you might sympathize with--getting posts to show up in Technorati. So as a cheater, I've created this post that links to a bunch of posts that I don't think have been showing up. Not all of them like links to current discussions or issues, just the ones I think are  important that have been missed.

So I'm sorry this isn't new content, but perhaps somewhere in the last six months there's something useful that Technorati didn't let you see the first time around.

Templates, Writer, general setup and toolbars

• Creating and using OpenOffice templates, including converting Microsoft Word templates for use in OpenOffice
• Doing calculations in tables
• The styles posts on what they are, how to get styles from one document to another, how to create and update them, etc.
• The much-discussed topic of centering text vertically in a Writer document
• Starting page numbering on page 2 and related topics (relates to page styles)
• Tables of contents
• Searching and replacing for carriage returns (paragraph breaks), tabs, and other items, using regular expressions (regex)
• Creating your own toolbar
• Setting up OpenOffice under Tools > Options and Tools > Autocorrect. This is how to turn off word completion, stop automatic formatting, set defaults, increase icon size, and much more.

Calc spreadsheets and charts

• Calc spreadsheet data entry shortcuts
• Dragging a cell in a spreadsheet (see the end of the post)
• Charts in Calc and Writer; how to click in just the right spot and modify exactly what you want.
• Opening a CSV text file in Calc so that it opens as a spreadsheet. Useful if you export files from databases of other formats and want to create them as spreadsheets or databases in OpenOffice.
• Controlling what people can put into spreadsheets with error messages, online help for the cell, ranges of permitted data, etc.

Draw, Diagrams, Impress presentations

• Using the 3D shapes in Draw (I love this stuff)
• Using OpenOffice FontWork, which is kind of like WordArt
• Creating your own arrows and other line ends for UML diagrams and other purposes
• Using the OpenOffice Gallery to store graphics; this too is useful for UML diagrams
• Printing Impress presentation handouts

Web publishing and PDF

• What I did with the web tools, creating colors, image maps, etc. to redo my getopenoffice.org StarOffice and OpenOffice Training page
• PDF, with linked articles on PDF presentations, and using hyperlinked PDFs. That post is particularly interesting since you can generate PDFs from linked OpenOffice.org documents, and the links carry over to the PDF.
• Using the wonderful Web Wizard (that's the techtarget article, here's the blog link) for creating web sites from existing OpenOffice, Microsoft, and graphics documents. You can also use it for  PDF batch convert.

Mail merge, labels, envelopes, and databases

• The very very powerful Mail Merge Wizard.
• The less powerful but far easier Secret Mail Merge that you need to add to your toolbar.
• Creating OpenOffice.org databases from spreadsheets, text files, and your address book. Even Access databases are easy.
• A bunch of articles for TechTarget.com on creating databases from scratch in Base,  creating forms, doing reports, views and joins between tables , and all the fun new stuff in the Openoffice Base database module.
• How to do labels the regular way, and manually.

Openoffice training, change management, and general discussions

• My thoughts on training and its benefits.
• My suggestions for transitioning to OpenOffice.org, including the change management aspects.
• Why OpenDocument Format is important (parental advisory, contains references to tube tops).
• Switching to OpenOffice.org, or any open source product, means you can spend the budget leftover on something important. Education, firefighters--anything that's more important than using Microsoft Office.
• Office 2007 could be a big retraining job. Think about whether you'd rather spend money and time upgrading to Office 2007, or just time on switching to OpenOffice.

---

OpenOffice Styles: What, Why, and How to Use Them

March 2006: I've had some trouble getting some of my posts to show up, so I'm reposting a couple of the ones I think are most important.

Styles are like wearing your seatbelt, eating your broccoli, and apologizing to your honey even when you think you're right. They might be a bit of a pain to make yourself do, but they will so save your butt in the long run.

This is just the lead-in to  a big multi-blog post, grouped into a few different ones.

• What are styles?
• Why should everyone absolutely use styles?
• How do you create and modify styles?

---

OpenOffice Spreadsheet Printing Tips

March 2006: I've been having a bit of trouble getting some of my posts listed, so I'm reposting a couple of the ones I think are most important. This one is from back in November '05.

For all spreadsheet posts, see this index page.

Calc spreadsheet printing can be tricky. Which is too bad, because there are some fairly powerful features. Here are some questions people used to ask me a lot at when I worked at Sun. Between horrified peeks at my under-water stock options, this is what I told them.

My spreadsheet won't print the way I want. What should I do?

There is a powerful  and quite easily accessible tab that lets you do a bunch of stuff with printing. Choose Format > Page and click the Sheet tab. There, you will find many useful settings such as what direction to print in (all the way down then start again, or all the way across then start again), and which items to print (grid, formulas and much more).  Possibly the most useful, however, are the scaling options at the bottom which let you make the whole thing fit onto a specified number of pages or let you scale the whole thing up or down a bit.

 

The Page tab of that window can be useful too with more everyday settings—margins, page size, and centering the cells left-right and top-bottom within the page. Use the Table Alignment option for that last feature.

I really don't want to show McNealy the figures in column G. How do I hide that?

It's not tricky—not this approach to hiding, at any right. Select the whole column by clicking on the letter, then right-click and choose Hide. To show again, select the two columns on both sides, right-click, and choose Show.

No, I mean I want to show them in the spreadsheet while I'm working with them, I just don't want that column to print.

Oh, why didn't you say so? This is simple too. Select whatever cells shouldn't print, and choose Format > Cells. Select the Cell Protection tab. Mark the Hide When Printing option and click OK. (Remember to turn it back on again when you want them to be printed.) However, note that this won't make the cell spaces themselves go away. So hide the heading that goes with the data, and be prepared to answer questions about why that space is empty.

How do I print just specific rows or columns of my spreadsheet?

It's quick and easy, just not obvious.

1. Select the rows or columns that you want printed. It has to be rows or columns, the shape must be rectangular.
2. Then choose Format > Print Ranges > Define.
3. Choose File > Page Preview to be sure you got what you want, if you want to check first.
4. Then just print; only items defined in print range will be printed.

If you made a mistake defining the range, repeat the step with the right range and the new range will replace the old range. Or just select the old range and choose Format > Print Ranges > Remove and start over.

If you need multiple print ranges, it might be best to consider using one of the hiding features.

I need the headings across the top, Budget and Forecast and all that, to print on every page. How do I do that?

In the previous task, the window had fields where you could pick rows to repeat. You just use those fields.

1. Choose Format > Print Ranges > Edit.
2. Click in the Rows to Repeat (or Columns to Repeat field for columns).
3. Just use your mouse to select the rows to repeat.
4. Repeat with the other field if you want both columns and rows to repeat.
5. Click OK.

Here's a picture of the key point. Click to get a bigger graphic.

 

Choose File > Page Preview to check if you want to verify before printing.

---

Tags

OpenOffice.org  open office  OpenOffice.org training  open office training  open source  open source training  Office 12  Linux  Ubuntu  education  Microsoft Office  migration spreadsheets  printing  

OpenOffice Templates: Making Them, and Making New Documents Based on Them (Writer, Calc and Impress): Updated March 2006

March 2006: I've added info at the end about how to point to a directory of templates.

This is embarrassing. I have a huge blogapalooza on styles but left out templates. (Updated it yesterday a bit but still, no real thorough coverage.)

I shall remedy that immediately.

All right. Let's say you have the following:

• A bunch of styles you need to have when you create a document.
• Some canned text, like your signature or a graphic logo, that you use in a lot of documents
• Or you're just darned picky about how you want your documents ;>

You are an excellent candidate for using templates. Templates are documents that store styles, text, graphics, general formatting—whatever you want. You create a document, save it as a template in the Template Place, then point to that template when you want to create a new document using what it's got. (“Styles and canned text...that's what it's got....remember that.”)

And it really couldn't be easier.

Making a Template

Open a new document.

Put in it what you want in the template. Styles, graphics, text of every kind and variety. Whatever.

Then choose File > Templates > Save.

Specify My Templates unless you have another idea for a category.

(To create a new category, click the Organizer button, select a category in the button that appears, right-click on it and choose New. An untitled category will appear. IMMEDIATELY start typing the name of your new category. Click somewhere else in the category list when you're done typing. Click Close.)

Type the name of your template like Meeting Minutes.

Click OK.

Now you've got a template.

Using a Template

You need to create a new document using that template.

Choose File > New > Templates and Documents.

Select the Template icon on the left.

Open the category you chose like My Templates. You'll see your template.

Click Open and you'll have a new untitled document based on that template.

The Coolest Part—Making That Template Come Up When You Create Any New Document

Choosing File > New > Templates and Documents is fine but what if that's your template you use all the time? You can make it come up when you just choose File > New > [type of document] by setting it as the default template.

Choose File > Templates > Organize.

Open the category your template is in, in the left side.

Right-click on the template and choose Set as Default Template.

Click Close. You're done! Choose File > New > [type of document] and you'll see the effect.

To switch back to the normal original boring blank document, repeat the steps but this time choose Reset Default Template > [type of document]

Pointing to an Existing Directory of Templates

If you have a bunch of templates, Microsoft or OpenOffice.org, set up OpenOffice.org to know where they are. Then you can get to them as above by choosing File > New > Templates and Documents.

Converting, If You Want to Point to Microsoft Format Templates
First, if they're in Microsoft format, you need to convert them to OpenOffice format. Choose File > Wizards > Document Converter. Follow the wizard through to create a converted set of copies of the templates.

Step 1.

Step 2, then just follow the wizard as prompted.

Pointing to Templates to Use
Now, choose Tools > Options > OpenOffice.org > Paths. Select the Templates line.

Click Edit, then click Add, and add the path to the templates you want to point to. (Click this image to see a larger version if you like.)

Click OK, and OK, and OK  again.

Now you'll see the templates when you choose File > New > Templates and Documents.

---

http://rpc.technorati.com/rpc/ping

TechTarget.com Article: Charts

I've written an article for TechTarget.com about using the OpenOffice.org Writer and Calc chart tools. It basically says, OK, the tools are like trying to find the right candlestick to twist to reveal the secret bookcase, but once you've found the secrets, there are some decent capabilites.

It got slashdotted on Tuesday! Very exciting. Thanks to ScuttleMonkey and Jane Walker for posting it.

Of course, the slashdoterati slammed it six ways from Sunday but no more than the usual slashdot treatment, I think. ;>   Anything with a Nancy Drew intro is going to draw that type of attention in the  slashdot environment. (Though if you've used the chart tools, I think you'll agree that there's a certain validity to the analogy. With, of course, the corresponding "Whoohoo!" feeling when the mystery is solved.)

Let's hear from you on charts!
There's always another way to do charts (or anything), of course. What are your tips for using charts? Especially for some of the hard-core scientific stuff, which I don't do much of (any of...) so I'm not that familiar with what the charts need to do.

What's your favorite chart technique?

---

Tags: OpenOffice.org  open office  OpenOffice.org training  open office training  open source  open source training  StarOffice  Linux   education  database  base 

Times Tables Quiz Sheet in OpenOffice Calc: The File, and How to Do It

For one of the exercises in my training materials, I put together an exercise using the random number function. It's a times table document that regenerates itself every time you open it.

Click this image to see a larger version. The first number in each multiplication is fixed, and the second is random.

Here's the file if you just want to download it.

I did it by entering 1 - 12 in three columns. I just typed 1 in the top cell, then dragged the small lower-right-corner of the cell down to create a series of 1-12.

I did the same with the x for the multiplication sign.

And I put this formula in each cell where I wanted a random number between 1 and 12 to come up.
=RANDBETWEEN(1;12)

The document regenerates with different multiplication questions in different order each time you open it, so students can't remember which answers were where. Print the document to PDF if you want to preserve it in a particular way.

For other ideas, see Plugged In Home School for a tip on making a word find puzzle.

---

hr />

Opening CSV or Text Files as Calc Spreadsheets--and Vice Versa

When I worked at Sun, there were a few things people always asked, that were hard to figure out.

- How do you print the same rows or columns on every page of a spreadsheet? (See this post. )

- How do you print handouts for a presentation? (See this post.) 

- How do you get a blank paragraph line above a table that's at the top of a document? (You now simply have to press Return in the upper left cell of the table.)

Another very common question was:

How in the world do you open a perfectly good .txt  or .csv file (comma-separated values) in a spreadsheet? Basically, you've got data in rows and columns, but separated by tabs or commas rather than columns in a spreadsheet, and you want it in a spreadsheet. Comma-delimted files are a common way to get data out of a spreadsheet or database and into another.

The trouble is, in OpenOffice.org, if you just choose File > Open, the .txt or .csv file opens in Writer. Not what you want.

   

 

Here's how to open it in a Calc spreadsheet.

 

Opening a .txt or .csv File in a Calc Spreadsheet

1. Start OpenOffice.org. You can be in Writer, Draw, Calc—it doesn't matter.

2. Choose File > Open. (Click the picture to see a larger version of it, if you want.)

3. In the File Type list, select Text CSV. It's about a third of the way down the list of types, or you can click in the file type list and press T four times.

4. The window should now look like this, so just click Open.

5. You'll get a window where you specify how the file was created: what separates the data into columns, etc. (Click the picture to see a larger version, if you want.)

6. Also in the same window, if you have date-format data or other formats you want to specify, you can do that column by column.

7. When you're done, just click OK. You'll see the data in a spreadsheet.

8. You'll want to save it as a spreadsheet at some point. Choose File > Save As and select the standard OpenSpreadsheet .ods format.

9. Click Save.

 

 

 

Saving a Calc Spreadsheets as a .txt or .csv File

1. Let's say it's the other way around—you've got a spreadsheet and you want it to be in a nice neutral .txt or .csv file. Be sure the file is open.

 

2. Choose File > Save As and select the same Text CSV format in the file format list. This time you can click T just once.

3. Click Save.

 

4. You'll probably see this message. Click Yes.

 

 

5. In the next window, specify the type of character you want to separate columns, commas or tabs or another character.

6. Click OK; you're done.

 

---

Tags

OpenOffice.org    open office    OpenOffice.org training    open office training   open source   open source training   Office 12  Linux   comma-separated   text   databases  calc  spreadsheets    csv   

TechTarget.com Article: Connecting to Existing Data Sources Like Spreadsheets

I'm cleaning up my blog's sidebar areas, and categorizing posts. This post is one of the results--linking directly to an article on TechTarget.com.

This is an article on creating an OpenOffice.org datasource that''s actually just a spreadsheet, text file, or address book.

See also the articles on forms in the new OpenOffice.org 2.0 database tool: part 1 and part 2.

See also part 1 and part 2 of an article about creating databases in the new database tool in OpenOffice.org 2.0, and the article on  views in OpenOffice.org 2.0.

---

Tags: OpenOffice, open office, open office, open source, training, openoffice training, openoffice.org training, linux, education, databases

What I'm Learning This Week in OpenOffice Training in Largo, Florida

I always learn something when I teach a StarOffice or OpenOffice class. In Green River, Wyoming two weeks ago, I learned how to redock the slide pane in Impress/Draw (for some versions of the software). Click here to see the post.

I also learned that cleaning the dash on a Subaru can knock your hazards on, and they don't flash so it looks like they're your regular lights. I also learned that Green River Imports is a very fine car-fixing establishment that doesn't laugh at you when you bring in your car for something like that.

This week I'm in Largo, Florida at the City of Largo. The weather is gorgeous. I'll post pictures when I get back. I'm teaching an all-Linux set of classes this week--notable, the City of Largo completely skipped Microsoft Office. Never had it or Windows running for the city. Fabulous.

A few of the things I learned from student questions, or things that students discovered and told me about,  include:

- You can just click somewhere in a table, then choose Table > AutoFormat, to apply an autoformat--you don't need to select the whole table.

- When you're putting spacing between columns under Format > Page, Columns, or for a section too, it won't accept a space between the columns if you type the measurement. You have to use the arrows to put in the spacing.

- I should have seen this earlier but in mail merge documents, you need to specify the printer before you print. In labels or envelopes just click the Options tab and specify the printer. Or in any created document, choose File > Print Properties and select the printer. Then choose File> Print, click Yes to print a form letter, be sure the Printer radio button is selected, and click OK. Used to be, you got a chance after that window to specify the printer. Not anymore.

- The mail merge wizard in 2.0.1 now does let you do manual editing when you set up the contents and layout of the address block, so it's now less annoying.

 

- To sort a data source for mail merges, so that for instance everything prints in order by zip code, here's what you do. Choose File > Open to open the data source, the .odb file. Double-click the table you're basing the mail merge on. In the editing window, click the column you want to sort by, and click one of the sort icons, Sort Ascending or Sort Descending. Then do your mail merge. If you need to then change the sort for another mail merge, just repeat these steps.

- I've been reminded that it's not a bad idea to just export your Impress slides to PDF and run your presentation in Adobe Acrobat. (In OOo, File > Export as PDF.) For anyone looking for an OpenOffice.org Impress viewer application, PDF might be all you need. Granted, it won't run your cool effects but unless you're presenting on something where you need to demonstrate motion, you don't technically need custom animation or slide transitions.

You can of course also export to HTML and to Flash; I haven't played enough with Flash to know how the transitions come over, if at all.

- Hyperlinks transfer over to PDF!!! This did not happen in 1.x. However, in 2.0 OpenOffice.org, any hyperlink in a document such as a hyperlinked table of contents or any link period, transfers to the PDF when you choose File > Export as PDF. This is GREAT.  To create a plain old hyperlink, select some text to link and click the Hyperlink icon on the top toolbar.

- In labels, to go from one frame to another on the keyboard (or to go from one label to another, period), press Esc  Tab  Enter. Intuitive, huh? You can also choose Tools > Customize, click Keyboard, and set up a control key for it.

- Cuban food is yummy. I had a pork with black beans and rice thing the other day for lunch. However, when you carry the bag of Cuban food the wrong way, the black beans leak out of the container, out of the paper bag, and splash all over your light beige suit, when you don't have time to drive back to the hotel to change.  However,  my polyester suit from the fine folks at Target was easy to dab down and get clean with just soap and water. So that was a relief. (I mostly love Target, but will take this opportunity to plead with them to carry tall sizes in pants and jeans.)

 

---

Tags: OpenOffice.org OpenOffice.org, Open Office, StarOffice, StarOffice training, openoffice.org training, openoffice training, education, open source, city government, Largo

What Are Styles? An Overview of Paragraph, Page, Character, and List Styles

Note: This is part of a three-part post on styles. See this post for the intro and links to the other posts.

Styles are recipes, or definitions, or paragraphs, of individual characters, of pages, and of lists. (Frames too but I find them less useful.)

Here are some illustrations.

 

Here are some pictures of the definition window. They look a lot like the formatting windows, but you choose Format > Styles and Formatting, then create styles from the Styles and Formatting window.

Everything starts by choosing Format > Styles and Formatting. You get the Styles and Formatting window.  Click the icon at the top for the kind of style you want: paragraph, character, etc.  Right-click in that window and choose New or Modify to get the editing window you want.

Click each thumbnail if you want a bigger view.

Paragraph style definition window:

Character style definition window:

Page style definition window:

List style definition window:

---

Why Should You Use Styles?

Note: This is part of a three-part post on styles. See this post for the intro and links to the other posts.

Why should you use styles?

Lots and lots of reasons.

Styles Mean You Do the Formatting Once, Then Apply It Quickly Each Additional Time

Styles aren't so necessary for your short scathing memo to your intern, but anything longer and more complex will be a lot easier to format with styles.

Let's say you've got a 100-page white paper on how your company's flotsam server works. You've got these types of things in the document:

• Four levels of headings in various sizes and the same font (Arial)
• Regular body text in 12 point type and Times New Roman font
• Notes in bold, 10-point Arial, indented a half inch from the left
• Warnings bold and italic 10-point Arial, indented a half inch from the left
• Numbered lists with 1 at the top level, A at the 2nd level, and a special purple bullet at the third level

A cover page with no footer, introductory text that should be numbered in roman numerals, main body text with arabic numbers in the footer and the document title in the header, and two pages with big diagrams that need to be landscape (horizontal)

Do you really want to painfully do the formatting of every heading, every bulleted list, ever note and warning, every page, manually each time you have a new one?

You really don't.

With styles, you just do the formatting for each heading and each other formatted element of the document once, and then you just select the text and select the style each time you want to apply the formatting. Instead of selecting the text, clicking the Bold icon, clicking the Italic icon, selecting the font size, selecting the font, indenting the text....and so on for every freakin' individual chunk of differently formatted text.

Styles Mean You Can Update a Long Document's Formatting Easily

Let's say you wrote that 100-page white paper and formatted it manually. There's a layoff and you have a new manager who tells you that your paper needs to conform to the corporate marketing formatting standards. Which are completely different.

If you formatted it manually, you have days of work ahead of you.

If you formatted it with styles, you just need to either import a template from the corporate marketing group and be done with it. That's the best case scenario. If your marketing group isn't that organized, then you just need to update about 15 styles. That's all. When you update the styles, all the text says to itself “Oh, I'm Heading3, and Heading3 has changed. I'd better change too.”

Styles Are Required for a Lot of Essential Features in OpenOffice.org.

Do you want to create a table of contents? Do some fancy formatting in a table of contents or list? Do running headers? Do conditional formatting in a spreadsheet? You're going to need styles.

 

---

Spreadsheet Printing Tips: Hiding, Squishing, Printing Headers, and More

(First posted November 2005)

Calc spreadsheet printing can be tricky. Which is too bad, because there are some fairly powerful features. Here are some questions people used to ask me a lot at when I worked at Sun. Between horrified peeks at my under-water stock options, this is what I told them.

My spreadsheet won't print the way I want. What should I do?

There is a powerful  and quite easily accessible tab that lets you do a bunch of stuff with printing. Choose Format > Page and click the Sheet tab. There, you will find many useful settings such as what direction to print in (all the way down then start again, or all the way across then start again), and which items to print (grid, formulas and much more).  Possibly the most useful, however, are the scaling options at the bottom which let you make the whole thing fit onto a specified number of pages or let you scale the whole thing up or down a bit.

 

The Page tab of that window can be useful too with more everyday settings—margins, page size, and centering the cells left-right and top-bottom within the page. Use the Table Alignment option for that last feature.

I really don't want to show McNealy the figures in column G. How do I hide that?

It's not tricky—not this approach to hiding, at any right. Select the whole column by clicking on the letter, then right-click and choose Hide. To show again, select the two columns on both sides, right-click, and choose Show.

No, I mean I want to show them in the spreadsheet while I'm working with them, I just don't want that column to print.

Oh, why didn't you say so? This is simple too. Select whatever cells shouldn't print, and choose Format > Cells. Select the Cell Protection tab. Mark the Hide When Printing option and click OK. (Remember to turn it back on again when you want them to be printed.) However, note that this won't make the cell spaces themselves go away. So hide the heading that goes with the data, and be prepared to answer questions about why that space is empty.

How do I print just specific rows or columns of my spreadsheet?

It's quick and easy, just not obvious.

1. Select the rows or columns that you want printed. It has to be rows or columns, the shape must be rectangular.
2. Then choose Format > Print Ranges > Define.
3. Choose File > Page Preview to be sure you got what you want, if you want to check first.
4. Then just print; only items defined in print range will be printed.

If you made a mistake defining the range, repeat the step with the right range and the new range will replace the old range. Or just select the old range and choose Format > Print Ranges > Remove and start over.

If you need multiple print ranges, it might be best to consider using one of the hiding features.

I need the headings across the top, Budget and Forecast and all that, to print on every page. How do I do that?

In the previous task, the window had fields where you could pick rows to repeat. You just use those fields.

1. Choose Format > Print Ranges > Edit.
2. Click in the Rows to Repeat (or Columns to Repeat field for columns).
3. Just use your mouse to select the rows to repeat.
4. Repeat with the other field if you want both columns and rows to repeat.
5. Click OK.

Here's a picture of the key point. Click to get a bigger graphic.

 

Choose File > Page Preview to check if you want to verify before printing.

---

---

Tags

OpenOffice.org open office OpenOffice.org training open office training open source open source training Office 12 Linux Ubuntu education Microsoft Office migration spreadsheets printing 

---

Calc Quickies

When I have an advanced class that races through the material and gets done early, hungry for more, this is what I teach them.

Quick Fills: Formulas

If you use Excel you know that if you drag a formula, or just about anything, down with that little handle in the lower right corner, you can get what you want without having to type it in.

Same with OpenOffice.org. If you want to have a formula appear in 45 cells of a column, just drag down.

This illustration shows the whole series of steps; click to get the big picture.

Quick Fills: Things That Are Always in Order

Let's say you need one column that start with 1901 and go through 2005. You could type it in, or, since OpenOffice.org knows about numbers and years and all that, you can let the program do it.

Just type 1901 or whatever the starting year is, and drag down.

You can try this for things that aren't in order--just type for instance Beer, Pizza, Ice Cream, select those cells, and drag.

This also works for months, days, and anything else under Tools > Options > Calc > Sort Lists.

Can you make your own sort lists? Excellent question! Of course you can.

NOTE: In StarOffice 8 and OpenOffice.org 2.0, there is no Tools > Options > Calc. It just doesn't exist. The Help says it's there, and it should be there, but it's not. It does work in 1.x and StarOffice 7. So keep looking for it, and try it in 1.x or 7.0.

Making Your Own Sort Lists That Work With Dragging and Sorting

SEE NOTE ABOVE.

These are great. If you have a group of people or departments who need to be listed in a certain order, not alphabetic, or if you just want a quick shortcut for typing, use sort lists.

1. Choose Tools > Options > Calc > Sort Lists.
2. Type the items you want in the order you want them.
3. Click Add.
4. Click OK.

Now you can type the first item in the last and drag down or across in Calc to get the whole list, or use them when you sort.

Complex Numeric Fills

If you want to fill a column or row with something complex like a specific increment or a geometric increment, you'll want to use the fill window.

1. Select the range of cells you want the fill to be in.
2. Choose Edit > Fill > Series.
3. Select the values you want, like the starting value and a specific increment of 7.25.
4. Select the options you want, like Linear or Growth.
5. Click OK.

Selecting From a List

Let's say you're writing up a budget with lots of different rows, but the value in the Department column is always going to be Research and Development, Technical Publications, or Marketing and Advertising. And not in a predictable order so that sort lists are an option. You could type it all repeatedly, you could copy and paste, or you could use the lookup list option.

1. Click in the first cell in the column where you have repeated values.
2. Type Ctrl + D.
3. Select the item you want.