One of Excel’s key features is the ability to apply number formats to change the display of numeric data, without affecting the data itself. For example, these numbers are using the “General” number format. If I switch to “Currency”, or “Accounting” they appear differently, but the actual values are unaffected, as you can see in the formula bar. I’ll change back to General. Dates also use number formatting. I can easily switch from a short format to a long format. Custom number formats let you extend number formatting based on your own needs, using special codes. To apply a custom number format, use the keyboard shortcut control 1 to open Format Cells window, then visit the number tab. Here you’ll find a long list of built-in custom formats organized into groups. In the Custom group, you can enter any valid code. For example, I can display these dates as month names only with “mmmm”. By default, Excel charts will pick up number formats from the data being plotted. For example, if I use this data to make a simple column chart. Then apply the currency format, the chart pulls in that formatting. This behavior is controlled by a setting called “linked to source”, which you can find in the Format Task pane under Number. Notice if I uncheck this box, nothing happens, because Excel has already applied the currency format. However, if I undo back to the original format…then uncheck linked to source, and then apply currency format to the values again, the chart is unaffected. Dates work the same way. For example, if I apply a custom number format to display only month names… This format appears horizontal axis, because “linked to source” is enabled. You can also apply number formats to chart elements directly. For example, here I can use “mmm” as the format code to display abbreviated month names in the chart only. When I click add, the format is applied, and the “linked to source” checkbox is automatically unchecked. Notice that once you’ve applied a custom format like this to a chart, you’ll see it appear in the type menu.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.