Excel is extremely useful program to create spreadsheets, with many functions for various calculations and reporting ... But, sometimes, after long and hard work, an ideal table of 10 sheets comes out, but you need to fit everything on one sheet.

Fortunately, the program provides the ability to customize your spreadsheet to get everything on one page and make it look as perfect as possible. If your spreadsheet is too large for one page, you can use these settings to neatly distribute it over several pages.

PREVIEW BEFORE PRINTING

This is an important function, without which you would have to print everything, just to check how the table looks on the sheet, while spending a lot of time and paper. With the preview you can see how the printed document will look, and at this stage you can still make various changes to make everything look neater ...

Unfortunately Excel changed its structure and in its different versions you need to enter the preview in different ways: file \u003d\u003e print \u003d\u003e preview; or file \u003d\u003e print and you will see what your table will look like. If everything suits you, then go ahead and print the document. If not, try one of the strategies listed below.

CHANGE PAGE ORIENTATION

If your table is very wide, it is better to choose a horizontal page orientation. If it is very high - vertical. To select an orientation: open the page options \u003d\u003e select portrait or landscape; also orientation can be in "page layout" \u003d\u003e orientation.

DELETE OR HIDE ROWS (COLUMNS)

Large tables often have rows or columns that contain old information or information that does not need to be printed. These unnecessary rows and columns take up valuable space on the page, and are possibly the reason why all the information does not fit on one sheet. If you have rows or columns that you can delete - select them \u003d\u003e right-click \u003d\u003e select "delete".

If you think that they contain information that you will need again someday, you can hide them by right-clicking on the column or row header and selecting “hide”. To show the data again - select the columns or rows on both sides of the hidden ones and right-click \u003d\u003e show.

USE PAGE BREAK

As well as in a text editor, in Excel you can make a page break, while you yourself choose where it is best to do it. To do this, select the right place and use the "page layout", then select the breaks. Page breaks are inserted above and to the left of the selection.

CHANGING THE PRINT AREA

If your table has a huge amount of information, and you need to print only part of it, then select the desired fragment, click "Print", and then select "print the selected fragment" in the settings.

You can also specify the page numbers that you want to print, or choose other print options.

CHANGING PAGE MARGINS

If you do not have enough space to fit the table on a sheet of paper, then you can add extra space using the fields. To do this, go to the "page settings" menu and select the "Fields" column

But remember that text that is printed to the edge of the page not only looks ugly, but is also difficult to read.

CHANGE COLUMN SIZE

As a rule, Exel is more often used for numeric data, but where are numbers without accompanying text? Of which there are sometimes many, which in turn often makes it difficult to place a table on one page, or at least on several.

some text climbs far to the right and enlarges the document accordingly by several pages. Another way to reduce the size of your document is to reduce the width of the columns, but you also need to make sure you don't lose the data you want to print. To do this, go to the alignment menu and enable text wrapping. (in some Excel, you need to right-click on the cell \u003d\u003e cell format \u003d\u003e alignment tab \u003d\u003e word wrap)

now when the text does not crawl out of the column, you can expand the column size by simply dragging the header edge.

ZOOM TABLES

You can scale the table to fit the page or a specific number of pages. In the page settings, select the number of pages you want to print. Selecting fewer pages wide will scale the pages horizontally, choosing fewer pages high - vertically. You can also zoom in or out as a percentage

Using the scaling option can help you fit the desired decal onto the sheet of paper, but remember to preview the preview before printing, as you can reduce everything to hard to read.

There are many more useful functions in Excel, but we'll talk about them next time. If there are additions - write comments! Good luck 🙂

When working with spreadsheets sometimes it is necessary to increase their size, since the data in the resulting result is too small, which makes it difficult to read. Naturally, every more or less serious word processor has in its arsenal tools for increasing the table range. So it is not at all surprising that such a multifunctional program as Excel has them. Let's see how you can enlarge the table in this application.

I must say right away that there are two main ways to enlarge a table: by increasing the size of its individual elements (rows, columns) and by applying scaling. In the latter case, the table range will be increased proportionally. This option is divided into two separate methods: scaling on screen and on print. Now let's look at each of these methods in more detail.

Method 1: increasing individual elements

First of all, let's look at how to increase individual items in a table, that is, rows and columns.

Let's start by increasing the rows.

Sometimes it is necessary to expand not one row, but several rows or even all the rows of a table data array, for this we perform the following actions.

There is also another option for string expansion.

Now let's move on to the options for increasing the table array by expanding the columns. As you might guess, these options are similar to those with which we increased the line height a little earlier.

As with rows, there is an option to bulk increase the width of the columns.

In addition, there is the option of increasing the columns by entering their specific size.

Method 2: scaling on the monitor

Now let's see how to increase the size of the table by scaling.

It should be noted right away that you can scale the tabular range only on the screen, but it can be done on a printed sheet. Let's look at the first of these options first.

In addition, the zoom displayed on the monitor can be changed as follows.

The tool is quite useful "Zoom by selection", which allows you to zoom in on the table just enough to fit completely into the Excel window area.

In addition, the scale of the table range and the entire sheet can be increased by holding down the button Ctrl and scrolling the mouse wheel forward ("away from you").

Method 3: change the scale of the table on the print

Now let's see how to change the actual size of the table range, that is, its print size.

You can also change the scale of the table when printing in another way.

As you can see, you can enlarge the table in Excel different ways... And by the very concept of increasing the table range can mean completely different things: expanding the size of its elements, increasing the scale on the screen, increasing the scale on print. Depending on what the user needs at the moment, he must choose a specific option.

When working in Excel, some tables reach quite impressive size. This leads to the fact that the size of the document increases, sometimes reaching even ten megabytes or more. Increasing the weight of an Excel workbook leads not only to an increase in the space it occupies on the hard disk, but, more importantly, to a slowdown in the speed of execution of various actions and processes in it. Simply put, when working with such a document, Excel starts to slow down. Therefore, the issue of optimizing and reducing the size of such books becomes relevant. Let's see how you can reduce the file size in Excel.

An overgrown file should be optimized in several directions at once. Many users have no idea, but often an Excel workbook contains a lot of unnecessary information. When the file is small, no one pays special attention to this, but if the document has become cumbersome, you need to optimize it in all possible parameters.

Method 1: reducing the working range

The working range is the scope that Excel remembers. When recalculating a document, the program recalculates all cells in the work area. But it does not always correspond to the range in which the user actually works. For example, an inadvertently placed space far below the table will expand the size of the working range to the element where this space is located. It turns out that Excel will process a bunch of empty cells every time it is recalculated. Let's see how you can fix this problem using a specific table as an example.

If there are several sheets in the book that you are working with, you need to carry out a similar procedure with each of them. This will further reduce the size of the document.

Method 2: eliminate redundant formatting

Another important factor that makes excel document more difficult is redundant formatting. This includes the use of different types fonts, borders, number formats, but first of all it concerns filling cells with different colors. So before you additionally format the file, you need to think twice about whether it is necessary to do this or you can easily do without this procedure.

This is especially true for books containing a large amount of information, which in themselves are already quite large. Adding formatting to a book can even increase its weight several times. Therefore, you need to choose the "golden" mean between the clarity of the presentation of information in the document and the file size, apply formatting only where it is really needed.

Another factor associated with weighting formatting is that some users prefer to format cells “oversized”. That is, they format not only the table itself, but also the range that is under it, sometimes even to the end of the sheet, with the expectation that when new rows are added to the table, it will not be necessary to format them again every time.

But it is not known when exactly new lines will be added and how many of them will be added, and with such preliminary formatting you will make the file heavier right now, which will also negatively affect the speed of working with this document. Therefore, if you applied formatting to empty cells that are not included in the table, then you should definitely remove it.

The above steps will help you significantly reduce the size of your Excel workbook and speed up your work in it. But it is better to initially use formatting only where it is truly appropriate and necessary, than to waste time on optimizing the document later.

Method 3: remove links

Some documents have a very large number of links from which values \u200b\u200bare pulled. This can also seriously slow down the speed of work in them. External links to other books affect this show especially strongly, although internal links also negatively affect performance. If the source from where the link gets the information is not constantly updated, that is, it makes sense to replace the reference addresses in the cells with normal values. This can increase the speed of working with the document. You can view whether a link or value is in a specific cell in the formula bar after selecting an item.

But remember that this option for optimizing an Excel workbook is not always acceptable. It can be used only when the data from the original source is not dynamic, that is, it will not change over time.

Method 4: change the format

Another way to significantly reduce file size is to change its format. This method, probably more than anyone else, helps to compress the book, although the above options should also be used in combination.

There are several "native" file formats in Excel - xls, xlsx, xlsm, xlsb. The xls format was the base extension for Excel 2003 and earlier. It is already obsolete, but, nevertheless, many users still continue to use it. In addition, there are times when you have to go back to working with old files that were created many years ago in the absence of modern formats. Not to mention the fact that workbooks with this extension work with many third-party programs that cannot handle later versions of Excel documents.

It should be noted that a book with the xls extension has a much larger size than its modern analogue of the xlsx format, which Excel currently uses as the main one. First of all, this is due to the fact that xlsx filesare, in fact, compressed archives. Therefore, if you use the xls extension, but want to reduce the weight of the book, then this can be done simply by resaving it in the xlsx format.

In addition, Excel has another modern xlsb format or binary book. It saves the document in binary encoding. These files weigh even less than xlsx books. In addition, the language in which they are written is closest to excel programs... Therefore, it works with such books faster than any other extension. At the same time, the book of the specified format is in no way inferior to the xlsx format and is superior to the xls format in terms of functionality and the possibilities of using various tools (formatting, functions, graphics, etc.).

The main reason why xlsb has not become the default format in Excel is that third-party programs are practically unable to work with it. For example, if you need to export information from Excel to the 1C program, then this can be done with xlsx or xls documents, but not with xlsb. But, if you do not plan to transfer data to any third-party program, then you can safely save the document in xlsb format. This will allow you to reduce the size of your document and increase the speed of your work in it.

The procedure for saving a file in the xlsb extension is similar to what we did for the xlsx extension. In the tab "File" click on item "Save as…"... In the opened window save in the field "File type" you need to choose an option Excel Binary Workbook (* .xlsb)... Then click on the button "Save".

We look at the weight of the document in the section "Intelligence"... As you can see, it has decreased even more and now it is only 11.6 KB.

Summing up, we can say that if you work with a file in xls format, then the most effective way to reduce its size is to resave in modern xlsx or xlsb formats. If you are already using these file extensions, then to reduce their weight, you should correctly configure the workspace, remove excessive formatting and unnecessary links. You will get the greatest return if you perform all these actions in combination, and not limit yourself to just one option.

When you create documents for printing (for example, reports, invoices, invoices, etc.), it is important to customize them so that the printed sheet looks correct, convenient and logical. What worksheet settings can be made - I will tell you in this post.

Most of the settings can be made in the " Page settings". It is called by clicking on the icon in the corner of the tape block Page Layout - Page Setup .

Page Setup Icon

Page orientation in Excel

Depending on the shape of the data on the sheet, you can choose portrait (vertical) or landscape (horizontal) orientation. This can be done in the following ways:

1. Execute tape command Layout Page - Page Setup - Orientation ... In the menu that opens, select one of the two options
2. Execute File - Print , in the print settings window you can also choose the orientation

Setting sheet orientation

1. In the dialog box " Page settings"On the" Page", Block" Orientation» — select the radio button you want

Orientation in the Page Setup window

Each of the above methods will change the orientation of the active sheet, or all selected sheets.

How to resize a page in Excel

Although most printers print on A4 sheets (21.59 cm x 27.94 cm), you may need to resize the printed sheet. For example, you are preparing a presentation on A1 sheet, or you are printing branded envelopes of appropriate sizes. To resize the sheet, you can:

1. Use the team Layout Page - Page Setup - Size .

Resize worksheet in Excel

1. Execute File - Print and choose the size you want
2. In the window " Page settings»Choose from the list« Paper size»

Setting up fields in Excel

Excel margins are blank areas of the page between the edge of the sheet and the border of the cells. There are several ways to customize the fields:

1. Execute tape command Layout Page - Page Setup - Fields ... A menu will open to select one of the field options. Alternatively, you can click " Custom fields ..."To set the dimensions manually

Setting up fields in Excel

1. Execute File - Print , in the corresponding section there is a similar menu
2. Call the window « Page settings"And go to the" Fields»For fine tuning. Here you can manually specify the size of the left, right, top and bottom margins, as well as headers and footers. Here you can also center the work area relative to the margins by setting the appropriate checkboxes (the new position will be indicated on the thumbnail in the center of the sheet).

Add headers and footers to Excel

Headers and footers are information areas at the top and bottom of the page in the margin. Headers and footers are repeated on each printed page, they record auxiliary information: page number, author's name, report title, etc. At the top and bottom of the page there are three fields for headers and footers (left, center and right).

Headers and footers in Microsoft Excel

Yes insertion of headers and footers - go to, because headers and footers are clearly marked here. Click inside one of the headers and footers and write informative text. At the same time, the ribbon tab “ Working with headers and footers", Which contains additional commands.

So, you can insert automatic footer, which will indicate the number of the current page, the number of pages per sheet, file name, etc. To insert an automatic element - select it on the ribbon: Working with Columns - Constructor - Header and Footer Elements ... These elements can be combined with each other and with free text. To insert - place the cursor in the header and footer field and click on the icon in the "Header and footer elements" group (see figure).

On the "Constructor" tab, you can set additional parameters for the columns:

• Special header for the first page - Colonials of the first page are not repeated on other pages. It is convenient if the first page is the title page.
• Different headers and footers for odd and even pages - suitable for page numbering when printing a booklet
• Zoom along with the document - the setting is enabled by default, headers and footers are scaled the same as the entire page. I recommend keeping this option on to ensure the integrity of the layout.
• Align to page margins - the left and right headers and footers are aligned to the corresponding margins. This parameter is also set by default, there is no special point in changing it.

Headers and footers are a handy tool to give your work the final touch. The presence of high-quality, informative headers and footers is a sign of the performer's professionalism.

Insert a page break in Excel

If you need to insert a forced page break in some place on the sheet, place the cursor in the cell to the right below the break and execute the ribbon command Page Layout - Page Setup - Breaks - Insert Page Break. For example, to insert a break after column D and row # 10, select cell E11 and run the above command.

Insert a page break

To remove a break there is a reverse command: the command Page Layout - Page Setup - Breaks - Remove Page Break ... To remove all manual breaks there is a command Page Layout - Page Setup - Breaks - Reset Page Breaks .

After inserting a break, page separators appear on the sheet. In they take the form of blue frames, dragging which, you can change the printed page borders.

Add a title to Excel

When you print large tables, you need to repeat the table heading on every page. If you used, then when printing this function will not affect the header in any way, it will be printed only on the first page.

To repeat cells on every printed page, run the ribbon command Page Layout - Page Setup - Print Headers ... The window “ Page settings", Tab" Sheet". In this window, in the fields " Through strings"And" Through Columns»Provide links to rows and columns to repeat on each sheet. Please note that the selected cells will not be physically present on every page, but will only be repeated when printed.

Adding titles to Excel

Adjusting the print scale in Excel

Sometimes information on a sheet cannot be placed compactly so that it fits into the required number of pages. It is not always convenient to adjust the column widths and row heights to get a well-composed sheet. Much more convenient change print scale (not to be confused with display scale). Using this option, you rescale your data on the printed page.

To change the print scale, use the ribbon commands Page Layout - Fit ... You can manually set the scale using the Scale counter, but it is much easier and faster to use the " Width"And" Height". Thanks to them, you can set how many sheets in width and height you will have. For example, to fit the data into one page wide, set: Width - 1 page; Height - Auto.

Excel print scale

Hiding before printing

If you do not need to print some data, you can simply hide it. For example containing technical information, leave only meaningful data. Most often, reports should not contain details of the calculations, but only display their results and conclusions, prompting certain management decisions.

You can also make objects unprintable. To do this, select it and right-click on the object frame. In the context menu, select " Format…". In the opened dialog box in the "Properties" group, uncheck the box Print object.

Setting up printing of Excel objects

Views in Excel

If you are using the same document with different display layouts, you often need to change the same display and formatting options. For example, you update the original data and calculations on a daily basis, which you then hide when printing. Instead, you can create multiple views of the same sheet, after which it takes a couple of seconds and a few clicks to change the display.

That is, views in Excel are saved formatting and display settings that you can apply at any time, instead of manually doing it. The program saves the following formatting settings in views:

1. worksheet
2. Print Worksheet Settings
3. and cursor position
4. Anchored areas

After you have done all the listed settings, run the command View - Book View Modes - Views - Add ... In the "Add View" window that opens, specify a name for the new view and make a choice in the proposed refinements. Click " OK", And the show is created!

Adding a view to Excel

In the future, to apply the saved view to the workbook, run View - Book View Modes - Views , select the desired view from the list and click " Apply". Unfortunately, views do not work if there is one on the sheet, this limits the use of the tool.

These are the sheet settings that can and should be done in preparation for printing (and not only). Set up your workbooks correctly so your reports look perfect. Even the highest quality calculations look boring and useless if they are not formatted and prepared for printing. Even if you send reports by mail, most likely the manager will want to print them. Therefore, I recommend preparing each sheet of the report for printing, regardless of the method of submission!

Friends, if you still do not understand any details on the materials of the post, ask questions in the comments. And do not forget to subscribe to updates, become professionals with the site! Always yours, Alexander Tomm

18 comments

Hello, what solution can be found for the following case? There is a large table (more than 3 sheets) and there is at the end of the signatures of officials. When printing, not the entire table is printed, but its segment is of undefined length. Maybe less than a page, or maybe more than 2 pages. In this case, the signatures must also be. But since the length is not predetermined in advance, then a situation may arise when the signatures will be on different pages... If you put a forced break, then it can turn out that there is one line in the table, and the signatures are on another page, which is also unsightly. Is an automatic solution possible, without operator intervention with every print? Thanks in advance

1. Vladimir, you can try to insert signatures into headers and footers, but it won't work out very well. Or, solve the problem using VBA, but the best way out is to control with your eyes before printing.