Change data format in excel. Formatting cells

Home / Laptops

When copying NUMERICAL data into EXCEL from other applications, it happens that the Numbers are saved in TEXT format. If numeric values are stored as text in a worksheet, this may cause errors when performing calculations.In some cases you can quickly change text format to numeric in several cells at once.Let's make this transformation using Special insert from the Clipboard.

If a cell contains a number, but the number is saved as text, this may cause a calculation error. For example, the SUM() function ignores numbers stored as text and may return a value that is not what is expected.

As a rule, such cells have a text format. If you change the cell format to numeric (via Cell Format or Home/ Number/ Number format), then no changes will follow.

Now let's take a closer look and see a green triangle in the upper left corner of each cell. When you select a cell, a yellow icon will appear on the side of it; by clicking on it, you can convert the values to numbers.

After conversion, the numbers will be aligned to the right edge of the cell (if, of course, the cell format is set to Horizontal alignment - by value).

To convert several values at once, select them. A yellow icon will appear at the top cell - use it to convert all values at once.

Another way to batch convert numbers stored as text is to use the following approach:

enter a number in any empty cell 1 ;
select this cell and copy its value to Clipboard(CTRL+C);
select cells that contain numbers saved as text;
select Special rate(on the tab Home in the group Clipboard click the arrow next to the command Insert, and then select from the list Special rate);
select Multiply;
click OK.

After multiplying by 1 , the numeric values will not change, but will be converted to numbers (if they were highlighted by mistake text values, the multiplication operation will not affect them in any way).

For EXCEL 2007: If the cells had a text cell format before multiplying, it will be preserved (numeric values will still be converted, but will be left aligned like text). You can change the cell format to numeric in the standard way through the menu Cell Format(by clicking CTRL+1).

The same technique will help convert DATES saved as text to DATE format (

Need to change the default format to another

Format for presenting data in cells

Default cell format (General)

By default, after creating a document, all cells are in the "General" format. This format has a number of tricks:

numbers are aligned to the right, and text is aligned to the left;
If, by changing the column width, you make it smaller than a certain one, then the number in the cells is replaced with the symbols "#". This is not a mistake. This means making the column wider;
if the number is very large ("6000000000000") or very small ("0.00000000000001"), it is automatically converted to exponential (scientific) format ("6E+12" and "1E-14" respectively);
When changing column width, decimals are rounded. For example, if you write "3.1415" and then change the width so that the "5" no longer fits, the cell will display "3.142".

Often you need to add a currency symbol, a percentage sign to the number in a cell, set the number of decimal places, present the date in a certain format, etc.

Do not add currency symbols manually! After this, it may turn out that when you try to use the value from this cell in a formula, Excel will throw an error! There is a way to tell Excel that cells are in a certain format, and it will automatically add currency symbols (and more) for us.

There are 3 ways to change the format of data in cells:

automatically, after entering certain data into a cell, Excel itself will change the cell format;
using the buttons on the Formatting toolbar.
using the "Format Cells" window;

After you enter certain sequences of characters, Excel automatically changes the cell format. After this, Excel tries to convert all subsequent numbers entered into this cell into this format.

Date. If you write "1.2.3" or "1/2/3" in a cell, Excel will replace it with "02/01/2003" (the first day of the second month of the third year). If you write "1.2" or "1/2", Excel will replace it with "01.Feb". In this case, the cell format will be automatically converted to “Date”;
Percentage. If you write “1%” in a cell, the cell format will automatically change to “Percentage”;
Time. If you write “13:46:44” or “13:46” in a cell, the cell format will automatically change to “Time”;

Attention!!! On different computers, the default formats for representing numbers, currency units, dates and times may differ! You can configure them along the path "Control Panel" -> "Language and Regional Standards" -> "Regional Settings" tab.

Change the format of cells using the buttons on the Formatting toolbar

There are 5 buttons on the Formatting toolbar that let you quickly change the format of selected cells.

Description of the buttons (from left to right):

Currency format. The default currency will be used (see above);
Percentage format. If there is already a number in the cell, Excel will multiply it by 100 and add the "%" sign. Everything is correct, because 1 watermelon is “100%”, and “0.7” watermelon is “70%”;
Delimited Format (Number Format). In this format, groups of digits (hundreds, hundreds of thousands, etc.) will be separated by a space and 2 decimal places will be added;
Increase bit depth. Adds one decimal place;
Decrease bit depth. Removes one decimal place.

Changing the format using the Format Cells window

Unformatted spreadsheets can be difficult to read. Rich text and cells can draw attention to certain parts of a spreadsheet, making them visually more prominent 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.

Formatting text

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 right size font.

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 desired style borders.

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 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 in 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 customize 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.

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 you change the data format in an Excel cell, remember a simple rule: “Whatever a cell contains can be presented 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 formatting tools:

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.

Solution to the problem:

What options does the Format Cells dialog box provide? The functions of all 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 avoid serious mistakes when performing mathematical operations with dates and times.

Microsoft Excel allows you to apply many different number formats to numbers. Using formatting, you can change appearance numbers without changing the values themselves. In this lesson, we'll look at the basic built-in number formats in Excel, as well as several useful options for working with numbers.

One of the most powerful tools Excel is able to apply specific formatting to text and numbers. To avoid displaying the same content in all cells, you can apply the desired formatting to them. By formatting numbers in Excel cells You can customize the appearance of dates, times, percentages, currency formats, and more.

How to Apply Number Formatting in Excel

In the following example, we'll change the number formatting in several cells to change the way the date is displayed.

Number format options in Excel

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

In fact, there are many more preset number formats, but you can only access them from the tab Number dialog box Cell Format.

1. General

The General format is set by default in all cells. When you use this format, Excel automatically determines which number formatting is most appropriate.

For example, if you enter “1-5”, the cell will display the number in short date format “1/5/2010”.

2. Numeric

In format Numeric values are displayed with decimal places. For example, if you enter the number “4” in a cell, it will display “4.00”.

3. Money

The Currency format displays the value in a cell with a currency sign. For example, if you enter the number “4” in a cell and apply the ruble format, then “4.00 rubles” will be displayed in it. The Currency format allows you to apply special formatting to negative numbers, as well as set the number of decimal places.

4. Financial

The financial format is very similar to the monetary one, but there are a number of significant differences. For example, cells in financial format cannot be formatted horizontally, zero values are displayed as dashes, and currency signs are aligned vertically. This formatting makes long money lists easier to understand.

5. Short date format

The short date format displays the cell contents as DD.MM.YYYY. For example, August 8, 2014 will look like “08/08/2014”.

6. Long date format

7. Time

Time format allows you to display the contents of cells in the form H:MM:SS. For example: 8:40:25 or 17:35:15.

8. Interest

Percentage format multiplies cell values by 100 and displays them with a percent sign. By default, two decimal places are displayed, but you can specify any other number of decimal places. For example, if you enter the number “0.75” into a cell, it will appear as “75.00%”.

9. Fractional

This format allows you to display the contents of a cell as a fraction. For example, if you enter the value “1/4” in a cell that has the format General, then in the end we will get the date “ 01.Apr“. If you enter exactly the same value into a cell with the format Fractional, the fraction “1/4” will be displayed.

10. Exponential

This format displays cell values in exponential form. For example, if you enter the number “140000” into a cell, it will be displayed as “1.40E+05”.

Excel by default assigns scientific format to a cell when the input value contains a large integer. If you do not want to display large numbers in exponential form, then use the format Numerical.

11. Text

The text format assumes that all values you enter will be displayed exactly as you entered them. Excel automatically sets this format when a cell contains numbers and text.