Pivot Notes allows you to store multiple Columns of Notes and Formula alongside one or more Tabular, Outline or Compact Pivot Tables in the same Worksheet or separate Worksheets and use the +/- Buttons to Expand or Collapse separate hierarchy's of Note Columns. You can Filter or Sort Pivot Items and the Notes will persist. If the Pivot Table Source data changes, you can Index the Table to store new Notes whilst retaining any existing Notes. You can have multiple Columns of Notes for multiple Pivot Tables or specify a single Pivot Table to use in a Worksheet. Pivot Notes allows you to dynamically add and remove Fields from the Pivot Table (as long as those Fields are not the Index)
NEW - Version 4.2 includes some great new features (RELEASED 08 February 2017). Most noticeable, in the first image above is the ability to use different Styles for each Pivot Note Column and Header. You can also now add Data Validation to the Pivot Note Columns, for example you can display a Message to the user whenever the Cell is entered or add a Drop-down list to a Cell for the user to pick a particular Item. The additional "4.2" Worksheet uses an image for the Background demonstrating how you can use images with Pivot Tables / Pivot Notes - the Worksheet also contains an alternative Pivot Table Style for you to use. I have added a Quick Implementation article to the Help File along with Help on all of the features mentioned
The Indexing has been tweaked again to allow 3 different Indexing options, including:
i) remove all existing Notes and Indexes prior to Indexing. Indexes will be rebuilt from scratch from the Source data
ii) (DEFAULT) retain all of the existing Notes and create any new Indexes. New Indexes will be appended from the Source data
iii) retain all of the existing Notes but remove any Indexes and Notes that are no longer relevant to the Source data
Lastly I managed to et the time to rewrite part of the UpDateNotes() Routine to dramatically speed up the Saving of Notes when using very large Pivot Tables. 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 Target area of Notes are updated
I hope you all love these new features and my thanks go out to the people who have contributed to making this Software better with their comments and ideas...
WHAT'S NEXT? - for Version 4.3, I am going to revisit the RenderNotes() Routine to see if I can optimise the Code further
- 2 bespoke Pivot Table Styles that you can Copy and apply to any of your own Pivot Tables using the 'PivotTable Styles' Group of the 'DESIGN' Tab of the Ribbon
- filter, sort & slice multiple columns of Pivot Notes alongside Pivot Tables using Tabular or Outline layouts
- easy setup variables, self-contained code resides in the same Worksheet as the Pivot Tables
- Pivot Notes automatically Indexes Subtotals & Grandtotals
- insert / remove blank lines after each Item
- maintain & sort Formulas alongside the Pivot Table
- conditionally format Pivot Note Columns or tag individual Pivot Notes to highlight specific Notes in specific Columns
- use repeated item labels with a conditional format to hide duplicates, in essence allowing you to index by a non-unique Field and a hidden Field to create a unique index
- re-design your Pivot Table in real time, add / remove fields in the Pivot Table (may require indexing)
- add Pivot Notes to multiple Pivot Tables in the same Worksheet with separate Pivot Note lists
- index single or multiple Pivot Fields by their Field name
- move the Pivot Table & retain existing Pivot Notes using right-click, refresh
- pull in new Pivot Table source data & retain existing Pivot Notes (requires indexing)
- use the +/- Buttons to store Pivot Notes on multiple levels (Nodes) within the Pivot Table
- format multiple Pivot Note & Pivot Note Headers using different Custom Styles
- autofit or manually fix Pivot Notes Column Widths & Row Heights when rendering Pivot Notes (also tidies up moving Pivot Notes Column Range widths)
- use Wordwrap within Custom Styles to wrap the Pivot Notes or Pivot Note Headers
- Group Items & store Pivot Notes at the Grouped levels
- store Pivot Notes longer than 255 characters
- use Pivot Notes with the Data Model
- use Find & Replace on Pivot Notes
- Use Data Validation to display Messages or Select from Drop-down lists
- choose how you want to Index your Pivot Table Source data using an IndexBehaviour Variable
- use a background image with Pivot Notes
WINDOWS 7/10, Excel 2010 or Excel 2013 and a Pivot Table - this Product has been specifically designed for Excel 2013. Please Note: this Software does NOT work on an Apple MAC
What you get in the Download
Excel File: PivotNotes.XLSM
4.2 Worksheet: All of the latest features demonstrated in a new Verison Worksheet including using multiple Styles for Pivot Note Columns & Headers, Data Validation, Indexing behaviour and the ability to use a background image with Pivot Notes
Example1 Worksheet: Pivot Notes added to a single Pivot Table with 3 Columns of Notes using Tags to highlight specific Notes
Example2 Worksheet: Pivot Notes added to Multiple Pivot Tables
Example3 Worksheet: Pivot Notes Indexed by a single unique Field using Non-repeated Items
Example4 Worksheet: Pivot Table Outline layout Indexed by 2 Fields detailing how you can ONLY leave Notes against these levels
Example5 Worksheet: Pivot Table Compact layout Indexed by 1 Field detailing how using Pivot Tables aggregation allows you to leave Notes against all levels even with a non-unique second Field
Pivot Table Data Worksheet: containing the Tables used by all of the examples
Notes Worksheet: includes the Notes and Styles used by all of the examples
Note Colours Worksheet: includes examples of Pivot Notes Colours including Long Colours and RGB Codes
License Worksheet: licensing for Pivot Notes
PIVOT NOTES ONLINE HELP
Marks Quick Pivot Notes Implementation
How to Add Pivot Notes to your Pivot Table (3 Steps)
Pivot Notes Settings
How to Add a Formula that can be Sorted using Pivot Notes
How to Add Data Validation for Pivot Note Columns
How to Change the Indexing Behaviour when Indexing a Pivot Table Source
How to Use a Background Image with Pivot Notes
How to Move a Pivot Table in the same Worksheet using Pivot Notes
How to use Pivot Notes with Multiple Pivot Tables in the same Worksheet
How to use Custom Styles with Pivot Notes
How to use the New Multiple Styles with Pivot Note Headers & Columns
How to Pick the Index Fields to Index the Table
How to Get the Field to Index when using the Data Model with Pivot Notes
How to Tag & Highlight Notes
How to Highlight Notes in 2 Colours in a Column
How to Index a Non-unique Field by Conditionally Formatting & Hiding Repeated Item Labels
How to Update the Pivot Table Data Source
How to use Pivot Notes in Debug Mode
How to Filter Note Columns
How to Increase the Pivot Notes Font Size
How to Adjust Pivot Notes Row & Column Sizes
How to Add a Date/Time Stamp when someone enters Text into another Column
How to Lock a Column to prevent Cell Contects from being Edited or Deleted Altogether (Particularly useful for a Date/Time Stamp)
The Note Colours Worksheet
- Can I use Find & Replace with Pivot Notes?
- Can I have 10 or 20 or 50 Columns of Notes?
- Can I supress the Warning Message informing me that: 'There's already data in [Pivot Notes.xlsm]. Do you want to replace it?'
- Can I highlight Notes different Colours for specific Tags in all Columns?
- Can I use a UDF in one of the Note Columns like you would a Formula?
- Can I use Pivot Notes on a Pivot Table designed in "Compact" View?
- Can I Group Items and still use Pivot Notes?
- Can I change the formatting for the Pivot Notes & Pivot Notes Headers?
- Can I Hide the "Notes" Worksheet?
- I have Indexed my Pivot Table, but my Pivot Note does not appear to be stored?
- What options do I have to Email the File out and get people to update Notes or maintain the Notes myself for other peoples changes - can you explain how I can use Pivot Notes in the real world?
Marks Quick Pivot Notes ImplementationBack to Online Help
Here is how I always setup Pivot Notes when I want to add it to a new or existing Workbook:
1) Add a new Worksheet and name it "Notes"
2) Copy only the Styles that I require from the PivotNotes.xlsm File and Paste these into the same "Notes" Worksheet that I created (I may delete, tweak or change these later)
3) Copy & Paste the Code from PivotNotes.xlsm "Example1" Code Module into my Worksheet Code Module
4) Adjust the Setup section for the Pivot Table Name, required Indexes, Header and Note Column Styles plus any other settings that I may want to tweak
5) Right-click, refresh my Pivot Table, Filter a Field or Slice the Table to render everything correctly
6) Index the Pivot Table by pressing ALT+F8
How to Add Pivot Notes to your Pivot Table (3 Steps)Back to Online Help
- Step 1: Copy in the Notes Worksheet, Pivot Note Styles & Code (this example uses the Files: Pivot Notes.XLSM & Book1.XLSM with sample data from www.contextures.com)
When you have designed your Pivot Table, save the Workbook as an .XLSM or .XLSB File
Open Pivot Notes and accept the License terms
Select the "Notes" Worksheet. Right-click and choose 'Move or Copy...'
Select your Workbook as the 'To book:'
Select '(move to end)' for the 'Before sheet:'
Tick 'Create a copy' and click the 'OK' Button to Copy in the "Notes" Worksheet and Pivot Styles
Switch back to Pivot Notes and press ALT+F11 to enter the Code Editor (you should see 2 Workbooks, your own one and Pivot Notes)
In the 'Example1' Code Module, select all of the Code by pressing CTRL+A
Expand the Code Editor for your Workbook and Paste the Code into your Code Module for the Worksheet that you have your Pivot Table in
- Step 2: Setup the Notes Worksheet & Adjust the Settings in SETUP
In your Workbook, click on the "Notes" Worksheet that you copied earlier and clear down the example Notes from underneath the Headers (don't remove the Styles). Enter your Note Headers - you can see that I entered 4 Headers below: Index, Notes1, Notes2 & Notes3 (remember to leave a gap of at least one Column after your Note Headers and that 'Index' is your first Header)
Press ALT+F11 again to enter the Code Editor
Scroll down to the 'SETUP' section of the Code and change the Name of your Pivot Table if it is not called 'PivotTable1'
Change the Pivot Table Worksheet Name to the Name of the Worksheet that your Pivot Table is in if it is not called "Example1" - I changed mine to "Pivot Table" below because that is the Name of the Worksheet where I just created my Pivot Table
Change the Pivot Table Field Names for the Index - I changed mine to "OrderDate:Region:Rep:Item" because I want to create an Index that allows me to leave Notes at all or any of these levels
The best way of ensuring that the Names are correct is to click on each Field Header in your Pivot Table and then Copy the Name in the Formula bar like the image below. Pivot Notes Indexing always works best when you do it left to right, however, you can pick any Fields in different orders and they do not have to be contiguous (touching)
- Step 3: Index and start using Pivot Notes
Switch to the Worksheet containing your Pivot Table and press ALT+F8
Run the 'Sheet1.IndexPivotTablePT1' Macro as shown in the image below
A Message will pop up informing you of what the Code is about to do - check the information and then click OK (you can Click Cancel if the information displayed does not look correct for the Pivot Table that you are about to Index)
Pivot Notes will now Index the Pivot Table and render the Note Headers / Styles to the Screen as shown by the image below. If you have any stored Notes, these would now sit alongside the Pivot Table. Notice how the defaults in the SETUP section also render the Pivot Table Rows and the Pivot Note Column sizing
That's it, you are good to go. Begin to add some Notes. Remember Pivot Notes can store Notes at all +/- Node levels (if this gets confusing for the type of data you are using or is not required, then you can turn off the toggle Buttons). Here are some Notes that I have added at various levels including the lowest level. By adding the ">>" Tag to any Note, I can also highlight Notes that I want to stand out
Pivot Notes can easily Sort Filtered / Unfiltered Notes alongside the Pivot Table. Here I applied a Filter to 'Region' for "East" and a Filter to 'Rep' for "Jones". Then I Sorted the list by Newest to Oldest Dates for the 'OrderDate' Field. Check out many more Features by following the rest of this article!
Pivot Notes SettingsBack to Online Help
Here are all of the Pivot Note Local Settings - these apply for each Pivot Table in a Worksheet using Pivot Notes:
' ## 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 = "Example1"
' ## the Name of the Worksheet where your Pivot Notes will be stored ' remember you can hide this Worksheet, 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 should always Copy this 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 Description:Customer"
' ## 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 that you want to give your Pivot Notes Headers - the Headers for the Notes displayed alongside your Pivot Table ' Styles available in the Pivot Notes Example Workbook include: ' "Pivot Notes Header" ' "Pivot Notes Bold Header" ' "Pivot Notes Border Header" ' tip: modify or create your own Styles and then pass the Style Name into the PivotNotesHeaderStyle setting ' - Styles are stored in the "Notes" Worksheet. you should always Copy this into a new Project ' Please Note: 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 Dark Blue Border Header"
' ## the Style that you want to give your Pivot Notes - the Notes alongside your Pivot Table ' Styles available in the Pivot Notes Example Workbook include: ' "Pivot Notes" ' "Pivot Notes Highlight" ' tip: 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 Private Const PivotNotesStylePT1 As String = "Pivot Notes"Global
Here are all of the Pivot Note Global Settings - these apply for all Pivot Tables in a Worksheet using Pivot Notes:
' ## this setting will mean that any Filtered Pivot Fileds will be Unfiltered prior to Indexing. By default this is switched off to prevent expansion across Columns ' - Remember Pivot Notes will Index a Pivot Table in the state it is in if left as False. Set to True to force Pivot Notes to always Unfilter Filtered Fields ' including any Sliced Fields Private Const ClearAllFiltersOnIndexing As Boolean = False 'True
' ## the Pivot Note Column Adjustment & width settings. this controls how Pivot Notes sizes the Columns when the Pivot Notes are rendered ' - Column Adjustments are applied after a Pivot Table action so refresh your Table after changing this setting! ' - Pivot Note Styles control normal Note entry behaviour ' this constant can be set to one of the following: ' PivotNoteColumn.xlAutoFitColumns = 1 := autofit the Pivot Note Column ' PivotNoteColumn.xlFixColumns = 2 := fix the Pivot Note Column to the widths in the PivotNotesColumnWidth setting ' - the PivotNotesColumnWidth is ignored if you select PivotNoteColumn.xlAutoFitColumns Private Const PivotNotesColumnAdjustment = PivotNoteColumn.xlFixColumns
' pass in your Column Widths as a colon-delimited list for as many Pivot Note Columns that you have ' - this allows you to have different widths for multiple Pivot Note Columns ' - if you do not pass enough Column Widths to match the number of Note Columns then the first Column Width is applied to all Columns ' right-click, refresh to affirm these Column Widths for previously stored Notes having the same number of Notes Columns Private Const PivotNotesColumnWidth = "24.14:24.14:24.14"
' ## the Pivot Note Row Adjustment & height settings. this controls how Pivot Notes sizes the Rows when the Pivot Notes are rendered ' - Row Adjustments are applied after a Pivot Table action so refresh your Table after changing this setting! ' - Pivot Note Styles control normal Note entry behaviour ' this constant can be set to one of the following: ' PivotNoteRow.xlAutoFitRows = 1 := autofit the Pivot Note Row ' PivotNoteRow.xlFixRows = 2 := fix the Pivot Note Row to the height in the PivotNotesColumnHeight setting ' - the PivotNotesColumnWidth is ignored if you select PivotNoteColumn.xlAutoFitColumns Private Const PivotNotesRowAdjustment = PivotNoteRow.xlFixRows '.xlAutoFitRows Private Const PivotNotesRowHeight = "24"
' ## these are the Pivot Notes Conditional Format Tags. set these to characters or full words or a series of words! ' right-click refresh will update the Highlighted Colours for the Tags ' if you set any Note Columns to "*" (a fuzzy) then all Pivot Notes are highlighted within the Column ' - ie. "*:*:*" means highlight anything entered into 3 Columns of Notes by the Highlight Colours passed below ' if you set this to a Tag ie. ">>", then only Pivot Notes with a >> in the Text are highlighted ' - ie. ">>:>>:>>" means highlight anything containing a ">>" Tag entered into 3 Columns of Notes ' ">>:feedback:cust info" will highlight ">>" for the first Note Column, "feedback" for the next Note Column & "cust info" for the third Note Column ' - remember you can have different Colours for different Tags ' - if the number of Colours or 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 - see also, the HighlightColour setting below Private Const ConditionalFormatTag As String = ">>:feedback:!"
' ## these are the Pivot Notes Highlight Colours. set these to a Long Colour numbers or use one of the built-in Enum Long Colours below ' right-click refresh will update the Highlighted Colours for the Tags ' you can find out a long number from an RGB Colour by entering the following into the immediate Window ' ?RGB(249, 241, 220) ' 14479865 ' ...or on the "Font" Group of the "HOME" Tab of the Ribbon, choose "More Colors..." from the drop-down of the Fill Colour option ' here, you can see the RGB Colours on the "Custom" Tab of the "Colours" Dialog. you can also use pre-defined Excel Colours ie. vbRed, vbBlue etc. ' // Pivot Notes Colours, sorted Alphabetically - see "Note Colours" Worksheet ' rgbBrown = 3355443 ' rgbCreamy = 15264490 ' rgbDarkGrey = 14408667 ' rgbGreenThing = 12895414 ' rgbGrey = 15921906 ' rgbIceBlue = 16183786 ' rgbLeafGreen = 15332594 ' rgbLightGreen = 15987947 ' rgbLilac = 15258587 ' rgbMediumGreen = 14343891 ' rgbPinkThing = 13155536 ' rgbPlumThing = 11703473 ' rgbSilver = 15329508 ' rgbWarmBlue = 16247773 ' rgbYellow = 14479865 ' - remember to complete a Pivot Table Action for the changes to take effect ie. Sort, Filter, Slice the Pivot Table ' - remember you can have different Colours for different Tags ' - if the number of Colours or 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 - see also, the ConditionalFormatTag setting above Private Const HighlightColour As String = "14479865:16183786:15921906" 'rgbYellow & ":" & rgbIceBlue & ":" & rgbGrey
' ## this is the Pivot Notes Font highlight Colour ' - I have only allowed a single Font Colour to be passed, so ensure this looks nice with your Highlight Colours ;) Private Const HighlightFontColour As String = rgbBrown 'rgbIceBlue, rgbWhite
' ## this setting allows you to redesign your Pivot Table with Pivot Notes in situ by switching out of all Event Handlers that Index and / or fire the Pivot Note Subroutines ' - set this to True and the Event Handlers & Index Subroutine will not run. the default is False - run all of the Event Handlers & Index Subroutine Private Const DebugMode As Boolean = False
How to Move a Pivot Table in the same Worksheet using Pivot NotesBack to Online Help
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
How to Add a Formula that can be Sorted using Pivot NotesBack to Online Help
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)
How to Add Data Validation for Pivot Note ColumnsBack to Online Help
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 RangeData 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
And here is the result of my Data Validation following a right-click, refresh of my Pivot Table:
How to Change the Indexing Behaviour when Indexing a Pivot Table SourceBack to Online Help
You can change how you would like to Index your Pivot Tables and store Notes by a Vaiable in the Setup Section called IndexBehaviour. Here is the Variable with it's default setting (you change the setting after the '.' full-stop or period symbol):
Private Const IndexBehaviour = IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexesThe 3 Options available are as follows every time that you Index a Pivot Table:
i) IndexBehaviourType.xlRemoveAllIndexes = all Indexes & Notes will be removed
- allows you to start keeping Notes again from scratch by deleting all of the Indexes and Notes
ii) IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexes = existing 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
iii) IndexBehaviourType.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 - all older Notes will be deleted
How to Use a Background Image with Pivot NotesBack to Online Help
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...). This is the one I use for the "4.2" Worksheet.
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? ;)
How to use the New Multiple Styles with Pivot Note Headers & ColumnsBack to Online Help
A great new Feature with Pivot Notes 4.2 onwards 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.
How to use Pivot Notes with Multiple Pivot Tables in the same WorksheetBack to Online Help
Using Pivot Notes with Multiple Pivot Tables is easy but requires some Coding effort on your part! It requires you to relicate some settings, the Index Subroutine and Code in 2 Event Handlers. Basically for every Pivot Table you need a duplicate range of settings and Code that have an incremental suffix ie. instead of ending in 'PT1' the new settings and Code will end in 'PT2' for a second Pivot Table and end in 'PT3' for a third Pivot Table etc. This article will now step through adding a second Pivot Table - it assumes that the first Pivot Table has been setup correctly and that a second Pivot Table has been designed in the same Worksheet. Here is my Worksheet - I want to add Pivot Notes to 'PivotTable2':
I always start by adding my Note Headers in the "Notes" Worksheet. I will add them after my last set of Pivot Note Headers. I will add 'Index' and 'Anticipated Price Increase' where I want to keep Notes of forthcoming price increases for a variety of Drugs - see the image below
Now I will create a second set of settings. Press Alt+F11 to enter the VBA Editor, ensure that you are in the Code Module that already contains Pivot Notes Code for the first Pivot Table and scroll down until you find the ' ## PT1 ##' Header. Copy all of the settings until you reach the ' ## PT2 ##' Header and then Paste the data below this Header like the image below:
Select the data that you just Copied. Now press CTRL+F and do a 'Replace All' on PT1 for PT2, checking the 'Selected Text' checkbox and using 'Match Case'. You should see all of your new settings change to the suffix 'PT2'
Now change any settings for the new Pivot Table including the new Pivot Table Fields that you want to Index. Make sure that you change the 'NotesIndexCellPT2' setting to the new Cell for the 'Index' Note Heading in the "Notes" Worksheet. Now scroll right down the Code until you get to the Worksheet_Change() Event Handler. Copy the Code from the 'Case PivotTableNamePT1' down to 'End If' and Paste it underneath the ' // REPLICATE' Heading. Do the same 'ReplaceAll' for 'PT1' to 'PT2' as the image below:
Scroll down to the Worksheet_PivotTableChangeSync() Event Handler and repeat this process by Copying the Code from the 'Case PivotTableNamePT1' down to 'Application.ScreenUpdating = True' and Paste it underneath the ' // REPLICATE' Heading.
Before we are finished we just need to Copy the entire IndexPivotTablePT2() Subroutine doing a ReplaceAll on 'PT1' to 'PT2' again as the image below
Okay, so now we are finished. Ensure that your Pivot Table Fields are correct and in the same Worksheet as your second Pivot Table, press Alt+F8 and run the IndexPivotTablePT2() Macro to Index the second Pivot Table. Check the parameters displayed in the Message. If they do not look correct, press Cancel and adjust the settings again. Otherwise, press 'OK'
You may get a Warning Message that Repeated Items have been switched on at the end of the process and you will notice that the Notes Headers have been rendered already:
Now you can begin to add Notes to the new Table:
Global settings are applied to all Pivot Tables using Pivot Notes in the Worksheet ie. Conditional Format Tags, Note Highlight Colours and Columns & Row adjustments
How to use Custom Styles with Pivot NotesBack to Online Help
Pivot Notes uses Styles for all of its formatting apart from the Conditional Formatting used to highlight Notes. Pivot Notes has some Modified Styles that you can use to Style the stored Notes, the Pivot Notes Header and the Pivot Notes. These are shown on the "Styles" Group on the "HOME" Tab of the Ribbon.
Pivot Note Styles are kept in the "Notes" Worksheet. Clicking on one of these will show you which one you are using on the Ribbon. You will notice I have included a couple with 'Currency' and 'Percentage' formatting - you can still use these with Text, but if you add currency or a percentage this will be formatted correctly
You can modify these Styles or duplicate the Style and then create your own Styles to use. If you want to Copy a Style to use in your own Project then simply Copy & Paste the Style from one Workbook to another or you can Copy in the Worksheet "Notes" from the PivotNotes.xlsm Workbook into your own Workbook which will then include all of the Pivot Note Styles. To duplicate a Style, right-click on the Style and select "Duplicate...". To then modify a Style that has been duplicated, right-click on the Style and select "Modify..." giving the Style a sensible new Name to distinguish it from the original that you copied. Now just make your changes ie. adjust the Font, Alignment, Fill etc. and use the new Style Name in the Setup Variables to style your Pivot Notes, Pivot Note Header and Note Styles. To find out the Name of the Style to use with Pivot Notes, you can Hover over a Style or you can right-click on the Style and select "Modify..." - the Style Name will be shown at the top of the Style Dialog next to the 'Style name:'. Tip: you can Copy this Text and Paste it straight into any of the Pivot Notes Variables in the Setup section of the Code that require a Style. Please Note: you can also use any of the in-built Excel Styles as formatting for Pivot Notes.
How to Pick the Index Fields to Index the TableBack to Online Help
Choosing how to Index the Pivot Table is very important. You need to remember that in order for Pivot Notes to store Notes it MUST create a unique Index otherwise chaos will ensue - Notes will appear to move around or just not be stored at all.
You need to either pick a Field that is already UNIQUE or you need to create the Unique Index by using one or more Fileds from your Pivot Table Source - for Pivot Tables not using the data model, these are the Fields that are displayed in the Pivot Table 'Field List' the popout Menu on the right-hand side of Excel:
The combination of the Fields that you pick will be used to store, update and render Notes, so it is vital to get this part correct.
Product A/Customer A/
Product A/Customer B/
Product A Total//
Product A Total/
Think about how you or your users want to use the Pivot Table that contains your Notes. Do you or your users they need to see Notes on different levels? If not, then try to Index with a single unique Field, turn off the +/- Buttons and Repeated Items - this will give you a nice, easy-to-read Pivot Table view
You can view the way that the Index is created in the "Notes" Worksheet. This will also show you if you have Indexed the Pivot Table correctly - here is what my "Example1" Index looks like allowing me to leave Notes at all levels of Material and Customer, for Material Sub Totals and Grand Total:
How to Tag & Highlight NotesBack to Online Help
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 ie. 'rgbChathamsBlue, 'rgbBarberry', 'rgbCinder'
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
You can use the following Function to write the long Colour Code of any RGB Code to the Immediate Window:
Private Sub PrintLongRGB() ' enter your RGB Colour Code & press F5 ' use View->Immediate Window to see the Long equivalent Dim col As Long col = RGB(255, 185, 1) Debug.Print col End SubYou can also find out a long number from an RGB Colour by entering the following directly into the immediate Window:
?RGB(249, 241, 220) 14479865Another way is on the "Font" Group of the "HOME" Tab of the Ribbon, choose "More Colors..." from the drop-down of the Fill Colour option - here, you can see the RGB Colours on the "Custom" Tab of the "Colours" Dialog. You can also use pre-defined Excel Colours ie. vbRed, vbBlue etc.
Some default colours used by Pivot Notes:
Ice blue: 16183786
How to Highlight Notes in 2 Colours in 1 ColumnBack to Online Help
Pivot Notes is designed to allow 1 Colour per Tag per Column, but what if you want to highlight 2 different Tags in the same Column? 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 Green, Good Tag '>' and a Red, Bad Tag '!' in the same Column (in fact in all 3 Columns).
Change the Settings for the Format Tag and the Highlight Colour as below - this is the GREEN COLOUR PART:
Private Const ConditionalFormatTag As String = ">:>:>" Private Const HighlightColour As String = vbGreen & ":" & vbGreen & ":" & vbGreenAnd then add the Code below underneath the first Format in the RenderNotes() Subroutine - this is the RED COLOUR PART:
' // ... ADD THE CODE BELOW 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 = 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 WithNow you can add both Tags to highlight Colours for Green and Red in all Columns:
How to Update the Pivot Table Data SourceBack to Online Help
Using Tables with Pivot Table Source Data means that you do not have to use dynamic Defined Names to capture Ranges as the Table expands for any new data. Here I have added a new Row to my Data Source Table that I want to show in my Pivot Table using Pivot Notes:
Whenever your Pivot Table Data Source needs updating, you should refresh it by selecting any Cell in the Pivot Table and using right-click 'Refresh' or from the "Data" Group of the "ANALYZE" Tab on the Ribbon, select 'Refresh' or 'Refresh All'
Then you should Index the Pivot Table again so that Notes can be left for any new data levels by pressing ALT+F8 and running the IndexPivotTablePT1() Subroutine (where the Suffix may be "PT2" or "PT3" etc. for Multiple Pivot Tables. Here are the results after refreshing and Indexing my Pivot Table for the single new line of data:
Q&ABack to Online Help
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. The "4.2" Worksheet in PivotNotes.XLSM File demonstrates multiple Columns of Notes and uses many 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"
This means highlight the first Column when ">>" characters are found yellow, highlight the second Column when the word "feedback" is found in light blue and finally highlight the third Column grey when a "!" character is found
Q. Can I use a UDF in one of the Note Columns like you would a Formula?
A. Yes, you can but you can only calculate what is in one of the other Columns. This is because when the Notes are rendered the UDF's are recalculated which is fine, but if, for example, you were trying to store a Date/Time Stamp this would be recalculated on the Notes Sheet every time. There is a way around Date/Time Stamps whereby you can use some small Code - see the online Help for more Information. Anyhow to allow a UDF simply reference it using a Formula. Suppose that I had 5 Columns of Notes from "N:R". In Cell "R13" I add the Formula:
=AddValues(N13,O13)And then add the AddValues() UDF in a new Code Module (not a Worksheet Code Module) like this - the UDF takes 2 Long Parameters and returns a Long:
Public Function AddValues(cell1 As Long, cell2 As Long) As Long AddValues = cell1 + cell2 End FunctionNow the result in "R13" will be 3 if "N13" + "O13" are 1 and 2 respectively. This Formula including the UDF will be stored in the Notes Worksheet and then rendered next to the Pivot Table. You can then Sort, slice and dice the Table as you want and the values will be correct
Q. Can I use Pivot Notes on a Pivot Table designed in "Compact" View?
A. Pivot Notes is designed for 'Tabular' and 'Outline' Table layouts. You can ONLY use Pivot Notes on a Pivot Table in "Compact" View if all of your Items are unique which would be extremely unlikely but may happen depending on the design of your Pivot Table
Q. Can I Group Items and still use Pivot Notes?
A. Yes you can but if you want to STORE NOTES at the new Grouped levels, then you will have to re-index your Pivot Table after the Grouping, so it is wise to Group if possible from the outset as you may lose Notes because they will be on different levels. Right-click on the Pivot Table Items and select "Group". Rename the Items how you want them grouped. You can see that I have grouped two lots of Materials already in the image below and that my Notes have not yet been rendered - Excel has renamed the Grouped Field as 'Material2':
Once you have grouped your Items (I called my groups, Monitoring, NPD's & Phaseout), re-index your Pivot Table ensuring you add the new Field into the Pivot Notes Field Names setting:
Q. Can I change the formatting for the Pivot Notes & Pivot Notes Headers?
A. Yes you can do both by adjusting the Settings in the Setup section of the Code. You need to use a Style, either a Custom Style or one of the pre-defined Excel Styles. You can only set the entire Range style for the stored Notes or Pivot Notes (for individual/selective Note formatting it is suggested you use the Conditional Formatting Tag).
' ## the Style that you want to give your Pivot Notes - the Notes alongside your Pivot Table Private Const PivotNotesStylePT1 As String = "Pivot Notes" ' ## 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 Dark Blue Border Header"Q. Can I Hide the "Notes" Worksheet?
A. Yes you can. Right-click on the Tab and select "Hide". If you want to make the Worksheet very hidden you can do this too - in the VBA Editor set the Sheet Code Module to 'xlSheetVeryHidden'. All changes use Range Objects and do not rely on selecting Ranges.
Q. I have Indexed my Pivot Table, but my Pivot Note does not appear to be stored?
A. You need to ensure that your Indexes are UNIQUE, for example you may have an Index like: "Total Sales/" in your Pivot Notes once but in your Pivot Table more than once, meaning that any subsequent occurance would never be stored. To avoid this you need to select Report Layout->Repeat All Label Items in the "Layout" Group on the "DESIGN" Tab of the Ribbon and Index your Pivot Table again
Other things to check are:
1. That you are not using a Date in a Pivot Table Field like: '14/03/2015' which may include the '/' backslash character with the '/' Delimiter character in the Setup Code: 'IndexDelimiter1' as the index will not be split correctly. If this is the case, then change the Delimiter to a Tilde '~' like this:
Private Const IndexDelimiterPT1 As String = "~"2. That you haven't changed the Delimiter and then Indexed the Table without removing the original Indexes - this will cause multiple Indexes to be created and your Notes may include a previous Delimiter and therefore will never show next to the Pivot Table. It is advisable, that following an adjustment to the Delimiter that you remove the original Index
Q. What options do I have to Email the File out and get people to update Notes or maintain the Notes myself for other peoples changes - can you explain how I can use Pivot Notes in the real world?
A. You have a couple of options when you would like to maintain Pivot Notes for changes where multiple people are involved. Here are the options:
1. You maintain a MASTER File and update this yourself based upon other peoples changes via Email. For example consider the scenario where you have a large File and only Email out a weekly Pivot Table without the Source to 4 or 5 people. Any of these 4 or 5 people make some comments. They then return their Files to you with their comments. You can then view these and update your MASTER File of Notes so that it is always representative of everyones comments ready for the next refresh. To see this trail take a look at the flow below:
2. You Email the original File and a person in each Department make changes, returning the original File. For example consider the scenario where you want to Email around the updated File in some sequence so that different areas of the Business make changes before finally returning the original File back to you by Email. To see this trail take a look at the flow below:
How to Get the Field to Index when using the Data Model with Pivot NotesBack to Online Help
Pivot Notes 4 uses Field Names to create the Index and Update / Render Notes. When using Pivot Notes with the Data Model the Field Names may not be quite what they appear to be and this causes an error when Indexing.
In order to ensure that you are Indexing the correct Pivot Table Fields 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 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
How to use Pivot Notes in Debug ModeBack to Online Help
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 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
How to Filter Note ColumnsBack to Online Help
You can Filter Note Columns in Pivot Tables that use Pivot Notes as long as you DO NOT Sort any Pivot Table Fields when Filtering. This can be useful to quickly locate all Notes containing some Text. Click on the first Note Header and select all of the Note Headers and the Notes underneath right down the Range of Notes - this ensures that all of the data is captured during a Filter. Then on the "Sort & Filter" Group of the "DATA" Tab on the Ribbon, click the 'Filter' Button. Now you can Filter a Note:
Here I have Filtered the 'Commercial Team' Header by '>> no sales since wk38?' - the Pivot Table will respond by also Filtering the data as shown in the image below:
Please Note: You can still leave Notes whilst Filtering, however, if you do attempt to Sort or Slice through Pivot Table data whilst also Filtering Pivot Note Columns you will get some very strange results and you will have to do a right-click 'Refresh' on your Pivot Table to bring back the correct Notes
How to Increase the Pivot Notes Font SizeBack to Online Help
As previously mentioned Pivot Notes uses Styles to format the Notes because this is a very fast and efficient way of writing out Formats to Cell Ranges. To increase the Font Size of your Pivot Notes simply Modify the Style that is used to render the Notes. Maximise the Excel Workbook Window. Click on a Pivot Note. You will see the Style for the Note on the "Styles" Group on the "HOME" Tab of the Ribbon - choose right-click, 'Modify...':
Now click 'Format' on the 'Style' Dialog. Click on the 'Font' Tab of the 'Format Cells' Dialog. Increase the Size of the Font - I increased my Font Size from '10pt' to '11pt'. Click 'OK' and 'OK' again to close all of the Dialogs. The Font for the Notes will increase automatically but you may need to adjust your Columns and / or Row Sizes depending on your setup ie. if you are using 'Fixed' Column and Row Sizes:
How to Adjust Pivot Notes Row & Column SizesBack to Online Help
You have quite a mix of options to Size the Columns and Rows when using Pivot Notes although always remember these Settings apply to ALL Columns of Notes in a single Worksheet regardless of the number of Note Columns and / or Pivot Tables. Pivot Notes Styles using 'Wordwrap' also helps to format lovely looking Notes. I will list the options here to avoid any uncertainty:
1. You fix the Columns and / or Rows
2. You allow Pivot Notes to automatically Size the Columns and / or Rows
3. You do any of the above with Styles including Wordwrap or not including Wordwrap
4. You consider Cell Alignment in with the Sizing of the Note
To try to make things a little easier, I will suggest some nice variations for 1 Note Column and then you can experiment!
OPTION #1: Let's start by choosing to FIX the Columns to a standard width and FIX the Rows to a standard height using Wordwrap, a Horizontal Left Indent of 1 and a Vertical Center Alignment to wrap any long Text. I like this method as you can adjust the Column width and keep Notes brief, clean & crisp without too much disturbance to the Pivot Table ;)
Pros: looks really nice on the eye; short, brief notes look clean & crisp; hides really long Text without affecting the Note Cell or the Pivot Table Cells; keeping the uniform Row height with the Pivot Cells adds a nice aesthetic touch; you have control over how your Pivot Note Columns look
Cons: any Text longer than the width and height AFTER WORDWRAP is applied is not displayed to the user; Zooming the display less than 100% may result in an ill-fitting Note, although this is rare
1. Add the 'Pivot Notes' default Style to the Pivot Notes Setting:
Private Const PivotNotesStylePT1 As String = "Pivot Notes"2. Change the Row adjustment Settings:
Private Const PivotNotesRowAdjustment = PivotNoteRow.xlFixRows Private Const PivotNotesRowHeight = "24.14" '24 to 36 works well so feel free to widen slightly...3. Change the Column adjustment Settings:
Private Const PivotNotesColumnAdjustment = PivotNoteColumn.xlFixColumns Private Const PivotNotesColumnWidth = "24.14"This is what the result looks like for a small Note - the Text for longer Notes will still be saved but will not be fully visible unless you double-click into the Cell whereby the Cell will expand its contents:
OPTION #2: So now we will FIX the Columns to a standard width and AUTOFIT the Row Height using Wordwrap, a Horizontal Left Indent of 1 and a Vertical Center Alignment to wrap any long Text. This method is good when you want a user to read the entire Note entered in a Cell
Pros: the entire Note can be read in the Cell; you still retain control over how your Pivot Note Columns look; it works well when using ALT+ENTER to add New Lines (Line Feeds); you do not have to set the 'PivotNotesRowHeight' Setting as this is ignored when you set the 'PivotNotesRowAdjustment' Setting to 'PivotNoteRow.xlAutoFitRows'
Cons: the Pivot Table Cell Rows will expand dynamically depending on the length of Pivot Note entered alongside the Pivot Table - this may or may not be acceptable; a lot of whitespace will be left in the Pivot Table especially when using non-repeated Items
1. Add the 'Pivot Notes' default Style to the Pivot Notes Setting:
Private Const PivotNotesStylePT1 As String = "Pivot Notes"2. Change the Row adjustment Settings:
Private Const PivotNotesRowAdjustment = PivotNoteRow.xlAutoFitRows3. Change the Column adjustment Settings:
Private Const PivotNotesColumnAdjustment = PivotNoteColumn.xlFixColumns Private Const PivotNotesColumnWidth = "24.14"This is what the result looks like fo a longer Note:
How to Add a Date/Time Stamp when someone enters Text into another ColumnBack to Online Help
There may be an instance when you would like to record a Date/Time Stamp in a Pivot Notes Column when Text is entered into another Pivot Notes Column. You can add a small piece of Code to do this work for you. All you need to do is to change the Code to pickup the relevant Note Column where the Text will be entered and an Offset to the Column where you want the Date/Time Stamp to be added. In the example Pivot Table you can see that I have 2 Note Columns. The first Column, "Commercial Team" is a Column that a user can enter Notes into, the second Column, "Last Changed" is a Date / Time Stamp Column where the Date and Time will be entered whenever the "Commercial Team" Column is edited:
The Code to enable this is already included with Version 4.2 of Pivot Notes. You can edit this Code in the Worksheet_Change() Event Handler. This is the Code that you should uncomment and edit (I have left the part to lock the Column commented out). You only need to specify the "Commercial Team" Column Header and set the Offset to 1):
' // code to handle a Date/Time Stamp for Pivot Notes and/or lock a specific Column for editing ' - this is required due to the fact that Pivot Notes cannot use UDF's within Formula stored for Notes ' - code allows multiple editing for a specific (moving) Column by Header, ie. add by paste, remove by select & delete ' - code locks a specific (moving) Column for editing by Header On Error GoTo Catch_ Dim rngFind As Range Dim rngCell As Range Dim strColumn As String Dim intFindColumn As Integer Dim intColumnOffset As Integer Application.EnableEvents = False ' // setup the 'locked' Column Header below, enter the Column Header for the Column that you want to lock ' Set rngFind = Cells.Find("Last Changed", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext) ' If Not rngFind Is Nothing Then ' If Target.Column = rngFind.Column Then ' Dim bUndo As Boolean ' With Application ' Dim objRange As Range ' For Each objRange In Target ' bUndo = True: Exit For ' Next objRange ' If bUndo Then .Undo ' End With ' End If ' End If ' // setup the 'editable' Column Header below that will enable the storage of the Date/Time Stamp, enter the Column Header Set rngFind = Cells.Find("Commercial Team", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext) If Not rngFind Is Nothing Then intFindColumn = rngFind.Column If intFindColumn = Target.Column Then For Each rngCell In Target.Cells ' // edit the Offset below for the 'locked' Column to receive the Date/TimeStamp ' so if the Column of the Date/Time Stamp is the next Column to the right, set the Offset to 1 intColumnOffset = 1 ' // when setting the Format, you must also change the Pivot Note Style to the same Date/Time format otherwise ' it will be over-written when rendering Notes If rngCell.Value = "" Then rngCell.Offset(0, intColumnOffset).Value2 = "" _ Else rngCell.Offset(0, intColumnOffset).Value2 = Format(Now(), "m/d/yyyy h:mm:ss AM/PM") Next rngCell End If End If Catch_: Application.EnableEvents = TrueNow, how to retain the specific Date/Time Stamp Format for your Column?
When setting the Format, if you require a specific Format for your Date/Time Stamp, you must also change the Pivot Note Style to the same Date/Time format as was setup in the Code, otherwise it will be over-written when rendering Notes. Normal Text can still be used with a Pivot Notes Style, despite that Style having a Date/Time Stamp. Once you have modified your Pivot Notes Style the finished Date/Time Stamp looks like this whenever Text is entered into the "Commercial Team" Column:
How to Lock a Column to prevent Cell Contects from being Edited or Deleted Altogether (Particularly useful for a Date/Time Stamp)Back to Online Help
Okay, so continuing on from the example above on 'How to Add a Date/Time Stamp when someone enters Text into another Column' (please read this first), we can now lock this or any other Column that we want to prevent a user editing or removing the contents. In the Worksheet_Change() Event Handler uncomment the rest of the Code that we previously left commented out and enter the name of the Date/Time Stamp Column that you want to lock like I have done below (my Column is called "Last Changed"). Now you will not be able to edit or remove the Date/Time Stamp. If you only want to lock a Column then comment out the Code to add a Date/Time Stamp.
' // code to handle a Date/Time Stamp for Pivot Notes and/or lock a specific Column for editing ' - this is required due to the fact that Pivot Notes cannot use UDF's within Formula stored for Notes ' - code allows multiple editing for a specific (moving) Column by Header, ie. add by paste, remove by select & delete ' - code locks a specific (moving) Column for editing by Header On Error GoTo Catch_ Dim rngFind As Range Dim rngCell As Range Dim strColumn As String Dim intFindColumn As Integer Dim intColumnOffset As Integer Application.EnableEvents = False ' // setup the 'locked' Column Header below, enter the Column Header for the Column that you want to lock Set rngFind = Cells.Find("Last Changed", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext) If Not rngFind Is Nothing Then If Target.Column = rngFind.Column Then Dim bUndo As Boolean With Application Dim objRange As Range For Each objRange In Target bUndo = True: Exit For Next objRange If bUndo Then .Undo End With End If End If ' // setup the 'editable' Column Header below that will enable the storage of the Date/Time Stamp, enter the Column Header Set rngFind = Cells.Find("Commercial Team", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext) If Not rngFind Is Nothing Then intFindColumn = rngFind.Column If intFindColumn = Target.Column Then For Each rngCell In Target.Cells ' // edit the Offset below for the 'locked' Column to receive the Date/TimeStamp ' so if the Column of the Date/Time Stamp is the next Column to the right, set the Offset to 1 intColumnOffset = 1 ' // when setting the Format, you must also change the Pivot Note Style to the same Date/Time format otherwise ' it will be over-written when rendering Notes If rngCell.Value = "" Then rngCell.Offset(0, intColumnOffset).Value2 = "" _ Else rngCell.Offset(0, intColumnOffset).Value2 = Format(Now(), "m/d/yyyy h:mm:ss AM/PM") Next rngCell End If End If Catch_: Application.EnableEvents = True ' // raise errors by default in this Subroutine ' all unhandled errors will end up being caught by Catch: On Error GoTo Catch
The Note Colours WorksheetBack to Online Help
The "Note Colours" Worksheet has the default Pivot Notes Colours for you to use to format Colours in Excel or VBA. The Colours can be copied or you can use the RGB values or Long Codes. The Worksheet looks like this:
How to Index a Non-unique Field by Conditionally Formatting & Hiding Repeated Item LabelsBack to Online Help
There may be a time whereby you have a couple fo Fields that cannot be Indexed unless you switch on 'Repeat All Item Labels' in 'Report Layout' on the 'DESIGN' Tab of the 'PIVOTTOOLS' Ribbon. There is a workaround that will allow you to do this but still 'hide' the repeated item labels, in essence still allowing Pivot Notes to Index the Pivot Table Fields and create a unique Index but letting you keep the minimilistic Layout of your Pivot Table. To do this switch on 'Repeat All Item Labels' in 'Report Layout' on the 'DESIGN' Tab of the 'PIVOTTOOLS' Ribbon. Now select all of the Field items that you want to hide by hovering near the top of the Field - you will see the cursor change. Add the following Conditional Format by selecting Conditional Formatting->New Rule from the 'Styles' Group of the 'HOME' Tab of the Ribbon. Select 'Use a formula to determine which Cells to format' and add '=COUNTIF($A$16:$A16,A16)>1' replacing A16 with the first Item in the Pivot Table Field. Choose 'Format...' and select either a White Fnt to hide the Text or use Number->Custom and add ;;; (three semi-colons).
Now you can Index the Pivot Table again (you should clear your Notes first). The Index will now include the hidden Field items and create a unique Index (as long as the combined Fields are indeed unique. The following image details how Pivot Notes will now create 2 unique Indexes '/ADMIN/Aaron/' and '/ADMIN/Holt/'.
ChangelogBack to Online Help
08.02.2017 - (Version 4.2)
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
16.12.2016 - (Version 4.1) A change to the IndexPivotTablePT1 Subroutine due to speed issues and complexity / ambiguity when a user Indexes a Pivot Table. Now a Pivot Table is Indexed as viewed / used and it is left to the user to switch on Sub totals and Grand totals should they require them to be Indexed during the process. Also, change Time() to Timer to ensure that a unique value is calculated for whenever there are many blank Rows as in the case of Indexing an Outline style Table for a number of Fields. 2 NEW Example Worksheets have been added to demonstrate using Pivot Notes with an Outline layout and a Compact layout
25.09.2016 - (Version 4) leaner & meaner, complete re-write and code optimisation (3 Subroutines). I have heavily commented all of the Code. New features include multi-column notes, improved error trapping / handling and R1C1 annotation allowing you to store and Sort Formula alongside Pivot Tables. Many of the changes below are now redundant, although the functionality is still retained and / or has been improved - they are left in situ for legacy versions
24.08.2016 - (Version 3.1.1) TransposeDictionary replaces the WorksheetFunction.Transpose to allow greater Items limit and Pivot Notes > 255 characters
16.04.2016 - (Version 3.1.0) Add the Setup Constant 'PivotTableColumnsOffset1' to allow an offset to be specified when Indexing a Pivot Table. In normal operation, this is left as 1 (one, the first Field), however if you wanted to Index a Unique Column ie. PO Number and that Column is a Field that is 4 Columns along the Fields in a Pivot Table then you could specify the offset as 4 and the Indexing would begin from there. This allows you to Index a single Unique Column if required, the only caveat being that it MUST be Unique! The amend includes adding an additional Variable to the IndexPivotTable(), UpdateNotes() & RenderChanges() Subroutines
10.02.2016 - (Version 3.0.2) Code amend to skip an error in the RenderChanges() Subroutine when a user dynamically adds extra Columns in a Pivot Table Pivot & the routine attempts to clear the Range within the Pivot Table itself
26.01.2016 - (Version 3.0.1) Code amend to remove Resize when transposing arrays. The Transpose Function has a 255 limit on String Lengths stored within a Variant Array so Pivot Notes are limited to 255 characters only but can include Carriage Returns etc.
14.12.2015 - (Version 3) General release. Change the Dictionary object to late binding. Fix incorrectly declared Variable to have suffix of 1 ie. 'PivotTableFieldNames1'. Added Global Variables to use Conditional Formatting, a Conditional Format Tag & the highlight Colour. Notes are now highlighted automatically via the "*" Tag
03.11.2015 - (Version 2.1.1) Add the ability to auto-fit Rows & set the default Column Width when using Word-wrap. Add a Conditional Format Modification to allow a single dynamic Conditional Format to be used to highlight Pivot Notes containing the string "*" (anything or a specific Tag ie. ">>")
23.10.2015 - (Version 2.1) Code change. Add the easy Setup Variables section
22.10.2015 - (Version 2) Re-write, all code now resides in a single Worksheet Code Module
16.10.2015 - (Version 1) Released for beta testing