Pivot Notes documentation
5 Steps to start working with Pivot Notes
Pivot Notes was written by Mark Kubiszyn
code attributionin situ
' © Copyright/Author: ' Mark Kubiszyn 2012-2017. All Rights Reserved ' Website/Follow: ' http://www.kubiszyn.co.uk/ ' https://www.facebook.com/Kubiszyn.co.uk/
You can Purchase Pivot Notes for £2.99 via FastSpring by clicking here
Here I will describe how to get the correct Pivot Table Field Name to use as the Index when congiuring Pivot Notes - failure to identify the correct Field Names will cause errors when attempting to Index a Pivot Table
If someone has changed the Field Name:
If someone has changed the Field Name you can check this by clicking anywhere in the Pivot Table to view the Field List on the right-hand side. If this does not automatically pop up, then you can bring the Field List up by selecting 'Field List' on the 'Show' Group of the 'PIVOTTABLE TOOLS', 'ANALYZE' Tab of the Ribbon. Now check the Field Name in the respective area ie. 'FILTERS', 'COLUMNS' or 'ROWS'. Typically the 'VALUES' area will not be used for Indexed Fields when using Pivot Notes. The Name that you see for the Field will represent the correct Name displayed in the Pivot Table - try it, change one of your Field Names and you will see that the Name changes in the Field List too
If you are using the Data Model:
In order to ensure that you are Indexing the correct Pivot Table Fields when using the Data Model, you can use the following Code to determine the Field Names and then enter the Name into the Pivot Note Field Names Setting ie. "[Region_Data].[Region].[Region]". Press ALT+F8 to enter the VBA Editor and in the Code Module where you pasted Pivot Notes Code add the Subroutine. Click into the Subroutine and press F5. A Message Box will pop up with the Names. They will also be printed to the Immediate Debug Window so you can simply Copy & Paste the Names into the Settings Variable in the Setup section
Sub ListPivotTableFieldNames() Dim objPivotTable As PivotTable Dim objPivotField As PivotField For Each objPivotTable In ActiveSheet.PivotTables For Each objPivotField In objPivotTable.PivotFields MsgBox "Data Model Field to use in Pivot Notes Settings: " & objPivotField & vbLf & vbLf & "Original Field Name without Data Model: " & objPivotField.Caption Debug.Print "Data Model Field to use in Pivot Notes Settings: " & objPivotField Debug.Print "Original Field Name without Data Model: " & objPivotField.Caption Next objPivotField Next objPivotTable End Sub
Here are the results of a Pivot Table that uses the Data Model. The different Names have been highlighted - these are the Names that you need to use when configuring Pivot Notes prior to Indexing:
Data Model Field to use in Pivot Notes Settings: [Region_Data].[Region].[Region] Original Field Name without Data Model: Region Data Model Field to use in Pivot Notes Settings: [Product_Data].[Prod Cat].[Prod Cat] Original Field Name without Data Model: Prod Cat Data Model Field to use in Pivot Notes Settings: [Measures].[Sum of Revenue] Original Field Name without Data Model: Sum of Revenue
Choosing how to Index the Pivot Table is very important. You need to remember that in order for Pivot Notes to store and sort Notes correctly it must have a unique Index creating from one or more visible Pivot Table Fileds. If you don't get this bit right, Notes will not be rendered correctly and may appear to move around or not be saved at all. Here are some key points:
1. If possible, choose 1 visible Field that is unique for every single Row in the Pivot Table and Index by that Field
2. If step 1. is not possible then you need to Index by more than one Field in order to create a unique Index. Remember that these must be visible in the Pivot Table so Repeat Label Items should be switched on. If you don't want to repeat Item Labels you can hide the Fields using Conditional Formatting afterwards. Sometimes you may have to Index by 3 or 4 Fields in order to have a truly unique Index
Pivot Table Department Vendor A Department A Vendor B Department A Vendor C Department A Vendor Index A Department/A Vendor/ B Department/A Vendor/ C Department/A Vendor/
Pivot Table Department Vendor A Department A Vendor B Department A Vendor C Department A Vendor C Department A Vendor Index A Department/A Vendor/ B Department/A Vendor/ C Department/A Vendor/
Please Note: if your Pivot Table has Blanks in the Fields you should 'Exclude' these prior to Indexing the Pivot Table
' ## the Name of your Pivot Table. click on the Pivot Table and check the setting for the "PivotTable" Group on the "ANALYZE" Tab of the Ribbon ' - a check will be made by the Code when Indexing this Pivot Table Private Const PivotTableNamePT1 As String = "PivotTable1" ' ## the Name of the Worksheet where your Pivot Table is stored. we will not use ActiveSheet! ' we need to explicitly refer to this in case of Sheet switching whilst in Cell Edit Mode (F2) and also for error reporting ' - this prevents the potential loss of Notes if a user decides to go to another Worksheet whilst editing a Note ' - a check will be made by the Code when Indexing a Pivot Table for this Worksheet Private Const PivotTableWorksheetNamePT1 As String = "Example" ' ## the Name of the Worksheet where your Pivot Notes will be stored ' remember you can hide this Worksheet (xlSheetHidden or xlSheetVeryHidden), the Code will still work ' - you can store Notes for more than 1 Pivot Table in a single Worksheet ' - Pivot Notes Styles are also stored in the "Notes" Worksheet. you can Copy this directly into a new Project Private Const NotesWorksheetNamePT1 As String = "Notes" ' ## the Index start Cell for your stored Pivot Notes ' typically this will be the first Cell where you have added the Headers ie. Index, Notes 1, Notes 2, Notes 3 ' the Notes are stored in multiple Columns per Pivot Table in the Notes Worksheet specified above ' - a gap of at least 1 Column should be left between Notes when using more than 1 Pivot Table Private Const NotesIndexCellPT1 As String = "A1" ' ## the Field Names that will be used as the unique Index for the Pivot Table to link your Pivot Notes ' ensure that you separate these using a colon ':' if you enter more than one Field Name ie. "Field 1:Field 2" etc. ' remember to pass these in the order that you want the Index to be created, preferably left to right ' - there is no longer any requirement to specify an offset, the first Field is where the Code will begin to create an Index Private Const PivotTableFieldNamesPT1 As String = "Material:Key Figure" ' ## the delimiter that is used when Indexing the Pivot Table ie. "-", ", ", "*", " / ", "~" etc. ' remember when using Date Fields it may be wise to use a tilda "~" ie. ~03/09/2016~ will allow the Code to split & Index correctly Private Const IndexDelimiterPT1 As String = "~" ' ## the Style(s) that you want to give your Pivot Note Headers - the Headers for the Notes displayed alongside your Pivot Table ' - Styles are stored in the "Notes" Worksheet ' - you can now pass in different Styles for each Pivot Notes Header Column that you are using ' - these can be passed in using a colon-delimited list ie. "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7" ' - if you don't pass enough Styles for all of your Header Note Columns the code will use the first Style as the default ' - when changing this setting retrospectively, you may need to Index the Table again to pull in the New Style Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7" ' ## the Style(s) that you want to give your Pivot Notes - the Notes displayed alongside your Pivot Table ' - modify or create your own Styles and then pass the Style Name into the PivotNotesStyle setting ' - Styles are stored in the "Notes" Worksheet. you should always Copy this into a new Project ' - you can now pass in different Styles for each Pivot Notes Column that you are using ' - these can be passed in using a colon-delimited list ie. "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7" ' - if you don't pass enough Styles for all of your Note Columns the code will use the first Style as the default ' - when changing this setting retrospectively, you may need to Index the Table again to pull in the New Style Private Const PivotNotesStylePT1 As String = "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7"
You can change how you would like to Index your Pivot Tables and store Notes by a Variable in the Setup Section called IndexBehaviour. Here is the Variable with its default setting (you change the setting after the '.' full-stop or period symbol):
Private Const IndexBehaviour = IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexes
The 3 Options available are as follows every time that you Index a Pivot Table:
IndexBehaviourType.xlRemoveAllIndexesall Indexes & Notes will be removed - allows you to start keeping Notes again from scratch by deleting all of the Indexes and Notes
IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexesexisting Indexes will be retained and any new Indexes appended - DEFAULT: allows you to keep Notes forever, appending Notes regardless of whether they belong to the previous or current Pivot Table Source data
IndexBehaviourType.xlRetainSourceIndexesOnlyonly Indexes matching the Pivot Table Source data will be retained - allows you to only keep Notes that only belong to the current Pivot Table Source data - all older Notes will be deleted
Pivot Notes uses Styles for formatting Notes and is designed to allow 1 Colour per Tag per Column, but what if you want to highlight 2 or more different Tags? Well this is possible, but you need to make a couple of changes to the settings and add a snippet of Code. Here I will show you how to change the Code to have a standard light Grey Tag '>>', a Green Tag '#' and a Red Tag '!'. Please Note: these changes will apply across all Columns of Pivot Notes - you cannot simply just change the Cell Colour or formatting because the Styles are overwritten by the RenderNotes() Subroutine
Using the Example Code in Pivot Notes Version 5, you need to adjust the HighlightColour in the Setup Section to explicitly set the different Colours per Column for each Tag:
Private Const ConditionalFormatTag As String = ">>:>>:>>" Private Const HighlightColour As String = rgbGreyHighlight & ":" & rgbGreyHighlight & ":" & rgbGreyHighlight
rngFormatAddress.FormatConditions.Add Type:=xlTextString, String:="#", TextOperator:=xlContains With rngFormatAddress.FormatConditions(2) ' // only use if the Format requires Priority over all other existing Formats '.SetFirstPriority ' // each global highlight colours .Interior.Color = vbGreen ' // the default Font Colour .Font.Color = vbWhite ' // only set to True if you wish to stop after this Format. we will apply the default .StopIfTrue = False End With rngFormatAddress.FormatConditions.Add Type:=xlTextString, String:="!", TextOperator:=xlContains With rngFormatAddress.FormatConditions(3) ' // only use if the Format requires Priority over all other existing Formats '.SetFirstPriority ' // each global highlight colours .Interior.Color = vbRed ' // the default Font Colour .Font.Color = vbWhite ' // only set to True if you wish to stop after this Format. we will apply the default .StopIfTrue = False End With
You can move a Pivot Table that is using Pivot Notes anywhere in the Worksheet without changing any of the settings for Pivot Notes. To do this, click on any Cell within the Pivot Table and select the 'ANALYSE' Tab of the Ribbon. Click the 'Move PivotTable Button' in the 'Actions' Group. Select another Cell in the existing Worksheet and press 'OK'
The Pivot Table will move to the new Cell leaving the Notes on the Right-hand side of the Worksheet. Now right-click->Refresh anywhere on the Pivot Table. The Pivot Notes will be rendered next to your Pivot Table
Please Note: Pivot Notes may not clean up areas that overlap depending on where you Move the Table to (it may be that you are moving the Table somewhere within the Notes Range themselves) - if you see any old Notes, simply select them and clear the Formats. Refresh the Pivot Table again
Styles are at the heart of Pivot Notes for rendering Note Headers and Text. You can use my Pivot Notes Style Sheet to Copy Styles into your own Workbooks. This is particularly useful when setting up Pivot Notes from scratch for a new Pivot Table - it means that you don't have to Copy the entire 'Notes' Worksheet from PivotNotes.xlsm, you can just Copy the Styles that you need
Style Sheet.xlsx (11.7 KB)
When configuring Pivot Notes or if you are using Pivot Notes and wish to completely redesign your Pivot Table, Pivot Notes will send out lots of errors because it is constantly firing Code, trapping the Worksheet_PivotTableChangeSync() Event Handler. You can switch off Pivot Notes until you are ready to use it by adjusting the 'DebugMode' Setting in the Global Settings section of Setup in the Code to 'True'. This means that everytime an Event Handler is fired the Code will exit without throwing any error messages. The Setting is shown below:
Private Const DebugMode As Boolean = False ' set this to True when redesigning Pivot Tables using Pivot Notes
Pivot Notes can store and sort Formula for Pivot Table Fields and for Pivot Table data using normal Formula or the =GETPIVOTDATA() Function - just as it would if you were writing it in a Column next to a Pivot Table that was not using Pivot Notes. The main difference using Pivot Notes to store Formula is that when you refresh the Source data for a Pivot Table the Formula will persist next to the Pivot Table Row where the Note was stored. The only thing you need to be careful about is not to cause any stored Notes circular references. Let's add a simple Formula to reference the Customer Cell in the Pivot Table, remembering to lock the Column
Now we will Slice the Table for 2 Weeks and for MINERALS - the Formula still references the correct Customer, "Customer A"
This example luckily did not result in a stored Note circular reference. So what about something a little more complex and what about the circular references. Consider a Formula that counts the number of gaps for any number of Weeks Sales per Customer and idetifies whether they have traded or have been trading intermittently. I will enter the Formula below, locking the relevant Columns and copy it down the Range. Then I will remove the Formula for Sub Totals and Grand Totals. The Formula can be seen below (notice I have changed my Pivot Note Columns using a simple refresh)
If I select Week 38.2016 to Week 42.2016 for just 'Customer E' I now have a view of how each Customer is trading up until Week 42.2016 (there is no data after Week 42 yet in my Source data). You can see 'Customer E' is trading intermittently on 'Product B' and not trading at all on 'Product D'
I can now carry on leaving Notes - the Formula will persist. So, what about the circular references? Excel hasn't reported anthing unusual. Well if I click into the "Notes" Worksheet and double-click a Formula Cell, you can see that indeed the Formula does reference itself. Closing and re-opening the Workbook will cause a circular Formula warning which we don't want
So, how can we fix this. Well the answer is to count how many Columns the Pivot Table uses plus Column A and move our Notes at least that distance away in the "Notes" Worksheet. To start, ensure that you have no Filters on the Pivot Table. Go to the "Notes" Worksheet. Insert a Column and then press "F4" to repeat for as many Columns as you need (I did 18). Press ALT+F8 to enter the VBA Editor and change the Pivot Notes Index Cell to the start of your moved Notes ie. I changed mine to "S1". Now, the Formulas in the "Notes" Worksheet will have changed, but we have a neat trick to update them again. Go back to the Notes alongside your Pivot Table, press "F2" in one of the Notes and press "Enter". All of the Notes will update and we have fixed the circular Formula issue (remember as long as the Notes Formula do not refer to themselves the fact that they are calculating something different on the "Notes" Worksheet is irrelevant)
Please Note: when adding Data Validation as a List linking to a Range, unless you explicitly link the Range to the same Worksheet as your Pivot Table, then you MUST add the Range specifying the Name of the Worksheet like this:
=Sheet2!A1:A4 ' Excel may remove the Name of the Worksheet when it displays the Address, but it will be recognised correctly as a Range
Data validation is a feature available in Microsoft Excel. It allows you to do the following:
- Make a list of the entries that restricts the values allowed in a cell
- Create a prompt message explaining the kind of data allowed in a cell
- Create messages that appear when incorrect data has been entered
You can add Data Validation for Pivot Note Columns by adding them to one or more of your Note Headers in the "Notes" Worksheet. Here I will add a simple Drop-down list to pick a,b,c in a Cell by adding it to the "commercial" Note Header in my "Notes" Worksheet
Select the Notes Header "Commercial" in my "Notes" Worksheet
On the Data menu, click Validation
On the Settings tab, click List in the Allow drop-down list
By default, the Ignore blank and In-cell Dropdown check boxes are selected
In the Source box, type a,b,c
Now you need to go into the Code and set the UseDataValidation Variable to True
Private Const UseDataValidation As Boolean = True 'False
And here is the result of my Data Validation following a right-click, refresh of my Pivot Table:
If you want to change the Background Colour of a Worksheet you may find that using Pivot Tables remove the Colour when they resize leaving the default 'No Colour' displaying Gridlines. A good way around this is to use a Background Image
You just need to create a small '.JPG' image about 16 pixels by 16 pixels. I have one here if you want to download and use this one (open the image and use right-click to download or Save as...)
Then simply select the Worksheet that your Pivot Table is on and on the 'Page Setup' Group of the 'PAGE LAYOUT' Tab of the Ribbon click the 'Background' Button. Browse to where you saved the image and select it. The Worksheet will now change Colour to the same Colour as your image. If you have any Styles, these will always over-ride the image and in fact you can still use any kind of Fill to change the Colour of a Cell even with the Background using an image. Now try Slicing or resizing your Pivot Table - it will now have the image as it's background too, neat eh?
A great Feature with Pivot Notes is the ability to use multiple Styles for both the Note Headers and the Note Columns. To do this you simply create the Styles that you want to use for both the Headers and Note Columns and then pass the Names of the Styles as Colon-delimited lists for the Variables below:
Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head1:Pivot Notes head2:Pivot Notes head3" Private Const PivotNotesStylePT1 As String = "Pivot Notes col1:Pivot Notes col2:Pivot Notes col3"
Please Note: if you don't pass enough Styles for all of your Headers or Note Columns the code will use the first Style as the default for all of the Headers or Note Columns
To create a new Style, first Duplicate a Style by selecting a Blank Cell and then on the 'Styles' Group of the 'HOME' Tab of the Ribbon right-click any Style and select 'Duplicate...'. A Style Dialog will popup and you can set the Parameters for your new Style including a New Name for the Style - the New Name is the Name that you should add to the Variables above. Take a look at the "4.2" Worksheet and the "Notes" Worksheet as I have used the Styles created on the "Notes" Worksheet for the Pivot Note Headers and Note Columns
Remember you can have different Custom Formats for your Styles too, like Dates, Times etc.
You can use Tags to Highlight (by Conditionally Formatting) all or some of the Notes in each separate Column of Notes that you use. You can pass Colours to change the Highlight Colour. You setup how you want Pivot Notes to Highlight Notes by adjusting the Settings in the Global Setup Section of the Code. Tags and Colours should be passed separately and equally ie. If you have 3 Columns of Notes and you want a different Tag for each Column with a different Highlight Colour then you need to pass 3 Tags and 3 Colours like this:
Private Const ConditionalFormatTag As String = ">>:feedback:!" Private Const HighlightColour As String = "14479865:16183786:15921906"
This tells Pivot Notes to use 3 Tags and for each Tag to use 3 Colours. So the first Column of Notes will be Highlighted 'Yellow' if the ">>" Tag is included in a Note. The second Column of Notes will be Highlighted 'Light Blue' if the word "feedback" is included in a Note. The third Column of Notes will be Highlighted 'Grey' if the "!" Tag is included in a Note. If none of the Tags are used the Notes will not be Highlighted - see the image below
If you want to Highlight all Notes then you can pass the "*" (fuzzy) Tag
You can also use the built-in Enum Colour Codes:
Private Enum RGBLongColour rgbBrown = 3355443 rgbGrey = 15921906 rgbGreyHighlight = 15329769 rgbIceBlue = 16183786 rgbYellow = 14479865 End Enum ' // pass the Colour Codes like this Private Const HighlightColour As String = rgbGreyHighlight & ":" & rgbYellow & ":" & rgbIceBlue
Please Note: If the number of Colours or the number of Tags is less than the number of Notes Columns then the Code will use as many Colours & Tags that are passed for all of the Notes Columns
Q. Can I use Find & Replace with Pivot Notes?
A. Yes, you can do a Find & Replace to find and replace text within your Pivot Notes as you would normally with a Worksheet. When you do this, the replaced Notes are automatically updated
Q. Can I have 10 or 20 or 50 Columns of Notes?
A. There is no set limit. I have tested with 100 Columns of Notes for a small Pivot Table with no loss of speed. You can have multiple Columns of Notes that use different Styles for both the Headers and Note Columns
Q. Can I supress the Warning Message informing me that: 'There's already data in [Pivot Notes.xlsm]. Do you want to replace it?'
A. No, unfortunately this message is processed before any Code can manipulate it. It is the Pivot Table determining that it needs to resize itself to allow the correct amount of data to be displayed whenever Text is present next to a Pivot Table Field - simply accept the Message by clicking 'OK' and the Table will resize and your Pivot Notes will be rendered next to the newly resized Table
Q. Can I highlight Notes different Colours for specific Tags in all Columns?
A. Yes, you can only use 1 Tag and 1 Colour per Column though - simply pass in the relevant number of Tags and Colours, one of each for every Note Column in the Global settings for 'ConditionalFormatTag' and 'HighlightColour', like this:
Private Const ConditionalFormatTag As String = ">>:feedback:!" Private Const HighlightColour As String = "14479865:16183786:15921906"
Public Function AddValues(cell1 As Long, cell2 As Long) As Long AddValues = cell1 + cell2 End Function
' ## the Style that you want to give your Pivot Notes - the Notes alongside your Pivot Table Private Const PivotNotesStylePT1 As String = "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7" ' ## the Style that you want to give your Pivot Notes Headers - the Headers for the Notes displayed alongside your Pivot Table Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7"
Private Const IndexDelimiterPT1 As String = "~"
Pivot Notes showing a single Product Filtered by the 'Material' Pivot Table Field and Collapsed with a Note left underneath the 'Customer Information' Pivot Note Header - this is an aggregated Note showing you what you can do if you want to store Notes at different levels, of course you may only want to store Notes at a single level (this Pivot Table has been Indexed by the 'Material' and 'Key Figure' Pivot Table Fields):
Pivot Notes showing a single Product Filtered by 'Material' and Expanded with Notes left next to the 'Total Sales', 'Key Figure' Pivot Table Field underneath the 'Customer Information' and 'Commercial Team' Pivot Note Headers:
Pivot Notes Unfiltered and showing additional Calendar Weeks of data picked by the Slicer Control. In this Screen Shot the Demand Team have left a Note against Product 17468 underneath the 'Demand Team' Pivot Note Header indicating that there are no sales for the Product against the 'Final Forecast' Pivot Table Field:
Support is only given to Purchased Software. You should only contact me on the Support Email address supplied by FastSpring - please provide proof of purchase ie. Email address or Order Reference
25.05.2017 - (Version 5)
a) rebuild the Pivot Notes Workbook to be 1 Example Pivot Table Worksheet, 1 Notes Worksheet containing Styles and 1 Data Worksheet this is an effort to makes things more straightforward and ease future issues as I will only be updating 1 Source File
b) go through the Code and Comments tidying up anything that does not make sense or should not be there!
c) add the ability to use Hyperlinks. this is done via a small routine inserted into the RenderNotes() Subroutine. a user may also use a Hyperlink as the Style so that the Cell resembles a Hyperlink - drawback will be a degradation in speed for large Pivot Tables
08.02.2017 - (Version 4.2 & Version 4.2.1 - bug fix)
a) Change to allow Pivot Notes to use a different Style for each Note Column and/or Note Header if required. This is useful not just for visual appearances, but also for Custom formats for Dates, Times etc. Note and Header Style Names are passed using colon-delimited lists
b) Code to add/remove Date/Time Stamps en masse to a separate Column and to lock a specific Column has been added to the Worksheet_Change() Event Handler - this is commented out and can be used by uncommenting the relevant code part(s) as required
c) Add in the ability to fix the Header Row Height. this is useful to emphasise the Headers by making them taller
d) Add in the ability to use Data Validation through the use of a Global Variable. Data Validation should be added to any of the Headers in the "Notes" Worksheet - this will then be transposed down the respective Column next to the Pivot Table. Data Validation can be any type of validation and can include Formula lists from another part of the Workbook Other features of Data Validation can be used ie. when you click into a Cell in the Column a Message can be displayed to the user or you could restrict data input
e) Add in a Global Enumeration Variable to control the Indexing behaviour whenever the Pivot Table Source data requires refreshing:
i) xlRemoveAllIndexes = all Indexes & Notes will be removed
(allows you to start keeping Notes again from scratch)
ii) xlRetainOldIndexesAndAppendNewSourceIndexes = existing Indexes will be retained and any new Indexes appended
(DEFAULT: allows you to keep Notes forever, regardless of whether they belong to the previous or current Pivot Table Source data)
iii) xlRetainSourceIndexesOnly = only Indexes matching the Pivot Table Source data will be retained
(allows you to only keep Notes that only belong to the current Pivot Table Source data)
f) Speed increase for larger tables when updating Notes. Now only Dictionary Indexes for the Target area of Notes are built which are queried against a Notes Dictionary of Indexes - if they exist then only the Taret area of Notes are updated