How to move a table to another sheet in Excel

A table in Excel is a complex array with set of parameters. It can consist of values, text cells, formulas and be formatted in various ways (cells can have a certain alignment, color, text direction, special notes, etc.).

When you copy a table, sometimes you do not have to transfer all of its elements, but only some of its. Consider how this can be done.



The past special

It is very convenient to carry out the transfer of the data in the table using the past special. It allows you to select only those parameters that we need when copying. Let's consider an example.

We have the table with the indicators for the presence of macaroni of certain brands in the warehouse of the store. It is clearly visible, how many kilograms were at the beginning of the month, how much of them were bought and sold, and also the balance at the end of the month. Two important columns are highlighted in different colors. The balance at the end of the month is calculated by the elementary formula.

We will try to use the PAST SPECIAL command and copy all the data.

First we select the existing table, right click the menu and click on COPY.

In the free cell, we call the menu again with the right button and press the PAST SPECIAL.

If we leave everything as is by default and just click OK, the table will be inserted completely, with all its parameters.

Let's try to experiment. In the PAST SPECIAL we choose another item, for example, FORMULAS. We have already received an unformatted table, but with working formulas.

Now we will insert not the formulas, but only the VALUES of the results of calculations.

That a new table with values to get the appearance similar to the sample, you need to select it and insert the FORMATS using a special insert.

Now let's try to choose the item WITHOUT FRAME. We got the full table, but only without the allocated borders.

The wholesome advice! To migrate the format along with the size of the columns, you need to select not the range of the source table, but the entire columns (in this case, the A: F range) before the copying.

Similarly, you can experiment with each item of the PAST SPECIAL to see clearly how it works.



Transfer of data to another sheet

Transferring data to other worksheets in the Excel workbook allows you to link multiple tables. This is convenient because when you replace a value on one sheet, the values change on all the others. When creating annual reports, this is an indispensable thing.

Consider how this works. For a start we rename Excel sheets in months. Then, with the helping of the PAST SPECIAL that we already know, we move the table to February and remove the values from the three columns:

1. At the beginning of the month.
2. The incoming.
3. The expense.

The column «At the end of the month» is given by the formula, so when you delete values from the previous columns, it is automatically reset to zero.

We will transfer to the data on the remainder of macaroni of each brand from January to February. This is done in a couple of clicks.

1. On the FEBRUARY sheet we place the cursor in the cell, indicating the amount of macaroni of grade A at the beginning of the month. You can see the picture above - this will be cell D3.
2. We put in this cell to the sign EQUAL.
3. Go to the JANUARY sheet and click on the cell showing the amount of macaroni of grade A at the end of the month (in this case it is cell F2).

We get the following: in the cell C2 the formula was formed, which sends us to the cell F2 of the JANUARY sheet. Stretch the formula down to know the amount of macaroni of each brand at the beginning of February.

Similarly, you can transfer the data to all months and get to the visual annual report.

Data transfer to another file

Similarly, you can transfer data from one file to another one. This book in our example is called EXCEL. Create another one and name it EXAMPLE.

Note. You can create new Excel files even in different folders. The program will automatically search for the specified book, regardless of which folder and on what drive of the computer it is located.

We copy in the book EXAMPLE to the table using the same PAST SPECIAL and again delete the values from the three columns. We will perform the same actions as in the previous paragraph, but we will not go to another sheet, but to another book only.

We have received to the new formula, which shows that the cell refers to the book EXCEL. And we see that the cell =[raports.xlsx]January!F2 looks like =[raports.xlsx]January!$F$2, i. e. it is fixed. And if we want to stretch to the formula in other brands of macaroni, first you need to remove the dollar badges for removing to the commit.

Now you know how to correctly transfer data from tables within one sheet, from one sheet to another, and from one file to another one.