Standard OpenXML Cell Numbering Formats

When working with OpenXML documents, you can always specify a numbering format implicitly in the stylesheet and then set it on a desired cell or cells. If you choose to use those, your custom numbering formats are going to start at at 0xA4 (decimal 164) and continue on. Have you ever wondered why Microsoft Excel has a predefined list of formats that you can access by right-clicking on a cell and selecting Format Cells and whether they are really custom formats? Well, they are not. The formats falling under that magic number of 164 are the standard formats conforming to Standard ECMA-376 Office Open XML File Formats second edition draft. However, not all of them are necessarily defined and, in my personal opinion, it would have made sense to rather have a flag attribute informing the application reading an OpenXML file to format a particular value according to a value of a custom format attribute or format it according to an Id of a standard attribute. This way, 164 standard numbering formats does not become an issue if you ever need more. But that’s just me and, in any case, here are some of the implied cell numbering formats you can use:

1 	0
2 	0.00
3 	#,##0
4 	#,##0.00
9 	0%
10 	0.00%
11 	0.00E+00
12 	# ?/?
13 	# ??/??
14 	mm-dd-yy
15 	d-mmm-yy
16 	d-mmm
17 	mmm-yy
18 	h:mm AM/PM
19 	h:mm:ss AM/PM
20 	h:mm
21 	h:mm:ss
22 	m/d/yy h:mm
37 	#,##0 ;(#,##0)
38 	#,##0 ;[Red](#,##0)
39 	#,##0.00;(#,##0.00)
40 	#,##0.00;[Red](#,##0.00)
45 	mm:ss
46 	[h]:mm:ss
47 	mmss.0
48 	##0.0E+0
49 	@

I hope this helps you in one or another way!

This entry was posted in Microsoft Excel and tagged , , , , , , , , , . Bookmark the permalink.

One Response to Standard OpenXML Cell Numbering Formats

  1. pharmacist says:

    Beneficial info and excellent design you got here! I want to thank you for sharing your ideas and putting the time into the stuff you publish! Great work!

Leave a Reply

Your email address will not be published. Required fields are marked *