• 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
    • 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
You are here: Home

Lost password?
sp_Search
Advanced Search
Advanced Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search

Please confirm you want to mark all posts read

Mark all topics read

sp_MobileMenu Actions
Actions
sp_LogInOut
Log In
sp_Search

Search Forums

sp_RankInfo
Ranks Information
Avatar

New/Updated Topics

General Excel Questions & Answers

  Count Number Of Students in Training per week

  Small formula

  Return Dynamic value in Specific Tab using Lookup and If Log…

  Compair two pair of cells: function usage

  Copy and autoupdate lists

  Find and Combine

  Need help with Excel formulas

VBA & Macros

  VBA Refrenence

  VBA to match PDF file in folder and hyperlink to spreadsheet

Power Query

  Extract unique sorted list from two different columns ignori…

  Unsolved error in Excel Power Query

Power Pivot

  how to do it in power pivot?

Excel Dashboards

  Slicer error in OneDrive/Blog

Excel Expert

  Excel formula

  mail merge from excel to word

Select Forum

  Rules and Guides

Forum Rules and Guides

  Public Forums - For Registered Users

General Excel Questions & Answers

Dashboards & Charts

VBA & Macros

Power Query

Power Pivot

  Course Members Only

Excel Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Power BI

Excel

Word

Outlook

Excel Expert

Excel for Customer Service Professionals

Excel Analysis Toolpak

Excel Tables

Excel for Operations Management

Financial Modelling

Advanced Excel Formulas

Pivot Tables Quick Start

ForumsGeneral Excel Questions &…
sp_TopicIcon
Add sparklines automatically to a pivot table column
Avatar
MGB
Posts: 32
Level 0
November 27, 2022 - 9:59 pm

1

Hi,

I've googled for any way to automatically add a new column to a pivot table and insert sparklines inside it automatically but I was not able to do it automatically.

All I was able to do, was just to inset a sparkline in the cell far after the last column of the pivot table, then drag it till the end.

Is there any way to include it inside the pivot table in order to be updated automatically when data changes?

And if it is not possible, is it possible to insert a formula beside the pivot table that could be updated automatically with my interaction to drag the cell down to update the sparklines?

My point is to get an updated sparkline automatically when data inside the pivot table changed and add new rows automatically.

Thanks

The attached file includes a sheet titled "Sparklines-Inside-PivotTables" for the pivot table. K column includes the manually inserted sparklines, and as you can see I need to manually drag the cells down manually to update the sparklines.

My request please if there is any way to update the sparklines automatically shown in column M. 

Or maybe there is any way to insert a table in the column M and let it get updated automatically according to the contents of the pivot table, then insert the sparklines inside the table cell, so when the pivot table gets updated the newly added tables get updated as well, and in turn, the sparklines get updated as well.

Thanks a lot and too much appreciated your valuable time reading my post and helping me.

sp_AnswersTopicSeeAnswer
Avatar
Mynda Treacy
Posts: 4354
Level 10
November 29, 2022 - 11:52 am

2

Hi,

As you've found, it's not possible to include a Sparkline in a PivotTable. Please see this tutorial on relative dynamic named ranges. Towards the end there is an example that uses Sparklines.

I hope that points you in the right direction.

Mynda

Avatar
MGB
Posts: 32
Level 0
November 29, 2022 - 4:29 pm

3

Thanks a lot Mynda ... I will follow your advice.

Avatar
Velouria
London or thereabouts
Posts: 574

Level 4
November 29, 2022 - 7:12 pm

4

You can also do it with code. Add this to a normal module:

Sub UpdatePivotSparklines(pt As PivotTable)
    With pt
        Dim sparkRange As Range
        Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
    End With
    pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Clear
    With sparkRange.SparklineGroups
        .Add Type:=xlSparkLine, SourceData:=pt.DataBodyRange.Address

With .Item(1)
With .SeriesColor
.Color = 9592887
.TintAndShade = 0
End With
With .Points
.Highpoint.Visible = True
.Lowpoint.Visible = True
.Firstpoint.Visible = True
.Lastpoint.Visible = True
.Markers.Visible = True
With .Negative.Color
.Color = 208
.TintAndShade = 0
End With
With .Markers.Color
.Color = 208
.TintAndShade = 0
End With
With .Highpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Lowpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Firstpoint.Color
.Color = 208
.TintAndShade = 0
End With
With .Lastpoint.Color
.Color = 208
.TintAndShade = 0
End With
End With
.Axes.Horizontal.RightToLeftPlotOrder = True
End With
End With
End Sub

 

then right click the pivot table worksheet, choose View Code and paste the following code into the code pane:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
UpdatePivotSparklines Target
End Sub

Macro enabled version of your file attached.

sp_AnswersTopicAnswer
Avatar
MGB
Posts: 32
Level 0
December 8, 2022 - 5:04 am

5

Hi,

Thanks a lot for your valuable time helping me.

Excellent indeed.

But, if I've several sheets (about 59 sheets), each one includes a pivot table, and each has sparklines beside it, of course.

Is it possible to set different colors and styles for each sparkling sheet? I do not want to let them all have the same look and theme, as with each pivot table refresh all sparklines reset to the same theme.

Thanks

MGB!

Avatar
Velouria
London or thereabouts
Posts: 574

Level 4
December 8, 2022 - 9:31 pm

6

I do like the idea of a sparkling sheet! 😉

If you already have the sparklines in place, then the code can be a lot simpler:

 

Sub UpdatePivotSparklines(pt As PivotTable)
   With pt
      Dim sparkRange As Range
      Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
   End With
   With pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Item(1)
      Set .Location = sparkRange
      .SourceData = pt.DataBodyRange.Address
   End With
End Sub
Avatar
MGB
Posts: 32
Level 0
December 9, 2022 - 3:29 pm

7
Sub UpdatePivotSparklines(pt As PivotTable)
   With pt
      Dim sparkRange As Range
      Set sparkRange = .DataBodyRange.Offset(, .DataBodyRange.Columns.Count).Resize(, 1)
   End With
   With pt.TableRange1.Worksheet.UsedRange.SparklineGroups.Item(1)
      Set .Location = sparkRange
      .SourceData = pt.DataBodyRange.Address
   End With
End Sub

Is this code to be added after the last line of the original code above?

Or it is to replace the old code with the new one?

If you already have the sparklines in place, then the code can be a lot simpler:

Please note that I can not determine the future growth of the pivot table growth, as many rows are to be added later on.

I've tried to paste the above code after the end of the original code but got an error.

Thanks a lot and much appreciated your valuable time helping me my friend.

MGB

Avatar
Velouria
London or thereabouts
Posts: 574

Level 4
December 10, 2022 - 6:01 pm

8

That routine replaces the previous one of the same name. The worksheet code that calls it remains the same.

 

as long as there are spark lines on the pivot sheets it should work.

Avatar
MGB
Posts: 32
Level 0
December 10, 2022 - 6:18 pm

9

Thanks a lot for your valuable time replying to my thread and helping me.

But if I want to merge both VBAs into one single code, what would it be?

The reason for asking is that I need to add the sparklines to other workbooks and I want to randomize its theme.

So I want to keep the full code to achieve the 2 things in the same newly created workbook.

Thanks a lot.

Avatar
Velouria
London or thereabouts
Posts: 574

Level 4
December 12, 2022 - 4:32 am

10

I don’t really understand. One code adds and formats spark lines while the other only adjusts the location and data range of existing spark lines. It doesn’t make sense to combine them.

Avatar
MGB
Posts: 32
Level 0
December 13, 2022 - 2:31 am

11
Thanks a lot for your valuable time helping me.

Please, I mean if I am creating a new workbook, and this new workbook has several sheets.

Each sheet includes a pivot table and no sparklines had been added yet at all.

And I want the format or the theme of the newly added sparklines for each sheet beside its associated pivot table to be a random theme.

So, I am asking what would be the code to do both tasks for the new workbook that has a pivot table only.

1st task: Add sparklines.

2nd task: Set the sparklines theme to be a random theme.

Sorry if my poor English does not help me to express myself correctly.

Thanks a lot for your help.

Avatar
Velouria
London or thereabouts
Posts: 574

Level 4
December 14, 2022 - 11:01 pm

12

Unfortunately there is no option in VBA to simply apply a theme to a sparkline. You have to individually set the colours of the line, marker, high point, low point, first point and last point for example. Doing that randomly will most likely produce terrible combinations a lot of the time. I would personally suggest that you pick a theme and stick to it.

Avatar
MGB
Posts: 32
Level 0
December 15, 2022 - 3:40 am

13

Thanks a lot.

Avatar
Velouria
London or thereabouts
Posts: 574

Level 4
December 15, 2022 - 8:15 pm

14

If you really wanted to pursue it, one option might be to create arrays with related colour values in (eg one has colour values for the line, one for the markers and so on) then use something like Randbetween to select a particular item from each array. At least that way you can design some combinations that work well beforehand.

Forum Timezone:
Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Bouskila stephanie, James Hwang
Guest(s) 65
Currently Browsing this Page:
1 Guest(s)

Devices in use: Desktop (52), Phone (15)

Forum Stats:
Groups: 3
Forums: 24
Topics: 6047
Posts: 26543
Member Stats:
Guest Posters: 49
Members: 31497
Moderators: 2
Admins: 4
© Simple:Press
  • 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
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

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.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x