As an administrator, you can edit the different formatting rules your team have access to within Excel. They all use native Microsoft Excel features, but we have some specific guidance on areas that will be useful for you.
Note:
Custom Number Formats are not an UpSlide functionality but an Excel one. You can find the official documentation here.
Custom Number Formats
An Excel number format consists of 4 sections of code, separated by semicolons, in this order:
POSITIVE; NEGATIVE; ZERO; TEXT
Here's an example of a custom Excel format code:
Excel formatting rules use a number of characters as outlined in the table below
| Code | Description | Example |
|---|---|---|
| 0 | Digit placeholder that displays insignificant zeros. | #.00 - always displays 2 decimal places. If you type 5.5 in a cell, it will display as 5.50. |
| # | Digit placeholder that represents optional digits and does not display extra zeros. If a number doesn't need a certain digit, it won't be displayed. |
#.## - displays up to 2 decimal places. If you type 5.5 in a cell, it will display as 5.5. If you type 5.555, it will display as 5.56. |
| ? | Digit placeholder that leaves a space for insignificant zeros on either side of the decimal point but doesn't display them. It is often used to align numbers in a column by decimal point. | #.??? - displays a maximum of 3 decimal places and aligns numbers in a column by decimal point. |
| @ | Text placeholder. | 0.00; -0.00; 0; [Red]@ - applies the red font color for text values. |
| \ | Stabilising the currency | \ forces uses the currency in your number format even if you change the regional settings on your machine. Example: \£#,##0.0;\£(#,##0.0);-* "-"??- |
The following screenshot demonstrates a few number formats in action:
As you may have noticed in the above screenshot, the digit placeholders behave in the following way:
- If a number entered in a cell has more digits to the right of the decimal point than there are placeholders in the format, the number is "rounded" to as many decimal places as there are placeholders. For example, if you type 2.25 in a cell with #.# format, the number will display as 2.3.
- All digits to the left of the decimal point are displayed regardless of the number of placeholders. For example, if you type 202.25 in a cell with #.# format, the number will display as 202.3.
Below you will find a few more examples that will hopefully shed more light on number formatting in Excel.
| Format | Description | Input values | Display as |
|---|---|---|---|
| #.000 | Always display 3 decimal places. | 2 2.5 0.5556 |
2.000 2.500 .556 |
| #.0# | Display a minimum of 1 and a maximum of 2 decimal places. | 2 2.205 0.555 |
2.0 2.21 .56 |
| ???.??? | Display up to 3 decimal places with aligned decimals. | 22.55 2.5 2222.5555 0.55 |
22.55 2.5 2222.556 .55 |
For UK and European based editors: you may find that the number preview in the UpSlide dropdown for a £ or € is showing as $ but on your machine it works as a £/€ in Excel when applied. You can add a \ before the currency symbol to force it in a way that is different to using "" marks. Example:
- Works: \£#,##0.0;\£(#,##0.0);_-* "-"??_-
- Does not work: “\£”#,##0.0;”\£”(#,##0.0);_-* "-"??_-
- Does not work: “£”#,##0.0;”£”(#,##0.0);_-* "-"??_-