After the table is created, you may find that some rows need to be moved to a different partition or even to a different table for reordering purposes. The Smartsheet app allows a sheet owner and collaborators with editor or admin rights and . To perform this operation, collaborators must have the same level of access to the table into which the rows are being moved.

Moving a row within a table

1. Click a row number and drag it up or down in the table. A dark one will appear horizontal line, indicating where the line will be moved when you release the mouse button.


2. Release the mouse button to move the line.
   ADVICE. To move a row within a table, you can also cut and paste it. Click the line number and press CTRL+X (Windows) or CMD+X (Mac) to copy the line. Then click the number of the line you want to move to and press CTRL+V (Windows) or CMD+V (Mac) to paste the line.


3. You can move multiple rows at the same time by selecting them first.
   • If the rows are consecutive, click the first row number, scroll the table to the last row, press and hold SHIFT key, and then click the last line number to select all lines in between. Drag the number of one of the selected rows to move them all to a new location.
   • If the lines are scattered, hold down CTRL (on Windows) or CMD (on Mac) and click the numbers of the lines you want to highlight them. Drag the number of one of the rows to move them all to a new location.
     NOTE. If a filter is applied to a table, the rows may not be in order.

Move row to another table

If you want to keep the data in the original table, we recommend using the .

Data located in a cell or a range of cells can be moved from one place in the worksheet to another using both direct drag and drop, buttons on the ribbon tabs, context menu, and procedures (macros)- programs written in Visual language Basic for Application, VBA for short.

The easiest way to move cells from place to place is to use the mouse. To move a cell to a new location, you need to select the desired cell or range, move the mouse cursor over the border of the selected range so that the cursor becomes an inclined arrow with a cross, as shown in the figure and with the left mouse button pressed, drag the selected area to a new location and after that release the mouse button. If, when you hover the mouse cursor over the border of the selected range, it does not turn into a slanted arrow with a cross and makes it impossible to drag cells, then it is disabled. special setting and it must be enabled by clicking on the "Office" button in the upper left corner of the program and going to Excel Options/Advanced. In the Editing Options group, check the box next to Allow fill handles and cell dragging.

Cut and paste

To transfer cells, along with moving them with the mouse, cut and then paste are used. In order to cut/paste a cell or a range of cells, select the "Cut" command, move the cursor to a new location and select the "Paste" command. To do this, you can use the context menu (the menu that appears when you right-click in the area of ​​the selected area), the Clipboard group on the Home tab, or hot keys (a keyboard shortcut to which this or that command is assigned).

The keyboard shortcut for cutting is Ctrl+X or Shift+Delete. The keyboard shortcut for pasting is Ctrl+V or Shift+Insert.

When moving a large number cells, there is a desire to select all the necessary cells at once and move them all at once. Standard Excel tools allow you to do this only with a range. If a group of nonadjacent cells is selected, then Excel will display the message " This command not applicable for unbound ranges. Select one range and select the team again." In this case, you can first move the cells so that they can be selected by one range, and then continue working with them.

In addition, there can be so many cells that transferring them manually can turn into a nightmare. Another problem that may arise when moving cells from place to place may be the search for the cells needed to move. For clarity, consider the following example: it is necessary to spread the cells into different columns, depending on their values.

All these and similar tasks are easily solved with the add-in for Excel.

Add-in for fast moving cells

For fast transfer cells from one place to another, an Excel add-in is created, and the button that launches this add-in is displayed on separate tab Excel ribbons or separate panel tools (depending on Excel versions). Pressing the button brings up a dialog box in which the user can select the conditions, values, ranges, direction of movement, and so on. The movement is performed automatically with a macro. In the example above, the following conditions were applied: find all cells with numerical values containing "3100" and move them 1 column to the left and 1 line up.

add-on video

1. With one click of the mouse, call the macro dialog box directly from the excel toolbar;

2. set the desired value for the cells;

3. set the search range;

4. choose one of twelve conditions for cell values;

5. determine the direction of movement for cells that meet the specified conditions and values:

a) move cells a specified number of lines up or down;

b) move cells a specified number of columns to the left or right;

c) move cells simultaneously up/down and left/right;

d) move cells to a given column;

e) move cells to a given row.

Table offset

Since we have allocated the first six lines of the worksheet as the input area, we need to move the table we created earlier down by this number of lines. We will describe different methods solution to this problem.

Adding Cells

The most time-consuming method is to shift the table area down with adding cells to upper part worksheet. It is implemented as follows:
1. Select cells A1:K6(range corresponding to the input area).
2. Press the right mouse button and in context menu activate the team Add Cells.
3. In the opened dialog box Adding Cells(fig. 3.9) select radio button cells, shifted down and press the button OK.

NOTE: This dialog box is also invoked via the command Insert/ Cells.

Inserting rows

The second way (less time consuming) is to add six lines on top:
1. Select lines 1-6 with the mouse.
2. Press the right mouse button and select the command in the context menu Add Cells.

Rice. 3.9. Add Cells Dialog Box

Table dragging

The third, easiest way is to select the entire table and drag it down. This operation can be compared to cutting a range of cells and pasting it to a new location. Select the range of cells where the table is located. Move the cursor to the end of the range and when the cursor changes to an arrow, drag the table down while holding down left button mice.

With the release of Excel 2010, Microsoft almost doubled the functionality of this program, adding many improvements and innovations, many of which are not immediately noticeable. Whether you're an experienced user or a beginner, there are many ways to make working with Excel easier. We will talk about some of them today.

Select all cells with one click

All cells can be selected with a combination Ctrl keys+ A, which, by the way, works in all other programs. However, there is an easier way to select. By pressing the button in the corner Excel sheet, you will select all cells with one click.

Opening multiple files at the same time

Instead of opening every excel file individually, they can be opened together. To do this, select the files you want to open and press Enter.

Navigate through Excel files

When you have multiple workbooks open in Excel, you can easily navigate between them using the Ctrl + Tab key combination. This feature is also available throughout Windows system, and it can be used in many applications. For example, to switch tabs in the browser.

Adding New Buttons to the Quick Access Toolbar

Standard in the panel quick access Excel are 3 buttons. You can change this number and add the ones you need.

Go to File menu ⇒ Options ⇒ Quick Access Toolbar. Now you can select any buttons you need.

Diagonal line in cells

Sometimes there are situations when you need to add a diagonal line to the table. For example, to separate date and time. For this, on the main Excel page click on the familiar borders icon and select "Other borders".

Adding empty rows or columns to a table

Inserting a single row or column is easy enough. But what if you need to insert much more of them? Select the desired number of rows or columns and click "Insert". After that, select the place where you want to move the cells, and you will get the right amount empty lines.

High-speed copying and moving information

If you need to move any information (cell, row, column) in Excel, select it and move the mouse over the border to change the pointer. After that, move the information to the place you need. If you need to copy information, do the same, but with the Ctrl key pressed.

Quick removal of empty cells

Blank cells are the scourge of Excel. Sometimes they just appear out of nowhere. To get rid of them all in one go, highlight the desired column, go to the Data tab, and click Filter. An arrow pointing down will appear above each column. By clicking on it, you will be taken to a menu that will help you get rid of empty fields.

Advanced Search

By pressing Ctrl + F, we get to the search menu, with which you can search for any data in Excel. However, its functionality can be extended using the symbols "?" and "*". The question mark is responsible for one unknown character, and the asterisk is responsible for several. They should be used if you are not sure what the query you are looking for looks like.

If you need to find a question mark or an asterisk and you don't want Excel to look for an unknown character instead, put a "~" in front of them.

Copying Unique Records

Unique records can be useful if you need to highlight non-repeating information in a table. For example, one person of each age. To do this, select the desired column and click "Advanced" to the left of the "Filter" item. Select the source range (from where to copy) and the range in which you want to place the result. Don't forget to check the box.

Making a selection

If you are doing a survey that only allows men between 19 and 60, you can easily create a similar sample with using Excel. Go to the menu item "Data" ⇒ "Data Validation" and select required range or other condition. By entering information that does not fit this condition, users will receive a message that the information is incorrect.

Quick navigation with Ctrl and arrow

By pressing Ctrl + arrow, you can move to the extreme points of the sheet. For example, Ctrl + ⇓ will move the cursor to lower part sheet.

Transposing information from a column to a row

Enough useful feature, which is not needed very often. But if you suddenly need it, it is unlikely that you will transpose one at a time. For transposition in Excel there is a special insert.

Copy the range of cells you want to transpose. After that click right click on the Right place and select a special insert.

How to hide information in Excel

I don’t know why this might come in handy, but nevertheless there is such a function in Excel. Select the desired range of cells, click "Format" ⇒ "Hide or Show" and select the desired action.

Concatenating text with "&"

If you need to combine text from multiple cells into one, you don't have to use complex formulas. It is enough to select the cell in which the text will be connected, press "=" and select the cells sequentially, putting the symbol "&" in front of each.

Changing the case of letters

Using certain formulas, you can change the case of the entire text information in Excel. The UPPER function makes all letters capitalized and the LOWER function makes all letters lowercase. "PROP" capitalizes only the first letter in each word.

Entering information with leading zeros

If you enter in excel number 000356, then the program will automatically turn it into 356. If you want to leave leading zeros, put an apostrophe "'" in front of the number.

Speed ​​up typing complex words

If you type the same words often, you'll be glad to know that Excel has AutoCorrect. It is very similar to autocorrect in smartphones, so you will immediately understand how to use it. With its help, you can replace repetitive constructions with abbreviations. For example, Ekaterina Petrova - EP.

More information

In the lower right corner you can see various information. However, few people know that by right-clicking there, you can remove unnecessary lines and add the necessary lines.

Renaming a sheet with a double click

This is the easiest way to rename a sheet. Just double-click on it with the left mouse button and enter a new name.

Solution in one word: transposition (transpose). Then the searcher can google and find this article.

There is a regular table, how can I transfer all the data so that the columns become rows and the rows become columns? I know three ways to solve the problem, each of which is convenient in its own way.

Select one column or row, copy. In a new place or sheet where the transposed table will be located, right-click "Paste Special".

Check the box for transpose. When using such a transposition, all formulas, all links between tables “fly off”. Therefore, most likely we will also need to transfer only the values ​​(or values ​​​​and formats) of numbers.

Of the controversial advantages: all the design of the cells will be preserved, which is not always required. But main disadvantage method is a rather laborious process. What if there are more than 100 rows and columns? One hundred times to transfer the data line by line?

1. We use the formula

Much more elegant solution.

The ADDRESS(row_number; column_number) function returns the reference (address) of the cell by 2 numbers, where the first is the row number, the second is the column number. Those. record =ADDRESS(1;1) will return us a link to cell A1.

Using the functions LINE(cell) and COLUMN(cell), we change the output order of the ADDRESS function - not (row, column), but (column, line).

In its current form, the formula =ADDRESS(COLUMN(A1),ROW(A1)) will return the text $A$1, you need to convert the result into a link by wrapping the entire expression in the INDIRECT(text_link_link) function.

INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

In English Excel:

INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Applying the formula for cell A9 (in the example in the picture), we stretch it to the rest. Result:

And you can immediately see 2 small minuses of this method:

• Empty cells are filled with zeros, edited manually;
• The cell format is numeric, so Dates will also need to be converted manually.

But still, on large tables, this formula is definitely faster than the special insert.

Even the formula is not quite what you need. We are converting the data back and forth, although we just need to work with the table itself.

The most rational solution is a pivot table. We need to correct the original data, each column should have a heading!

Select the table, select Insert from the menu - pivot table. Specify where to insert new table(can be on new leaf or somewhere on the current one), schedule - yes / no. OK. In the settings, we swap blocks of row names and column names. Result: