Introduction
As general formatting and alignment, are essential, the formatting of numbers is even more important. The most prominent usage of a spreadsheet is calculations, data presentations and interpretations; thus, numbers need to be formatted properly.
You can find the icons and dropdown menu for formatting numbers in the number group of the Home Tab in the MS Excel Ribbon.
The Basics of Number Formatting in Excel
In the picture below you can see the default number formats of MS Excel. In the MS Excel Ribbon dropdown menu and/or icons, you can choose which format you’d like to use. In column A you can see what the format looks like when you put in a number.
In general, number formatting only affects the appearance of the data, it does not change the underlying value.
Types of Number Formats:
- General: Not specified. The default format is without decimals. If you want to show decimals, you need to add the decimals (see picture below).
- Number: Same as general, but the default format is with two decimals.
- Currency: Has the currency symbol before the number, two decimals, and decimal separators.
- Accounting: Same as currency, but with the currency symbol left-aligned.
- Short Date: Depending on the language formatting month/day/year or other type of formatting. Can be changed according to preferences.
- Long Date: Weekday, month, day, year. Can be changed according to preferences.
- Time: Hour/Minute/Second AM/PM. Can be changed according to preferences.
- Percentage: Number format with percentage symbol.
- Fraction: Number format as a fraction.
- Scientific: Long numbers will be shortened in scientific format.
Common Number Formatting Techniques
Applying Basic Formats
If you want to apply basic number formats, simply select the cell or range, then go to the “number format” dropdown on the Ribbon, and choose the format you want accordingly. Very straightforward.
Custom Number Formatting
Sometimes, you might want to have a specific format. Then you can select “custom” in the format cells dialog box and enter your format code. This is very common when using phone numbers. You can format them as ###-###-####. The dashes will then be automatically added to your cells.
Using Conditional Formatting with Numbers
If you want to apply different formats in your spreadsheet cells based on their values, you can use conditional formatting. You can highlight cells that exceed a certain number or apply a color gradient for varying data intensities. When using conditional formatting, large datasets can be interpreted with ease.
Formatting Numbers for Specific Needs
Currency and Accounting Formats
Currency and accounting formats include currency symbols, but they are different in presentation. The accounting format aligns currency symbols on the left side and uses decimal points within the column. This is useful in financial reports. For general purposes, using the currency format shows the currency symbol next to the number.
Date and Time Formatting
Time-based data need to be presented in date and time formatting. As always, you should also be consistent with your date and time formatting to keep your spreadsheet clean and well-organized. MS Excel has a wide variety of date and time formats. You should check them out and decide which ones fit best with your spreadsheet.
Percentage Formatting
If you want to show exact percentages, simply use the percentage formatting. The cell value will then be multiplied by 100 and a percentage symbol will be added by MS Excel.
Scientific Notation
Scientific notation expresses numbers as a coefficient multiplied by 10 to the power of an exponent. This makes it easier to work with extreme (high or low) values in a scientific or engineering environment.
Advanced Number Formatting
Custom Formatting Codes
If you want to tailor the number presentation exactly to your needs, you can use custom formatting. Custom codes provide additional flexibility beyond the MS Excel default formats. A good example would be to show positive numbers in green color, negative numbers in red color, and zero in black color. The code would then be [Green]0;[Red]-0:[Black]0.
Text and Number Combinations
With text and number combinations both, text and numbers can be displayed in a single cell. Custom formats can combine this, i.e. “Day ” 0 will display a number like “Day 1” when the value 1 is entered.
Using Excel Functions for Formatting
Functions like TEXT() allow you to format numbers within a formula. The formula =TEXT(A1, “0.00%”) will format the number in cell A1 as a percentage with two decimal places.
Best Practices for Number Formatting
Consistency in Formatting
As mentioned before, consistency is key to building professional-looking spreadsheets. Always ensure that data types are formatted the same way across the spreadsheet.
Choosing the Right Format for Your Audience
Depending on your target audience, different formats might be appropriate. For example, financial analysts appreciate numbers with decimal separators, while another audience may prefer rounded numbers.
Conclusion
With both, basic and advanced formatting techniques, you can enhance the presentation of your spreadsheets and make them visually appealing. I know many people who hate to work with MS Excel and one of the reasons is that they are unorganized in their daily lives. The bonus point when using Excel and spreadsheets, in general, is that they force you in some way to get organized.
I can tell from my own experience that my work with MS Excel helped me also to get other things in the row in my life. And the proper formatting and appealing spreadsheet design is just one single part of it.
FAQ: Formatting Numbers in Excel
How do I apply the same number format to multiple cells at once?
- Select cells or a range of cells.
- Go to the Home Tab and choose the format you want from the number format dropdown. Alternatively, you can right-click the selected cells, choose “format cells”, and apply the format from the dialog box.
Why are my numbers showing as dates in Excel?
- When Excel misinterprets a number as a date, select the affected cells, right-click, choose “format cells”, and select the appropriate number format.
- If still not correct, the data might be stored as text. Simply convert it to a number then.
How can I remove formatting from a cell?
- Select the cell or range of cells.
- Go to the Home Tab and click the “clear” dropdown in the Editing group.
- Choose “clear formats.
How do I format negative numbers in red in Excel?
- Select the cells.
- Right-click and choose “format cells”.
- In the Number Tab, select “number” or “currency” and check the option for displaying negative numbers in red.
- Alternatively, create a custom format using the code 0;[Red]-0.
How can I ensure consistency in number formatting across a large Excel workbook?
- Use Excel’s built-in styles or create custom styles for numbers.
- Apply the same formatting to entire columns or rows, not just individual cells.
- Utilize the “Format Painter” tool to copy formatting from one cell to another.
- Consider creating a template with pre-set formats if you frequently work on similar types of data.