How to make a transfer in Excel in a cell. Inserting and deleting cells, rows and columns How to insert data in Excel

The “If” function in Excel is one of the most popular among all existing ones in this editor. Thanks to it, you can perform various comparisons using Boolean expressions. In this case, you can specify what to do depending on the result of the condition being met.

The principle of operation is very simple: if the expression is true, then do this, otherwise, do something else.

That is, as a result of checking the condition, two events can occur:

• the first - with truth;
• the second - if false is returned.

Let's look at the basic syntax. When you call the “Insert Function” window, a description of each proposed option is indicated below.

The help for the IF function is as follows.

IF(logical_expression, value_if_true, value_if_false)

To do this you need to do the following:

1. Click on the formula insert icon (Fx).
2. In the menu that appears, select “IF”.

1. As a result of this, you will see a window in which you need to specify the function arguments:
   • boolean expression;
   • value if true;
   • value if false.
2. After entering values ​​in these fields, a preliminary result will be displayed to the right of them.
3. To insert, click on the “OK” button.

Examples

Let's look at a few different Boolean expressions to help you understand how to use this function.

1. Make cell "A1" active.
2. Repeat the steps described above to open the “Arguments” insertion window.
3. Enter the following code in the condition field.

C1=””

The meaning of this expression is as follows: if cell C1 contains a void. That is, there is nothing in it.

1. In the field for truth, enter the following.

"Cell C1 is empty"

This is the text that will be displayed if this condition is met.

1. In the “if false” field, enter the following.

"Cell C1 is not empty"

We will see this text if the condition is false.

1. To insert our formula, click on the “OK” button.

1. As a result of this, we will see the following (since the cell is empty, the message is appropriate).

1. Enter any text in cell C.

1. After clicking on the Enter button, you will see the following result.

As you can see, the Excel editor produced the message that we indicated in case of a false result of the condition.

Comparison Operators

Below you see a list of comparison operators:

Arithmetic operators

And this is a table of arithmetic operators:

You can read more about arithmetic operators and their precedence in Excel in the online help on the Microsoft website.

Multiple conditions

If you want, you can use a large number of Boolean expressions. Everything works exactly the same. Let's look at an example of a more complex condition.

We will create the formula using the toolbar. This is much more convenient than writing everything on one line, since it is very easy to get confused. Especially at the very beginning, when you are just learning.

To do this, you must perform the following steps.

1. Go to the "Formulas" tab. Click on the “Insert Function” button.

1. As a result, the entire contents of the cell will be automatically substituted into the “constructor”.

1. Delete everything in the "Value_if_false" field.

1. Now a new logical expression will be written in this line. That is, we will have a new nested condition. First, enter the condition “If the value of cell C1 is greater than 1000.” To do this, enter the following code. Be sure to include a semicolon at the end.

IF(C1>1000;

Pay close attention to your current keyboard layout. Many people make mistakes and enter the Russian letter C instead of the English C. Visually you won’t see the difference, but for the editor this is very important. In this case, nothing will work.

1. Now let's add a message that will be displayed if the number in cell C1 is greater than 1000.

All text values ​​must be entered in quotes.

1. Now, in exactly the same way, we enter the value if this condition is not met.

1. Finally, click on the “OK” button.

1. As a result of this, we see a message stating that the entered number is greater than 1000.

1. Delete the contents of cell C. The result is the following.

We tested all three possible outcomes. Everything works great.

Copying a function in tables

Sometimes it happens that the entered logical expression needs to be duplicated on several lines. In some cases, you have to duplicate a lot. This automation is much more convenient than manual checking.

Let's look at an example of copying on a table of bonuses for employees during the holidays. To do this you need to take the following steps.

1. Create the appropriate table.

1. Click on an empty cell in the first row and select “Fx” in the input field.

1. In the window that appears, select the “IF” function and click on the “OK” button.

1. In the first field, enter the following condition.

C6=”M”

This way we check whether the employee is a man.

1. If true, enter some number. For example, 3000. If the condition is false, enter 0. This means that the girls do not need to be given a bonus. To insert, click on the “OK” button.

1. Next, hover your cursor over the lower right corner of the cell. After the cursor changes its appearance to “black plus”, without releasing your finger, drag it to the very last line.

1. The result will be the following.

1. Now completely copy the entire table (using the keyboard shortcut Ctrl + C and Ctrl + V). Change the title to March 8th.

1. Go to the first cell. Click on the formula input field.

1. Change the letter "M" to "F".

1. Now duplicate the function to the very bottom.

1. The result will be the following.

Here we see that the complete opposite has happened. This means everything is working correctly.

Using Additional Operators

In addition to arithmetic expressions and comparisons, you can also use the AND and OR operators. Let's take a closer look at them, because thanks to them, the capabilities of the “IF” function are significantly expanded.

First of all, create a table that will have several fields by which you can compare rows. In our case, using the “Employee Status” field, we will check who needs to pay money and who does not.

Operator AND

We will use the old function in which we checked whether the employee is a man. Now you need to make the following changes.

1. Add a parenthesis to the condition and the letter “I” next to it.
2. The old condition will be the first, and the second will be after the semicolon.

=IF(AND(C35="M";D35="Main staff");3000;0)

1. Duplicate this formula all the way to the bottom.

1. As a result of this, we see that those employees who are not part of the core staff will be deprived of bonuses for the holidays. Even though they are men.

Operator Or

The same effect can be achieved using the OR operator.

To do this, make the following changes to the formula:

1. Change the letter "F" to "M".
2. Change the employee status to “Part-time worker”.
3. Put 0 in the truth field, and 3000 for a false event. We swap them.

1. Let's duplicate the formula until the very last line.

1. The result was exactly the same. The fact is that the operators “AND” and “OR” are the complete opposite of each other. Therefore, it is very important to correctly indicate the values ​​​​in the true and false fields. Make no mistake.

1. To check, you can change the status of one employee to “Primary Staff”.
2. Immediately after this, you will see that the number 3000 will appear opposite his last name.

SUMIF function

Surely many of you have done calculations in Excel. But there are times when you need to count not all rows in a table, but only some that meet a certain condition.

For example, let's take a table that lists various products, their cost, quantity, and status. Calculating the total amount is easy. But how can you find out the number of products sold?

To do this, use the “SUMIF” function.

1. First of all, go to the cell in which you want to display the result.
2. Then click on the formula input field.

1. Start typing the name of the function. Help will appear automatically.

1. After this, use the mouse to select the last column. Its values ​​will be automatically substituted into the formula.

1. Then add a semicolon.
2. After that, add the word “Sold” in quotation marks and again put the symbol “;”.

1. Now we indicate the field that we will count - also manually.

1. Add a closing parenthesis.

1. Press the Enter button. As a result, you will see the number of items sold.

COUNTIF function

This function counts the number of rows that match the condition.

It is not the value of the cells that is counted, but their number.

=COUNTIF(E2:E7,"Sold")

The result will be as follows.

Possible problems

What to do if an error occurs? Typically they are displayed as follows.

In order to try to fix it, you need to do the following:

1. Click on the field to enter the formula.
2. Look carefully - perhaps there is a missing parenthesis, quotation mark or semicolon somewhere.
3. In our case, the letter Ж (text symbol) is not indicated in quotation marks.

1. Once you fix everything, the error will disappear immediately.

In addition, the editor himself can provide assistance in the search. To do this, just click on the warning sign next to the cell. This will show you a tooltip and a link to online help for the issue.

Conclusion

This article looked at various ways to use the IF function:

• the simplest case;
• with several conditions;
• using additional operators;
• using arithmetic symbols and other options.

If suddenly something doesn’t work out for you, perhaps you made a typo somewhere. Another common mistake that most people make is incorrect logic in the conditions.

Video instruction

If you still have any questions, you can watch the video attached below. In it you can find additional comments on the described instructions.

In this article we will show you a few more useful options that the tool is rich in. Special insert, namely: Values, Formats, Column Widths and Multiply/Divide. With these tools, you can customize your tables and save time on formatting and reformatting data.

If you want to learn how to transpose, remove references and skip empty cells using the tool Paste Special(Paste Special) Refer to the article Paste Special in Excel: Skip Blank Cells, Transpose and Remove References.

We insert only values

Let's take, for example, a spreadsheet that reports profits from cookie sales at a charity bake sale. You want to calculate how much profit was made in 15 weeks. As you can see, we used a formula that adds up the amount of sales that we had a week ago and the profit we made this week. You see in the formula bar =D2+C3? Cell D3 shows the result of this formula – $100 . In other words, in the cell D3 value is displayed. And now comes the most interesting part! In Excel using the tool Paste Special(Paste Special) You can copy and paste the value of this cell without formula or formatting. This opportunity is sometimes vital, and I will show why later.

Suppose, after selling cookies for 15 weeks, you need to submit a general report on the results of the profit received. You may want to simply copy and paste the line that contains the grand total. But what happens if you do this?

Oops! Is this not at all what you expected? As you can see, the usual copy and paste action ended up copying only the formula from the cell? You need to copy and paste special the value itself. That's what we'll do! We use the command Paste Special(Paste Special) with parameter Values(Meanings) so that everything is done as it should.

Notice the difference in the image below.

Applying Special insert > Values, we insert the values ​​themselves, not formulas. Great job!

You may have noticed something else. When we used the command Paste Special(Insert Special) > Values(Values), we have lost formatting. See how the bold font and number format (dollar signs) were not copied? You can use this command to quickly remove formatting. Hyperlinks, fonts, and number formats can be quickly and easily cleared, leaving you with just the values ​​without any decorative stuff that might get in the way in the future. Great, right?

In fact, Special insert > Values is one of my favorite tools in Excel. It is vital! Often I am asked to create a table and present it at work or in community organizations. I'm always worried that other users might wreak havoc on the formulas I've entered. After I finish working with formulas and calculations, I copy all my data and use Paste Special(Insert Special) > Values(Values) on top of them. This way, when other users open my spreadsheet, the formulas can no longer be changed. It looks like this:

Note the contents of the cell's formula bar D3. There's no formula to it anymore =D2+C3, instead the value is written there 100 .

And one more very useful thing regarding the special insert. Let's say that in the table for accounting for profits from a charity cookie sale, I want to leave only the bottom line, i.e. delete all rows except week 15. See what happens if I just delete all these rows:

This annoying error appears #REF!(#LINK!). It appears because the value in this cell is calculated using a formula that refers to the cells above it. After we deleted these cells, the formula had nothing to refer to and reported an error. Use the commands instead Copy(Copy) and Paste Special(Insert Special) > Values(Values) on top of the original data (as we already did above), and then delete the extra rows. Great job:

Paste Special > Values: Highlights

1. Select data
2. Copy them. If the data is not copied, but cut, then the command Paste Special(Paste Special) will not be available, so be sure to use Copy.
3. Select the cell where you want to paste the copied data.
4. Click Paste Special(Special insert). It can be done:
   • by right-clicking and selecting from the context menu Paste Special(Special insert).
   • on the tab Home(Home), click the small triangle below the button Paste(Insert) and select from the drop-down menu Paste Special(Special insert).
5. Check the option Values(Data).
6. Click OK.

We insert only formats

Special insert > Formats This is another very useful tool in Excel. I like it because it makes it quite easy to customize the appearance of the data. There are many uses for the tool Special insert > Formats, but I will show you the most remarkable. I think you already know that Excel is great for working with numbers and performing various calculations, but it is also great when you need to present information. Besides creating tables and counting values, you can do a variety of things in Excel, such as schedules, calendars, labels, inventory cards, and so on. Take a closer look at the templates that Excel offers when creating a new document:

I came across a pattern Winter 2010 schedule and I liked the formatting, font, color and design about it.

I don’t need the schedule itself, especially for the winter of 2010, I just want to remake the template for my purposes. What would you do if you were me? You can create a draft of the table and manually repeat the template design in it, but this will take a very long time. Or you can delete all the text in the template, but this will also take a lot of time. It's much easier to copy the template and make Paste Special(Insert Special) > Formats(Formats) on a new sheet of your workbook. Voila!

Now you can enter data while maintaining all formats, fonts, colors and designs.

Paste Special > Formats: Highlights

1. Select the data.
2. Copy them.
3. Select the cell where you want to paste data.
4. Click Paste Special(Special insert).
5. Check the option Formats(formats).
6. Click OK.

Copy column widths to another sheet

Have you ever wasted a lot of time and energy circling around your spreadsheet trying to adjust the column sizes? My answer is of course yes! Especially when you need to copy and paste data from one table to another. The existing column width settings may not be suitable, and even though automatic column width adjustment is a convenient tool, in some places it may not work as you would like. Special insert > Column widths is a powerful tool that should be used by those who know exactly what they want. Let's look at the list of the best US MBA programs as an example.

How could this happen? You can see how carefully the column width was adjusted to fit the data in the image above. I copied the top ten business schools and placed them on another sheet. Look what happens when we just copy and paste the data:

The content is inserted, but the column widths are far from appropriate. You want to get the exact same column widths as the original sheet. Instead of setting it manually or using auto-fit column width, just copy and paste Paste Special(Insert Special) > Column Widths(Column Widths) to the area where you want to adjust the column widths.

See how simple it is? Although this is a very simple example, you can already imagine how useful such a tool will be if the Excel sheet contains hundreds of columns.

You can also adjust the width of empty cells to format them before manually entering text. Look at the columns E And F in the picture above. In the picture below I used the tool Special insert > Column widths to expand the columns. So, without any fuss, you can design your Excel sheet the way you want!

Paste Special > Column Widths: Key Points

1. Select the data.
2. Copy the selected data.
3. Place the cursor on the cell whose width you want to adjust.
4. Click Paste Special(Special insert).
5. Check the option Column Widths(Column widths).
6. Click OK.

Paste Special: Divide and Multiply

Remember our cookie example? Good news! A giant corporation found out about our charity event and offered to increase profits. After five weeks of sales, they will be donated to our charity, so the income will double (become twice as large) compared to what it was at the beginning. Let's go back to the table in which we kept track of the profits from the charity cookie sale, and recalculate the profits taking into account the new investments. I added a column showing that after five weeks of sales, profits will double, i.e. will be multiplied by 2 .

All income starting from the 6th week will be multiplied by 2 .To show new numbers, we need to multiply the corresponding column cells C on 2 . We can do this manually, but it will be much nicer if Excel does it for us using the command Paste Special(Insert Special) > Multiply(Multiply). To do this, copy the cell F7 and apply the command on the cells C7:C16. Totals have been updated. Great job!

As you can see, the tool Special insert > Multiply can be used in a variety of situations. The situation is exactly the same with Paste Special(Insert Special) > Divide(Divide). You can divide an entire range of cells by a specific number quickly and easily. You know what else? With help Paste Special(Insert Special) with option Add(Fold) or Subtract(Subtract) You can quickly add or subtract a number.

Paste Special > Divide/Multiply: Highlights

1. Select the cell with the number you want to multiply or divide by.
2. Copy the selected data.
3. Place the cursor on the cells in which you want to perform multiplication or division.
4. Click Paste Special(Special insert).
5. Check the option Divide(Split) or Multiply(Multiply).
6. Click OK.

So, in this tutorial you have learned some very useful features of the tool. Special insert, namely: we learned how to insert only values ​​or formatting, copy column widths, multiply and divide data by a given number, and also add and remove a value directly from a range of cells.

Insert and delete rows, columns, and cells to optimize the layout of your worksheet.

Note: Microsoft Excel has the following row and column limits: 16,384 columns wide by 1,048,576 rows high.

Inserting and deleting a column

To insert a column, select it and then on the tab home click the button Insert and select Insert columns into a sheet.

To remove a column, select it and then on the tab home click the button Insert and select Remove columns from a sheet.

You can also right-click at the top of the column and select Insert or Delete.

Inserting and deleting a row

To insert a row, select it, and then on the tab home click the button Insert and select Insert rows into a sheet.

To delete a row, select it, and then on the tab home click the button Insert and select Remove rows from a sheet.

You can also right-click the highlighted line and select the command Insert or Delete.

Inserting a cell

Select one or more cells. Right click and select command Insert.

In the window Insert select the row, column, or cell to insert.

For example, to insert a new cell between the Summer and Winter cells:

Click the Winter cell.

On the tab home Insert and select a team Insert Cells (Shift Down).

A new cell is added above the "Winter" cell:

Inserting Rows

To insert one row: Right-click the entire line you want to insert a new line above and select Insert rows.

To insert multiple rows, follow these steps: Select the same number of rows above which you want to add new ones. Right-click the selection and select Insert rows.

inserting columns

To insert one new column, follow these steps: Right-click the entire column to the right of where you want to add the new column. For example, to insert a column between columns B and C, right-click column C and select Insert columns.

To insert multiple columns, follow these steps: Select the same number of columns to the right of which you want to add new ones. Right-click the selection and select Insert Columns.

Delete cells, rows, and columns

If you no longer need any cells, rows, or columns, here's how to delete them:

Select the cells, rows, or columns you want to delete.

On the tab home click the arrow below the button Delete and select the desired option.

When you delete rows or columns, the following rows and columns are automatically moved up or to the left.

Advice: If you change your mind right after you delete a cell, row, or column, simply press CTRL+Z to restore it.

In Excel, you always have to carry out various manipulations with tables, and then we will look at how to copy a table in Excel and how to paste a table into Excel. These operations are actually very simple, but when using them it is necessary to take into account a number of subtleties when working in this program.

To copy a table into Excel, you naturally need to first select it. The fastest way to copy is to use a keyboard shortcut "Ctrl + C" or use the corresponding menu item "Copy" on the tab "Home".

Next to the menu item "Copy" There is an arrow, when you click on it, an additional option appears “Copy like a drawing...”. When you select this option, the table you select will be copied as a picture, but a dialog box with additional copy settings will be displayed.

The table copied in this way in Excel will be stored in the clipboard as a picture.

Let's assume that we managed to copy the table; now it's time to figure out how to paste the table into Excel. Here, too, everything can be very simple, since you can use the key combination "Ctrl + V". But when inserting a table into Excel, as when inserting any other data, you should take into account the fact that the inserted data will, starting from the selected cell on the sheet, occupy the necessary space, replacing all the information placed in the cells required for this table. Data is inserted starting from the top left cell. And you should also remember that if, after copying a table or other data, any manipulation is performed on the sheet other than moving the cursor, the data from the clipboard will be deleted. This means that you need to paste the table into Excel immediately after copying it.

You can also use the menu item to insert "Insert" on the tab "Home".

We are all accustomed to the fact that we can copy and paste data from one cell to another using standard Windows operating system commands. To do this we need 3 keyboard shortcuts:

• Ctrl+C copying data;
• Ctrl+X cut out;
• Ctrl+V paste information from the clipboard.

So, Excel has a more advanced version of this feature.

Special insert is a universal command that allows you to paste copied data from one cell to another separately.

For example, you can paste separately from a copied cell:

• Comments;
• Cell Format;
• Meaning;
• Formula;
• Decor.

How Paste Special Works

First, let's find where the command is located. Once you've copied a cell, you can open Paste Special in several ways. You can right-click on the cell where you want to paste data and select “Paste Special” from the drop-down menu. In this case, you have the opportunity to take advantage of quick access to the insertion functions, and also by clicking on the link at the bottom of the list to open a window with all the options. This item may be different in different versions, so do not be alarmed if you do not have an additional drop-down menu.

You can also open a special insert on the tab home. At the very beginning, click on the special arrow, which is located under the button Insert.

The window with all the functions looks like this.

Now we’ll go through it in order and start with the “Insert” block.

• All- this is a common function that allows you to completely copy all cell data to a new location;
• Formulas- only the formula that was used in the copied cell will be transferred;
• Values- allows you to copy the result that is obtained during execution in a formula cell;
• Formats- only the cell format is transferred. Also, the cell design will be copied, for example, background fill;
• Notes- copying a cell note. In this case, the data (formula, values, etc.) is not copied;
• Conditions for values- using this option you can copy, for example, the criteria for acceptable data (drop-down list);
• With original theme- the cell is copied while maintaining the design. For example, if you use a background fill in a cell, that will be copied too. In this case, the formula will be copied;
• No frame- if a cell has a frame on either side, it will be deleted when copying;
• Column widths- the column width will be copied from one cell to another. This feature is very convenient to use when you are copying data from one sheet to another. Only column widths are transferred;
• Formulas and number formats- the formula and number format are transferred;
• Number values ​​and formats- the result and number format are transferred.

Let's look at a few examples. There is a table in which the full name column is collected using the Concatenate function. We need to insert ready-made values ​​instead of the formula.

To replace a formula with results:

• Copy the full name column;
• Right-click on the topmost cell and select Paste Special;
• Set the item to active Meaning and press the key OK.

Now the column contains the results instead of the formula.

Let's look at another example. To do this, copy and paste an existing table next to it.

As you can see, the table did not save the width of the columns. Our task now is to transfer the width of the columns to the new table.

• Copy the entire source table;
• Stand on the top left cell of the new table and right-click. Next, select Paste Special;
• Set the item to active Column widths and press the key OK.

Now the table looks exactly the same as the original one.

Now let's move on to the block Operations.

• Fold- the inserted information will be added to the values ​​already in the cell;
• Subtract- the inserted information will be subtracted from the values ​​already present in the cell;
• Multiply- the value in the cell will be multiplied by the inserted one;
• Divide- the value in the cell will be divided by the value being inserted.

Let's look at an example. There is a table in which there is a column with numerical values.

Task: multiply each number by 10. What you need to do to do this:

• In the new cell you need to put the number 10 and copy it to the clipboard;
• Select all the cells of the column in which we will multiply;
• Right-click on any of the selected cells and select Paste Special;
• Set it to active Multiply.

In the end we get the required result.

Let's consider one more problem. It is necessary to reduce the results obtained in the previous example by 20%.

• In the new cell we set 80% and copy it;
• Select all the cells of the column in which we will calculate the percentage;
• Right-click on any of the selected cells and select Paste Special;
• Set it to active Multiply.

As a result, we get values ​​reduced by 20% from the original ones.

There is only one note here. When you work with a block Operations, try to display in a block Insert option Meaning, otherwise, when pasting, the formatting of the cell that we are pasting will be copied and the formatting that was originally there will be lost.

There are two last options left that can be activated at the bottom of the window:

• Skip empty cells- allows you not to paste empty cells from the copied range. The program will not erase data in a cell into which a blank cell is inserted;
• Transpose- changing the orientation of copied cells, i.e. rows become columns and columns become rows.

That's all, if you have any questions, be sure to ask them in the comments below.