Confusing Pivot Tables

[gshockxcc]

I have been using Excel for more than 20 years, but rarely use Pivot Tables. I’m moving away from Excel completely and trying to do everything in Libre Office. I am truly baffled by how the Pivot Table functions in Calc. I understand how it’s “supposed” to work, but I don’t understand the output, based on what I’m doing in the Pivot Table Layout dialog box.

Questions:

1. Why is “Data” always in the Column field? I understand what it’s doing. It’s trying to count or sum a number based on the row item. But I don’t have any data. These are just lists of equipment by OEM. So the “Data” column just shows up as “empty”. Which makes sense because there is no data, but the column is not useful in this instance, and I can’t get rid of it.

2. When I select the range to place the Pivot table, why can’t I edit the field with F2? I have to click in the box, or double-click the cell for the upper left corner of the Pivot Table. And F4 doesn’t appear to work to lock the cell reference. That happens automatically when I double-click the cell for the destination.

3. Does the data range for the Pivot Table automatically adjust to capture new data? Or do you need to edit the Pivot Table each time new rows are added? Seems I can’t use F2 to edit this value either. I have to click in the box to enter edit mode.

4. Does it make more sense to put the data into Tables in Base and link that data to the Calc spreadsheet?

Thanks in advance. I have tried to answer these questions on my own with the Calc guide, but I find the text description to be lacking in detail and a bit ambiguous.

[EarnestAl]

@gshockxcc

It’s trying to count or sum a number based on the row item. But I don’t have any data

This what pivot tables do; I don’t see a point in using a pivot table without calculating or grouping something.

@gshockxcc

Tables in Base and link that data to the Calc spreadsheet?

What do you want the Calc spreadsheet to do?

[gshockxcc]

Hi, thanks for your comment. I do want to group the fields, but not calculate. So I’m confused by how to handle the Data field when it wants to perform a summation of the values.

For this particular spreadsheet, I’m using it to develop proposals because much of what goes into them (proposals) are calculated values depending on the scope of items the client wants.

[Wanderer]

@gshockxcc

I do want to group the fields, but not calculate

That sounds more like sorting by group…

[Villeroy]

@gshockxcc

Why is “Data” always in the Column field?

This only matters when there is more than one data field.

@gshockxcc

When I select the range to place the Pivot table, why can’t I edit the field with F2?

Just enter F2. The address is always an absolute one.

@gshockxcc

Does the data range for the Pivot Table automatically adjust to capture new data?

This behaves exactly like any other spreadsheet reference. The cell formula SUM(A1:A99) expands to SUM(A1:A100) when you insert one row.
In order to make this happen more reliably, you have to check option Tools>Options>Calc>General>“Expand references when new rows or columns are inseted”. With that option being off, references do not expand when inserting new cells directly below/behind. You have to insert within the range.

@gshockxcc

Does it make more sense to put the data into Tables in Base?

Yes, of course. The learning curve is much steeper, but in the end you get something that is easily and safely usable for many years.

I do want to group the fields, but not calculate.

This is what the pivot in your screenshot does. It selects existing combinations of OEM and MODEL for the selected EQUIPMENT.

[gshockxcc]

@Villeroy

In order to make this happen more reliably, you have to check option Tools>Options>Calc>General>“Expand references when new rows or columns are inseted”. With that option being off, references do not expand when inserting new cells directly below/behind. You have to insert within the range.

Now I understand why the field wasn’t expanding. Must be this setting is turned off on my installation.

@Villeroy

Just enter F2. The address is always an absolute one.

I tried that, but it wasn’t working. I had to click the cells behind the dialog box, and then click again to get the pink halo that indicates which cell I selected. Only then did the absolute reference get populated in the range field.

@Villeroy

Yes, of course. The learning curve is much steeper, but in the end you get something that is easily and safely usable for many years.

Believe it or not, I understand databases a lot better than I understand Pivot Tables.

[gshockxcc]

@Villeroy

This behaves exactly like any other spreadsheet reference. The cell formula SUM(A1:A99) expands to SUM(A1:A100) when you insert one row.
In order to make this happen more reliably, you have to check option Tools>Options>Calc>General>“Expand references when new rows or columns are inseted”. With that option being off, references do not expand when inserting new cells directly below/behind. You have to insert within the range.

This option is checked, but when adding new data to the source table, the next row is not automatically added to the range.

In this case, I added a fictitious name and email, and the range is not updated.

But the option is enabled in the Tools setting in Calc.

[Villeroy]

@gshockxcc

This option is checked, but when adding new data to the source table, the next row is not automatically added to the range.

You’ve got to insert cells (or entire rows) anywhere within or directly below the referenced range. This will expand all references in formulas, named ranges, conditional formatting, validations, list boxes and all kinds of source ranges.

[gshockxcc]

So, you have to actually insert a new row? You can’t just enter new data in the adjacent row below?

[Villeroy]

@gshockxcc

You can’t just enter new data in the adjacent row below?

That would be a terrible mess. Spreadsheets expand references by cell insertion, but only if the reference includes more than one row (or more than one column when expanding column wise). The sum of a single row (SUM(A1:F1) will not expand.
Alternatively, you may reference entire columns like A:D and take care that the space below the actual data remains empty.

@gshockxcc

Does it make more sense to put the data into Tables in Base and link that data to the Calc spreadsheet?

This is one of many reasons why I prefer databases to arithmetic calculators.

[gshockxcc]

Sorry for the tardy reply to your last post.

@Villeroy

That would be a terrible mess. Spreadsheets expand references by cell insertion, but only if the reference includes more than one row (or more than one column when expanding column wise).

Perhaps my confusion comes from what I can do in Excel versus what I want to do in Calc. I’m trying to exploit the use of tables for my data, and I just recently learned about using table structured references in Excel. I want to do that in Calc. After a quick search, it appears that I can, but I need to save the file in .xlsx format to preserve that functionality.

Calc doesn’t appear give me the option to format my data in a “Table” like Excel does. If I add a new row of data to the Table in Excel, the range of the table automatically expands to include the new row.

Unless I’m misunderstanding how Pivot Tables are created, the data has to be formatted as a Table, so that you can Pivot the data. But it appears that Calc doesn’t let you select a range of data and format it manually as a Table. You select the range of data in the Pivot Table wizard. It does it for you if you select any cell within the range of data that you want to work with.

I assume that I can’t select a range of data and define it as a “Table” like I can in Excel so that I can use the Structured References I mentioned above

But, if I add new data, the range for the Pivot Table doesn’t expand. Hence, my title for this post.

Am I missing something in setting up the Pivot Table in Calc? Is there a setting I don’t have enabled? Or does Calc just fundamentally work differently?

The more I try to understand Pivot Tables, the more I go back to database functionality. That just makes so much more sense.

Late addition. I just stumbled onto this while typing this post.

Is this what I’m looking for?

[Villeroy]

@gshockxcc

Perhaps my confusion comes from what I can do in Excel versus what I want to do in Calc.

Same with Excel. Excel expands references by cell insertion