In Excel, a UK number format normally uses commas for thousands, a full stop for decimals, day/month/year dates, and the pound sign (£) for pounds sterling. Select the cells and use Home > Number, or press Ctrl+1 to choose a date, currency, or custom format in more detail.
What a UK number format means
There is no single format that applies to every value in a UK workbook. The appropriate format depends on whether a cell contains a price, percentage, date, count, or another kind of value. UK conventions are often described as a comma thousands separator and a point decimal separator: 1,234.56. Dates are commonly written day first, such as 03/11/2026 for 3 November 2026, and currency amounts in pounds are commonly shown with £.
Excel separates a cell’s stored value from its display format. A numeric value can be displayed with a pound sign or as a date without the underlying value becoming text. This matters because numeric values can still be added, sorted, charted, and used in calculations. A format changes how a value looks; it does not convert pounds to another currency, change a date’s meaning, or repair incorrectly imported text.
How to apply a UK format in Excel
For Microsoft Excel for Microsoft 365 on Windows, select the cells and choose a suitable option from Home > Number. The dropdown includes common choices such as Number, Currency, Accounting, Short Date, and Percentage. For more control, open Format Cells with Ctrl+1, then use the Number tab to select a category and its options.
To choose a UK date type through Format Cells, select the cells, press Ctrl+1, choose Date, and set Locale (location) to English (United Kingdom) if that choice is available. Select a day-first example from the list and confirm with OK. For currency, choose Currency or Accounting and select the pound sterling symbol. The exact list of formats can vary with Excel settings and updates.
- Use Number for ordinary quantities that may need decimal places or a thousands separator.
- Use Currency or Accounting for monetary values; Accounting aligns currency symbols and decimal points in a column.
- Use Date for dates, and Percentage for proportions that should be displayed as percentages.
- Use Custom when the built-in choices do not match the presentation you need.
On a Mac, the Format Cells dialog is generally opened with Command+1 rather than Ctrl+1. Ribbon labels and some dialog details can differ between desktop platforms. Excel for the web has its own interface, so if a particular locale or custom-format control is not visible there, open the workbook in the desktop app for the fuller Format Cells options.
UK date formats and date order
A common UK date display is dd/mm/yyyy, where dd is the day, mm is the month, and yyyy is the four-digit year. For example, a real date value for 3 November 2026 can display as 03/11/2026. A shorter form such as d/m/yy may display the same value as 3/11/26. Choose a four-digit year when the workbook may be read later or shared with people who could interpret a two-digit year differently.
Excel stores valid dates as serial numbers and applies a date format to display them as calendar dates. Applying a date format does not change the date value. Conversely, formatting text that looks like a date does not necessarily turn it into a usable Excel date. This is why 03/11/2026 can be ambiguous when moved between systems: a reader using month/day/year conventions may read it as March 11 rather than 3 November.
For shared files, use an unambiguous display such as 3 Nov 2026 or 2026-11-03 where appropriate, and make sure the cells contain real date values. ISO-style year-month-day is particularly useful for labels and data exchange, though it may not be the familiar display for every audience. When importing dates, check a few records against the source rather than assuming that changing the display format has corrected the interpretation.
Numbers, pounds and custom display formats
For a standard UK decimal display, a number format can show 1234.5 as 1,234.50. In Excel’s custom-format box, a common pattern is #,##0.00. To display a numeric amount with a pound sign and two decimal places, use a currency format from the built-in list or a custom pattern such as £#,##0.00. The exact presentation can depend on the workbook’s locale and format settings.
Two decimal places are useful for many prices, but they are not compulsory for every financial or operational figure. A whole-pound display can use £#,##0, while a count may be better shown with no decimal places and no currency symbol. A number format can also show a percentage: a stored value of 0.125 formatted as Percentage with two decimal places displays as 12.50%. The stored value remains 0.125, so applying Percentage to a value already entered as 12.5 would display 1,250%.
Custom formats can also add leading zeroes to identifiers. For example, the format 00000 displays the number 42 as 00042. It does not turn 42 into a different number, and it is not a safe way to preserve all identifiers: long account codes, postcodes, and other labels are often better stored as text if calculations are not needed. Use a display format when the value is genuinely numeric and only its appearance needs padding.
Set decimal and thousands separators
Excel normally follows the separator settings it receives from the operating system. In a typical UK setup, the decimal separator is a full stop and the thousands separator is a comma. If Excel is showing 1.234,56 or interpreting typed values unexpectedly, check whether its separator settings have been overridden; the workbook’s number format alone is not always the cause.
In Excel for Microsoft 365 on Windows, open File > Options > Advanced. Under Editing options, check Use system separators. When that option is cleared, Excel lets you specify the decimal and thousands separators directly. For the usual UK convention, those are a full stop and a comma respectively. Changing these settings can affect how values are entered and displayed in Excel, so consider the impact on other workbooks and on data copied from external sources.
Separator settings are distinct from the visual format of a particular cell. A custom format can control decimal places and grouping, while the application’s separator settings determine which characters Excel uses for those roles. Avoid switching separators just to make one imported column look right; first establish whether the column contains valid numbers or text, then address the import or conversion issue.
Fix numbers and dates imported from files
CSV files and copied data may arrive as text because the source uses different separators or date conventions. A cell containing the characters 1.234,56 may not become the number 1234.56 merely because you apply a UK number format. If it is text, Excel will generally preserve the text appearance and may not include it correctly in calculations. Check alignment, the formula bar, and whether a simple total includes the value; none of these checks alone is conclusive, but together they can reveal a conversion problem.
For a repeatable CSV import, use Data > From Text/CSV, review the preview, and choose Transform Data when you need to inspect or adjust column types before loading. In Power Query Editor, right-click the relevant column and choose Change Type > Using Locale, then specify an appropriate data type and the locale matching the source text. For example, select a UK locale for values written with UK separators, or use the source locale when the file uses another convention. Confirm the converted values against sample rows before relying on the result.
For dates, inspect day and month values that could be interpreted either way. If the source says 04/05/2026, it could mean 4 May or April 5 depending on its convention. Choose the correct source locale during import rather than relying on a later display-format change. Keep an untouched copy of the source data when the interpretation is uncertain.
Common mistakes and useful checks
- Assuming the symbol converts the amount: adding £ to a cell changes its display, not its currency value. A value of 25 remains 25; it is not converted from euros or dollars.
- Confusing text with numbers: a format cannot make text behave like a numeric value. Convert or re-import the data using the correct locale.
- Misreading day and month: changing a date’s display can hide an earlier import mistake. Verify the underlying intended date against the source.
- Using a percentage format on a whole-number percentage: Excel treats 12.5 as twelve and a half, so Percentage displays 1,250%, not 12.5%.
- Using number formatting for identifiers: codes may contain meaningful leading zeroes or exceed Excel’s reliable numeric precision. Store identifiers as text when they are labels, not quantities.
Before distributing a workbook, check that key columns sort and calculate as expected, that totals include the intended rows, and that dates are clear to the people receiving the file. If a workbook will be opened on computers with different regional settings, use explicit date labels and consistent number formats, and test a copy under the expected conditions.
Frequently Asked Questions
What is the UK number format in Excel?
For ordinary numbers, the common UK convention is a comma between thousands and a full stop before decimals, as in 1,234.56. UK dates are commonly day/month/year, and pounds are shown with £ when the value is in sterling.
How do I change Excel dates to UK format?
Select the date cells, press Ctrl+1 on Windows, choose Date, set Locale (location) to English (United Kingdom) if available, and pick a day-first format such as dd/mm/yyyy. This changes display, not the underlying date.
How do I show pounds in Excel?
Select the amount cells and choose the pound sterling symbol from Home > Number or from the Currency or Accounting options in Format Cells. The symbol formats the display; it does not perform currency conversion.
Why does Excel show a comma instead of a decimal point?
Excel may be using different system or application separator settings. On Windows, check File > Options > Advanced and the Use system separators setting. Also check whether the cells contain text imported from a source that uses different separators.
Why does changing the format not fix my imported numbers?
The values may be stored as text, or the source’s decimal and thousands marks may have been interpreted incorrectly. Import or convert them with the correct locale, then verify sample values and totals before using the data.
Related
- Excel Shortcut for Selecting a Column in One Step
- Excel Shortcut for Filtering a Table: Keys and Tips
- Excel Shortcut for Inserting a Row on Windows and Mac
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Unhide Rows in Excel at Once: A Clear Step-by-Step GuideTo unhide every hidden row on one Excel worksheet in one go, click the Select All corner at…
- Insert an Image Into Excel: Steps and OptionsYes. In Excel for Microsoft 365 on Windows, select a cell and choose Insert > Pictures > Place…
- Excel Zero Conditional Formatting: Highlight Zero ValuesTo highlight zero values in Excel, select the cells, then choose Home > Conditional Formatting > Highlight Cells…
- Excel Gridlines: How to Make Them Print on Every PageTo make worksheet gridlines print in Excel for Microsoft 365 on Windows, select the worksheet, open Page Layout,…
- Excel Group Rows at Multiple Levels: Steps and TipsYes. In Excel for Microsoft 365 on Windows, you can group rows in nested levels: select a set…
- Calculate Age in Excel for a Specific DateTo calculate age in Excel on a particular date, put the birth date in A2 and the date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.