How to replace a concatenation formula

In our previous tip we shared a cash flow statement template with you and showed you how to change the way your data appears to make it easier to read, using Custom Formats. This time around we want to show you how to replace a concatenation formula in the same template, still using Custom Formats.

Note: Download your cash flow statement template.

Applies To: Microsoft® Excel® 2007, 2010 and 2013

Open your template. You will see a part of a cash flow statement report.


Note that the Period Numbers in row 9 are listed as single numbers. All numbers should be in a double digit format, followed by the year e.g. 01 – 2014.


  1. Select cells E9 to P9.


2. Press CTRL + 1. This opens the ‘Format Cells’ window.



  1. In the Category window, select Custom.



  1. In the Type box, highlight the General


5. Type 00 “ -2014” then click



The period values have now been adjusted to a two digit format to represent the period, and are appended with a “ – 2014” value.



While the same result could have been achieved by using a concatenation formula, this method of using Custom Formats removes the need for it. As it is a format that will be saved in this workbook, it can be applied to other cells very easily.