Complete list of productivity pack macro functions
Some references before you start:
- Download the latest version of the macros
- Guidance on setting up the macros
- Customising the macros and changing the settings
- Release notes for the latest version of the macros
Hide / Unhide macros
The macros file is designed to run in the hidden state. To make changes to any of the keystrokes or settings, you'll first need to unhide the file.
When you are done making any changes, rehide with the same keystroke.
Workflow actions
Apply placeholder square brackets
Adds placeholder square brackets to text in the selected cell.
Create list of placeholders
Creates a list of all line items in the model which have been marked as a placeholder.
Jump to precedent/return from precedent
Intended to be used on links, for quick navigation/movement around the model. Replacement for the native Excel keystroke Ctrl+[ which doesn't work on non-QWERTY keyboards.
Quick link
Creates a link to a previously copied cell, whose address is currently on the clipboard.
Quick sum
The quick sum function has two behaviours. It adds a row total if used in the row totals column. It adds a total of the time series line above if used in the constants column.
- How the row total macro is used when creating a new calculation
- [Addng a sum of the time series row above]
Relocate inputs
This moves inputs that have been added to calculation sheets to the selected input sheet, and replace them with a link.
Note that your model needs the constant input sheet structure for this week, including a specific range name.
- Skill 1: How to add an input
- Skill 10: How to relocate an input
- Range names needed for productivity macros
Relocate outputs
This adds either a constant or a row total to the output tracking / analysis sheet.
Anchoring
This allows the batch changing of the row and column anchoring settings on a range of cells.
- [How to change anchoring settings on a range of cells]
Paste value
This is a direct replacement for the native "paste special" keystroke. The purpose is to avoid having to remember and press a series of keys.
Paste format
This is a direct replacement for the native "paste special" keystroke. The purpose is to avoid having to member and press a series of keys.
Add model query / feedback
This opens a dialogue box to allow structured commenting in the model. It's intended for people who are reviewing the model to leave comments or questions. These are different than placeholders which are intended to indicate temporary line items.
Delete worksheet
This is a direct replacement for the native excel shortcut sequence. The purpose is to avoid having to member and press a series of keys. The same warning will be given when it's used. We use the F11 quick chart frequently which creates a need to quickly delete the charts we created.
Expand / Collapse group
These keystrokes are to allow quicker "expending" and "collapsing" of grouped rows.
Manage exceptions list
There are times when we do not want certain of the macros to perform their default behaviours. This keystroke opens up a dialogue to manage exceptions. These are specifically around sheets that we do not want import or export marking on, and words that will not follow the default capitalisation approach.
Reset end cell
If this keystroke is activated in any of the time series columns, it will reset the end column. If it's used in any of the columns before the time series, it will reset the end row.
Add to change log / Save change log values
These keystrokes automate the process of adding to a model change log.
Create variance tabs
Variance tabs allow more detailed comparative analysis on targetted sections of the model.
Create output track
This adds a new column to the output track sheet and saved the current values as a new tracked output case.
Add a sparkline to a calculation
This adds a sparkline in the constants column (usually column F) for a time series calculation.
Translate row labels
This translates selected text via an AI translation service, either MS Azure or Google Translate.
Model utilities
Open utilities dialogue
This keystroke opens the macro utility dialogue. The keystrokes to activate each utility are listed. Utilities are run on the sheets selected. Alt+a selects all sheets.
Section 1: Quick build
These provide a quick way of adding a new balance corkscrew. The macro will first ask for the name of the balance.
This shortcut builds a Template sheet carrying the default page set up.
Section 2: Prepare model for release
This shortcut will apply a zoom setting to all selected sheets. This is often used as part of a model tidy up process.
This repositions each selected sheet back to the home position and selects cell A1 on each sheet. Often used as part of a model tidy up process.
When copying and pasting model sections and building new parts of the model, grouping can quickly become a mess. This macro reapplies grouping according to Column A and Column B headings.
Adding or removing worksheet gridlines has to be done on a sheet by sheet basis. This shortcut removes gridlines on all selected sheets.
Consistent label capitalisation is important. This shortcut checks heading and label capitalisation. Often used as part of a financial model tidy-up process. You can maintain a list of exceptions within the macro file.
- How to check and correct label capitalisation
- What makes a poor row label
- Section completion checklist
Collapsing all grouping across all selected sheets can be useful when preparing a model for release. Similarly, when working on a model you may want to expand all the groupings while working.
This shortcut changes print heading settings across all selected sheets. It's easy to forget this and put models out with the wrong project or client name on them.
This function removes any links to blank cells that have been created when copying a link across from Column E. These occur when creating series links due to the constants column and the blank column before the timeline being empty, and where there are now row totals, for example on balances.
Section 3: Model information
The shortcuts in this section return information about your model. These can help you to review and tidy up your model.
The purpose of listing all the labels in your model is primarily to check for duplicates. This can be used as part of a model review / model release checklist.
Similar to listing labels, this is a review tool to help you check for label inconsistency.
Conditional formats can cause the file size to increase. This shortcut lists all conditional formats in the business and allows you to remove any you're not using.
Managing used ranges helps to keep Excel file size under control.
This shortcut is a simple tool to allow you to check for inconsistent font usage.
This will create a list of the unique formulas for model review / audit purposes.
This report is also for model review and maintenance purposes.
This will create a report of all the Named Ranges in your model to help you identify unused or hidden Names or Names with errors.
This macro will delete any Range Names on the Range Name report which you have indicated as not required by leaving Column A blank.
This will create a sheet containing Excel version, file paths, adds in etc, about your excel environment. Used for general info and debugging purposes.
Number formatting
Apply "normal" style
This is the default number style in the model. This can be customised by changing the format of Cell H48 in "Custom Shortcuts"
Increase decimal places
This will add one decimal place to the number in the selected cell(s)
Decrease decimal places
This will remove one decimal place from the number in the selected cell(s)
Factor style
This will apply the factor style, as defined in call H39 on the Custom Shortcuts sheet. We tend to use 4 decimals places for factors (for example discount factor, escalation factors, partial period factors).
Percentage style
This will apply the percentage style as defined in cell H40 in the Custom Shortcuts sheet.
Toggle date style
This will toggle between the "long" and "short" date styles, as defined in Cells H42 and H43 in the Custom Shortcuts sheet.
Black font
Used for local links and calculations.
Import marking / Export marking
Use for imported links.
Cell formatting
Clear shading
Clears all cell shading. Does not clear borders. Native excel keystroke Ctrl+shift+- will clear borders.
Placeholder shading
Applies yellow shading for placeholders (or other colour as defined in the macro settings)
Input shading
Applies input cell formatting as defined in the macro settings.
- How to add an input
- Managing inputs - Part 1: Cases, scenarios and sensitivities
- Managing inputs - Part 2: Using and comparing cases
- Managing inputs - Part 3: Running scenarios
- Managing inputs - Part 4: Input sheet mechanics
Macro based value shading
Sometimes macros are required to write values into the model. These are inputs, but not inputs intended to be changed by users.
Apply borders
Applies top of row and bottom of row borders to the selected cells.
Comments
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.