OpenOffice.org Training, Tips, and Ideas

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.

---

Top Ten Reasons to Start a Healthy New Relationship With OpenOffice.org (Repost)

(Originally posted in October 2005.)

I'm not a raving crazy open source OpenOffice fanatic. Not quite, anyway.

I do like it quite a bit, though.  I've used it for six years, about the same amount of time I've used Word and Framemaker. I've trained people on it for the last three years and have written about it for five years.

So I know about the limitations, the cool stuff, the weird stuff, and the normal stuff.

And I think it's a far better choice then Microsoft Word.

(It's not always a better choice than Framemaker, but it was never meant to be. It's far more similar to Word. See my blog on whether OpenOffice.org is a good tool for techwriters and other book publishing professionals.)

I want anyone who's frustrated with Microsoft Office, who doesn't have the money for Microsoft Office, or who just wants the features of OpenOffice.org, to understand everything the program has to offer. With Sun in charge of marketing the program, the great aspects of the program are far less well known than they should be, and so many people are out there cursing the random bulleting in Word when they could be writing documents with a program that does exactly what they ask it to. (Always refreshing.)

So here are ten reasons for anyone out there to use OpenOffice.org. They're not presented in any particular order.

Price, Price, Price

You can read all the reports from Microsoft that you want but that doesn't change the fact that OpenOffice.org is absolutely free. All updates are free. Always will be. If you personally on your own computer decide to download OpenOffice.org for free instead of buying each new version of Microsoft Office, you're spending absolutely no money. If your department switches to OpenOffice.org at work, you're spending absolutely no money.

Add Linux to the mix and you're spending maybe $100, depending on your distro.

Microsoft Office costs more. Windows costs more.

If you purchase books and training, you're spending a flat one-time amount, and it's not going to be $500 a person unless you're buying some serious one-on-one tutoring. I trained 300 people for a client in Houston over three weeks, with each person attending two day-long classes, for a total cost to my client of $15,000. That's $50 a person. That's a good price.

Document conversion can take time and money too, but that's a one-time fee, too. And with the enhancements in OpenOffice.org to the compatibility with Microsoft Office ( http://www.openoffice.org/dev_docs/features/2.0/index.html), converting your Microsoft Office documents to OpenOffice.org is even easier.

And frankly, there's a bunch of learning that users need to do with each new version of Microsoft Office. That takes training money, or else users never get the training and might not learn the new version very well.

There Are More Important Things to Spend Money on the Office Suite Software

Think about the most important things in life.

Health. Love. Family. A roof over your head. Education. World peace.

The right or privileg of using office suite software you're familiar with, or even just having everyone using the same software, is not on the list. I challenge anyone out there to rank using Microsoft Office as one of the most important things in life.

Which leads one naturally to think about schools. Police stations. Libraries. City government. State government. Federal government. What do they spend money on? Some fairly important stuff. Much of the stuff from our earlier list. They pay teachers' salaries, police salaries, allocate money for healthcare, and a zillion other things. And of course, most of them use Microsoft Office. Not for free. Take all the money they spend on Microsoft Office, replace it with OpenOffice.org, and that frees up a lot of money for important things. I'm not sure how much but I don't think I'm out of line in saying hundreds of millions of dollars a year.

Runs on Windows, Linux, Solaris, Mac, OS/2 and Other Operating Systems

You're not stuck with Windows when you use OpenOffice.org. If you're happy with Windows, then you can stay with Windows. If you're all about Linux, or Mac, or OS/2, or Solaris, then OpenOffice.org works the same way on those operating systems, too. Which makes sense, since your operating system shouldn't have so much control over what software you use.

Learning a New Program Isn't as Difficult as You Might Think

Think about writing a text document.

What do you do?

You type. You make things bold. You print.

There are only so many things you can do in a text document, and there aren't that many different ways to do them. The same applies to numbers in spreadsheets. You add, you subtract, you calculate the internal rate of return.

The point is, the differences between office suites aren't all that huge. Take a look at the main text toolbar for Microsoft Word and OpenOffice.org.

Something else I've observed in training is that a lot of people never received training on Microsoft Office in the first place. They aren't working as efficiently or powerfully as they could with the office suite they currently use. So when you switch people to a new office suite like OpenOffice.org and give them a day or two of training, they're going to be working more efficiently with the new office suite than the old one.

OpenOffice.org Is Free, Runs on All Operating Systems, So Everyone Can Use It. No More Digital Divide

With many functions of jobhunting, schools, government, and other essential functions done using the computer, it's very important that everyone be able to complete these functions. When a government agency sends out information or requires submission of information using a Word document, that's unfair and an undue burden on someone who just needs a job or needs to pay taxes. Imagine that FEMA required that all applications for assistance had to be done in Word.

Anything that we are required to do or need to do must be doable without paying $500 for the tool. Especially when something as useful and powerful as OpenOffice.org is freely available now.

OpenOffice.org means equal opportunity. You can read Word, Excel, and Powerpoint files, and create them, as well.

Small File Sizes

This seems kind of trivial, after talking about universal access to required functions. But it's something to think about. OpenOffice.org file sizes are roughly 1/10th the size of their Microsoft Office counterparts. This isn't so much an issue for your own personal computer, but think about network servers, and about email files around. Having smaller file sizes is a tremendous advantage.

Great Drawing Tools

Anyone using Illustrator, or perhaps Photoshop should take a look at OpenOffice.org Draw (File > New > Draw), especially the new 2.0 version. The prefab shapes are amazing, there are precise controls for curve points, you can export to EPS, JPG and more, you can convert any item to a curve, polygon, bitmap, or 3D, there's curved text formatting like in Photoshop...the list goes on and on. I can spend an entire weekend just fiddling happily with Draw.

Take a look at this OpenOffice.org 1.x file; it's all done from scratch within the program. Here's a thumbnail of what's in it.(And by the way, speaking of small file sizes in the previous item, the file size is 14k.)

Download bowloffruit.sxd

Solid Styles for Formatting Text and Page Layout

Styles are how you make your document do what you want it to. Styles are things like the paragraph style Heading1 which is 18 points big, Helvetica, with 12 points of space below it. Or the bulleted list style DiamondBullet that has a black diamond for the bullet character, and has three tenths of an inch from the bullet to the text. Or a page style with a footer centered at the bottom of the document half an inch from the edge, a header at the right side of the top of the document, and a left margin of an inch and a half.

Create or modify the styles you need, apply them to the text as you need, and life will be good.

OpenOffice.org doesn't randomly create new bullet or other styles and apply them to your text, it doesn't take every style you've ever created and infiltrate them into your other documents, and it doesn't get all twitchy with the formatting the way Microsoft Word does. Microsoft Word drives me up the wall. I think it's a sign of the apocalypse that something this uncontrollable is used so widely.

If you've always used Microsoft Word, take a look at OpenOffice.org. Use the styles. (Format > Styles and Formatting.) See how free of cursing and hair-pulling-out life can be.

In OpenOffice.org 2.0, a Nice Presentation Tool

The 1.x version, I must admit, was serviceable but not sexy. The 2.0 version looks a lot like Microsoft Powerpoint, has a zillion effects, and makes everything a lot easier. Check it out if you've used 1.x and been disappointed.

OpenOffice.org Doesn't Randomly Decide to Do Stuff to Your Document

You can see my blog on how to easily configure OpenOffice.org to do what you want it to do, not the default stuff by default.You control it through Tools > Autocorrect.

---

TechTarget Article: Lists, Simple/Easy and Complex/Powerful, in OpenOffice Writer

I've written a nice long article on lists for TechTarget.com.

It covers a bunch of topics:
- simple lists
- using the extreeeemely helpful list toolbar
- what you need to know about styles to use the really powerful list features
- the really powerful list features

Here's a kind of list you can create with the power features.

---

The Microsoft Office 2007 Upgrade Is Going to Have a Big Learning Curve--and a Big Price. Now's the Time to Consider OpenOffice.org Instead.

(Originally published February 2006)

Office 2007 Is Just Another Upgrade...Right?
In a word. No.

This is going to be huge. Painful. Expensive. And that's before you get to the retraining costs.

Here's why.

Microsoft Office 2007 Is a Radical Redesign of the User Interface, and Will Require Plenty of Retraining

Here's the current Microsoft Word toolbar.

Courtesy of LInux Watch  and linked articles, here's a screen shot of MS Word 2003. Click to see a larger version.

MS Office 12 looks entirely different, and changes constantly as you move in the document. Click this image to see a larger version.

And again courtesy of Linux Watch, here's a screen shot of MS Office 2007. Click to see a larger version.

You can't just install this on all 500 computers at your organization and tell people there's a new version.

The Buzz on Microsoft Office 2007 Is the Retraining

Experts around the planet are leary of the radically changed new interface.

Here's an excerpt from an article on eWeek.

Heading the list of challenges facing Microsoft is the fact that Office 2007 has a new user interface, which could require extensive staff retraining at a significant cost, as well as a new file format, which has the potential to create compatibility issues, analysts such as Joe Wilcox of Jupiter Research, told eWEEK.

"When you introduce something new, it disrupts, and this increases things like help desk costs and employee downtime," Wilcox said. "So, to get to the benefits that come with this, they have to get past whatever retraining will be needed around the new user interface and any hardships around the new file format, which are always disruptive. These are two big hurdles Microsoft has to get around."

Enterprise customers such as Robert Rosen, CIO for the National Institute of Arthritis and Musculoskeletal and Skin Diseases and an eWEEK Corporate Partner, agree. The new user interface and file formats pose "major concerns and will slow up adoption significantly," Rosen said. "Since we don't know enough about the benefits of Office 2007, we have not yet developed any plans to move forward."

And another quote:

Jupiter Research's Wilcox told eWEEK that if there were ever an opportunity for StarOffice and OpenOffice.org, "this might be it, going head-to-head against Office 2007, because we have a new file format and a new user interface, which means a lot of extra cost," and which could torpedo many enterprises from upgrading.

In addition, a lot of Software Assurance contracts are expiring between now and the end of July, and Microsoft will be aggressively beating the sales drum. Those businesses might well be looking at their alternatives and options before signing a new contract, he said.

Here's Paul Thurott:

For the first time ever, Microsoft has dramatically changed the Office interface, replacing the standard menu-and-toolbar interface we've known since the earliest Windows applications with a new UI paradigm based on context-sensitive ribbons and tabs."

Here are quotes from Paul's article on the new office software, from Jacobe Jaffe, Group Product Manager on the Office team.

As a personal anecdote, I have a variety of PCs, and on one of those machines for a variety of reasons, I still have Office 2003 installed. I use Office 12 essentially full time, and for me to go back into 2003 is not so good. It's pretty painful, actually."

And Paul's follow-up comment--keeping in mind that he's an expert user.

But because this requires a different skill set to accomplish, I had to relearn how to do this. Long story short, most things are easier, but some power user features will require some more work....I'm nitpicking here, of course. The truth is, the Office 12 interface is so much dramatically better than previous versions, it's hard to find fault with it. On the other hand, I am a power user who uses Office all day long, and I slightly resent having to relearn certain skills. I'll get over it.

Mark Shuttleworth (founder of the Ubuntu project, second space tourist and freedom toaster guy), makes this point, posted by Justin here.

This might not be a direct quote but it's Justin's restatement of Mark's point.

"Office 12 has had substantial UI changes, since Microsoft is trying to distance themselves from the Open Office project. End result, users will require re-training. So which is easier? Re-train users in new Office 12? Or simply, start using Open Office which quite frankly looks just like MS Office today."

Here's another blog along the same lines, Ted's Radio Blog, with a similar conclusion at the end.

"Seven different versions. Dozens of applications, with various features disabled. Nightmarish new licenses. New servers. What a mess! All this to print documents, calculate spreadsheets and do other routine office work? I think Microsoft is overreaching here. They may sell to their captive audience, but new computer users whose machines come with Corel Office or OpenOffice are going to be hard-pressed to find a reason to switch. If you haven't tried OpenOffice.org, there's no better time than the present!"

Think about all those users out there.  Switching, if they all do, is not going to be easy. Take a look at just one part of it. Think about how the people who call you, the IT support folks, are going to react. Think about Laura in accounting or Bob down in contracts.

It All Comes Back to Economics 101

You're happy now with Microsoft Office.

But things change, and the cost can grow to outweigh the benefit.

What if this:


changed to this?

Think About the Cost and Benefit of the Upgrade to Microsoft 2007. Really Think About It.
If you feel that the new UI in this version could change your world and your users' worlds,  there might be a wonderful promised land of fabulous easy of use waiting on the other side of the River of Retraining. There might be. But ya gotta cross the River of Retraining first, and some of your users are going to rock the boat.

I'm not saying you shouldn't use Microsoft Office just because they're changing the interface. I'm saying, you're going to have to retrain people. As long as you have to retrain people, why not consider all your options?

Switching from your current Microsoft software to Office 2007 will require:

• A lot of money for the software
• Training and documentation
• Time and effort to install and convert documents

Switching from your current Microsoft software to OpenOffice.org will require:

• A lot of money for the software
• Training and documentation
• Time and effort to install and convert documents

 

<>

The Microsoft Office 2007 Upgrade is more expensive in money, time, and effort than switching to OpenOffice.org.

This Too: OpenOffice.org and Microsoft Office Currently Look More Similar Than Office 2003 and Office 2007
Training your users to go from Office 2003, or before, to OpenOffice.org, might be easier than teaching them the whole new Office 2007 system.

Here's the current Microsoft Word toolbar, and the toolbar for OpenOffice.org Writer. Right now, they're really similar.  Click each to see a bigger image. See how long it takes to tell which is which.

And here's an OpenOffice.org screen shot. Not that different.

Retraining Aside: Do You Need the 2007 Features?

As a commenter on this blog, George Wenger, states, "The vast majority of users in a so-called "average" business setting already have no use for 90% of the existing features of Word, let alone a whole set of new features."

Your job is to make everyone else's job easier. When you walk by the software users in the hall, you want them to say, "Hey, Jim! I can do that mail merge now!" and maybe offer a high five. 

Does Marsha in Accounting or Bob in Contracts need ribbon toolbars and a new UI paradigm?

How many of the new features does your organization actually need? Have the support staff been begging you for this one?

"We're also enabling a new mobile scenario with OneNote Mobile," Jaffe told me. "So literally you'll be able to have a OneNote notebook available to you on a mobile device, like a Windows Powered Smartphone. You can take notes on your Smartphone, or read your [PC-based notes on the Smartphone. The pages in that Smartphone notebook align to the information you have in your PC version of OneNote. They sync up through ActiveSync."

And think about these questions:
- How many complaints will you get about how everything's different?
- What will the overall attitude be like when users come in Monday morning and their desktops are different?
- How much will you spend on training and documentation to get people up and running on Office 2007.

Here's a blog on this topic: Dave Rosenberg states that Vista gives you an opportunity to really compare the actual cost and the actual benefits, and he quotes Jon Oltsik from Enterprise Strategy Group.

"

Later this year, Microsoft will throw a $500 million PR and advertising party aimed at convincing users to upgrade their PCs to Vista. This provides a perfect opportunity for the Linux crowd to persuade CIOs to evaluate Linux and compare pricing. In this way, Microsoft will likely open the door to some unintended Linux desktop momentum.

I have every expectation that Vista will be a much better OS than XP, but do users really need it? Perhaps. Then again, many CIOs may conclude that the more prudent choice would be a Linux desktop and Open Office migration offering good enough functionality, at 10 percent of Microsoft's price."

Switching to OpenOffice.org Means You Can Have Your IT Cake and Eat at Least Part of It, Too
With OpenOffice.org, you can be the kick-butt IT guy who gives your users software that might even be easier to learn than Office 2007, and saves the company money. The VP's assistant Chris loves you, and the VP loves you. Doesn't get much better than that.

If after careful evaluation of 2007, the alternatives (OpenOffice.org among others), and what is involved in upgrading, you still think MS Office 2007 is the best solution for your users' needs, go ahead and upgrade. (Keep in mind that users do not need to be "cool."  ;>  They do need health insurance.)

But it might not be, and it's important to think about your choices. If your job is to deliver the best product for the best value and have your users be able to do their jobs well, please think about the choices.

References for switching to OpenOffice.org: See this post on the process of migrating a group of users who might be resisting the process, and this post on top ten reasons to switch to OpenOffice.org (besides the one covered in this post).

---

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 

Manually Controlling What's In Your Labels, in OpenOffice Writer (Repost)

(First posted February 2006)

I received this question from Chris in relation to the blog on labels.

“Is there a way to tell OpenOffice to save the document in such a way as that it essentially "exports" it to another OpenOffice document that isn't attached to the data source, so that you can hand-edit the labels? The only way I found to do it was to save it as a Word document and then re-save _that_ as a standard OOo text document. Is there a more direct way?

Thanks,

Chris”

Good question. There are a few different things you can do.

Change the Label Display So You Can See the Data, Then Edit

I don't know of a way to separate the label data from the data source in a sensible way. However, you can just change the content.

Create the labels, connected to the data source, like you normally do. Don't click the Synchronize option in the third tab.

Choose View > Data Sources.

Click the Data to Fields icon in the toolbar above the data sources.

You'll see content in the labels instead of field placeholders.

Now just edit each field normally. Change Bob to Gretchen, Fargo to Kalispell, etc, just by typing.

When you print, you'll get a conglomeration of the database data and what you typed.

Change the Data Source, Not the Labels

If you want to edit the labels, edit where the information comes from, if that's an option. If your data source is a spreadsheet, just edit the contents of the spreadsheet. Be sure that the .odb database file, and any label documents accessing the database, are all closed when you edit the data itself.

Just Make Labels That You Type the Content Into

This approach is entirely manual, but you can copy and paste into the labels. If you want control over what' s in there, just make blank labels and type.

Choose File > New > Labels. Do everything pretty much the same way, i.e. pick the right label type. But don't put any data or fields in the big empty data box in the first tab, and in the third tab don't click Synchronize. Click New Document. (Click this image to see a bigger version if you want.)

Then just type whatever you want in any of the frames.

Quickly Go From One Label to Another

The labels are in frames, which is a bit of a pain. To quickly go from one to another using the keyboard, press Esc Tab  Enter. (Intuitive, huh?) You can also choose Tools > Customize, click Keyboard, and set up a control key for going between frames.

 

---

Tags

OpenOffice.org    open office    OpenOffice.org training    open office training   open source   open source training   Office 12  Linux   Ubuntu   education   Microsoft Office  labels  mail merge    printing  

---

Taking a Summer Vacation

Hi all,

I'm working an extra job this summer, so it seems kind of odd to say I'm taking a vacation. ;>  But in order to work in the big things, I'm going to take a couple weeks' vacation from blogging. This will help me free up time to do some more stuff with my workbook, continue to badger my publisher into actually publishing my completed book, do some training, write articles, and of course occasionally spend some time sitting on the back patio, actually enjoying the summer.

I'll continue to post "classic" blogs that never got into Technorati's search engine so there will be new content for those who didn't see the old blogs.

See you in August!

---

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 

---

A Ray of Hope Regarding My OpenOffice 2 Book

Very important note:  The blogger at http://somethingfishyatbarnesandnobles.blogspot.com/2006/07/last-year-i-was-hired-to-run-28-public.html   is drawing his own conclusions from the reluctance of large bookstore chains to buy copies of my yet-unpublished OpenOffice.org 2.0 book. My only information, and my only conclusions, are in this blog.

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

I just talked to my editor and apparently the salespeople at Barnes and Noble, Borders, etc.  just don't want to sell an OpenOffice book right now. Market's soft, blahblah.

Well, I said, poop on them, or something to that effect, and then my editor suggested that we just sell the book as a PDF, bypassing that whole bricks-and-mortar aspect.

That's the plan, now--I hope--to have a whole PDF of my OpenOffice 2.0 book for sale from this blog, through a site managed by Prentice Hall, by the end of August.

If you feel the urge to needle the bricks-and-mortar bookstores, feel free to go to your local bookstore and order my OpenOffice 2.0 book from them. If there's enough demand, maybe we can get a print version out before the end of the decade, too. Thanks to anyone who does this. ;>

Just ask for the OpenOffice 2.o Resource Kit, ISBN 013148205X

Here's the link to Amazon as a reference.

http://www.amazon.com/gp/product/013148205X

So--there's hope! Not certainty, but hope. And if this doesn't work, I'll try demanding the rights back from the publisher to just publish it myself.

---

Mail Merge OpenOffice Labels, OpenOffice.org 2.0 (Repost)

Here's how to do labels.

First, get your data in a spreadsheet, text file, address book, and create a database. You can do this by choosing File > New > Database. 

This post contains instructions for spreadsheets.

This post contains instructions in step 2 for spreadsheets or text files.

Then, once you've got the database set up in OpenOffice.org, you're ready to go.

1. Choose File > New > Labels. (To do Envelopes, open an OpenOffice.org Writer document, and choose Insert > Envelope.)

2. In the Labels tab of the Labels window, select the database  you created in the Database dropdown list. You're looking for the name of the database you created by choosing File > New > Database, not the spreadheet, addressbook, or text file containing the data.

(Click the picture to see a larger image.)

3. Select a table from the Table list. This will be Sheet1 or whatever the sheet name is, if you are using a spreadsheet to hold your data.

4. Select the first field you want to use from the Database Field list.

5. Click the arrow next to it to insert it in the Label Text field.

Type a space after the field and you can add the next field, such as LastName.

6. Use the Database Field list to insert any other fields you need. If you want fields on the second line, click after the last character of the last field you inserted, in the big text box, and just press Enter. If you need to change the arrangement later in the created label document, you can.

7. In the Brand dropdown list, select Avery Letter Size if you're not using A4.

8. In the Type dropdown list, scroll through the billions of labels. Select the type of label you're using, 8160 Inkjet Address is a good one but just use whatever is on the envelope of labels.

9. This step and step 10 , and step 15, are optional but recommended. Click the Options tab.

10. Select the Synchronize Contents option IF you want to apply formatting, like a different font or colors or adding graphics, and make those changes apply to all of your labels.

11.Click the Setup button next to the printer display.

12. Select the printer you want to print to.

13. In the printer options (this will vary according to your operating system) specify the appropriate paper feed or tray. For now, select Manual Feed.

14. Click the New Document button at the bottom of the window. The labels will appear. This is how it's supposed to look. You won't see the data, you'll see the field names like first name. When you print, the correct data will appear.

15. OPTIONAL: IF YOU SELECTED THE SYNCHRONIZE CONTENTS CHECKBOX

If you need to make changes, like adding spaces, rearranging fields, or changing formatting, do so in the upper left label. Make the text an interesting font, or make it the size you need. You can also right-click on the border of the upper right label, choose Frame, go to the Border tab, and give it a background color.

Then click Synchronize to apply those changes to all labels.

16. Choose File > Print. A message will ask if you want to print a form letter. Click Yes.

17. If you want to print labels for only certain records, you can select them in the scrolling list of records. Select one, press Ctrl, select the next, and so on. Or you can select a range of records like 1-20.

18. If you want to just print all the labels, choose All.

19. When you're ready, just click OK. You'll be prompted again to choose your printer. Print normally.

Note: If you have any problems printing, check your printer setup using your operating system setup tools.

Note: You can save the label document and just go back to it again when you need to use it again.

---

---

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.

 

---

Who Knew "Desperate Housewives" and "Alias" Were Linux Fans?

Karim sent me links to these dandy screen shots. Yes, the glamorous soap opera "Desperate Housewives" uses Linux and OpenOffice.org when they shoot computer screens.

Alias episode 212 with OpenOffice Writer
Click to see it bigger.

Desperate Housewives episode 220 with OpenOffice Calc
Click each to see it bigger.

See also the post on "House", Linux, and OpenOffice.org

---

Go Ben! Energetic coverage of the goal to advertise OpenOffice.org

Ben Horst continues to hit the virtual bricks and get coverage of his effort to make sure everyone knows about OpenOffice.org. Every time I look around, someone's asking me "did you know about this?" Including people not in the OpenOffice world.

Here's the gist.

A group of grassroots activists in the OpenOffice.org community have just announced they are going to undertake a similar media campaign to Spread Firefox, starting with a free (as in beer) New York City daily newspaper called "The Metro," published by Metro International. Most penguinistas know what a huge success the grassroots Spread Firefox ad campaign was. Through ads in the New York Times and the Frankfurt General Newspaper, and the hilarious