Custom Formatting Tricks

Even if you have no idea what is custom formatting, these tricks will help you win over some common formatting needs for your data

 

I will share with you 10 custom formatting codes that you need to copy in the custom formatting box

Make sure to select the cells that you want to format before you open the custom formatting box. Let’s start rolling the tricks.

 

1. Format numbers with preceding 0 (zero/s)

At times you need your numbers to appear like this

1. Select the numbers
2. In the custom formatting box type the code – 000

This code will have at the max 3 zeros and will show preceding zeros depending on a number for eg

• 7 (james bond) will appear as 007
• 10 will appear as 010
• and 110 will appear as 110

 

2. Disappear data

1. Using the code – ;;; (3 semicolons) will not show the data in the cell.
2. Although the data will still be there in the cell but not visible in the cell

You can use this trick to hide dummy data for charts or even fool around with your boss at times (Caution: I am just saying :D)

 

3. Convert Numbers in Indian Comma Format

1. To display the numbers in Indian Comma Format use the code- [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0
2. If you want to add a currency symbol (like Rs or INR) use the modified code- [>=10000000]”Rs” ##\,##\,##\,##0;[>=100000]”Rs” ##\,##\,##0;”Rs” ##,##0

 

4. Format the numbers like a switch (Yes/No, Hide/Display)

1. Use the code- “Show”;;”Hide”
2. This code will replace all 1 (positive numbers) with the word “Show” and 0 (zeros) with the word “Hide”

 

5. Covert into Total Hours

1. To convert the numbers into total hours use the code- [H]
2. Bonus Tip : To have total hours and minutes use the code- [H]:mm
   • With the above code the number 4.02 will be displayed as 96:28
   • and the number 1 will be displayed as 24:00

 

6. Convert into Total Minutes

1. Just as the way we can convert into total hours we can convert into total mins as well, just use the code- [M]
2. You can even have seconds after total hours by using the code- [M]:ss

 

7. Add a text after or before a number

1. You can add a Text before or after the number by applying the above codes
2. Once you get a hang of how it works, you can modify it to your need

 

8. Get Trailing Dots after the text

1. This is an extremely useful trick for creating forms
2. Use the code- @*.
3. This code will add trailing dots after the text for the remaining cell width

 

9. Covert Large Numbers into thousands or million format

1. For converting the numbers into thousand use the code- 0.0, “k”
2. For converting the numbers into million use the code- 0.0,, “Mn”
3. Applying a comma (,) after the 0 means dividing the number by 1000, similarly applying 2 commas means dividing by 10 ^ 6 (1,000,000)
4. This is an extremely good way to trim your numbers to shorter format without actually affecting the number

 

10. Custom Formatting also works for Charts

Let’s say that I want to format the axis in a thousands format

1. Select the axis
2. Press Ctrl + 1 to open the format axis box
3. Under the Number Category
4. Go in the Format Code box and type your code- 0, “K”
5. Click on Add and Boom! the axis will be in thousand format

 

11. Bonus Tip : Color to your negative numbers in Red

1. To add a color to let’s say negative numbers use the code- 0;[Red]-0
2. The beauty about this trick is that you can customize red to what ever color your want

All you need to know is the Color Index Table

Here are a couple of things that you should keep in mind

1. Colors are always written in square brackets [ ] and at the start of the code
2. Color names are only applicable to the standard 8 colors rest follow the color index number. Black is a default color
3. The case does not matter [red] = [Red] = [RED] (all are same)
4. Download an Excel file containing the Color Index Table

So let’s say that you don’t want red but want maroon for negative numbers. Write the code this way 0;[color 9]-0

 

Which trick/s did you find the most interesting ?

1. Do you have some tricks up your sleeve ?
2. Which tricks have you found useful enough and how are you going to apply to your data?

Put down your thoughts and questions in the comments

 

Other Custom Formatting / Formatting Tricks

1. Learn Custom Formatting from Scratch
2. 4 Interesting Date Custom Formats
3. Beauty Tips for your Excel Reports