OpenOffice.org Training, Tips, and Ideas

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

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

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

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

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

Here's how it looks in normal view.

 

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

 

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

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

 

Choose Tools > Scenarios.

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

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

 

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

 

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

Choose Tools  > Scenarios.

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

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

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

 

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

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

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

 

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

 

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

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

 

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

 

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

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

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

---

---

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

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

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

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

Want more detail? See this PDF.

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

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

Solveig Haugland, GetOpenOffice.org

There are three basic steps:

1. Determine the Name and Location of the CSV Files

2. Bring Each CSV File Into a Separate Sheet

3. Combine the Separate Sheets Together in One Sheet

Notes before beginning:

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

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

Determine the Name and Location of the CSV Files

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

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

Bring Each CSV File Into a Separate Sheet

In a new or existing spreadsheet, follow these steps.

1. Choose Insert > Sheet From File.

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

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

4. Click OK.

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

6. Click OK.
   

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

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

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

Combine the Separate Sheets Together in One Sheet

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

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

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

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

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

5. Copy that cell.

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

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

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

---

---

Keyboard shortcuts for keyboard navigation, in OpenOffice Writer and Calc

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

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

Printing a selected print range in OpenOffice Calc spreadsheets

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

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

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

Here's how.

How to name a range
Select the range.

Choose Insert > Names > Define.

Name the range.

Click Add.

Click OK.

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

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

Select the range name from the list.

The range is automatically selected.

Choose File > Print.

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

---

---

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

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

Select the column and choose Data > Text to Columns.

This is what the window looks like by default.

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

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

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

Phone number data:

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

Click OK and you get this.

 

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

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

---

---

Using SUMIF() and IF() functions together to conditionally add different numbers

SUMIF() is great for adding different groups of numbers. See http://openoffice.blogs.com/openoffice/2009/08/using-sumif-in-openoffice-calc-spreadsheets-.html for more info on that function.

But let's say you want to add up one set of numbers in one case, and another if something else is true.

You can use IF to put together two SUMIFs. Here's a basic example.

=IF(K2="PHP";SUMIF(G2:H9;G2;H2:H9);SUMIF(G2:H9;G5;H2:H9))

Let's say you've got two developers at your web site development company. One of them specializes in PHP; the other in JavaScript and Wordpress. You want to list the revenue for projects you've done, but you want to list only the ones for PHP, if you're putting together a bid for a PHP project; otherwise you'll list the revenue for JavaScript and Wordpress.

You're putting together a template. Here's the example.

So you put together a function, IF K2 is PHP, then sum up the revenue for Bob; otherwise sum up the revenue for James.

 

Here's one with lines from the formula.

So you get this figure if it's PHP

 

And this if it's anything else.

---

---

Using SUMIF() in OpenOffice Calc spreadsheets

SUMIF(), like other functions, works the same way in Calc as in Excel. However, it's a powerful function that I want to make sure readers know about, if they don't already.

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

financial items such as business expense ,donations; mortgage interest

Or perhaps
client activities such as calls to this customer; calls to another customer; calls to yet another customer

Or
expenses for various hobbies like 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. And you don't want to re-sort them to put the categories together because you're always entering new stuff, or you're sharing the spreadsheet--or you just don't want to.

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 lets 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.

---

---

Quick spreadsheet tips

Just a few quick spreadsheet tips.

Right-to-Left Orientation
I just noticed this. If you like your spreadsheet weighted on the right, you can make it that way.

Right-click on a tab and choose Sheet Right to Left.

The sheet will flop and the number headings of the rows, etc. will be on the right.

Moving or Copying Sheet Content Easily Between Spreadsheets
I also want to encourage everyone to consider using the Move/Copy feature to get content of one sheet to another document. It's quicker than copying and pasting, and preserves the formatting.

Right-click on the sheet you want to move or copy and choose Move/Copy Sheet.

 

In the window that appears, select an open spreadsheet (it needs to be open already) to add the sheet to, and specify where. Also, SELECT COPY IF YOU DON'T WANT TO MOVE IT. IF YOU DON'T WANT IT TO DISAPPEAR FROM WHERE IT CURRENTLY IS, SELECT COPY.

Click OK. In the spreadsheet that you moved or copied the sheet to, you'll see the sheet where you specified it should appear.

Using regular expressions in OpenOffice Calc filters -- phrases like "contains" rather than equals, less than, etc.

Life just isn't cut and dried. Sometimes when you're filtering you don't want to just say "give me all the people whose last name is Hanson." You want Hanson, Hansen, and Hansengaaardennn (those Dutch really go for the jawbreaker names).

You'd like to filter out everyone except those whose names contain "Hans".

Here's how to do that. Select the item in the Comparison Field from the dropdown list in the standard filter, then type what you want in the other field. Click More, and select Regular Expressions, then click OK.

Example of what you want	What to enter in the Condition field	Syntax for what to enter in the Value field	Example of what to enter in the Value field
Begins with Hans	=	^x.*	^Hans.* (you can also skip the ^, I've found)
Does not begin with Hans	<>	^x.*	^Hans.*
Ends with Hans	=	.*x$	.*Hans$
Contains Hans	=	.*x.*	.*Hans.*
Does not contain Hans	<>	.*x.*	.*Hans.*

Here are some examples. Let's say you want all names that start with Hans, but not all names that simply contain Hans.

Here's the data.

Select all  the data, or just click in the headings, and choose Data > Filter > Standard Filter. Make the window look like this.

Click OK and you get this; Bob Montrahans is not included. (It's not because of the case.)

Here's a different example. I want names that DON'T CONTAIN the series of letters Hans.

Data

The window with the restrictions:

And the results.

Here's some information from the OOo wiki about regular expressions.

---

---

OpenOffice.org 3.1 Chart Feature: What to do with zero values

If you're missing data, you have three options now: skip the data, assume zero, or compute a logical continuation.

Let's say you've got this data, with this line chart. The default is to leave the missing data out completely.

If you don't want that, though, you can double-click the chart, then click on one of the data lines, and choose Object Properties.

Select another option, such as the one setting all missing values to 0.

That might or might not be what you want.

The Continue Line option gives the best-looking, if maybe not the most accurate, results.

How to set up a score card for your Iron Chef party (or tabulate other voting/scoring situations) in OpenOffice Calc spreadsheets

Here's the point where MAX, COUNTIF, data validity, and conditional formatting, come together to tabulate voting results. The project also involves chocolate and squash.

I'm throwing an Iron Chef party pretty soon. It's potluck style -- I've designated the "secret" ingredients, squash and chocolate (which need not be used together) and people will bring a dish using one of them. After we eat, we'll vote for the best squash dish, best chocolate dish, best overall, most original, and best imitation of Alton Brown or Chairman Kaga.

With up to 30 people coming, though, I don't want to be in my office tabulating paper ballots while the revelry continues in the rest of the house.

So I thought to myself, how can I set up a cool (aka geeky) spreadsheet so that people can just type in their votes and the winners will just reveal themselves?

I think I'm actually end up going to use a database and a form. But this is a decent approach too, and the tabulation process (since I don't do a lot of SQL coding) is the same.

Here's the situation. I'm condensing it a bit to make it simpler but this is the essential. I've got 10 people coming, and seven people: Bob, Ellen, and others, are actually making dishes. All 10 people need to vote, reasonably anonymously, for the best dish in each of three categories. Each person randomly chooses to vote as Voter1, Voter5, whatever. They choose the person who created the best dish from the dropdown list.

That's how we start. How do you create it?

(To just download it, click here.)

Type the Voter1-Voter10, and the headings for the three prizes.

Switch to Sheet2 (just to keep Sheet2 clean) and type in all the names of the people who cooked dishes.

Now in the first blank cell under the first heading, next to Voter1, choose Data > Validity.

You're going to use the Cell List feature to create a dropdown list with whatever values are in the cell range you specify. Use absolute references as shown to refer to the range of cells in Sheet2 that contain the names.

Click OK.

The list will look something like this.

Use the small black square to drag the dropdown list cell to the other cells that need dropdown lists.

You're all set. Now let's say it's party time and people have made their votes. Or at least this is what it would look like.

Now let's set up the tabulation. First, bring in the range from Sheet2 so it's easy to see which person got the number of votes. Just type =Sheet2.A1  and drag down.

Now you're going to use the =COUNTIF() function to count, for each category/name combination, the number of votes for each member.  It's =COUNTIF(RANGE;VALUE) or, for the first one, =COUNTIF(B$2:B$11;$A16)   Be sure to use absolute references for some of the values as shown so you can drag the values to save time and effort.

Now drag the value down, and over, to fill all the cells for each name/category combination.

Now, you'll use the =MAX() function to pull out the biggest number in each column.

This is the result.

Now, you can either look for the number that MAX() gives you, to find the winner. That's all you'd need to do once all the guests have voted.

Or you could use Conditional Formatting to highlight the number that is equal to the MAX value.

Select the first column and choose Format > Conditional Formatting.

Set up the window as shown. You're going to make the cell that is equal to the MAX cell formatted using the Heading style.

Do the same for the other two columns, referencing the MAX cell corresponding to each.

Now that you've got this all set up, you'd just delete the values out of the voting table so it's blank. Then set it up on your laptop at your next Iron Chef party, and let people vote. The totals will automatically be calculated, and the winner for each category (or the people tying for first, as with one of the categories here) will be automatically highlighted. All you have to do is look at the spreadsheet and pass out the prizes.

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.

---

---

Using the OpenOffice.org Data Form extension to enter information in spreadsheets

Let's say that you've got a spreadsheet where you need to enter a lot of information. Here's one example, but let's say that you have lots of fields, aka lots of columns, and you have to scroll around a lot to enter the data.

What if there were a bit of an easier way to enter the data?

It's the Data Form extension. Download and install the Data Form extension here:

http://extensions.services.openoffice.org/project/DataForm

Download the file.

Then install that extension file.

After installation close both OpenOffice.org and the Quickstarter (the icon in the traybar), and start OpenOffice.org again.

Then set up the data like this, get it going.

Then click somewhere in the data, not a blank cell, and choose Data > Form. The data entry form will appear.

Using named data ranges to have a more readable reference to items in formulas, in OpenOffice.org Calc spreadsheets

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.

---

---

Times Tables Quiz Sheet in OpenOffice Calc: Two Versions

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.

I've created another version here, a random number between 1 and 25, multiplied by another random number between 1 and 25.

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

---

---