• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Can we do some Excel layout magic for displaying some pivot tables?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Can we do some Excel layout magic for displaying some pivot tables?|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Can we do some Excel layout magic f…
sp_PrintTopic sp_TopicIcon
Can we do some Excel layout magic for displaying some pivot tables?
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
1
June 17, 2020 - 7:20 am
sp_Permalink sp_Print

OK, I have a pivot table question, but this is more about general Excel layout than it is any heavy duty number crunching.  I have 2 pivot tables, which house a lot of data.  The pivot tables are linked by common slicers that I have added to the left of the pivot tables.  A user would start from a high level slicer and then drill down into the data by selecting settings in subsequent slicers.  

My predicament is that when all the data items are shown for each pivot table, there are at least 100 rows of data in the table.  So, when both pivot tables are totally unfiltered, the 2nd pivot table is about 100 rows below the first.  But, after all the filters in the slicers are selected, there could be only 5-10 items visible.  If I don't do anything, then that 2nd pivot table is still 100 rows below the first pivot table, which is now only 5-10 rows long.  So, there's a lot of "space space" and unnecessary scrolling from the 1st to the 2nd pivot table.  What I'd really like to do is some Excel magic that moves the 2nd pivot table to just a few rows south of where the 1st pivot table ends, regardless of how many rows long that first pivot table is.  

Does anyone know a relatively simple way to do this?  I'm open to a VBA solution, which I believe would be required.  One potential workaround would be to place that 2nd pivot table to the right of the 1st one.  Each pivot table has a fixed number of columns, just a variable number of rows.  It would just be more desirable to keep scrolling down the sheet to see more granular levels of detail rather than scroll continually from side to side. 

Thanks in advance if anyone comes up with a creative solution.  🙂   

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
June 17, 2020 - 6:20 pm
sp_Permalink sp_Print

Since most people have their monitors in landscape orientation in my experience, I'd probably put them side by side but, if you want them under each other, I'd just position them (with all the data displaying) so that there are a couple of blank rows in between, then use the Worksheet_PivotTableUpdate event to hide/unhide rows in between as the pivot tables change size.

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
3
June 17, 2020 - 7:43 pm
sp_Permalink sp_Print

As I understand it, that's exactly what Scotty wants Velouria, but (I think) not 'by hand' but by 'some Excel magic'.

I think about a button under the slicers with a text as 'Hide unnecessary blank rows' which activates a macro that 'automatically' does that trick for the user.

I'm not about VBA, so can't solve that, but think this is a solution Scotty maybe wants.

I'm afraid the solution with the pivot tables side by side don't work when you filter them for different items where the number of rows are different in both pivot tables.

Frans

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
June 17, 2020 - 8:12 pm
sp_Permalink sp_Print sp_EditHistory

Frans,

I assumed that, which is why I suggested using event code to automate the hiding/showing of rows.

I'm not clear on why side-by-side would be affected by different numbers of rows in the pivot tables? There wouldn't be any rows being hidden in that layout.

 

So, assuming a vertical layout, and two blank rows left between the fully expanded pivot tables, you could use something like this:

 

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pvTop As PivotTable
Set pvTop = Me.PivotTables("PivotTable1")

' don't need to do anything if it's not the top pivot table updating
If Not Target.Name = pvTop.Name Then Exit Sub

On Error GoTo finally

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Dim pvBottom As PivotTable
Set pvBottom = Me.PivotTables("PivotTable2")

Dim LastHideRow As Long
LastHideRow = pvBottom.TableRange1.Row - 2

With pvTop.TableRange2
Dim FirstHideRow As Long
FirstHideRow = .Row + .Rows.Count + 1
Range(Cells(.Row, 1), Cells(LastHideRow, 1)).EntireRow.Hidden = False
End With
If LastHideRow >= FirstHideRow Then Range(Cells(FirstHideRow, 1), Cells(LastHideRow, 1)).EntireRow.Hidden = True

finally:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
5
June 18, 2020 - 12:13 am
sp_Permalink sp_Print sp_EditHistory

Wow, what a code. Not my cup of coffee, but I hope Scotty can work with that! Good work Velouria, thanks.

Frans

Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
6
June 18, 2020 - 7:57 am
sp_Permalink sp_Print

Velouria,

Yes! A VBA macro that updates when the pivot table is updated sounds like just the solution!  I dabble in VBA, but I'll have to play with it to see how it works.  Essentially, I'd need the code to figure out how many rows that first pivot table is so I know how many rows to hide to make it appear that the 2nd table is right under it.    Thank you for providing that code example.

As far as Frans Visser's comments, I do think that a side by side solution would work too.  And it's true that with large monitors, the side by side thing may not be that big a deal.  Concerning the pivot tables, the 1st table will just contain summary data and inherently be somewhat short.  The 2nd pivot table will contain details, and will always be longer than the first.  But, that shouldn't be an issue when they are side by side. 

Looking forward to test drive this solution.  🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lynnette Altomari, Roy Lutke, Jeff Krueger, Natasha Smith, Monique Roussouw
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.