7 must-know number formatting Excel tips:
1. Custom Format (with emojis)
Tired of boring data? Give it some personality by visualizing your data with Emojis 🙂. Open the Format Cells dialog box > Number Tab > Custom > enter ‘[Color 10]0.00%🙂;[Color 3]-0.00%☹️’ as type.
2. Add Text to Numbers
Adding text to numbers such as ‘Million’ manually is time-consuming and will cause calculation errors because the values are read as text. Instead, use custom formatting. Open the Format Cells box > Number Tab > Custom > enter ‘#,, “Million”’ as type.
3. Disguise Numbers as Text
Words can speak louder than numbers in Excel. For ex., we may not need to know a student’s exact score, but just whether they passed or failed. To disguise numbers, open the Format Cells box > Number Tab > Custom > enter ‘[<70]”Fail”;”Pass”’ as type.
4. Create Invisible Data
Have you ever deleted distracting data from your worksheet, and then all of a sudden, all you see are #REF! errors? To prevent this, you can just hide the data by opening the Format Cells dialog box > Number Tab > Custom > enter ‘;;;’ as type.
5. Convert Numbers -> Percentages
If you’ve ever converted integers to percentages by selecting the percentage icon on the Home tab, you’ve noticed it adds two decimal places. Here's a solution: Open the Format Cells dialog box > Number Tab > Custom > enter 0\% as type.
6. Add Leading Zeros
When entering data with leading zeros, you may have noticed Excel automatically omits the leading zeros from the number. To keep the leading zeros, open the Format Cells dialog box > Number Tab > Custom > enter ‘00000’ (# of digits in the number) as type.
7. Add Ending Zeros
Instead of counting out zeros when entering large numbers, we can use exponential formatting to add the zeros for us. Just type the number followed by ‘e7’ (7 being the number of zeros), and then update the number format to general.
Don’t get lost in the numbers with these formatting tricks! 😏
Follow me @exceldictionary for even more Excel tips and tricks.
And if you never want to forget my excel tips at your desk, check out my shortcut merch and ebooks. shop.morningbrew.com/collections/ex…
Share this Scrolly Tale with your friends.
A Scrolly Tale is a new way to read Twitter threads with a more visually immersive experience.
Discover more beautiful Scrolly Tales like this.