Complete list of productivity pack macro functions

Some references before you start:

Hide / Unhide macros

Ctrl+shift+7

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

Ctrl+shift+t

Adds placeholder square brackets to text in the selected cell.

Create list of placeholders

Ctrl+alt+p

Creates a list of all line items in the model which have been marked as a placeholder.

Jump to precedent/return from precedent

Ctrl+shift+j / Ctrl+shift+k

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.

Ctrl+shift+q

Creates a link to a previously copied cell, whose address is currently on the clipboard.

Quick sum

Ctrl+shift+n

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.

Relocate inputs

Ctrl+alt+r

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.

Relocate outputs

Ctrl+alt+o

This adds either a constant or a row total to the output tracking / analysis sheet.

Anchoring

Ctrl+alt+a

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

Ctrl+alt+d

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

Ctrl+alt+s

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

Ctl+alt+q

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

Ctrl+alt+- (minus)

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

Ctrl+e / Ctrl+shift+e

These keystrokes are to allow quicker "expending" and "collapsing" of grouped rows.

Manage exceptions list

Ctrl+alt+e

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

Ctrl+alt+c

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

Ctrl+shift+1 / Ctrl+shift+2

These keystrokes automate the process of adding to a model change log.

Create variance tabs

Ctrl+shift+3

Variance tabs allow more detailed comparative analysis on targetted sections of the model.

Create output track

Ctrl+shift+5

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

Ctrl+shift+s

This adds a sparkline in the constants column (usually column F) for a time series calculation.

Translate row labels

Ctrl+shift+4

This translates selected text via an AI translation service, either MS Azure or Google Translate.


Model utilities

Open utilities dialogue

Ctrl+shift+6

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

Q - Corkscrew with no initial balance
W - Corkscrew with an initial balance

These provide a quick way of adding a new balance corkscrew. The macro will first ask for the name of the balance.

T - Add temp sheet

This shortcut builds a Template sheet carrying the default page set up.

Section 2: Prepare model for release

1 - Set zoom level

This shortcut will apply a zoom setting to all selected sheets. This is often used as part of a model tidy up process.

2 - Reset focus and select cell A1

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.

3 - Reapply level 1 and 2 grouping

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.

4 - Remove gridlines 

Adding or removing worksheet gridlines has to be done on a sheet by sheet basis. This shortcut removes gridlines on all selected sheets.

5 - Check and fix all label capitalisation

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.

6 - Collapse all grouping
7 - Expand all groupings

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.

8 - Change all print header settings

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.

9 - Remove links to blank cells

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.

L - List all used labels

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.

U - List all unique units

Similar to listing labels, this is a review tool to help you check for label inconsistency.

C - List all conditional formats

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.

R - List all used ranges

Managing used ranges helps to keep Excel file size under control.

F - List all used fonts

This shortcut is a simple tool to allow you to check for inconsistent font usage.

O - list all unique cell formulas in workbook

This will create a list of the unique formulas for model review / audit purposes.

V - list all data validations formulas in the workbook

This report is also for model review and maintenance purposes.

M - Create Named Range report

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.

D - Delete unwanted Range Names

This macro will delete any Range Names on the Range Name report which you have indicated as not required by leaving Column A blank.

X - list Excel application information

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

Ctrl+shift+, 

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

Ctrl+.

This will add one decimal place to the number in the selected cell(s)

Decrease decimal places

Ctrl+,

This will remove one decimal place from the number in the selected cell(s)

Factor style

Ctrl+shift+.

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

Ctrl+shift+p

This will apply the percentage style as defined in cell H40 in the Custom Shortcuts sheet.

Toggle date style

Ctrl+shift+l

This will toggle between the "long" and "short" date styles, as defined in Cells H42 and H43 in the Custom Shortcuts sheet.

Black font

Ctrl+shift+b

Used for local links and calculations.

Import marking / Export marking

Ctrl+shift+m / Ctrl+shift+x

Use for imported links.


Cell formatting

Clear shading

Ctrl+shift+c

Clears all cell shading. Does not clear borders. Native excel keystroke Ctrl+shift+- will clear borders.

Placeholder shading

Ctrl+shift+y

Applies yellow shading for placeholders (or other colour as defined in the macro settings)

Input shading

Ctrl+shift+i

Applies input cell formatting as defined in the macro settings.

Macro based value shading

Ctrl+shift+v

Sometimes macros are required to write values into the model. These are inputs, but not inputs intended to be changed by users.

Apply borders

Ctrl+shift+d

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.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.
jamie@example.com
Subscribe