Changing the data format in Excel. Numeric formatting in Excel

Useful feature in Excel, which is often forgotten, is the search for (and changing) the formatting of the contents of the cells. For example, for cell data for which the Calibri font is 14 points, it is easy to simply set another font and its size. However, this process is not so intuitive that he could be, so I will present you step by step.

Suppose that for the contents of most working sheet cells, the yellow background and the Calibri font is 14 points with bold-ups. In addition, the same cells are available on other sheets. workbook. Change the formatting of the contents of all these cells as follows: Font - Cambria; Inscription - bold; Size - 16 points; Color letters - white; The color of the cells is black.

To change formatting using the search and replacement function, follow these steps.

Highlight any cell (or several cells, if you want to search in a specific range) and select the command Home Editing Find and Select Replace (or click Ctrl + H.) To display the window Find and replace.
Make sure the fields find and replace the Find and replace empty dialog box.
Press the button Format Near the field To findto open the dialog box Find format. If this button is not visible, click Parameters.
In the dialog box Find format You can specify formatting parameters. But to speed up this procedure, click on the arrow next to the button Format, Select Team Choose a cell formatAnd then click on the cell with the formatting you want to replace.
Now click Format Near the field Replace on to display the dialog box Replace format.
And again: either select the command Choose a cell format and specify the location cell to formatting which you need to replace, or use the dialog box tabs Replace formatTo specify the desired formatting. Click the tab Font And select Cambria, specify the size 16, bold stitching and set the white color of the text. On the tab Fill Choose a black color as a color background color. If you installed the same settings, then your dialog box to find and replace should look like in Fig. 22.1.
Click the Replace All button.

Using step 4 team Choose a cell formatYou 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 coincide. For example, you will not be able to apply the formatting of the contents of the cell for which the numeric format is specified. Common, to the contents of a cell that has a numeric format date. To fix it, click Formatand then in the dialog box To find The format on each tab whose settings do not match the specified, press the Clear button.

In some cases. For example, you want to write about how to buy a furniture tour to China, visa to China, tickets to the PRC, it is better to simply select the cells of a specific format. To do this, follow steps 1 to 4 and then click Find all. In the dialog box, more information about the coinciding cells will appear.

Click the list on the list at the bottom of the dialog box, and then press the key combination Ctrl + A.. Now that all coincident cells are highlighted, you can format them as you like. Please note that by dragging the borders of the columns, you can increase their width, as well as sort the contents of the column by clicking on its title.

Unfortunately, Excel 2010 did not provide the ability to search for cells on their styles using the Find and Replace dialog box. Despite the fact that Microsoft (starting with Excel 2007) pays a lot of attention to the styles of cells, there is no search function for all cells using a certain style, and the use of another style for these cells. You can find and replace formatting, but the cell style will not change.

When copying numeric data to Excel from other applications, it happens that the numbers are stored in text format. If the numeric values \u200b\u200bon the sheet are stored as text, this may result in errors when performing calculations. In some cases, you can quickly change text format On a numeric immediately in several cells.We will produce this conversion using a special insertion from the clipboard.

If the cell contains a number, but the number is saved as text, it may be an error in calculations. For example, the amount () function ignores the numbers saved as text and can return the value that is expected from it.

As a rule, text format is installed in such cells. If you change the cell format on the numeric (through Format cells or Home / Number / Numerical Format), the changes will not follow.

Now we will look at and see in the upper left corner of each cell a green triangle. When the cell is selected on the side of it, a yellow icon will appear by pressing which you can convert the values \u200b\u200binto numbers.

After the transformation, the number will be aligned with the right edge of the cell (unless, of course, the horizontal level is installed in the cell format - by meaning).

To convert several values \u200b\u200bat once - highlight them. The top cell will appear the yellow icon - with the help of it, convert all the values \u200b\u200bat once.

Another way to group transform numbers saved as text can be used the following approach:

enter into any empty cell number 1 ;
highlight this cell and copy it to Clipboard(Ctrl + C.);
select cells that contain numbers saved as text;
choose Special rate (on the tab the main in a group Clipboard Click the arrow next to the command Insertand then select the item in the list Special rate);
choose Multiply;
click OK.

After multiplying 1 The numeric values \u200b\u200bwill not change, but will be converted to numbers (if the text values \u200b\u200bwere allocated by the error, the multiplication operation will not affect them).

For Excel 2007: If the cell multiplying cells had a text format, it will be saved (numeric values \u200b\u200bwill still be converted, but they will be aligned to the left edge as text). Change the cell format on the numeric in the standard way through the menu Format cells(Pressed Ctrl + 1.).

The same reception will help convert dates saved as text to date format (

Not formatted spreadsheets can be hard readable. Formatted text and cells can draw attention to certain parts of the spreadsheet, making them visually more noticeable and easier for understanding.

In Excel there are many tools for formatting text and cells. In this lesson you will learn how to change the color and style of text and cells, align the text and set a special format for numbers and dates.

Text formatting

Many text formatting commands can be found in the font group, alignment, the number that is located on the tape. 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 the cells with color. Group teams Alignment allow you to specify the display of text in the cell both vertically and horizontally. Group teams Numberallow you to change the method of displaying numbers and dates.

To change the font:

Highlight the desired cells.
Click on the arrow of the Commands drop-down menu on the Home tab. A drop-down menu appears.
Load the mouse cursor to different fonts. In the selected cells will be interactively changing the font text.
Select the desired font.

To change the font size:

Highlight the desired cells.
Click on the arrow of the drop-down menu command font size on the Home tab. A drop-down menu appears.
Purge the mouse cursor for different font sizes. In the selected cells will be interactively varying the font size.
Select the desired font size.

You can also use commands to zoom in size and reduce the size to change the size of the font.

To use bold font commands, italics, underline:

Highlight the desired cells.
Click on the team of fat (g), italic or underlined (h) in the Font group on the Home tab.

To add borders:

Highlight the desired cells.
Click on the arrow of the command drop-down menu borders On the Home tab. A drop-down menu appears.
Select the desired border style.

You can draw boundaries and change styles and lines color using border drawing tools at the bottom of the drop-down menu.

To change the color of the font:

Highlight the desired cells.
Click on the drop-down menu arrow next to the Text Color Team on the Home tab. The text color menu appears.
Purge the mouse cursor for different colors. On the sheet will interactively change the color of the text of the selected cells.
Select the desired color.

The choice of colors is not limited to the drop-down menu. Select other colors at the bottom of the list to access the advanced color option.

To add the color of the fill:

Highlight the desired cells.
Click on the drop-down menu arrow next to the Fill Color Team on the Home tab. The color menu appears.
Purge the mouse cursor for different colors. On the sheet will interactively change the color of the fill of the selected cells.
Select the desired color.

To change text alignment horizontally:

Highlight the desired cells.
Select one of the alignment options horizontally on the Home tab.

Align text to the left edge: Lines text to the left edge of the cell.
Align in the center:Lines text in the center of the cell.
Align text on the right edge: Aligns text on the right edge of the cell.

To change the text alignment vertically:

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

At the top edge: Aligns text on the upper edge of the cell.
Align in the middle:Lines the text in the center of the cell between the upper and lower edge.
At the bottom edge: Lines text along the lower edge of the cell.

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

Formatting numbers and dates

One of the most useful features 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 of numbers and dates:

Numeric formats

Common - This is a format of any default cell. When you enter a number into the cell, Excel will offer the most suitable number in its opinion. For example, if you enter "1-5", the number in the cell is displayed in the format of the date, "1/5/2010".
Numerical Formats the numbers in the view with decimal discharges. For example, if you enter into the cell "4", then the number "4.00" will be displayed in the cell.
Monetaryformats the numbers in the view with the display symbol currency. For example, if you enter into the "4" cell, then in the cell number will be displayed as "".
Financial Formats the numbers in the view similar with a cash format, but additionally aligns currency symbols and decimal discharges in columns. This format will make it easier to read long financial lists.
Short format date Formats the numbers in the view M / D / GGY. For example, a record August 8, 2010 will be presented as "8/8/2010".
Long date format Formats numbers in the appearance of the day of the week, month of DD, yyyy. For example, Monday, August 01, 2010.
Time Formats the numbers in the view of the CC / MM / SS and the signature of AM or PM. For example, "10:25:00 am".
Percentage Formats the numbers in the view with decimal discharges and the mark of interest. For example, if you enter into a cell "0.75", "75.00%" will appear in it.
Fractional Formats the numbers in the form of fractions with a slash. For example, if you enter into the cell "1/4", then "1/4" will be displayed in the cell. If you enter a shared "1/4" to the cell with a format, then "4-jan" will be displayed in the cell.
Exponential Formats the numbers in an exponential record. For example, if you enter into the cell "140000", "1.40E + 05" will be displayed in the cell. Note: By default, Excel will use an exponential format for a cell if a very large integer is recorded. If you do not want this format, then use a numeric format.
Text Formats the numbers as text, that is, in the cell, everything will be displayed exactly as you entered it. Excel uses this format for cells containing and numbers and text.
You can easily configure any format using other numeric formats item. For example, you can change the US dollar sign on the symbol of another currency, specify the display of commas in numbers, change the number of decimal discharges displayed, etc.

Microsoft Excel allows you to apply many different numeric formats to numbers. Using formatting, you can change appearance numbers without changing the values \u200b\u200bthemselves. In this lesson, we will look at the basic embedded numeric formats in Excel, as well as several useful options for working with numbers.

One of the most powerful excel Tools It is the ability to apply certain formatting to text and numbers. To avoid the same display of content in all cells, you can apply the desired formatting to them. Using formatting numbers in excel cells You can give the appearance of the date, time, interest, money format and much more.

How to Apply Numeric Formatting in Excel

In the following example, we will change the numeric formatting in several cells to change the method of displaying the date.

Options for numerical formats in Excel

The figure below shows 11 options for numerical formats that Excel offers us.

In fact, pre-installed numeric formats are much more, but you can only access them on the tab. Number dialog box Format cells.

1. General

The general format is set by default in all cells. When using this format, Excel automatically determines which numeric formatting is the most appropriate.

For example, if you enter "1-5", the number in the cell is displayed in the summary of the date "1/5/2010".

2. Numeric

In the format of numeric values \u200b\u200bare displayed with decimal signs. For example, if you enter the number "4" into the cell, then "4.00" appears in it.

3. Monetary

The money format displays the value in the cell with a monetary unit sign. For example, if you enter the number "4" into the cell and apply the ruble format, then it will "4.00r.". The money format allows you to apply a negative number of special formatting, as well as set the number of decimal signs.

4. Financial

The financial format is very similar to money, but there are a number of significant differences. For example, horizontal formatting cannot be applied to cells in a financial format, zero values \u200b\u200bare displayed in the form of a dash, and monetary units are aligned vertically. Such formatting makes it easier to perceive long money lists.

5. Short date format

A brief format of the date displays the contents of the cell in the form of DD.MM.YYYY. For example, on August 8, 2014 will look like "08.08.2014."

6. Long date format

7. Time

The format of time, allows you to display the contents of the cells in the form of h: mm: ss. For example: 8:40:25 or 17:35:15.

8. Interest

In percentage format, the values \u200b\u200bin the cells are multiplied by 100 and are displayed with the percent sign. The default displays two signs are displayed, but you can set any other number of decimal signs. For example, if you enter the number "0.75" into the cell, it will look like "75.00%".

9. Drinking

This format allows you to display the contents of the cell in the form of a fraction. For example, if you enter the value "1/4" in a cell that has a format Common, then in the end we will get the date " 01.Apr.". If you enter exactly the same value in a cell having a format FractionalThe fraction "1/4" will appear.

10. Exponential

This format displays values \u200b\u200bin cells in exponential form. For example, if you enter the number "140000" into the cell, then it will appear as "1.40E + 05".

Excel default assigns an exponential format cell when the value is entered contain a large integer. If you do not want to display large numbers in an exponential form, then use the format Numerical.

11. Text

The text format assumes that all the values \u200b\u200byou enter will be displayed exactly as you entered them. Excel automatically sets this format if the cell contains numbers and text.