How to copy a table in Excel by saving the format cells

Those who regularly work in Microsoft Excel often wonder how to copy the table data properly, retaining the format, formulas or their values.

How to paste a formula in an Excel table retaining the format? It's possible to save a truckload of time solving this task. For this, we will use the function «Paste Special». It's a simple tool that allows for solving complicated tasks quickly and mastering the basics of its usage. This unsophisticated tool is employed to quickly apply the same format to all tables and achieve the necessary result.



How to copy a table retaining column width and row height

Let's assume we have a table, the format of which needs to be preserved when you copy it:

format of which needs.

When you copy it to a different spreadsheet using the usual Ctrl+C – Ctrl+V method, the result is far from desired:

Ctrl+C – Ctrl+V.

You will have to adjust the column width manually, whipping it into the convenient shape. If the table is large, you will be fiddling around for quite a while. However, there is a way to significantly reduce your loss of time.

Method 1. Using paste special

  1. Select the initial table that needs to be copied, and press Ctrl+C.
    2. initial table.
  2. Select the new (already copied) table, where you need to apply the column width format, and right-click on a cell. Find the line «Paste Special» (CTRL+ALT+V) in the drop-down menu.
    3. CTRL+ALT+V.
  3. In the dialog window, select the option «Column widths», and click «ОК».
Column widths. Done.

The table now features the initial parameters and looks perfect.

Method 2. Selecting the columns before copying

This method has a secret: if you select the columns together with their headings before copying the table, the width of every column will also be copied when you paste it.

  1. Select the columns containing the initial data.
    2. initial data.
  2. Copy and paste to quickly obtain the desired result.
Done.

It's rational to use each of the methods depending on the task. However, the second method allows to both quickly carry the table over together with the format, and copy the row height. Note that the paste special menu lacks the option “row height.” Thus, you will need to take the following steps to fulfill such a task:

  1. Select the all rows and column on sheet (CTRL+A).
    2. CTRL+A.
  2. Paste its copy below.
Paste its copy below.

Helpful hint! The quickest way to copy a large and complicated table retaining its column width and row height is to copy it as an entire sheet.

drag the tab of the sheet.

While holding the CTRL key, drag the tab of the sheet.



How to paste formula values retaining table format

Although the paste special tool is not perfect, you should not underestimate its capabilities. For example, let's view how to copy the value of a formula into an Excel table retaining the cell format.

Step 1:

  1. Select the initial table containing the formulas and copy it.
    2. table containing the formulas.
  2. In the spot where you need to paste the range of values (without the formulas), select the «Values» option. Click OK.
Values.

Step 2:

Since the copied range is still in the clipboard after you copied it, proceed to open the paste special dialog again and select the option «Formats». Click OK.

Formats.

We have pasted the values of the formulas in the table retaining the cell format. As you might have guessed, you can also take a third step and copy the column width as described above.

example.

Microsoft Excel offers users virtually limitless possibilities for calculating the simplest functions and fulfilling a number of other tasks. The program allows you to set formats, save cell values, work with formulas, transfer and change them in a convenient way.


en ru