Graphs and charts (5)
Working with VB project (12)
Conditional formatting (5)
Lists and Ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

Cell format does not change

Once again, on one of the forums, I saw a question with a description of an incomprehensible bug: when trying to change the format of cells or call the Format Cells dialog box (right mouse button - Cell Format Nothing happens. Which makes it impossible to change the visual style of the cells. In this case, this problem may not occur for all cells, but selectively for any cell on the sheet. If someone did not understand the problem: for some cells it is impossible to assign or change cell borders, fill color, font color and style, etc.

Possible reason: at some point when working with a file, a format conflict occurred in the cells and the cell style was locked. Typically, this occurs when working with a file in versions of Excel 2007 and later. I suppose that this is due to the fact that in new file formats, data on cell formats are stored in XML schemas and sometimes, when changing, there is a conflict of styles and Excel cannot determine the current cell format and, as a result, change it. I can't say 100% if this is really the reason, but I have no other options yet. It is necessary to find out from Microsoft, but none of the "owners" of the problem can say at what moment the reason appeared, and without this it is unlikely that it will be possible to dig something up. I myself could not simulate such a situation.

Solution: I still know two possible solutions to this problem. Which one to use, if you are faced with this problem, is up to you.

Denial of responsibility: all actions you perform at your own peril and risk. The author of the article does not bear any responsibility for any kind of damage that may be incurred as a result of your actions.
Therefore, I highly recommend trying solutions to problems on copies of your files in order to save your data anyway.

option 1

That's it, now all that remains is to save the book. True, there is a nuance here: with a similar approach, all formats will also be deleted, even those cells with which there were no problems (the fill of cells, color and style of fonts, borders, etc. will be removed).
This option is the most effective, because removes all formats from the book and the possibility of a repeat of the problem in the near future is very small.

Option 2

1. copy any cell for which the format changes without problems
2. select the "naughty" cells-right mouse button- Paste Special - Formats

This option (in books, kindly provided by the bearers of the problem) worked pretty well for me. What is its obvious advantage over the first option - you do not lose all the formats in the book. However, there is another nuance - it will not be possible to say with certainty that the problem will not recur in the very near future. Although it cannot be said that it will be repeated.

Did the article help? Share the link with your friends! Video lessons

("Bottom bar" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

When filling Excel sheets with data, no one succeeds in filling everything beautifully and correctly at once on the first try.

In the process of working with the program, you constantly need something: change, edit, delete, copy or move. If erroneous values ​​are entered into a cell, naturally we want to correct or delete them. But even such a simple task can sometimes be difficult.

How to set cell format in Excel?

Each Excel cell contains three elements:

1. Meaning: text, numbers, dates and times, logical content, functions and formulas.
2. Formats: type and color of borders, type and color of fill, way of displaying values.
3. Notes.

All three of these elements are completely independent of each other. You can set the format of the cell and not write anything to it. Or add a note to an empty and unformatted cell.



How to change the format of cells in Excel 2010?

To change the format of cells, open the corresponding dialog box with the key combination CTRL + 1 (or CTRL + SHIFT + F) or from the context menu after clicking the right mouse button: the option "Format cells".

There are 6 tabs available in this dialog box:

If you do not achieve the desired result on the first try, call this dialog box again to correct the cell format in Excel.

What formatting is applicable to cells in Excel?

Each cell always has some format. If there have been no changes, then this is the "General" format. It is also the standard Excel format in which:

• numbers are aligned to the right;
• the text is aligned to the left;
• Colibri font with a height of 11 points;
• the cell has no borders and no background fill.

Deleting a format is a change to the standard "General" format (no borders and fills).

It should be noted that the format of cells, unlike their values, cannot be deleted with the DELETE key.

To remove the format of the cells, select them and use the "Clear Formats" tool, which is located on the "Home" tab in the "Editing" section.

If you want to clear not only the format, but also the values, then select the "Clear all" option from the drop-down list of the tool (eraser).

As you can see, the eraser tool is functionally flexible and allows us to make a choice of what to delete in the cells:

• content (same as DELETE key);
• formats;
• notes;
• hyperlinks.

The Clear All option combines all of these features.

Removing notes

Notes, as well as formats, are not deleted from the cell with the DELETE key. Notes can be deleted in two ways:

1. Eraser tool: "Clear Notes" option.
2. Click on the cell with the note with the right mouse button, and from the appeared context menu select the option "Delete note".

Note. The second way is more convenient. When deleting several notes at the same time, you should first select all their cells.

Instructions

By default, Excel applies a format called "general" to each cell in a spreadsheet. It implies that an equal sign at the beginning of the cell contents means that a formula is placed in it. This is inconvenient if, in fact, the equal sign is only part of the text. In such cases, the spreadsheet editor usually displays an error message instead of text in. You can fix the problem by changing the cell format to "text". To do this, start by highlighting the desired area of ​​the table - a column, a row, or a specific group of cells.

Open the drop-down list located in the top line of the "Number" group of commands on the "General" tab of the application menu. Scroll to the bottom and select the bottom line - "Text". The same can be done in another way - right-click the selected range and select the "Format cells" line in the context menu. Click on the "Text" line in the "Number Formats" list and click the OK button. This completes the procedure.

If the cells display formulas instead of their results, it seems that one of the Excel preferences should be changed. To do this, go to the "Formulas" tab and click on the "Show formulas" icon - it is located to the right of the "Influencing cells" label in the "Formula dependencies" command group. When enabled, this icon should be highlighted in yellow.

Such a defect can be repeated every time the document is opened, if this option is fixed in the Excel settings. To change the corresponding setting, open the main menu of the spreadsheet editor and select the "Parameters" item. In the list of sections, click on the "Advanced" line and scroll the list of settings to the subsection "Show parameters for the next sheet". In the "Show formulas, not their values" box, clear the check box and click OK. When you close the document, do not forget to save it so that the situation does not repeat itself the next time you load it into the spreadsheet editor.

Sources:

• Replace formulas with calculated values ​​in Excel 2013

Excel makes it possible to perform various calculations, including analytical ones. The program allows you to create your own formulas or use built-in functions. But often it is necessary to keep the final result in a "stable" form. Or you simply do not want the other user to see the formulas used for the calculations. In these cases, use storing as values.

Instructions

Press the right mouse button. A context menu will appear. Activate the item "Copy" in it, a dotted frame should appear around the cell. Right-click again. In the list that appears again, select the "Paste Special" line. A new window will open with insertion options. With the left mouse button, mark the item "values" or "values ​​and formats of numbers". Then click the "OK" button. You will see that the result number appears in the function bar instead of the formula.

You can change formulas to values ​​in several cells at once. To do this, you first need to select these cells, and then repeat the steps described in step 2.

In order to change formulas to values ​​in an entire row or column, you must first select the entire corresponding area. To do this, move the cursor to the upper border of the sheet with the names (letters) of the columns or the left border with the line numbers and set it at the required level so that the cursor turns into a black arrow. Then press the left mouse button and you will see that the entire row (column) is highlighted. Follow the algorithm from clause 2 of this instruction.

If you want to save all calculations on the worksheet as values, move the cursor to the upper left corner of the worksheet. When you see how the corner square is highlighted, press the left mouse button. This will select the entire work area. Repeat the sequence of actions from step 2.

note

After saving the file with "values", you will not be able to restore the formulas by which the calculations were carried out. Therefore, it is better, just in case, to make a copy of the original file and save it without formulas.

The number of known chemical compounds is estimated at millions. As science and production develop, there will be more and more of them, and even the most qualified specialist will not be able to remember them all. But you can learn how to compose formulas yourself, and this will allow you to navigate much more confidently in the world of chemical compounds.

You will need

• - periodic table of D.I. Mendeleev;
• - salt solubility table;
• - the concept of valence.

Instructions

Consider the periodic table of chemical elements of D. I. Mendeleev. You will see that all the elements that are there are divided into groups. Each of the groups occupies a specific column. On the top line of the table, you will see Roman numerals. They indicate the group number and are at the same time an indicator in each column.

The program has a formula bar. When you enter data and arithmetic signs, the contents of the cell are reflected in that row at the same time. After you have entered everything you need, press "Enter". Look at the status bar. There should appear the inscription "Done". If you leave this cell and select another, the label on the status bar will disappear. It will appear again as soon as you enter this cell again.

Microsoft Excel allows you to enter not only simple formulas, but also those that contain links. For example, you need to perform actions with data located in two cells. Select the cell where you will paste the formula. As in the first case, enter the "=" sign in it. Pay attention to the status bar, where the text "Enter" should appear.

Place the mouse in the cell, the data from which will be applied. Click the mouse. The link should appear on the formula bar and in the first cell. Control the process line by line. The word "Specify" should appear there. Check the icon for the desired arithmetic operation, and then click on the next cell. In the status bar, you will first see the word "Enter" again, and then - "Specify." Hit Enter. Don't forget to look at the status bar. After you enter the formula in the desired cell, the result will appear there. The status bar will appear as soon as you select this cell.

Quite often it becomes necessary to edit the formula. This can be done in two ways - directly in the cell or in the status bar. To change the formula on the status bar, select a cell. Go to the line where the required formula should be displayed at this moment. Replace all or part of it.

Related Videos

note

In the OpenOffice spreadsheet editor, you enter a formula in a similar way.

Useful advice

To make edits directly in a cell, in the "Edit" tab, find the "Options" window, and in it - the "Tools" menu. Select the edit mode, Select the desired cell and press the F2 key. You can go the other way, first converting the formula to text, and then, after changing, back to the formula. To do this, it is enough to remove and again put an equal sign in front of the expression.

In Microsoft Office Excel, formulas are the main tool for processing and analyzing table data. To streamline processes, the application has a large number of simple and complex functions that can be called by the user in formulas and applied to the values ​​entered in cells.

Instructions

A formula in Excel can include numeric and text values, signs of logical and mathematical operations, references to other cells, and calls to functions. The result of the calculation can be both numerical values ​​and logical ones - True / False.

When calculating a formula, the program uses the same procedure as in mathematics. Each formula starts with an equal sign and ends with the Enter key. The used one is displayed in the formula bar, while in the cells only the result of the calculations is visible.

You can either write the formula yourself, or choose one of the built-in functions. Place the mouse cursor in the cell where you want to get the result and go to. If you want to set the formula yourself, press the [=] key on the keyboard and, using mathematical and other symbols, enter the desired formula, indicating the names of the cells in the format A1, B2, and so on.

So, to calculate the sum of the data in the range of cells B1, B2, B3 and B4, the formula will look like this: = B1 + B2 + B3 + B4. When specifying cell addresses, lowercase letters can be used. After entering the formulas, the program automatically converts them.

If you want to use the built-in functions, enter an equal sign in the formula bar, refer to the box at the far right of the formula bar. Use the drop-down list to select the function that suits your case.

If the required function is not in the list, select the last item "Other functions" in the context menu, a new dialog box will open. Use the Select Function and Category groups to find the one that suits you. When you have decided, confirm the actions with the OK button or the Enter key.

A new Function Arguments window appears. Enter in the empty field the names of the cells to which you want to apply the formula, or select them in the worksheet with the mouse. When you are finished selecting, press the Enter key or the OK button in the dialog box.

An alternative to the latter is to press the fx button in the formula bar. It calls the "Function Wizard", in the window of which you need to select the formula that suits your case, enter or use the mouse to specify a range of cells with data and press the Enter key or the OK button.

Related Videos

Sources:

• how to apply a formula to a column

You can see the formula placed in a cell in a Microsoft Office Excel spreadsheet if you turn on Edit Mode. It is also displayed in the formula bar of an open sheet. However, sometimes you need to look at the algorithm for calculating values ​​not in a separate cell, but in all cells of the table. Excel provides the ability to replace the display of values ​​in rows and columns with formulas that calculate them.

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

If a cell contains a number, but the number is stored as text, then it may become a calculation error. For example, the SUM () function ignores numbers stored as text and may return a different value than expected.

Typically, these cells are set to 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 which you can convert the values ​​to numbers.

After conversion, the numbers will be aligned to the right edge of the cell (unless, 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 transform all the 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;
• choose Special rate(on the tab home in Group Clipboard click the arrow next to the command Insert, and then select from the list. Special rate);
• choose Multiply;
• click OK.

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

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

This same technique will help you convert DATES stored as text to DATE format (

The need to change the default format to another

Format of data presentation 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 right-aligned and text is left-aligned;
• if, by changing the width of the column, to make it less than a certain one, then the number in the cells is replaced by the symbols "#". It's 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);
• decimals are rounded when the column width is changed. For example, if you write "3.1415", then change the width so that "5" no longer fits, the cell displays "3.142".

Often you need to add a currency symbol, a percent 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 that, it may turn out that when you try to use the value from this cell in the formula, Excel will give an error! There is a way to tell Excel that the cells are in a certain format, and it will automatically add currency symbols (and not only) for us.

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

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

After entering certain sequences of characters, Excel automatically changes the format of the cell. After that, Excel tries to convert all further numbers entered into this cell into this format.

• date... If you write "1.2.3" or "1/2/3" in a cell, Excel replaces it with "02/01/2003" (the first day of the second month of the third year). If you write "1.2" or "1/2", then Excel replaces 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, date and time may differ! You can configure them along the path "Control Panel" -> "Regional and Language Options" -> "Regional Options" tab.

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

The Formatting toolbar contains 5 buttons that you can use to quickly change the format of selected cells.

Description of buttons (from left to right):

• Monetary 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. That's right, 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;
• Reduce bit depth... Removes one decimal place.

Change the format using the Format Cells window