How to customize number formats

Having a sufficient amount of cash is key to maintaining a healthy business, which is why it’s crucial to track where your cash comes from and where it goes. We’ve provided you with a cash flow statement template which you can customise. In this tip we show you how you can customise number formats.

Conditional formatting allows you to change the font, size and colour of text in cells (among other things). By using Custom Formats you can change how the data itself appears without having to change the data values in the cells. This can make data easier to read, particularly with financial information.

Note: Download your cash flow statement template to practice this exercise

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

  1. Open the exercise workbook. You will see a part of a cash flow statement report. You will see that a number of cells, such as some cells in row 19, are actually negative values.

tip-1

  1. Select cells E14 to P29. From the Numbers group, on the Home tab, the drop down list identifies that these cells are currently formatted as Number. To format them as a currency values click the drop down arrow of the Format box and select Currency.

tip-2

tip-3

tip-4

The numbers will now change to currency values without you having to change the data. In this example, we’ve used South African Rands (R).

tip-5

  1. If you’d like to apply accounting formatting, click the drop down arrow of the ‘Format’ box and select Accounting. You will see that the format of the figures will change and zero values will become dashes.

tip-6

tip-7

To make it easier for you to recognize negative values, you can change the format of negative values so that they are distinct. In this case, you can change the color of the values to red, and have the values in brackets. Here’s how:

  1. Press CTRL + 1. This opens the Format Cells window.

tip-8

5. In the Category window, select Custom.

tip-9

6. In the ‘Type:’ window, select the 0.00_);[Red](0.00) option, then click OK.

tip-10

You will now see the negative values are now in red, with brackets around them.

tip-11

While conditional formatting can change the visual format of a cell, adjusting the actual format of the cell can change how the data itself is represented, without having to manually type more characters or make physical adjustments to the text.