How to make your own cell format in excel. Number Formatting in Excel

Unformatted spreadsheets can be difficult to read. Rich text and cells can draw attention to certain parts spreadsheet, making them more visually noticeable and easier to understand.

Excel has many tools for formatting text and cells. In this tutorial, you'll learn how to change the color and style of text and cells, align text, and set a custom format for numbers and dates.

Text formatting

Many commands for formatting text can be found in the Font, Alignment, and Number groups found on the ribbon. Group teams Font allow you to change the style, size and color of the text. You can also use them to add borders and fill cells with color. Group teams Alignment allow you to set the display of text in a cell both vertically and horizontally. Group teams Number Allows you to change the way numbers and dates are displayed.

To change the font:

Select the required cells.
Click the drop-down arrow for the Font command on the Home tab. A drop-down menu will appear.
Hover your mouse over different fonts. The text font in the selected cells will change interactively.
Select the font you want.

To change the font size:

Select the required cells.
Click the drop-down arrow for the Font Size command on the Home tab. A drop-down menu will appear.
Hover your mouse over different font sizes. The font size in the selected cells will be interactively changed.
Select the desired font size.

You can also use the Increase Size and Decrease Size commands to change the font size.

To use the bold, italic, underline commands:

Select the required cells.
Click on the command bold (F), italic (K) or underlined (H) in the font group on the Home tab.

To add borders:

Select the required cells.
Click on the command drop-down arrow borders on the home tab. A drop-down menu will appear.
Select the border style you want.

You can draw borders and change line styles and colors using the border drawing tools at the bottom of the drop-down menu.

To change the font color:

Select the required cells.
Click the drop-down arrow next to the Text Color command on the Home tab. The Text Color menu appears.
Hover your mouse over different colors. The text color of the selected cells will interactively change on the worksheet.
Select the color you want.

The choice of colors is not limited to the drop-down menu. Select More Colors at the bottom of the list to access an expanded Color selection.

To add a fill color:

Select the required cells.
Click the drop-down arrow next to the Fill Color command on the Home tab. The Color menu appears.
Hover your mouse over different colors. The fill color of the selected cells will interactively change on the worksheet.
Select the color you want.

To change the horizontal alignment of text:

Select the required cells.
Select one of the horizontal alignment options on the Home tab.

Align text left: Aligns text to the left edge of the cell.
Align to Center: Aligns text to the center of the cell.
Align text to the right: Aligns text to the right edge of the cell.

To change the vertical alignment of text:

Select the required cells.
Select one of the vertical alignment options on the Home tab.

Along the top edge: Aligns text to the top edge of the cell.
Align to Middle: Aligns text to the center of the cell between the top and bottom edges.
Along the bottom edge: Aligns text to the bottom edge of the cell.

By default, numbers are aligned to the right and bottom edges of the cell, and words and letters are aligned to the left and bottom.

Formatting numbers and dates

One of the most useful functions Excel is the ability to format numbers and dates different ways. For example, you may need to display numbers with a decimal separator, a currency or percentage symbol, etc.

To set the format for numbers and dates:

Number Formats

General– This is the default format of any cell. When you enter a number in a cell, Excel will suggest the number format it thinks is most appropriate. For example, if you enter "1-5", the cell will display a number in the Short Date format, "1/5/2010".
Numerical formats numbers to decimal places. For example, if you enter “4” in a cell, the cell will display the number “4.00.”
Monetary formats numbers into display form currency symbol. For example, if you enter "4" in a cell, the number will appear as "".
Financial formats numbers in a format similar to Currency format, but additionally aligns currency symbols and decimal places in columns. This format will make long financial lists easier to read.
Short date format formats numbers as M/D/YYYY. For example, the entry August 8, 2010 would be represented as "8/8/2010".
Long date format formats numbers as Day of Week, Month DD, YYYY. For example, "Monday, August 01, 2010".
Time formats numbers as HH/MM/SS and signature AM or PM. For example, "10:25:00 AM".
Percentage formats numbers with decimal places and percent signs. For example, if you enter “0.75” in a cell, it will display “75.00%”.
Fractional formats numbers as fractions with a slash. For example, if you enter “1/4” in a cell, “1/4” will be displayed in the cell. If you enter "1/4" in a cell with the General format, the cell will display "4-Jan."
Exponential formats numbers into scientific notation. For example, if you enter “140000” into a cell, the cell will display “1.40E+05”. Note: By default, Excel will use exponential format for a cell if it contains a very large integer. If you do not want this format, then use the Number format.
Text formats numbers as text, that is, everything in the cell will be displayed exactly as you entered it. Excel uses this format by default for cells that contain both numbers and text.
You can easily configure any format using the Other number formats item. For example, you can change the US dollar sign to another currency symbol, specify the display of commas in numbers, change the number of decimal places displayed, and so on.

A useful feature in Excel that is often overlooked is finding (and changing) the formatting of cell contents. For example, for cell data that uses the 14-point Calibri font, it is quite easy to specify a different font and size. However, this process is not as intuitive as it could be, so I will present it to you step by step.

Let's assume that most of the worksheet cells have a yellow background and 14-point Calibri bold font for the content. In addition, the same cells are available on other sheets of the workbook. Let's change the formatting of the contents of all these cells as follows: font - Cambria; style - bold; size - 16 points; letter color - white; cell color is black.

To change formatting using Find and Replace, follow these steps:

Select any cell (or multiple cells if you want to search a specific range) and choose Home Editing Find and select Replace(or click Ctrl+H) to display the window Find and Replace.
Make sure that the Find and Replace fields in the Find and Replace dialog box are empty.
Click the button Format next to the field Find to open the dialog box Find format. If this button is not visible, click the button Options.
In the dialog box Find format you can specify formatting options. But to speed up this procedure, click on the arrow next to the button Format, select a command Select format from cell, and then click the cell with the formatting you want to replace.
Now click the button Format next to the field Replace on to display the dialog box Replace format.
And again: either select the command Select format from cell and specify the cell with the content whose formatting you want to replace, or use the dialog box tabs Replace format to specify the desired formatting. Go to the tab Font and select Cambria, set the size to 16, bold and set the text color to white. On the tab Fill Select black as the cell background color. If you set the same settings, then your Find and Replace dialog box should look like in Figure. 22.1.
Click Replace All.

Using the command in step 4 Select format from cell, you may find that the content formatting is not replaced in all cells. The fact is that, as a rule, one or more formatting settings do not match. For example, you will not be able to apply formatting to the contents of a cell that is set to number format General, to cell contents that have a number format date. To fix this, click the button Format and then in the dialog box Find format on each tab whose settings do not match the specified ones, click the Clear button.

In some cases. for example, you want to write about how to buy a furniture tour to China, a visa to China, tickets to the PRC, then it’s better to just select cells of a certain format. To do this, follow steps 1 to 4 and then click the button Find all. A dialog box will display more information about the matching cells.

Click the list at the bottom of the dialog box, and then press the keyboard shortcut Ctrl+A. Now that all matching cells are selected, you can format them however you like. Note that you can drag the column borders to increase their width, and you can also sort the contents of a column by clicking on its header.

Unfortunately, Excel 2010 never provided the ability to search for cells by their styles using the Find and Replace dialog box. Although Microsoft (since Excel 2007) has paid a lot of attention to cell styles, there is no feature to find all cells that use a particular style and apply a different style to those cells. You can find and replace formatting, but the cell style will not change.

The cell format includes parameters that determine: » the type of representation of the cell's numeric value; » alignment and orientation of cell contents; » font type; “cell framing; padding (background) of the cell. In addition, you can set protection parameters for data written to the cell. Typically cells are formatted by changing individual

parameters. Before you can change the format of a cell, you must place the table cursor on it. If you need to set the format for several cells at the same time, you should first select these cells.

Standard Formats Excel has six standard cell formats (sets of options) that can be used in all documents. When you start working in a new table, all cells have the same default standard format. This format is called Normal. It has the following options:

The depicted value repeats the true value;

Horizontal alignment depends on the data type;

Vertical alignment is done at the bottom edge;

Text without word hyphenation;

The orientation of the displayed value is horizontal;

The content is displayed using a standard font without highlighting or effects;

There is no frame around the cells;

No filling.

The remaining standard formats differ from the Normal format only in the display of numbers in cells:

Financial (or Delimited) - the number is rounded to two places after the decimal point, for example, the number 43.569 is represented as 43.57;

Financial(0) - the number is rounded to the nearest integer, for example the number 43.569 is represented as 44;

Monetary - the number is rounded to two digits after the decimal separator and a monetary unit sign is added, for example, the number 43.569 on a “Russian” computer is represented as 43.57 rubles;

Monetary(0) - the number is rounded to a whole number and a monetary unit sign is added, for example, the number 43.569 is represented as 44 rubles;

Percentage - if a number from 0 to 1 is entered into a cell after setting the format, then it is multiplied by 100, then the number (regardless of the value) is rounded to a whole number and the % sign is added to it, as a result of which, for example, the numbers 0.43569 and 43.569 44% will present themselves equally. If in the Options dialog box on the Edit tab you turn off the Automatic input, percent switch, then all numbers will be multiplied by 100, not just from 0 to 1. As a result, for example, the numbers 0.43569 and 43.569 will be represented as 44% and 4357 %. The same thing will happen if this switch is turned on, but numbers were entered into cells before they were assigned the Percentage format.

When using financial and monetary formats, the separator between the digits of units, thousands, millions, etc. is set in the Windows settings: the Numbers tab in the Language and Standards program in the Control Panel. This is usually a space. At Windows setup The sign of the monetary unit of the currency is also set: the Currency tab in the Language and Standards program on the Control Panel. For the ruble this is usually r. You can change the thousands separator used without changing your overall Windows setting.

To do this, in the Options dialog box, on the International tab, in the Numbers group, turn off the Use system separators switch and enter a different separator in the Thousand separator field. Sometimes a comma or apostrophe is used as such a separator. To apply standard formats, you can use the appropriate Formatting panel tools and keyboard shortcuts, as well as the Style dialog box. Presentation of a numeric value As already noted, values after they are entered into cells have the default form.

However, it is possible to change the appearance of the cells as desired. To change individual parameters, you can use the Formatting panel tools and keyboard shortcuts. However, the most complete options for formatting cells are provided by the Format Cells dialog box. To open the Format Cells window, do one of the following:

Execute the command Cells...(Format);

Run the Format Cells... context menu cells (selected cells);

Press the combination Ctrl+1.

On the Number tab of the Cell Format window, the Number Formats list contains the names of the groups of types of representation of numeric values available in Excel. By default, the list is set to General, which displays numeric values as they were entered by the user. After selecting a different value, additional options appear on the tab that allow you to set parameters for the appearance of numeric values.

For example, when you select Numeric, the following appears:

Field Number of decimal places - setting the number of places after the decimal separator;

Switch Number group separator - enable the number separator (space) between groups of units, thousands, millions, etc.;

Negative numbers list - select the type of representation of negative numbers. When choosing a format, it is convenient to use the Sample field, which changes in accordance with changes in the tab options. If the value (all formats) is selected in the Number formats list, then the Type list appears, including all available number formats, which, however, are written in the form of codes ( symbols). Having understood these codes, the user can independently set formats in the Type field.

For example, format codes use the following notation:

An optional digit, i.e. if there is no digit in the place of the # sign in the number, then nothing is displayed in that place;

Mandatory digit, i.e. if there is no digit in place of the 0 sign in the number, then a zero is displayed in this place (usually this is how the display of zeros after the decimal separator is set, for example, the 0.000 format sets the mandatory display of three digits after the separator);

Mandatory place, i.e. if in place of a sign? there is no digit in the number, then a space is displayed in this place (usually this ensures the alignment of numbers in one column, for example, the format ?????0.00 sets the alignment to the decimal separator of all numbers with up to six digits before the separator); h, hh, m, mm, s, ee, D, DD hours, minutes, seconds and days of the month (when using a single-letter notation, numbers less than 10 are displayed without a leading zero, i.e., for example, the time is 6 hours 5 minutes in the format h:m will look like 6:7, and in hh:mm format - 06:07); M, MM, MMM, MMMM month (for example, February in different formats will look like 2.02, fairy, February); IT, YYYY year (recorded using 2 or 4 digits, respectively).

Separators in date and time formats are determined by the settings specified in Windows settings:

Date and Time tabs in the Language and Standards program in the Control Panel. More detailed information about the notation used in the codes can be found in help system Excel. The user-created format can then be deleted by clicking the Delete button. When changing the format of numeric values, their true values (entered or calculated using formulas) do not change, i.e., for example, no matter how many decimal places are displayed, all available decimal places will be used in formula calculations.

In other words, installing a new format only changes appearance numerical value without changing its value. At the same time, it is possible to set a mode in which the accuracy of the true values will always be equal to the accuracy of the numbers presented on the screen. To do this, in the Options dialog box, on the Calculations tab, in the Book Options group, enable the Accuracy as on screen switch. This setting will apply to all tables in the book.

To set the format of numeric values, you can use the Formatting panel, which contains the following tools:

Currency format setting the standard format Currency;

Percentage format setting the standard Percentage format;

Format with delimiters sets the standard format Financial,

Increase the bit depth by one by increasing the number of digits after the decimal separator;

Reduce bit depth..;

Decrease by one the number of digits after the decimal separator. Keyboard shortcuts to set formats for numeric values:

Ctrl+Shift+" .... setting the format to Normal;

CtrI+Shift+1 ... setting the number format to 0.00 with a digit group separator;

Ctrl+Shift+2.... setting the time format hmm;

Ctrl+Shift+З.... setting the date format DD. MMM.YY;

Ctrl+Shift+4.... set the format to Cash;

Ctrl+Shift+5.... setting the format to Percentage;

CtrI+Shift+6.... setting the format to 0.00E+00 (exponential view). If you enter into a cell numeric value, however, it is required that it is perceived by the program as text, then for such a conversion in the Cell Form dialog box, on the Number tab, in the Number formats list, select the Text value.

Changing the cell format in Excel allows you to organize the data on your worksheet into a logical and sequential chain for professional work. On the other hand, incorrect formatting can lead to serious errors.

The contents of a cell are one thing, but the way the contents of the cells are displayed on a monitor or printed is another. Before changing the data format in Excel cell There is a simple rule to remember: “Whatever a cell contains can be represented in different ways, and the presentation of data display depends on the formatting.” This is easy to understand if shown with an example. See how you can use formatting to display the number 2 in 2 different ways:

Most Excel users use only standard tools formatting:

buttons on the “Home” panel;
ready-made cell format templates available in the dialog box are opened using the hotkey combination CTRL+1.

Formats of data entered into spreadsheet cells

Fill the range of cells A2:A7 with the number 2 and format all cells as shown in the figure above.

The solution of the problem:

What options does the Format Cells dialog box provide? The functions of all the formatting tools that the Home tab contains can be found in this dialog box (CTRL+1) and even more.

In cell A5 we used the financial format, but there is also a monetary format, they are very often confused. The two formats differ in the way they are displayed:

When displaying numbers less than 0, the financial format puts a minus on the left side of the cell, and the monetary format puts a minus in front of the number;
the default monetary format displays negative values in red font color (for example, enter the value -2р. in a cell and the monetary format will be assigned automatically);
in financial format, after abbreviating currencies, 1 space is added when displaying values.

If you press the hotkey combination: CTRL+SHIFT+4, the cell will be assigned a currency format.

Regarding the date in cell A6, it is worth mentioning the Excel rules here. The date format is considered as a sequence of days from January 1, 1900. That is, if a cell contains a value - the number 2, then this number in date format should be displayed as 01/02/1900 and so on.

Time for Excel is the value of numbers after the decimal point. Since we have an integer in cell A7, the time is displayed there accordingly.

Date format with time in Excel

Let's format the data table so that the values in the rows are displayed according to the column names:

In the first column, the formats already correspond to its name, so go to the second and select the range B3:B7. Then press CTRL+1 and on the “Number” tab indicate the time, and in the “Type:” section select the display method as shown in the figure:

We do the same with the ranges C3:C7 and D3:D7, selecting the appropriate formats and display types.

If a cell contains a value greater than 0 but less than 1, then the date format in the third column will be displayed as: January 0, 1900. While in the fourth column the date is already displayed differently thanks to a different type (1904 date system, see below for more details). And if the number

Notice how time is displayed in cells that contain fractional numbers.

Since dates and times in Excel are numbers, it is easy to perform mathematical operations with them, but we will cover this in the following lessons.

Two Excel date display systems

Excel has two systems for displaying dates:

The date January 1, 1900 corresponds to the number 1.
The date January 1, 1904 corresponds to the number 0, and 1 is already 01/02/1904, respectively.

Note. To ensure that all dates are displayed by default using the 1904 system, you can make the appropriate settings in the parameters: “File” - “Options” - “Advanced” - “When recalculating this book:” - “Use the 1904 date system.”

We clearly give an example of the difference in displaying dates in these two systems in the figure:

Excel help lists the minimum and maximum numbers for dates in both systems.

The settings for changing the date system apply not only to a specific sheet, but to the entire program. Therefore, if there is no urgent need to change them, then it is better to use the default system - 1900. This will help you avoid serious mistakes when performing mathematical operations with dates and times.