Applying Basic Number Formats

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!