# OpenOffice spreadsheet question

**URL:** <https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249>\
**Category:** Factual Questions\
**Created:** [March 26, 2003, 7:27am UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249 "2003-03-26T07:27:28Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![friedo](https://avatars.discourse-cdn.com/v4/letter/f/8edcca/32.png) [@friedo](https://boards.straightdope.com/u/friedo)\
**Post date:** [March 26, 2003, 7:27am UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/1 "2003-03-26T07:27:28Z")

</div>

Does anyone know how to make pivot tables in OpenOffice 1.0? I know it can do it, as I’ve opened Excel files in OO with pivot tables and they work. But I can’t figure out how to make them. Someone said “Data Pilot” has something to do with it, but I can’t figure out WTF that thing is supposed to do. It doesn’t seem to want to make me a pivot table, anyway.

Thanks.

---

<div class="post-metadata">

**Author:** ![Early\_Out](https://avatars.discourse-cdn.com/v4/letter/e/6f9a4e/32.png) [@Early\_Out](https://boards.straightdope.com/u/Early_Out)\
**Post date:** [March 26, 2003, 1:36pm UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/2 "2003-03-26T13:36:54Z")

</div>

This is admittedly just a WAG, since I’ve played with OpenOffice only a couple of times (enough to know that its version of Excel was lacking some things I need, like array formulas), so take it with a huge grain of salt.

I believe that when you create a pivot table, Excel is just automatically creating the otherwise difficult coding required to produce the resulting table. In other words, once you’ve created the table, there aren’t some kind of special “pivot table” codes embedded in the spreadsheet, but rather just a bunch of standard Excel coding that would be difficult and time-consuming to create on your own.

So, when you open that spreadsheet in OpenOffice, the table works fine, but OpenOffice on its own doesn’t have the capability to _generate_ the coding necessary to produce a new pivot table.

(Since I’m just guessing, I’ll happily retract all of this if someone comes along who knows better!)

---

<div class="post-metadata">

**Author:** ![Dreaming\_of\_Maria\_Callas](https://avatars.discourse-cdn.com/v4/letter/d/f14d63/32.png) [@Dreaming\_of\_Maria\_Callas](https://boards.straightdope.com/u/Dreaming_of_Maria_Callas)\
**Post date:** [March 26, 2003, 1:46pm UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/3 "2003-03-26T13:46:20Z")

</div>

Um, the correct name of the program is [OpenOffice.org](http://OpenOffice.org). Please don’t call it “OpenOffice”, as this has trademark issues. See this [explanation](http://www.openoffice.org/FAQs/faq-other.html#6) from the developers.

UnuMondo

---

<div class="post-metadata">

**Author:** ![dylan\_73](https://avatars.discourse-cdn.com/v4/letter/d/ecccb3/32.png) [@dylan\_73](https://boards.straightdope.com/u/dylan_73)\
**Post date:** [March 26, 2003, 3:21pm UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/4 "2003-03-26T15:21:24Z")

</div>

**Early Out** : Seems you’re wrong. I tried making a pivot table in Excel, and then tried the Data Pilot in [Openoffice.org](http://Openoffice.org). I don’t understand what the problem is, **friedo**. The Data Pilot is almost _exactly_ the same as the Excel Pivot Table Wizard…if you can use one, the other…well, it’s virtually the same thing.

---

<div class="post-metadata">

**Author:** ![dylan\_73](https://avatars.discourse-cdn.com/v4/letter/d/ecccb3/32.png) [@dylan\_73](https://boards.straightdope.com/u/dylan_73)\
**Post date:** [March 26, 2003, 3:39pm UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/5 "2003-03-26T15:39:49Z")

</div>

**Early Out** : Forgot to ask: Are array formulas the same as what OOo Calc calls matrix functions?

---

<div class="post-metadata">

**Author:** ![friedo](https://avatars.discourse-cdn.com/v4/letter/f/8edcca/32.png) [@friedo](https://boards.straightdope.com/u/friedo)\
**Post date:** [March 26, 2003, 7:26pm UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/6 "2003-03-26T19:26:05Z")

</div>

> [@](#):
>
> \*Originally posted by dylan\_73 \*  
> \*\ ***Early Out** : Seems you’re wrong. I tried making a pivot table in Excel, and then tried the Data Pilot in [Openoffice.org](http://Openoffice.org). I don’t understand what the problem is, **friedo**. The Data Pilot is almost _exactly_ the same as the Excel Pivot Table Wizard…if you can use one, the other…well, it’s virtually the same thing. \*\*

Hehehe. Well I’ve never actually _used_ the Excel one, you see. I’ve just opened files made by other people with [OpenOffice.org](http://OpenOffice.org). So obviously I’m missing something about how this Data Pilot thingy is supposed to work. Let’s say I’ve got the following data:

```auto

        A B C D
    ________________________________
1 | Jan Feb Mar
2 | Alice
3 | Bob
4 | Carla

```

Now I want to add a pivot table for row 2, which adds a third criterion, (a state code) so one could then see a list of total sales made by Alice in January for each US state. Does that make sense? If so, for the life of me, I can’t figure out how to make Data Pilot work.

(And of course I want a similar deal for Bob and Carla too.)

---

<div class="post-metadata">

**Author:** ![dylan\_73](https://avatars.discourse-cdn.com/v4/letter/d/ecccb3/32.png) [@dylan\_73](https://boards.straightdope.com/u/dylan_73)\
**Post date:** [March 27, 2003, 11:03am UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/7 "2003-03-27T11:03:30Z")

</div>

Ahh…I see. Sorry, can’t really help because I always hated Pivot tables in Excel because I got used to the way they worked in SPSS.

I would ask one of your friends who send you them how to make one, or make a new GQ asking how they’re made in Excel (which more people are likely to answer). As I say, it’s exactly the same in OOo.

I suppose it’s one of these things I ought to know though, so I’ll find out and send you a reply if you’ve had no luck in that time…

---

<div class="post-metadata">

**Author:** ![Early\_Out](https://avatars.discourse-cdn.com/v4/letter/e/6f9a4e/32.png) [@Early\_Out](https://boards.straightdope.com/u/Early_Out)\
**Post date:** [March 27, 2003, 1:10pm UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/8 "2003-03-27T13:10:55Z")

</div>

Ah, **dylan\_73** , it appears that our friend **friedo’s** problem isn’t with the software, but with the user! In that case, my WAG is clearly worthless, since OOo _is_ capable of doing pivot tables. Oh, well.

It’s tough to describe what array formulas do in Excel, and I don’t have a copy of OOo to play with any longer (and disliked it enough that I wouldn’t reinstall it on my new machine), but here’s an example. Say you’ve got a two-column spreadsheet. Column A contains a bunch of values. Column B contains some blanks, and some cells with “x” in them (as in, "has the check cleared or not?).

Now, you want a single cell somewhere that is the total of the values in Col A, but only for those rows that also have an “x” in Col B. Without array formulas, you’d have to create a third column (blank if Col B is blank, copy the value from Col A if Col B = “x”), then add up the resulting values in Col C. Array formulas let you do it in one step. The resulting formula would look like this:

{=SUM(IF(B1:B1000=“x”,A1:A1000))}

though you can’t enter it directly by typing in the curly braces (for reasons that escape me); you enter it without the curly braces, but then hit CTRL-SHIFT-ENTER.

There are a lot of more complex uses for the beast, of course, but that excercise is left to the student.

---

<div class="post-metadata">

**Author:** ![Early\_Out](https://avatars.discourse-cdn.com/v4/letter/e/6f9a4e/32.png) [@Early\_Out](https://boards.straightdope.com/u/Early_Out)\
**Post date:** [March 27, 2003, 1:21pm UTC](https://boards.straightdope.com/t/openoffice-spreadsheet-question/164249/9 "2003-03-27T13:21:55Z")

</div>

Now, to take a stab at **friedo’s** problem. This is a bit like trying to thread a needle with mittens on, however. This is one of those problems that needs a hands-on demonstration.

The pivot table doesn’t let you create _new_ criteria. It just lets you organize your existing data in different ways. You wouldn’t use a pivot table to _add_ information about sales by state. You’d use a pivot table to take your existing data and put it into different totaling buckets.

Let’s say you had sales for each person. Col A might be the person’s name, Col B the amount of the sale, Col C the month the sale was made, Col D the state in which the sale was made. There’s one row for each sale:

Jane $5 Feb CA  
Jane $4 Jan AZ  
Mary $2 Apr WY  
Mary $8 Sep MT

and so on.

A pivot table would let you display a table of results with totals by salesperson, by month, or by state. You could easily have the states running down the left side, and the months running across the tops of the columns, with the total sales by state, by month, in the field of the table. Or, you could have the salepeople running down the left side, and the states running across, with the total sales by salesperson, by state, in the field of the table.
