Microsoft Excel Tutorial – Number Format

Microsoft Excel can be an overwhelming program when you’re new. The program has so many functions that even experienced users often find new aspects of Excel that they need to learn. In this Microsoft Excel tutorial we’ll review how to use Excel’s number format. In other words, what types of information can I put into those excel boxes and how can I format that information?

Note: If you’re new to Excel you may also be interested in our Excel tutorial for beginners.

Note: This post may contain affiliate links.

Note: This post was written using Microsoft Excel version 2007. The theories described below apply to other versions of Excel but the menus may be slightly different. If you’re using a different version of Excel and need help finding the menus, leave a comment below and we’ll help you out.

Microsoft Excel Tutorial – Number Formats

The information, or data, that you put into each cell in Excel can come in different forms, or data types. It can be text (ie: words), numbers, dates, currency (ie: money), percentages, times and more. These different types of data can be referred to as the number format in Excel.

How to Enter Data in MS Excel

To enter data into a cell you simply go to the cell where you want to enter data (using your mouse cursor or arrow keys on your keyboard) and start typing. Anything you type will show up in the formula bar at the top of the screen and in the cell below. (Note: There may be times that the formula bar at the top doesn’t match what shows up in the cell below but we’ll discuss this later.)

What Number Formats does Excel Support?

There are many preset number formats available in Excel. In addition to these, users can create their own number formats. The most basic types of data that Excel supports are:

• General – most numbers will default to this data type; these will typically be displayed exactly as they’re typed
• Number – using a number format allows you to specify the number of decimal places, if you want a comma separator in the thousands place and how negative numbers will be displayed (ie: in red, parentheses, etc)
• Currency – used to show monetary values with the currency symbol of your choice; also gives you the ability to specify number of decimal places, if you want a thousands separator and how negatives will be displayed
• Accounting – also used for monetary values; decimal points and currency symbols will all be aligned in a column
• Short Date – displays a date in the number format (ie: 12/17/15)
• Long Date – displays a longer, written out, form of a date (ie: Thursday, December 17, 2015)
• Time – displays a time (ie: 12:00:00 AM)
• Percentage – multiplies the value by 100 and adds a percentage symbol; you can choose number of decimal places to display
• Fraction – displays the number as a fraction
• Scientific – displays a number using exponential notation; you can specify number of decimal places
• Text – for the most part data will be displayed exactly as it’s typed, even if it’s a number; Excel doesn’t recognize text as a number, even if the characters are numbers
• Special – can be used to identify special formats like zip codes and social security numbers
• Custom – allows the user to modify an existing format to create a new one

How to Change the Number Format (Data Type)

When you type into a cell, MS Excel may interpret what you’re typing and adjust the number format accordingly. For example, if you type 12/10/15 into a cell, Excel will understand that you’re typing a date and adjust the number format to a date.

However, there will be times when Excel doesn’t interpret what you’re trying to enter and you want to change the format. For example, you want to enter $1.32. You select the cell and type 1.32 but Excel doesn’t understand that you’re referring to money. In this case you’ll want to change the number format of the cell.

To change the number format you simply highlight the cell (or cells) that you want to change, go to the number section of the home menu and click the dropdown arrow where it shows the current data type.

Note: If you want to change one cell, highlight that cell by selecting it. If you’d like to change the number format for an entire column select that column by clicking on the letter at the top of the column to highlight the entire column. Similarly, to change the format for an entire row first select the row by clicking on the number in gray to the left of the row before changing the format.

The dropdown displayed in this section shows some of the most commonly used number formats. To change your selection to a number format, simply select one from the list.

If you’d like a number format that’s not listed in the dropdown or like to change the settings of the format, click ‘more number formats’ at the bottom to open Excel’s number format window.

With the number format dialog box open you can select the data type you’d like to use and also specify some additional settings. For example, to use a number format select number on the left hand side of the dialog box. The right hand side of the box will then display options for how the number will be displayed. You can specify the number of decimal places that will be shown, indicate whether you want a comma separating the number in the thousands position (ie: 1000 or 1,000) and how you’d like negative numbers displayed (ie: with a negative sign, in parentheses, in red, etc). In the box that says “sample” you will see an example of how your information will be displayed. This will allow you to modify settings until you achieve your desired result. When you’re happy with your settings click ok to continue.

Custom Number Formats

In addition to choosing a number format and modifying the options Excel provides you can also create your own number formats! To create your own format you would open the number format dialog box and select custom from the left side of the screen. Then, on the right side of the screen you can select one of Excel’s premade formats or type your own in the box using Excel’s defined symbols. We’ll discuss these more advanced methods in a separate post. Or, if you’d to read more about custom number formats in Excel now, check out this post from Excel Functions or this one from MBA Excel.

Do you have any questions on Excel’s number formats or data types? If you do, leave a comment below so we can help you out!