Pivot table - data format issues

Hi Linda

Thankyou for your question

I am not clear on what you are trying to do. Could you clarify please.

Are you running a pivot table based on a list within an Excel spreadsheet, and then adding extra rows to the bottom of the spreadsheet and hoping to include the extra rows in the pivot table?

Thanks

Stephen

Hi Linda

Thanks for the clarification

You can accomplish this by using a dynamic named range as the source for your pivot table. A dynamic range changes it's size to include any additional rows.

If involves nesting a COUNTA function within an offset function

Go to Insert-Name-Define
Type a name for the range, e.g. MyDataSource

In the Refers To box, enter an Offset Function that defines the range size.

=OFFSET(SourceSheet!$A$1,0,0,COUNTA(SourceSheet($A:$A),10)

The arguments used in the Offset function are:

Reference cell: SourceSheet!$A$1

Source Sheet is the name of the sheet where the data is held, and the cell is the start cell of the range

Rows to offset: 0
Columns to offset: 0
The above justs specifies that you are starting at cell A1

Number of Rows: COUNTA(Data!$A:$A)

This counts the number of rows in the range. It simply counts down until it finds a blank row

Number of Columns: 10

Or however many columns there are in your source data

Click OK


You can then simply use the range name "MyData" as the datasource for your pivot table

Hope this is useful

Regards

Stephen