Discover some of the most useful lesser known features that can have a huge impact on your financial modeling workflow when using Microsoft Excel.

The Camera Tool

This feature enables you to make dynamic photos of the Excel screen. This means that the photo will update as the captured information in the worksheet updates. This tool is very useful to see how other parts of a model are impacted when the assumptions change.

camera-tool

To install this feature:

  1. Right click the quick access toolbar and choose “Customize Quick Access Toolbar”. The Excel Options dialog box will open.
  2. Choose “All Commands” from the “Choose from commands” drop-down
  3. Scroll down the list of commands until you find the “Camera” feature
  4. Choose the “Add” button to install the camera function on the quick access toolbar
  5. Choose “OK” to close the Excel Options dialog box

To use this feature:

  1. Select an area of the screen to capture
  2. Click the Camera button on the quick access toolbar
  3. Click a destination point on the screen for the dynamic picture. The picture will appear as an object on the Excel worksheet. As you change the data captured in step 1 above, you should see the picture update.

Be careful: don’t use this feature as an infinite resource. The dynamic pictures consume memory and should ideally be deleted once you have used them.

The Watch Window

A great feature that allows you to see the content of specified cells from a completely different part of the model. Similar to the Camera tool, this feature is very useful when you want to see how changing assumptions impact different parts of the model.

watch-window

To use this feature:

  1. Select the cell or cells to “watch”
  2. Click the “Watch Window” button from the formulas ribbon. The Watch Window dialog box will appear
  3. Click the “Add” button and confirm by clicking OK from the “Add Watch” dialog box

The “Watch Window” will now display the cell or cells being watched, their values and formulas contained within them.

Speaking Cells

Yes you can have Excel speak the content of cells to you! Useful if you are feeling lonely at your desk late one evening! Joking aside, this feature probably sounds very gimmicky, but it’s actually a useful tool when you are trying to reconcile numbers in your models.

speaking-cells

To install this feature:

  1. Right click the quick access toolbar and choose “Customize Quick Access Toolbar”. The Excel Options dialog box will open
  2. Choose “All Commands” from the “Choose from commands” drop-down
  3. Scroll down the list of commands until you find the “Speak Cells” feature
  4. Choose the “Add” button to install the speak cells function on the quick access toolbar. Consider also adding the Stop Speaking Cells option
  5. Choose “OK” to close the Excel Options dialog box

To use this feature:

  1. Enter a sentence or a number into cell or a range of cells
  2. Select the cell or range of cells
  3. Click the “Speak Cells” button on the Quick Access Toolbar. Excel will speak the content of the cell or cells back to you!

Warning: make sure the volume on your computer is high enough to hear Excel speak. You may also need to wear headphones, depending on your set up.

If the data you want Excel to speak back at you is in a two dimensional range, you can use the “Speak Cells by Column” or “Speak Cells by Row” options to specify the order. Furthermore, you can also activate the “Speak Cells on Enter” feature, to have Excel speak the content of a cell back at you after pressing enter. Run through the first 5 steps above to install these features on the Quick Access Toolbar.

Spark Lines

These are mini charts that appear within a cell. However this cool feature is only available from Excel 2010 onwards. This feature is useful if you want to graphically represent the progression of a forecast in your model.

sparkline

To use this feature:

  1. Select the cell to contain the spark line
  2. Go the “Insert” ribbon and choose “Line” from the “Sparklines” group. The Create Sparklines dialog box will appear
  3. Select the source data for the spark line using the mouse. The range you select will appear in the Data Range edit box
  4. Choose OK. A mini chart (aka Sparkline) will appear in the chosen cell

Once you are familiar with using the “Line” feature, run through these steps again and try “Columns” and “Win / loss” mini charts too!

The Excel Add-In for Investment Banking

Not exactly unsung considering AMT has been developing this great addition to Excel for a number of years with thousands of downloads. It is completely free and has a variety of keyboard shortcuts, functions and tools to maximize your efficiency.

excel-addin-image-wide

To install the add-in:

Download the add-in via our Facebook page

There are no pre-requisites but if you do like our add-in we would greatly appreciate a like on our page. Once you have downloaded the add-in for your version of Excel simply run it to start the quick installation process.

Using the add-in:

Once you have installed the add-in, you will see an “AMT Macros” ribbon appear. There are various functions on this ribbon such as a style manager, which enables you to delete many styles simultaneously. There is also a Beauty Save function, which saves the file while positioning the active cell in the top left-hand corner of the each worksheet.

The add-in also includes a variety of customized functions enabling you to easily calculate items such as Levered or Unlevered Betas. To see a list of available functions included in the add-in, click the “Show Shortcuts and Functions” button on the ribbon. There are also numerous shortcuts here that will greatly improve your efficiency. To see a list of available shortcuts included click the “Show Shortcuts and Functions” button and go to the “Review Shortcuts” sheet.

To find out more about our add-in please visit our Excel Add-In for Investment Banking page.

Have your say

We hope you have found the above features, tips and tricks useful. If you have a favorite Excel feature you simply cannot live without for financial modeling please let us know in the comments below.