Hello all and welcome to a new regular feature from the ICAEW Excel Community where we endeavour to get ‘Your Questions Answered’. In this feature, we will address the Excel questions and worries you send in by providing you with solutions and guidance on the best practice of using Excel.
The question
The background
The format is created by entering special characters in the Custom section of the Number tab of the Format Cells dialog:
In this example, we have set up our three sections, separated by semi-colons, to show numbers using a comma as a thousand separator, with no decimal places. Negative numbers are shown in red with round brackets, and zero values are shown as a dash, followed by a space. Note that the positive number format ends with _). The underscore causes Excel to leave an amount of space equal to the width of the following character. So, _) leaves a space at the end of a positive number that matches the width of the closing bracket at the end of a negative number, ensuring that positive and negative numbers line up exactly:
#,##0_);[Red](#,##0);-?
The problem
Answer part 1
We could record a macro that applies our number format to the currently selected cells. We could then add a button to run that macro either to our Quick Access Toolbar or to the Ribbon. In either case, we right-click on the toolbar or the ribbon and choose the appropriate Customise option - we can choose whether to add our button to the toolbar or the ribbon. For the Quick Access Toolbar, we can choose Macros from the ‘Choose command from’ dropdown and then Add our new command to our toolbar. The operation is slightly more complicated for the ribbon because we can only add commands to custom groups, so we would need to create and name a new group in the appropriate ribbon tab first, before adding our command in the same way:
Depending on whether we saved our macro in the current workbook or in the Personal Macro Workbook, this command would work just when the current workbook is open or throughout Excel. The Personal Macro Workbook is a hidden workbook that is opened whenever Excel is opened, making any macros that it contains generally available.
If you would prefer to avoid the use of macros and programming code, there is a very quick and easy method to make the new format available in the current workbook. It involves using Cell Styles. Excel Cell Styles are found in the Styles group of the Home Ribbon tab and, at the bottom of the gallery of styles is group of Number Format styles, including a Comma format. This Comma format number style is linked to an existing Ribbon tab command: the Comma button in the Number group of the Home Ribbon tab. Accordingly, if you right-click on the Comma style in the Style Gallery and choose Modify, you can use the Format button to display the Format Cells dialog from which you can choose your custom number format to replace the existing comma number format. Having modified the Comma style in this way, you can apply your format just be selecting the relevant cells and clicking the Comma button:
Answer part 2
Unlike Word styles, Excel styles cannot be made directly available to all workbooks. If you have allocated your number format to your Comma style then, if a workbook is open that includes the style, you can use Merge Styles… to copy styles from that workbook over the existing styles in any other workbook. Note that any cells that have previously had the Comma style applied will then automatically change to use your custom number format.
There is a way to make your custom number format style, and its allocation to the Comma button, available to all new workbooks. You can add the style to an Excel template. If that template is saved with the name Book.xltx into the User StartUp folder (taking care not to overwrite an existing file) then your template will be used as the default template when creating a new workbook and your Comma button will apply your custom number format in all new workbooks: ICAEW 20 Principles Excel workbook template
Related links
- Your Questions Answered #11 – Tips and Tricks Live extended
- Your Questions Answered #10 – Tips and Tricks Live extended
- Your Questions Answered #9 – What is the purpose of # when using dynamic arrays?
- Your Questions Answered #8 – Does Power BI leave the underlying data source unchanged?
- Your Questions Answered #7 - How do I combine tables from multiple files into a single table?
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.