OpenOffice.org Training, Tips, and Ideas

Sorting data in OpenOffice.org Calc (repost)

You want to do some good, hard sorting. Not just sorting by the first column, but by perhaps the third column. Maybe you want to sort first by state, then by city, then by last name. For that, you need the Sort window under Data > Sort.

You have your data.  Select all the data to sort, and either select the headings or not. You're good either way.

Choose Data > Sort.

Click the Options tab. You need to tell Calc whether you've got headings selected or not. If you've got headings selected, you want the option shown, "Range Contains Column Labels," to be selected. If you didn't, unmark it.

Now click the Sort Criteria tab. If you selected headings you'll see the headings themselves; if you didn't then you'll just see Column A, etc. Select the column to sort by, and Ascending or Descending.

Click OK.

You get your results. Here, the amounts under the column for the year 2000 are sorted in ascending order.

Now, let's look at a different set of data. You have a lot of people from the same state, and several cities per state. In this case you might want to just sort by last name, but you could also group by location. So you'd sort by state (the broadest category), then city, then alphabetically by last name. Click the image to see more detail.

Select the data, with or without headings, and choose Data > Sort. In the Options tab, be sure to select the Range Contains Column Labels option if you selected headings.

In the Sort Criteria tab, select first State, then City, then Last Name.

Click OK.

You get your results. Here's a closeup of one section, followed by the complete data. Click either to see them closeup.

This window that I've covered in this blog entry is pretty much what you need. If you want to go a little farther and sort by something else, like days of the week in the order they come, not alphabetical order, tune in for the next sorting blog.

---

---

Un-displaying the grid lines in OpenOffice.org Calc

Tired of those pesky gridlines being displayed onscreen? Here's how to turn them off.

Thanks to Marek for pointing this out -- I like to consider myself the queen of "it's under tools > options" but I only looked under the View menu before resorting to hack measures.

Choose Tools > Options > Calc, View. Deselect Grid Lines. You can change the color of the grid lines here, too.

Note that by default, they don't print. To make them print, choose Format > Page, Sheet tab.

---

---

Creating a dropdown list in OpenOffice Calc that references a list of values (repost)

I just started using feature this recently, for my LinuxWorld presentation. It's very nice. Anytime you can reference some text, rather than embedding it in the formatting, it's good.

Some background: under Data > Validity, you can control what people can enter in spreadsheets, and offer them help in the form of lists, help tips, etc.

I wrote about the Validity tools here

http://openoffice.blogs.com/openoffice/2007/01/openofficeorg_c.html

and elsewhere on my blogs. However, what I haven't talked about yet is how to make a dynamic list.

Let's say you've got a spreadsheet documenting the year's performance for the 120 people in your company, one sheet for each person. They're summarizing what they've done this year, and you want them to list the projects they've worked on. You would like to list the Official projects rather than having them type in whatever. So you want a dropdown list. Howeve, you don't want to have those projects typed in that dropdown list because then if you change it, or use this spreadsheet next year, you'll have to correct the list 120 times. (You can copy and paste, but still.)

So you make a list of the projects that references a cell range in a sheet called Projects, say, and all you need to do is update that one cell range to make all the lists update.

Here's how you do it.

1. Click in the cell where you want the list to appear. If you want the list in multiple cells, select multiple cells.

2. Choose Data > Validity.

3. Select Cell Range in the list.

4. Type the range. To type a range in the same sheet , type something like this:

$F$1:$F$20

You need the $ to make the reference absolute.

If the range is in a different sheet, add the absolute sheet reference in front, like this.

$Projects.$F$1:$F$20

5. Click OK.

Then if you need to, just change the contents of the cell range and the list updates. Previous entries in those spreadsheets containing entries no longer in the list do not change.

---

---

Using Names to have a more readable reference to items in formulas

Who doesn't love a formula like this?

=(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))

Now, the math is fairly complex and I just made it up to look extra complex, but just looking at it, you're not sure what it's referring to.

If you used names, which let you give more clear, descriptive names to parts of your spreadsheet, it could look like this.

=(TotalSales*TotalMonths) / ((AVERAGE(2006Sales)) + SUM(SalesBonus))

Ahh.....that's a little clearer. (Since it's a random made-up example, it might not make total sense from a business standpoint, but you're at least in the ballpark now.)

That's all very well, but how to do you add the names?

You can assign any name you want by selecting one or more cells and choosing Insert > Names > Define.

 

Type the name, click Add, and click OK.

Then you can create formulas like this.

Note that you can't do this:
=Ement+Admit

because those names refer to ranges. You can't do  =Ement+Admit because you can't do =B17:B20+E17:E20

You'd need to create more names to refer to just the totals. You'd create another name for the total field where the =SUM(Ement) total is, so that then you can type =TotEment+TotAdmit  (total Entertainment Expenses plus Total Admission Expenses).

 

There's a quicker way to get all your columns named. Let's say you've got this data.

Select all the data and choose Insert > Names > Create. In this case it makes sense to select the checkmarks for top row and left column since that's where the labels are. Then click OK.

To look at the names created, choose Insert > Names > Define.

Now you can create totals, for instance, like this. Note that when you're in a formula and you start to type a name, it will appear in the Formula field as Prepaid_Taxes does here. Press Enter to accept it so you don't have to type the whole name.

As mentioned before, you'll probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, click Add, and click OK.

And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let's say that you've got all these amounts, Bus exp and Salary etc all over on different sheets or farther apart, so you would have to do the + instead. )

and get a result.

Tip: If you want some visual help figuring out where all those names come from, now that you don't have the cell references, you can choose Tools > Detective > Trace Precedents.

---

---

Inserting more than one row at a time in OpenOffice.org Calc

Inserting a row in OpenOffice.org Calc is easy. Let's say you've made this schedule and you need another row  because you need more slots for night life.

Right-click on the row below where you want the new row to appear, and choose Insert Rows.

Bam, you get a new row. Now you have two blank ones so if you decide you need even more new rows, you can select two rows, choose Insert Rows again....

and then you get two more new blank rows.

That's the way I've always done it.

But then I had a brainwave -- you don't have to select blank rows in order to insert rows. That is, if you want to insert three new rows, just select three rows, period, whether they have content in them or not. Then as usual right-click on the row headings and choose Insert Rows.

and you get your three new rows, above the rows that you selected.

I'm not sure how I got stuck on the idea that you have to select blank rows if you want to insert multiple rows at a time. But you don't. So here's the summary.

If you want to insert multiple rows, just select that number of rows, right-click on the row headings, and choose Insert Rows. Doesn't matter if there's content in the rows that you select;  the content won't be affected by the Insert Rows action.

---

---

Any way you want it: OpenOffice.org Calc spreadsheet keyboard shortcuts

I get a lot of requests from people switching office suites who want to know how a feature is done in Microsoft Office, compared to how it works in OpenOffice.org.

It usually ends up that the person wants a list of shortcuts. And I say "but you can have any shortcut you want in OpenOffice.org, it's incredibly configurable." I don't really like to give out lists like that because it gives the impression that it's fixed. If someone wants Ctrl 2 to make text double-spaced and they see it's assigned to the Heading2 paragraph style, they might think there's nothing they can do about it.

All this is kind of a meandering introduction to the following general topics:

You can have virtually any shortcuts you want in OpenOffice.org.

There are a zillion  features you don't know about that you can apply shortcuts to.

It's really easy to do. And once you've got it set to your preferences, you can easily give the file to your friends.

---

You can have virtually any shortcuts you want in OpenOffice.org.
Choose Tools > Customize, and click the Keyboard tab. Those are the current shortcuts. But to change one, just select something in the Category list, something in the Function list, the Keyboard Shortcut of your choice, and click Modify. Bam, you have your new shortcut.  The world is your oyster.

There are a zillion  features you don't know about that you can apply shortcuts to.
Sooooo many features that aren't on the menus or toolbars, and possibly not on the right-click menus, are in the Keyboard tab.

As mentioned, choose Tools > Customize, Keyboard tab.

Select the first Category, and scroll through the Functions. Look at all that. You can set up any of those to use a keyboard shortcut you want, or you might see something you like that has a keyboard shortcut with it already.

The keyboard shortcuts are different for OpenOffice.org in general, and for Writer, for Calc, for Impress, and for Draw.

Here are just a few functions for Calc. The first screen shot shows that it's Ctrl Page Down to go to the next sheet, and Ctrl Page Up to go to the previous sheet. You can use this as is or change it.

Here are just a few functions for Writer

Here are just a few functions for Draw

It's really easy to do. And once you've got it set to your preferences, you can easily give the file to your friends.

All right. You've chosen Tools > Customize. You've clicked the Keyboard tab. You've scrolled through and found the feature for making line spacing doubled. And in the Shortcut Keys list you find Ctrl 2, the shortcut you want to use. It's already occupied by being assigned to Heading 2. That's fine. Just click Modify and it'll switch.



Here's what it looks like now, with Ctrl 2 assigned to double-spacing.



Now let's say you want to share this and any other shortcut configurations you've made with your friends, or all the folks you support in your job as an IT professional.

* Click Save.
* Name the file and click Save.

Then give the file to someone else and tell them to:

* Choose Tools > Customize, Keyboard tab.
* Click Load
* Find the file and click Open

Want to give this post a plug on FSDaily?

---

---

Different page layouts for sheets in the same spreadsheet, in OpenOffice.org Calc

Let's talk spreadsheet page layout.

Doing it simply is simple enough. Choose Format > Page, and make your changes. Margins and orientation in the Page tab; Headers in the Header tab; Borders around the content in the Border tab; etc.

But....what if you've got content in a couple different sheets of your spreadsheet, or more. Let's say that the data in the first sheet is pretty wide so you'd like a landscape layout, and the data in the second sheet is narrow and a different type of information, so you'd like it not just portrait but with a different header. You want different page layout in each sheet of your spreadsheet.

Well, the way I just showed you won't work because the changes apply to the entire spreadsheet.

The reason that the changes apply to the entire spreadsheet is because the Default page style is applied to every sheet in the spreadsheet. (A page style is just a group of formatting attributes associated with a style name.) And when you choose Format > Page, even though you might not be thinking about styles at all, you're modifying the Default page style.

How to get around this? Well, you just need to have one page style on the first sheet, one page style on the second sheet, and so on.

That way when  you modify one page style, you're not affecting the other sheets because the page style is only applied to that one sheet.

All right. Enough description. Here's exactly how to do it.

Step 1: Create page styles

Step 2: Apply a different page style to each sheet

Just so you see the sample, here's my spreadsheet. The first tab has a few pages of sales data; the second sheet, with different and less data, is more of a summary set of data.

Step 1: Create page styles

Choose Format > Styles and Formatting. Click the Page Styles icon at the top.

Right-click in the window and choose New.

Name the style.

Set attributes for the style.

Click OK. The style appears in the Styles and Formatting window.

Now make another style, the same way. Name the style.

Set attributes. (Here's how you'd change content in the header.)

Click OK. The style will appear in the Styles and Formatting window.

Step 2: Apply a different page style to each sheet

This is pretty simple. Click on the tab for the first sheet, and in the Styles and Formatting window, double-click the page style you want to apply.

 

Then click on the tab for the next sheet, and double-click the style for that sheet. If you want to have one style for one sheet, and another style for three other sheets, just keep going, applying the appropriate style each time.

Now you can see in Page Preview that the layouts are different. Choose File > Page Preview.

Here's the layout for one of the pages in sheet1.

And here's a page in sheet2. (I zoomed in a little to show the perhaps-not-that-necessary header but trust me, it's portrait rather than landscape like the other sheet.)

---

---

A nice little Calc spreadsheet function: SUMIF (works in Excel too)

I came across this while working with a spreadsheet that was perhaps a little too fancy to come over from Excel to OpenOffice Calc.

Let's say you keep a spreadsheet full of various numbers, and they fall into about five or six categories. You have a bunch of things that are categorizable as:

business expense; donations; mortgage interest
or
toys; paints and crayons; DVDs; nap supplies
or
music festivals; improv classes; book club purchases; dance lessons
or other categories.

You want to add up all the items that are in one particular category since you want to know how much you're spending on music festivals; how much on climbing related purchases; etc. But the items are entered like this. Not really well categorized.

So here's what you do.

You enter all your information however you want, just make sure you have a column, as shown above, to indicate the category.

Then you have a separate summary section (or placed wherever you want) for the totals. This one shows the totals already there.

How do you get the total amount you're spending on dance in January, and on climbing in April, in there?

You use SUMIF() 

Here's the syntax.

SUMIF(range containing all the categories where you entered the detail; the category you want to add up that's in the summary area such as the cell for March climbing expenses;the column of numbers you want to add up such as the detail section's entire March column)

Here's an example in the spreadsheet, with the colored squares showing the cells or cell ranges being referenced. Note that the month is March for both the result of the calculation and for the range being added; they're just not above each other.

I also used absolute column and cell references by putting a $ in front of whatever column or row should stay the same.

=SUMIF($A$13:$A$22;$A6;E$13:E$22)

This let me drag the cell to create the calculation for every summary cell in the summary section.

If you'd like to play with the spreadsheet, here it is. Right-click on the link and choose to save it to your computer.

---

---

Good information on the OpenOffice.org documentation wiki about regular expressions in Calc and Writer

Calc 
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Calc

Writer
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Writer

---

---

How to do regular expressions in OpenOffice.org Calc functions

I talked in this post about how to use regular expressions in filters.  The key point is that where you would use * in Excel, you use .* in Calc.

Filters aren't the only place where regular expressions come up.  Let's say you want to count the number of people whose last name ends in "son" in a big list of names, range C5:C300. You can use COUNTIF. Here's an example. First you have the range, then the text you want to find in the range. The result is the number of time that text was found in the range.

I hit Return and I get the correct result, 2. (Only 2 because I'm lazy and didn't create 300+ sample names for this blog. ;>  )

Now, the reason that the correct result is shown is that I have this option marked under Tools > Options > OpenOffice.org Calc > Calculate. It's pretty straightforward: "Enable Regular Expressions in Formulas."

If I unmark  that option and click OK, then I get 0 as my result. So if you do regular expressions in formulas, keep it marked.

Note: I've also been told that you should deselect Search Criteria = and <> Must Apply to the Whole Cell but I haven't noticed an effect one way or the other.

Here's some additional guidance for using regular expressions.

Info on the OpenOffice.org wiki

Syntax examples for COUNTIF(), counting cells that meet the specified criteria in the range B2:B35. Use the same symbols for other functions that allow regular expressions.

Note: There aren't any handy dropdown lists in Filter windows so that you can select "Contains" or "Starts with". You need to type out the syntax. However, the following table should help.

 

What you want	What to type in the cell where the count should appear
Count the number of cells that contain only the word Linux	=COUNTIF(B2:B35;"Linux")
Count the number of cells that begin with Linux	=COUNTIF(B2:B35;"Linux.*")
Count the number of cells that are not equal to Linux	=COUNTIF(B2:B35;"<>Linux")
Count the number of cells that do not begin with Linux	=COUNTIF(B2:B35;"<>^Linux.*")
Count the number of cells that do not end with Linux	=COUNTIF(B2:B35;"<>.*Linux$")
Contains Linux	=COUNTIF(B2:B35;".*Linux.*")
Does not contain Linux	=COUNTIF(B2:B35;"<>.*Linux.*")

 

---

---

How to quickly get the cursor down and to the beginning of the next row, in OpenOffice Calc

I posted a blog here about how to modify OpenOffice.org Calc to make the Delete key just delete, and another key like the Backspace key bring up that little delete window.

Here's another little modification. A lot of folks who use Excel are used to pressing Enter or Return to go down one row and all the way to the beginning of the row. If you're on E4 and press Enter, then you are suddenly at F1.

When you press Enter in Calc, however, you just go down one, like from E4 to F4. You can go under Tools > Options > Calc > General and change what the Enter key does, but you can't change it there to doing it the Excel Way.

However, to go to the first cell of the next row almost as quickly in Calc as in Excel, you can press Enter, then Home. On my keyboard they're pretty close together. Enter takes you down one cell, and Home takes you all the way to the left of that row.

---

---

OpenOffice Calc and Writer Chart Users -- This Is for You

This looks pretty fantastic. New features for charting coming in OpenOffice.org 2.4.

http://blogs.sun.com/GullFOSS/entry/new_chart_features_in_openoffice

and more here: http://wiki.services.openoffice.org/wiki/Chart2/Features2.4

To try it out, click here for the announcement and download info.

Here's just one of the new features, quoting from this post.

Equation and Value of R² for Trend Lines

Now, it is possible to display the equation for a trend line (regression curve) next to it. The formula object can be moved around, formatted with a number format, font and graphical formatting.

Alternatively, or in addition, the correlation coefficient R² can be displayed in the formula object.

---

---

Going to a new line in the same OpenOffice Calc cell

In Excel and in Calc, you can create a carriage return, aka line break, to go to a new line within the same cell.

You do this by pressing Ctrl Enter to go to a new line.

However. In Excel you can do this with your cursor in the input field at the top or in the cell. In Calc, your cursor needs to be in the cell.

Also (thanks to a reader for a note on this). One occasional issue. This applies rarely and only if you've got some serious spreadsheet action. But keep in mind that the carriage return will sometimes be interpreted as a space. Do you care? Probably not if you're not running any evaluations of that cell. But keep this bug in mind.

---

---

How to Modify OpenOffice Calc So that the Delete Key Deletes Immediately, and Backspace or Another Key Brings Up the Delete Window

It's the little things, isn't it.

In OpenOffice.org Calc, you have to press Backspace to delete cell contents instead of pressing Delete like in Excel.

Press Delete in Calc, and you get a Delete window that lets you delete formats, text, formulas and/or other elements, a very useful feature. It just might not be quite what you expect.

Some folks note this as one of the issues with OpenOffice, like the Delete key thing.  That blog certainly isn't the only one; I'm just listing it as an example and to....well, just keep reading. I think Greg C's point in the blog is just that the Delete-versus-Backspace is an unnecessary difference from MS Office.

Would it make sense to set up the OpenOffice defaults to be the opposite way around? Sure. Did Novell swap it around in their version? Yes.  Is it still a good idea to consider switching to OpenOffice to save thousands or millions of dollars, even if your users will have to change how they do some tasks? Yes.

But honestly, is having to learn to press a different key all that big a deal? Really?

Besides. Can you change OpenOffice so that when you press Delete, the contents of the cell(s) are just plain deleted, like you expect? Yes.

Either follow these steps, or skip to the end and download the configuration file. Load it according to the instructions at the end.

1. Choose Tools > Customize, Keyboard tab.

2. Be sure that the Calc radio button is selected.

3. In the Shortcut Keys area of the window at the top, select Delete as shown. In the Functions area of the window at the bottom: under Category select Edit, and under Function select the FIRST of the two "Delete Contents" items. This is the one that just deletes, bam, with no window.

4. Click Modify. You'll see Delete now listed as a key.

5. If you don't want Backspace to delete contents, then select Backspace in the Keys list in the lower right corner and click the Delete button.

6. Now scroll through the Shortcut Keys list at the top and find a function you want to use to bring up the Delete window. It can be Backspace, or Ctrl 8, or whatever you want.

Then in the lower part of the window, as before, under Category select Edit and under Function select the SECOND "Delete Contents" item.

7. Click Modify. Your new selection will be the one that brings up the interactive Delete window.

8. If you want to share this configuration with others, click Save and name the file in the window that appears. Here's mine--right-click on that link and choose to save the link target to your computer.

Loading the Configuration File  Give the configuration file to your friends. They should choose Tools > Customize, Keyboard tab, and click Load to load that file. Then they'll be happily pressing Delete, as well.

---

---

How Various AutoFilter Operations Work on Data Not Currently Shown in OpenOffice Calc

I could have sworn I had posted this already, but I think it was just included in a comment on this post.

http://openoffice.blogs.com/openoffice/2007/11/using-the-autof.html

Thanks to Huw for the tips. For information on the sites, see:
http://www.openoffice.org/issues/show_bug.cgi?id=33851
http://wiki.services.openoffice.org/wiki/Calc/Drafts/Issue_33851
http://wiki.services.openoffice.org/wiki/Talk:Calc/Drafts/Issue_33851

 

Let's say you've got a big list of employees: name, address, etc. When you apply the filter and view, for instance, only people from Montana, some of the data isn’t shown. If you then copy, paste, delete, or perform other operations on the data, what happens to the data that isn’t shown? If you delete Artie Anderson from Montana and Cindy Chalmers from Montana, what happens to Betsy Bates from Nevada?

It all depends on the operation. Some, like delete, leave the unshown data alone. Some do affect the unshown data.

Operations that DO affect filtered out rows.

• Cut and Paste

• Move (dragging)

• Fille (Edit > Fill or dragging)

Operations which do NOT affect filtered out rows:

• Copy

• Delete contents

• Delete row

• Format

• Find & Replace in current selection

More About Operations that DO Affect Filtered-Out Rows

Cut and Paste, Versus Copy and Paste

With Cut and Paste, the non-shown data is cut and also pasted.

• When you paste the data outside of the filter range, the whole set of data is pasted and unaffected anymore by the filter.

• When you paste the data inside the filter range, the whole set of data is pasted, and all data is shown at first, even data that shouldn’t show for the current filter selections. However, if you re-apply the filter selections using the dropdown lists in the heading row, then the data is filtered correctly.

Click the following to see a bigger image.

With Copy and Paste, the behavior is different. If you cut, you get the nonshown rows, but if  you copy, you don’t.

Dragging Cells to Move Them

When you move rows (dragging) that include unshown rows, the behavior is the same as cut and paste. The unshown rows between shown rows are moved along with the shown rows.

Fill (Edit > Fill or Dragging the Cell Handle)

Here’s what happens with Fill. Here’s some sample data, and currently everyone is in the same department.

You now look at only people from Colorado.

You change the department for the first person from Colorado, and drag that department down through all the other people from Colorado.

Now, all the nonshown rows after the first row you changed are affected, but not the rows before that.

More About Operations that Don’t Affect Filtered-Out Rows

Copying and pasting, deleting, formatting, and Find and Replace don’t affect unshown data. The following section provides an example.

Deleting

Here’s a walkthrough of deleting rows while the filter is on. Rows that aren’t shown aren’t affected.

Look at the range from row 15, Dan Montbatten, to row 20, Beth Jerlin. Dan and Beth are both from Montana.  In between are Jon, Marcus, and Kyle.

The next illustration shows an AutoFilter with only people from Montana, which includes Dan and Beth but excludes the three rows between.

 

Now delete Dan and Beth.

And they go away. However, when the state autofilter criterion is removed, Jon, Marcus, and Kyle are still there.

 

 

---

---