Excel Beginners

Data Types and Formatting: Step 2 To Mastering Spreadsheets

Sometimes the generic options are good enough

Using The Formatting Tool

Navigating To The Formatting Tool

We have discussed data types, what they are, how they can be determined, and the steps to take to better organize them, but we have not yet covered how to display them to your liking. While it may be an underused part of Excel, it can often save time and energy compared to either adding in additional cells with descriptive information, or leaving the cells in a sub-par format and having to question the information at a later date.

The tool is a relatively simple one to use and access, despite its lack of popularity. To navigate to this formatting menu, you will begin on the Home Tab of the ribbon. Locate the number section and click on the arrow located in the bottom right corner of that area. A settings menu will open on the ‘number’ tab.

It is within this menu that you will be able to determine the exact formatting for the cells which were highlighted prior to its opening.

Using Pre-Existing Formatting Tools

This tool has a total of twelve sections under the Number tab, which is where the aspect of the tool important to us is located. The twelve sections establish the basic formatting style of the cell formatting drop-down as well as enable users the option to create their own formatting.

General Format

The general format is unique among the formats as it holds no precise formatting rules. Cells in the general format will take on the format which the system determines best fits the input value of the cell.

Number Format

The number format allows users to select the default number of decimal places to show in a field (when needed), determine if they want to use a 1000’s separator in their format, and how to display negative values in cells.

Currency Format

The currency format has the same criteria for modification as the number format with the exception of the 1000’s separator. That is replaced by the currency symbol selector, which allows users to decide their preferred currency symbol for the worksheet.

Accounting Format

The accounting format is more restrictive than the previous two formats as there are standardized ways to display values. Users can still select their preferred number of decimal places as well as the currency symbol of their choosing.

Date Format

The date format provides over half a dozen display options for how users may want to display the date within a cell. These options are also based on the country which users identify themselves with, as each country has certain standards which they normally abide by. You can see a preview of each date format in the selection window.

Time Format

The time format similarly displays multiple common formats to display time values, with the standard formatting options again being displayed based on the country the user selects.

Percentage Format

The percentage format is simple in that the only variable to modify is the number of decimal places which are to be displayed within a cell.

Fraction Format

The fraction format has none formatting options based on user preference. It is to be noted however that the fraction formats do round values to their nearest available point. The ‘up to __ digit(s)’ formats will display any fraction in it’s most reduced form, at a maximum number of digits in the denominator. The ‘As’ formats will round their values to the nearest multiple of the chosen format.

Scientific Format

The scientific format is very similar to the accounting format in that it is a highly standardized format, but in this situation the only setting available is the number of decimal places to show in a cell.

Text Format

The text format will convert whatever value is input to that cell into the text data type. Excel will naturally show a warning message to advise if it deems a value of another cell type is being stored as text, but will not force the format to change. There are no settings to adjust.

Special Format

The special format includes known special information types which are relevant to your region. For the region ‘Canada’ Excel offers a phone number format, as well as a social insurance number format which satisfy the formatting styles of these structures.