Excel 2013 : multiple pivot tables on one sheet (BELOW) each other

Question

I need to create dashboard with multiple pivot table in the same excel sheet one below the other.

The problem is that When the Pivot Table refreshes it may be longer (more rows) so it gives a warning that the rows below what it needs will be overwritten.

I'd like to know how to configure Excel pivot table for adding row in pivot table without overwrite the following.

I have already seen an example when Microsoft Techdays 2013 but I can not remember the method. (There is a check box to enable in Excel 2013)

Thank you.

There are ways to disable the overwrite warning and there is even an auto-format check box that will make sure the pivot table refreshes don't overwrite it's style formatting, I've been a developer beta tester for Excel 2013 for a few years and have not see a UI method to auto buffer these pivot tables. Are you allowed to have each pivot table on it's own sheet (as Microsoft recommends) and have each of these pivot tables aggregate into a summary report sheet at the end? I'm assuming you already know you can put the pivot tables side by side to avoid the issue, but it's probably not an option. — KLDavenport, Mar 31 '13 at 22:23
I want to deploy dashbord using Excel into SharePoint so i'm not allowed to use each pivot table in sheet. — Tlachtga, Jul 08 '13 at 10:53
This came up with an Excel/SP consultant recently and it reminded me of this post. We determined that it isn't possible, sorry that I couldn't help — KLDavenport, Aug 11 '13 at 02:08

score 0 · Answer 1 · answered Jan 04 '14 at 17:53

Whenever I have had more than one pivot table per worksheet I either

1) restrict the number of rows a pivot table eg I had report that need rolling 6 month values so I always had 6 rows but the values advanced each month. In this case you need to use VBA to 'check' and 'uncheck' the values that show. Use methods such as those found in this Google search to do this:

https://www.google.com/search?q=find+last+used+row#q=excel+pivot+table+vba+to+filter+row+values

2) upon each refresh, recreate each pivot table from scratch, positioning each pivot table accordingly, by deleting existing pivot tables, and then recreating pivot tables from top down. For this method you need to determine which is last row in pivot table so you can recreate next one below it. Use methods such as those in Google search to find the last pivot table row:

https://www.google.com/search?q=find+last+pivot+table+row

score 0 · Answer 2 · answered Jun 25 '14 at 14:55

I came across this issue lately, and built the following. Of course to have it work, the prerequisite must be met that in principle the two tables do fit on one sheet. What I than do, is build in sufficient space (=rows) between the two tables, and after refresh hide the rows that are left between the tables. So it does require VBA, and can for example be executed on the PivotTable_Update event.

Private Sub hideRowsBetweenListObjects(sheetName As String)
Dim tblRowPosition1 As Integer
Dim tblNrOfRows1 As Integer
Dim tblRowPosition2 As Integer
Dim tblNrOfRows2 As Integer

'Initialize
Application.ScreenUpdating = False

With Worksheets(sheetName).ListObjects(1)
    tblRowPosition1 = .Range.Row
    tblNrOfRows1 = .Range.Rows.Count
End With

With Worksheets(sheetName).ListObjects(2)
    tblRowPosition2 = .Range.Row
    tblNrOfRows2 = .Range.Rows.Count
End With

With Worksheets(sheetName)
    If tblRowPosition1 < tblRowPosition2 Then
        .Range(.Cells(tblRowPosition1 + tblNrOfRows1, 1), .Cells(tblRowPosition2 - 4, 1)).EntireRow.Hidden = True
    ElseIf tblRowPosition2 < tblRowPosition1 Then
        .Range(.Cells(tblRowPosition2 + tblNrOfRows2, 1), .Cells(tblRowPosition1 - 4, 1)).EntireRow.Hidden = True
    End If
End With

End Sub

score 0 · Answer 3 · answered Dec 17 '19 at 20:09

0

If you are using a Professional version of excel you can use powerview. to create dashboards. Wenn using powerview you can limit the space a pivottable uses.

see below links for more information:
how to activate powerview

microsoft tutorial on powerview

excel powerview combined with sharepoint

answered Dec 17 '19 at 20:09

Tom

11
3

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Pankwood Dec 17 '19 at 20:36

Excel 2013 : multiple pivot tables on one sheet (BELOW) each other

3 Answers3