Discover how to expertly apply and manipulate number formats in Excel worksheets, making your data easy to understand and work with. Learn how to create custom formats to suit your needs, whether it's for sales data, tax information, inventory values, or timesheets.
Key Insights
- Excel is a powerful tool that allows users to store, track, and manipulate various types of numerical data, including sales data, test results, tax information, and inventory values.
- You can apply various formats to numbers in Excel, including currency, percentage, and date/time formats, improving the readability and functionality of your data.
- Excel also offers the ability to customize how formats are applied, allowing you to add or remove the comma thousands separator, increase or decrease displayed decimals, and change the display of negative numbers.
- Additional number formats available include general, number, currency, accounting, date, time, percentage, fraction, scientific, text, special, and custom. Custom formats are useful when you need a format not available in the other categories.
- Using the custom format, you can create your own format in Excel using various characters and symbols, such as the European date format (day, month, year).
- All these formatting tools can be easily accessed via the Home tab ribbon in Excel, ensuring that you can format your numbers according to your specific needs.
Number Format Categories
-
Currency vs Accounting
Currency floats next to digits; Accounting aligns symbol on left edge.
-
Percent
Multiplies by 100 and adds %; preserves underlying decimal value.
-
Date
Multiple formats (3/14/2025, March 14, 2025) — same underlying serial number.
-
Custom Formats
Build your own with codes (e.g., '#,##0.00 "USD"').
Applying Basic Number Formats
Excel worksheets are packed with numbers – because that’s typically what people are storing, tracking, and manipulating in their worksheets. And whether it’s sales data, test results, tax information, inventory values, or timesheets with hours worked and dates and times, Excel’s got the format you need to make your numbers appear – and operate – as you need them to.
Here in this worksheet that’s tracking sales and commissions, we have numbers that fall into currency, percentage, and date/time formats – and in formatting them as needed, you’ll see the tools you can use to format your numbers, however you need to.
This worksheet shows all of the proper formatting in place, and then we’ll work in a sheet with identical data, but no numeric formatting applied yet.
Switching to that unformatted sheet, first, let’s apply the Currency Formatting. We can do that by selecting the cells that require that format – I’ll press the CTRL key as I click and drag to select all the relevant cells at once – and then click the dollar sign on the Home tab, in the Number section of the ribbon. This inserts the dollar sign on the left, the comma thousands separator, and 2 decimal places. Note that the Number section displays “Accounting” as the format applied, although by adding the dollar sign, we’ve made this data into currency.
Once that formatting is applied, we can change the way the format is applied by adding or removing the comma thousands separator, and increasing or decreasing the displayed decimals. Again, the buttons we need are right there on the ribbon. I’ll leave these numbers set with the default Currency settings in place.
We can also apply the Currency format by clicking the Number Format button on the ribbon, and then selecting Currency from within the Format Cells dialog box. We can then customize how the format is applied, using the options in the dialog box. Here, with the same cells selected, I’ll remove the decimal places. Note that when the numbers to the right of the decimal are.50 or greater, removing the decimal places rounds up the number to the left of the decimal. Note that Currency formatting also offers options for displaying numbers in red, which applies to negative numbers.
However, for the Commission Paid, we need the decimal places so the amount shown is the same as the amount of the check each Salesrep received. So, let’s make sure we have 2 decimal places displayed in column N, by selecting that row’s numbers and then using the Number Format button and the Format Cells dialog box to restore the decimals. We could have also used the Increase Decimal button on the ribbon.
Next, let’s apply percentage formatting to the numbers in Columns L and M. Again, we can do it from the buttons in the Number group on the Home tab or we can click the Number Format button and choose Percentage there from the Format Cells dialog box. I’ve also used the Decrease Decimal button on the ribbon to decrease the decimals in the Commission column to zero.
Last, let’s apply a date format to the Date Paid, in column O. We can do this from the drop list in the Number section, and choose from Short or Long formats.
And, of course, we can use the Number Format button to choose how our dates will display. There are many more options here, some of which include the time, should that be part of the data.
While we’re here, let’s look at the other formats available and their options:
General
Number
Currency
Accounting
Date
Time
Percentage
Fraction
Scientific
Text
Special
Custom
Custom is a great category to use when you don’t see the format you need in any of the other categories. There are symbols – poundsigns (also known as hashtags), various types of punctuation, and letters of the alphabet – all representing various types of content, including currency, basic numbers, dates, times, and even exponents. Each symbol has a different meaning and gives Excel a different instruction as to how to display the data.
For a simple example, if you’re in the US, but you need a European date format – day, month, year instead of month, day, year – you can type your own format, using the d, m, and y characters, plus dashes, slashes, or any other separators you’d like to use.
So I’ll select our Date Paid data and create a Custom format – two ds for the day, two ms for the month, and then 4 ys for the year, separated by slashes. I type this in the Type box, which originally displays the formatting that was already applied, but allows me to replace it with my own Custom format.
Once we apply it, the month and day positions are switched from the default US date format.
A quick tip – if you’re a fan of right-clicking to get at tools in Excel, you can right-click any selection and choose Format Cells from the pop-up menu. This opens the same dialog box we’ve been using all along, which we’ve been accessing from the Number Format button on the Home tab.
As you can see, Excel makes it possible to format your numbers your way – all starting with the Home tab ribbon and some easily accessible tools!
Video Transcription:
Full Video Transcript
Excel worksheets are packed with numbers because that's typically what people are storing, tracking, and manipulating in their worksheets. Whether it's sales data, test results, tax information, inventory values, or timesheets with hours worked and dates and times, Excel's got the format you need to make your numbers appear and operate as you need them to.
Here in this worksheet that's tracking sales and commissions, we have numbers that fall into currency, percentage, and date time formats. In formatting them as needed, you'll see the tools you can use to format your numbers however you need to. This worksheet shows all the proper formatting in place, but let's switch to a sheet with the identical data but no formatting applied yet.
Currency Formatting
Switching to that unformatted sheet, let's first apply currency formatting. We can do that by selecting the cells that require the format. I'll press the Ctrl key as I click and drag to select all of the relevant cells at once, and then click the dollar sign on the Home tab in the Numbers section on the ribbon. This inserts the dollar sign on the left, the comma thousands separator, and two decimal places.
It says Accounting as the applied format, but because there's a dollar sign involved, we're considering it currency. Once that formatting is applied, we can change the way the format is applied by adding or removing the comma thousands separator and increasing or decreasing the displayed decimals. Again, the buttons we need are right there on the ribbon. I'll leave these numbers set with the default currency settings in place.
Using the Format Cells Dialog Box
We can also apply formatting by clicking the Number Format button on the ribbon and then selecting Currency from within the Format Cells dialog box. We can then customize how the format is applied using the options in the dialog box. Here, with the same cell selected, I'll remove the decimal places. If the decimal is 0.50 or greater, removing the decimal places rounds up the number to the left of the decimal.
You'll notice that there are also options for displaying numbers in red, which would represent negative numbers. However, for the commission paid, we need the decimal places so that the amount shown is the same as the amount of the check each sales rep received. So let's make sure we have two decimal places displayed in column N by selecting that row's numbers and then using the Number Format button and the Format Cells dialog box to restore the decimals. We could have also used the Increase Decimal button on the ribbon.
Percentage Formatting
Next, let's apply percentage formatting to the numbers in columns L and M. Again, we can do it from the buttons on the Number group on the Home tab, or we can click the Number Format button and choose Percentage there from the Format Cells dialog box. I've also used the Decrease Decimal button to reduce the number of decimals to zero.
Date Formatting Options
Last, let's look at how we apply date formats. We'll apply them to Date Paid in column O. We can do this from the drop list in the Number section and choose from short or long formats. And of course, we can use the Number Format button to choose how our dates will display. There are many more options here, some of which include the time, should that be part of the data.
Exploring All Format Categories
Speaking of the Format Cells dialog box, let's look at all the other formats available and their options. We have General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, and Custom. Custom is a great category to use when you don't see the format you need in any of the other categories.
There are symbols, pound signs (also known as hashtags), various types of punctuation, and letters of the alphabet, all representing various types of content including currency, basic numbers, dates, times, and even exponents. Each symbol has a different meaning and gives Excel a different instruction as to how to display the data.
Creating Custom Date Formats
For a simple example, if you're in the US but you need a European date format (day, month, year instead of month, day, year), you can type your own format using the D, M, and Y characters plus dashes, slashes, or any other separators you'd like to use. So I'll select our Date Paid data and create a custom format: two Ds for the day, two Ms for the month, and then four Ys for the year, separated by slashes.
I type this in the Type box, which originally displays the formatting that was already applied but allows me to replace it with my own custom format. Once we apply it, the month and day positions are switched from the default US date format.
Quick Access Tips
A quick tip: if you are a fan of right-clicking to get to tools in Excel, you can right-click any selection and choose Format Cells from the pop-up menu. This opens the same dialog box we've been using all along, which we've been accessing from the Number Format button on the Home tab. As you can see, Excel makes it possible to format your numbers your way, all starting with the Home tab ribbon and some easily accessible tools.