Those who constantly work with Microsoft Excel often have to wonder about the correct copying of table data while maintaining formatting, formulas or their values.

How to insert a formula into an Excel table and keep the table format? After all, when solving this problem, you can save a car of time. To do this, we will use the "Paste Special" function - this is a simple tool that allows you to quickly solve complex problems and master the general principles of its use. Using this simple tool allows you to quickly format all tables in the same format and get the result you need.

How to copy a table with column width and row height

Let's say we have such a table, the format of which needs to be preserved when copying:

When copying to another sheet using the usual Ctrl + C - Ctrl + V method. We get the unwanted result:

Therefore, you have to manually “expand” it in order to bring it into a convenient form. If the table is large, then it will take a long time to “mess around”. But there is a way to significantly reduce temporary losses.

Method 1: Use a special insert

She received the original parameters and looks perfectly accurate.

Method 2: Select columns before copying

The secret of this method is that if you select its columns along with the headings before copying the table, then when you paste the width of each column will also be copied.

For each case, it is rational to apply its own method. But it is worth noting that the second method allows us not only to quickly transfer the table along with the format, but also copy the height of the rows. After all, there is no “line height” option in the paste special menu. Therefore, to solve such a problem, you should act like this:

Insert formula values ​​while maintaining the table format

Paste Special, while not perfect, should not underestimate its capabilities. For example, how to insert a formula value into an Excel spreadsheet and keep the cell format.

To solve such a problem, you should perform 2 operations using a special paste in Excel.

Since the copied range is still on the clipboard after copying, we immediately call the special paste again where we select the “formats” option. Click OK.

We have pasted the formula values ​​into the table and saved the cell formats. As you guessed, you can do a third operation to copy the width of the columns, as described above.

Useful advice! In order not to perform the second operation, you can use the Format Painter tool.

Microsoft Excel provides users with almost unlimited possibilities for calculating the simplest functions and performing a number of other procedures. Using the program allows you to set formats, save cell values, work with formulas, transfer and change them in a way that is convenient for users.

PDF - was developed as a format for presenting books and magazines on computers. In everyday life and business, it is most often used to create, so to speak, immutable documents. For example, you have created a table with tariffs or an agreement and you need to send it to the client so that the client does not change anything there, it is best to save it in PDF. After all, Excel can be easily, even if it is password protected.

XML- was developed as a language convenient for processing documents by programs and convenient and understandable for working with a document by a person. You can say a format specially created for the Internet. Very often, exchanges between two programs pass through such a universal language. For example, automatic unloading from Access and loading into 1C.

The full list of available formats is shown in the picture:

As shown earlier, to save Excel to PDF, you need to go to the menu, select Save As - then select PDF or XPS (in the first picture).

XPS is also a special format for protecting the content of a document, one might say an electronic sheet of paper.

Excel to JPEG or PNG

There is no special save function for the image format, but it is quite simple to do it using Windows tools.

First of all, almost every keyboard has a special Print Screen button (screenshot), it is located to the right of F12. Most often something like PrtScn or Prt Sc is written on it.

After clicking this button, the picture with the screen image is saved and you can paste it anywhere: in a letter, in Word, or in the Paint program. From Paint, you can already save to JPEG and PNG.

If you only want to copy the active window, press Alt + PrtScn - described in more detail.

It's even easier to find the scissors program (starting in Windows 7 is built-in). Start - all programs - Standard there are these same scissors - now you can select any fragment and immediately save it in the desired picture format. Convenient - I recommend it to those who do not use it.

If you have a MAC, press Shift + Command + 3 to take a screenshot

Save Excel to XML

Again, as shown in the big picture, left-click on Save as - then the save window will open in the File type window, look for XML data (*.xml).

If you get an error when saving, save as an XML 2003 table, also listed. Most often this happens because. An XML document may not accept more than 65 thousand lines, and the 2003 format cuts them off!

In most cases, you will most likely be saving your books in the current file format (XLSX). But sometimes you might want to save your workbook in a different format, such as an older version of Excel, a text file, or a PDF or XPS file. Just remember that whenever you save a workbook in a different file format, some of its formatting, data, and functionality cannot be saved.

The list of formats (also called file types) you can (and cannot) open or save in Excel 2013 is the file that appears at the end of this article.

Converting an Excel 97-2003 workbook to the current file format

If you have opened an Excel 97-2003 workbook but do not need to store it in this file format, simply convert the workbook to the current file format (XLSX).

On the menu File select a team Intelligence.

Click the button Transform.

Supported Excel file formats

In Excel 2013, you can open and save project files in the following formats.

Excel file formats

Text file formats

Note: If you save the book in any text format, all formatting will be lost.

Other file formats

File formats that use the clipboard

If you have copied the data to the clipboard in one of the following file formats, you can paste it into Excel using the command Insert or Special insert (home > Clipboard > Insert).

File formats not supported in Excel 2013

The file formats listed below are no longer supported, so you will not be able to open files in these formats or save files in these formats.

To work with workbook data in a program that is no longer supported, try the following steps.

Search the Internet for a company that makes file converters for formats not supported by Excel.

Save the workbook in a different file format that can be opened in another program. For example, save a workbook in an XML spreadsheet or text file format that may be supported by another program.

You can save an Excel 2007 and later workbook as previous versions of Excel, as a text file, and in other formats such as PDF and XPS files. Normally, you save in XLSX format, but you can change the default file format for saving. If you frequently use the command Save as, you can add it to the Quick Access Toolbar.

Save an Excel workbook in a different file format

Important: If you save a workbook in a different version of Excel file format, the formatting and features unique to Excel 2007 and later will be lost. See Use Excel with earlier versions of Excel for more information.

Open the workbook in Excel that you want to save in a format suitable for use in another program.

On the tab File select a team Save as.

Accept the name suggested in the field File name, or enter a new book name.

Drop File type select a format suitable for use in another program.

Use the arrows to scroll through the list and display more formats.

Note: Depending on the active sheet type (book sheet, chart sheet, or other sheet types), different file formats will be available for saving.

Save a different file format in Excel 2007 and later format

Any file that opens in Excel 2007 and later can be saved in the current Excel workbook format (XSLX). This way you can take advantage of new features that are not supported in other file formats.

Note: When you save a workbook created in an earlier version of Excel as an Excel 2007 or later workbook, some functionality and some formatting may be lost. For unsupported features in Excel 97-2003, see