Use Custom Number Formatting in Charts

Use Custom Number Formatting in Charts

Apply custom number formatting to values in charts to add meaning and clarity. 

When to use

We apply custom number formatting to values in Excel charts whenever we want to change the way the number is displaying. For example, we might not want to show a currency symbol or we might want to increase decimal places. 

Instructions

We apply custom number formats to charts to change the way values are displayed. This can be particularly useful if we have very long numbers that take up a lot of space in our charts and look messy. We can format them so the notation is shorter and neater. 

For example, if we have millions in our chart we could format this number to just show the value with 'M' after it, e.g. 6M instead of 6,000,000. 

Number Formatting - The Basics

Simple Number Formatting

Let's recap and understand what custom formatting is and how it works. 

• Switch to the 'Sheet1' worksheet. 

The values in the table are currently un-formatted. 

We can apply basic formatting to these numbers using simple number formats. 

• Select the values in the 'Sales' column. 
• From the Home tab, in the Number group, click the Comma Style command. 

This will add a comma separator to the values. 

• Click the Decrease Decimal command twice to remove the decimal places. 

• Select the values in the 'Profit' column. 
• From the Home tab, in the Number group, click the drop-down arrow and choose Currency from the list. 
• Click the Decrease Decimal command twice to remove the decimal places. 

• Select the values in the 'Conversion Rate' column. 
• From the Home tab, in the Number group, click the Percentage Style command. 
• Click the Increase Decimal command twice to add two decimal places. 

Custom Number Formatting

Custom Number Formatting allows us to be very specific about our number formatting. Sometimes, the format we want isn't available in the in-built formats available in the number formatting drop-down list. 

Using Custom Formats we can define how we want positive, negative, zero values and text to be formatted. 

• From the Home tab, in the Number group, click the diagonal arrow to open up the Format Cells dialog box. Alternatively, press the keyboard shortcut CTRL+1. 
• On the Number tab, click Custom from the list of categories. 

This will show a list of all the available custom formats. We can also create our own custom formats from here. 

• Select any custom format from the list. 

Custom formats are split into 4 parts separated by a semi-colon. 

The first part (before the first semi-colon) shows how positive numbers will be formatted. 

The second part shows how negative numbers will be formatted. 

The third part shows how zero values will be formatted (cells that contain 0). 

The final part shows how text will be formatted. 

# symbols represented variable numbers whereas 0's represent fixed numbers. We can see a preview of how our number will look in the Sample area above. 

Apply Custom Number Formats to the Chart

The vertical axis in our chart shows the sales values. Currently, these are displayed in full, e.g. 30,000. Maybe we want to shorten these so they just say 30K, 25K, etc. This is where we could apply a custom format. 

• Click on the values in the vertical axis. 
• Press CTRL+1 to open the Format Axis pane. 
• Click Axis Options. 
• Expand the Number group. 
• In the Format Code area, type 0,"K" and click Add. 

The chart will update to reflect the new custom number formatting. 

• Select the vertical axis again and press CTRL+1. 
• Expand the Number group. 
• Select Accounting format from the list. 

Hints & tips

• When applying formatting to numbers, the underlying number doesn't change. Formatting is simple a mask. The exception is percentage values which show in the formula bar the same way they show in the cell. 
• To format values as millions, e.g. 2,000,000 you would use the format code 0,,"M".