Objective 2.1: Apply Custom Data Formats and Validation (δείγμα)

  • 2.1.1. Create custom data formats
  • 2.1.2. Populate cells by using advanced Fill Series options
  • 2.1.3. Configure data validation

OBJECTIVE 2.1: Contents

2.1.1. Create custom data formats

One of the best ways to improve the readability of your worksheets is to display your data in a format that is logical, consistent, and straightforward. Formatting currency amounts with leading dollar signs, percentages with trailing percent signs, and large numbers with commas are a few of the ways you can improve your spreadsheet style. However, you can use Excel to go beyond these built-in formats to create custom number and date formats with which you can display your worksheet values exactly as you want them to be seen.

Excel’s built-in numeric formats give you a great deal of control over how your numbers are displayed, but they have their limitations. For example, there is no built-in format you can use to display a different currency symbol, such as the Euro symbol (€), or display temperatures using, say, the degree symbol (°).

To overcome these limitations, you need to create your own custom numeric formats. You can do this either by editing an existing format or by entering your own format from scratch. The formatting syntax and symbols are explained in detail later in this section.

Every Excel numeric format, whether built-in or customized, has the following syntax:

positive format;negative format;zero format;text format

The four parts, separated by semicolons, determine how various numbers are presented.

  • The first part defines how a positive number is displayed,
  • the second part defines how a negative number is displayed,
  • the third part defines how zero is displayed, and
  • the fourth part defines how text is displayed.

If you leave out one or more of these parts, numbers are controlled as shown in the following table.

Number of parts used

Format syntax

One

positive format;negative format;zero format

Two

positive and zero format;negative format

Three

positive, negative, and zero format
The following table lists the special symbols you use to define each of these parts.

Symbol

Description

Examples

#

Displays nothing if no number is entered.

If a cell's custom format is ### and you enter 25 into the cell, Excel displays 25.

0

Holds a place for a digit and displays the digit exactly as typed. Displays zero if no number is entered.

If a cell's custom format is 000 and you enter 25 into the cell, Excel displays 025.

?

Holds a place for a digit and displays the digit exactly as typed. Displays a space if no number is entered.

If a cell's custom format is 0??? and you enter 25 into the cell, Excel displays 025  (includin an extra space at the end).

. (period)

Sets the location of the decimal point.

If a cell's custom format is #.#0 and you enter 34.5 into the cell, Excel displays 34.50.

, (comma)

Sets the location of the thousands separator. Marks only the location of the first thousand.

If a cell's custom format is #,### and you enter 72345 into the cell, Excel displays 72,345.

%

Multiplies the number by 100 (for display only) and adds the percent (%) character.

If a cell's custom format is #% and you enter .75 into the cell, Excel displays 75%.

E+ e+ E- e-

Displays the number in scientific format. E- and e- place a minus sign in the exponent; E+ and e+ place a plus sign in the exponent.

If a cell's custom format is 0.00E+00 and you enter 723456789 into the cell, Excel displays 7.23E+08. Similarly, if a cell's custom format is 0.0E-00 and you enter 0.0000072 into the cell, Excel displays 7.2E-06.

/ (slash)

Sets the location of the fraction separator.

If a cell's custom format is 0/0 and you enter .75 into the cell, Excel displays 3/4.

\ (backslash)

Displays the following character correctly (some characters, eg. * # % can't be used directly in a custom number format as they won't appear in the result).

If a cell's custom format is \#0 and you enter 100 into the cell, Excel displays #100.
$ (  )  : - + <space>

Displays the character.

If a cell's custom format is $##0.00 and you enter 123.5 into the cell, Excel displays $123.50.
*

Repeats whatever character immediately follows the asterisk until the cell is full. Doesn't replace other symbols or numbers.

For example, you can create a dot trailer in a cell by adding *. to the format. So if the custom format is #*. and you enter 123 into the cell, Excel displays 123........... (where the dots continue until the cell is filled).
_ (underscore)

Inserts a blank space the width of whatever character follows the underscore, which can often help you to align your numbers.

For example, the custom format _(#.00 inserts a blank space the width of the opening parenthesis at the beginning of the displayed value.

Although the built-in date and time formats of Excel are fine for most purposes, you might need to create your own custom formats. For example, you might want to display the day of the week (for example, “Friday”). Custom date and time formats generally are simpler to create than custom numeric formats. There are fewer formatting symbols, and you usually don’t need to specify different formats for different conditions. 

The following table lists the date and time formatting symbols.

Symbol
Date Formats
Description

d

Day number without a leading zero (1 to 31)

dd

Day number with a leading zero (01 to 31)

ddd

Three-letter day abbreviation (Mon, for example)

dddd

Full day name (Monday, for example)

M

Month number without a leading zero (1 to 12)

MM

Month number with a leading zero (01 to 12)

MMM

Three-letter month abbreviation (Aug, for example)

MMMM

Full month name (August, for example)

yy

Two-digit year (00 to 99)

yyyy

Full year (7900 to 2078)
Time Formats

h

Hour without a leading zero (0 to 24)

hh

Hour with a leading zero (00 to 24)

m

Minute without a leading zero (0 to 59)

mm

Minute with a leading zero (00 to 59)

s

Second without a leading zero (0 to 59)

ss

Second with a leading zero (00 to 59)

AM/PM, am/pm, A/P

Displays the time using a 12-hour clock

/ : . -

Symbols used to separate parts of dates or times

[color]

Displays the date or time in the color specified

The best way to become familiar with custom formats is to try your own experiments. Excel stores each format that you try. If you find that your list of custom formats is getting a bit unwieldy or that it’s cluttered with unused formats, you can delete those formats.

To open the Format Cells dialog box

Image On the Home tab, in the Cells group, click Format, and then click Format Cells.

Image Right-click the cell or range, and then click Format Cells.

Image Press Ctrl+1.

To create and apply a custom number format

1. Select the cell or range of cells you want the new format to apply to.

2. Open the Format Cells dialog box.

3. On the Number tab, in the Category list, click Custom.

4. To base the custom number format on an existing format, click the base format in the Type list.

5. Edit or enter the symbols that define the number format.

Image

Define custom number formats in the Type box

6. When you are done, click OK to return to the worksheet.

To delete custom number formats

1. Display the Number tab of the Format Cells dialog box.

2. In the Category list, click Custom.

3. In the Type list, click the format you want to remove.

  • Tip: You can delete only custom formats; you can’t delete built-in formats.

4. Click Delete to remove the format from the list.

5. Click OK to close the Format Cells dialog box and return to the worksheet.