How to change column width to row height. Excel for Dummies - Change Columns, Rows and Cells

When you open a new workbook, all cells in it are at the default size. You have the ability to modify cells, insert and delete columns, rows and cells as needed.

In this lesson, you will learn how to change row heights and column widths, insert and delete rows and columns, wrap text in a cell, and merge cells.

Working with Rows, Columns, and Cells

By default, every row and every column of a new workbook has the same the height and width... Excel allows you to change column width and row height in different ways.

To change the width of a column:

If characters (#######) appear in a cell, the column is not wide enough to display the contents of the cell. Just increase the column width to display the contents of the cell.

To set the exact value of the column width:

Select AutoFit Column Width from the Format drop-down list, and Excel will automatically fit the width of each selected column according to the text you entered.

To change the line height:

To set the exact value of the line height:

Select AutoFit Row Height from the drop-down list of the Format command, and Excel will automatically fit the height of each selected row according to the entered text.

To insert lines:

When you insert new rows, columns or cells, you can see a button next to the inserted cells Paste options... This button allows you to choose how Excel should format them. By default, Excel formats inserted rows or columns the same way as cells in the row above the inserted one. To access more parameters, hover the cursor over the Paste Options button and click the drop-down arrow.

To insert columns:

By default, Excel formats inserted columns the same way as the columns to the left of them. To access more options, hover over the Paste Options button and click the drop-down arrow.

When you insert rows or columns, be sure to select the row or column by clicking on the header, that is, all their cells are selected. If you select only a cell, then only one new cell is inserted too.

To remove lines:

To remove columns:

Wrap text and merge cells

If the cell contains more text than can be displayed, you can select the Wrap text in cell option or merge the cell with empty neighbors. Wrap text makes the text appear in a cell on multiple lines. Merge cells connects adjacent cells into one.

To install Text Wrap:

If you change your mind to wrap text, just click the Wrap Text command button again.

To merge cells using the Merge & Center command:

If you change your mind about merging cells, just click the Merge and Center command again.

To access the merge options:

Click the drop-down arrow next to the Merge & Center command on the Home tab. A dropdown menu will appear.

Merge and center: Combines the selected cells and places the text in the center.
Concatenate by lines: Combines each row of selected cells into large cells. This command is useful when you are concatenating the contents of many cells into rows, but do not want to create one large cell.
Merge cells: Combines the selected cells into one.
Undo the merging of cells: Cancels the merging of the selected cells.

Practice!

Open up existing Excel 2010 book.
Resize the column.
Resize the line to 46 pixels.
Insert a column between columns A and B.
Insert a line between lines 3 and 4.
Delete a column or row.
Try to combine multiple cells.

For any spreadsheet, not just a spreadsheet, cell height and width is one of the basic questions. What are, for example, various unified forms, which are often implemented using Excel. In this article, you will learn about how to size elements. spreadsheets(rows, columns, cells).

Let's consider several options:

manual setting of borders;
auto-fit depending on what is entered in the cell;
precise dimensioning in selected units of measurement;
using the clipboard;
merging cells as a way to resize.

The first case is the most common. When you place the mouse cursor between the column names or row numbers, it changes shape to a double-headed arrow, the tips of which indicate in which directions the element can be resized.

The second option is best suited for a situation where you need to keep the row or column sizes as small as possible. Algorithm of user actions:

select the element or several elements of the table for which the sizes are set;
in the tab the main expand the team's list Format and select the appropriate command auto-fit.

The result of auto-fitting the column widths can be seen in the figure.

For the various standardized tables, it is important to set the dimensions of the elements accurately. In this case, you first need to decide on the units of measurement. The default sizes are Excel sheets are given in inches. You can change these settings in the section Additionally commands Parameters in the tab File.

Changes are more clearly displayed in the mode Page layout(on the tab View find the appropriate command). The rulers become visible with markings in the selected units, for example, centimeters. Next, select the custom element (column or row) and set the size of the element (in the example, this is a column) using the corresponding command in the button menu Format.

If you need two elements of the same size, you can use Paste Special. To do this, you first need to copy the master element. Then select the element to which you want to apply formatting settings (including dimensions) and select Paste special in command options Insert, then in the dialog box - Column width.

The problem often arises to resize only one cell. This is possible in Word, but not possible in Excel, so you have to resort to a workaround, namely, merging cells. This can be illustrated with the example of a uniform form. First, for all columns, the width is set to 1 mm, and then a number of cells are selected in the required row and combined using the button Merge and center.

The combination of all resizing capabilities allows you to create the most complex tables, while retaining all the advantages of automating calculations in Excel.

Quite often, the contents of a cell in a table do not fit within the default borders. In this case, the issue of their expansion becomes relevant so that all information fits and is in full view of the user. Let's find out in what ways you can perform this procedure in Excel.

There are several options for expanding cells. Some of them provide for manual pushing of boundaries by the user, and with the help of others you can customize automatic execution this procedure depending on the length of the content.

Method 1: simple drag and drop borders

The easiest and most intuitive way to increase the cell size is to manually drag the borders. This can be done on the vertical and horizontal scales of the coordinates of the rows and columns.

Attention! If, on the horizontal scale of coordinates, you position the cursor on the left border of the expandable column, and on the vertical scale, on the upper border of the row, following the drag-and-drop procedure, the sizes of the target cells will not increase. They will simply move to the side by changing the size of other sheet elements.

Method 2: expand multiple columns and rows

There is also the option to expand multiple columns or rows at the same time.

Method 3: manually entering the size via the context menu

You can also produce manual input the size of the cells, measured in numerical values. By default, the height is 12.75 units and the width is 8.43 units. You can increase the height to a maximum of 409 points and the width to 255.

The height of the lines is changed in a similar way.

The above manipulations allow you to increase the width and height of the cells in units of measurement.

Method 4: enter the size of the cells through the button on the ribbon

In addition, it is possible to set the specified cell size through the button on the ribbon.

Method 5: increase the size of all cells in a sheet or book

There are situations when you need to enlarge absolutely all cells of a sheet or even a book. Let's figure out how to do this.

We perform similar actions to increase the size of the cells of the entire book. Just to select all the sheets, we use a different technique.

Method 6: auto-fit

This method cannot be called a full-fledged increase in the size of cells, but, nevertheless, it also helps to completely fit the text into the existing borders. With its help, the text characters are automatically reduced so that it fits into the cell. Thus, we can say that its size relative to the text increases.

After these steps, no matter how long the entry is, it will fit into the cell. However, you need to take into account that if there are too many symbols in a sheet element, and the user will not expand it with one of the previous ways, then this entry can become very small, even unreadable. Therefore, it is not acceptable in all cases to be content exclusively with this option in order to fit the data into the boundaries. In addition, it should be said that this method only works with text, but not with numeric values.

As you can see, there are a number of ways to increase the size of both individual cells and entire groups, up to increasing all elements of a sheet or book. Each user can choose the most convenient option for performing this procedure in specific conditions. In addition, there is an additional way to fit content within the cell by using auto-fit. True, the latter method has a number of limitations.

For change width of one column you need to move the cursor over the line between the column names. The cursor changes to a double-headed arrow. Using the left mouse button, you can increase / decrease the width of the column: when changing the cursor, "click" on left button mouse and drag left or right to increase or decrease the column width. When you change the width, a hint about the value of the column width appears next to the cursor.

Therefore, every time we insert a new table, regardless of the method chosen, the created table will maintain the dimensions of this one. In the Table Properties dialog box, the Table tab has an Options button to open the Table Options dialog box.

At the entrance there is good example this. Here we can uncheck the default checkbox. Automatically adjust content size. Hope you enjoy this and it's helpful. If so, follow the blog and circulate it to your contacts. Data sharding labels apply filters with details so that it is easy to understand the data that is displayed and filtered in the table.

The column width can be changed and another way... If you right-click on the column name, a context menu appears, in which you need to select the item Column width ... A window will appear to enter the value for the column width. Enter the value and click OK .

Insert data sharding

You can also click the Insert tab and then click Data Segmentation. The "Insert Data" dialog box appears. All the column headings in the table are listed here. They can now be moved to another location on the sheet or changed as needed.

Data sharding filter

Click on any cell in the table.
Data segmentation also works with pivot tables.
Click on the "Design" tab.
Click the Insert Data Segment button.
Select the columns you want to use as segments.
Separate data segmentation is assigned for each selected column.
The segmentation is displayed on the sheet.

After the data segmentation is created, it is displayed on the worksheet throughout the table.

Similarly, you can change the line height, you just need to click on the line names.

Change the width of multiple columns (height of multiple rows)

It is necessary (you need to select it by clicking on their names) and change the width of any of them in one of the ways that were given in the first paragraph. This will change the width of all selected columns. The height of several lines changes in the same way.

Deleting and deleting data segmentation

Segments will overlap with each other if there are more than one. Just click on the value again to stop filtering the selected data. The window will be split into four panes by placing dividing bars above and to the left of the selected cell.

Select the values you want to include in the filter.
The table is filtered to display only the selected value.
Click the Remove Filter button.
All filters are removed, but the data segmentation remains on the worksheet.
Select Remove Filter Name.
Shortcut: Click segmentation and click Delete.
On the View tab, in the Window group, click the Split button.

Each of the four panels now has its own horizontal and vertical scroll bars that you can use to move the display area.

Change the width of all columns of a sheet (height of all rows)

To change the width of all columns of the sheet, you need to select all cells by clicking on the rectangle between the titles Column A and line 1, and change the width of any of them. The rest of the columns will resize automatically. Likewise, when changing the height of all lines.

You can also click any cell in one of the panels and scroll through any other means. The dynamic term is commonly used to indicate that a command is executed using the mouse, without clicking the command button on the ribbon. If we want dynamic and horizontal, find the small rectangular button that appears just above the top arrow of the vertical scroll bar. Click and drag out of this little rectangle, which will display a horizontal split pane that will split your window into two panes. Click and drag out of that little rectangle, which will display a vertical split pane that will split your window into two panes. This method is much more interesting as we now click, slide, and switch to the View tab.

Change column width to fit content

If the text does not fit in a cell and overlaps adjacent cells, you can increase the column width so that the text fits completely. To do this, just hover the cursor over the border between the name of the column in which the desired cell with the text, and the adjacent column on the right and double-click the left mouse button twice on this border. The column width will change automatically.

Dynamically resize panels

Of course, you can do both movements to keep your window split into four panes. If we want, we can resize the dynamically split areas. Move the mouse over one of the bytes of the split; When the cursor changes shape as shown in the next two images, click and drag.

To resize panels vertically. ... After creating a table, you can resize rows and columns, change their appearance, combine or split cells, and create tables. When you change the height or width of the table, only the row or column next to the selected grip will change. The table will maintain height and width.

The reasons for the increase in the volume of files lie in the internal format of the presentation of documents MS Excel. By itself, this format is mysterious. Of course, we will not consider it in detail - this is not an accounting task. But on the practical methods of reducing files in MS Excel, I propose to dwell in more detail. In most cases, such techniques can not only reduce the amount of space occupied by Excel documents, but also speed up their processing. And this is an important factor when working with large databases, which in accounting practice are found literally at every step. Let's start directly with the spreadsheet.

Modifying table cells

You can select a cell by clicking inside it. The grips will be shown in the middle of the cell borders. Click inside another cell to move the selection into it. Drag the grip of a cell to increase or decrease the size of the cell and its column or row.

When you have selected a cell, double click to edit the cell text. You can also start typing if the cell is highlighted to replace the current content. To select more than one cell, click and drag the cursor over multiple cells. If you click a cell in the table while the ribbon is active, the ribbon contextual tab of the ribbon is displayed. Inserting and deleting rows and columns Attaching and splitting cells Mapping cell styles Change the appearance of cell borders Editing the alignment and data format Locking and unlocking cells for editing Inserting blocks, fields and formulas Creating and editing cell styles Link a table to external data. With a cell selected, you can also right-click and use the options in the context menu to insert or remove columns and rows, merge adjacent cells, or make other changes.

Optimizing the worksheet

So, now our task is to figure out which elements of the worksheet MS Excel can increase the file size and what can be done in such a situation. First, let's work with a document that contains only data and calculation formulas. I chose the base as an example, a fragment of which is shown in Fig. 1. It contains exactly 5000 records, the document is presented in Excel format 2003 with the extension "* .xls ". The database contains seven fields, occupies about 367 KB - I propose to remember this figure. Now we do this:

Customize the display of column letters and row numbers

Default text editor in place displays column letters and row numbers when you select a cell in a table to edit it. To set a new background color, select the table, right-click and select the Color Table indicator from the context menu. The color, size and style of the text and the line color are controlled by the column heading options of the current table style.

Splitting tables in multiple parts

It is possible to divide a table into a large number of rows horizontally into a primary and secondary part. Select a table and use the Partition Tables section of the Properties palette to enable partitioning of tables. The generated secondary tables can be positioned to the right, left, or below the main table. You can also specify the maximum height and distance between the parts of the table. If you set Manual Positions to Yes, you can drag the child tables with your handles to different locations.

1. We stand at the end of the table. Press the combination "Ctrl + ↓ "(go to the end of the worksheet). In standard MS Excel format, this is line number 65536.

2. Change the format in any cell of this line. For example, select a cell with a yellow background.

3. Save the document and look at the table of contents of the disc. The file size has increased to 445KB. It would seem a little. However, the increase was about 21%.

If you've put together simple spreadsheets that seem huge in space of space, sometimes the reason may be easier and easier to delete than you think. This can be caused by some option that should not be activated even by formatting, which took up many more cells than it should. The purpose of this article is to show you some procedures that can help you reduce the size of a sheet that takes up more space than needed. It is recommended that you create a copy of the file before proceeding with these changes, because if something goes wrong as expected, the original file will remain intact and can be completely restored.

4. Remove formatting and save the file again. Document volume did not change- it is still larger than the original.

Important! When saving a fileExcel remembers not only the actually filled cells, but also the previously used range. When you clear cells, Excel does not exclude them from this range and stores more data in the file than is actually in the document.

You can remove formatting from strings you are not using in a very simple way. When you perform this procedure, the cursor should stop at the last used position on the sheet. Then, on the Home tab, go to Editing and open the checkbox next to the Eraser. Then select "Clear All".

To delete unused spreadsheets, right-click the tab of that spreadsheet and select the Delete option. When you store a pivot table, it saves time as it makes it easier to organize and summarize complex data and detailed analysis. However, it stores the database in use, which takes up a lot of space.

The solution to this problem is very simple.

Advice Delete all unnecessary (including empty!) Rows and columns located to the right and below the table, and resave the document. The table will become more compact.

Please note that complex, multi-color formatting, conditional formatting will increase the file size noticeably. To make the file take up less space, leave only the essentials in it. Delete unnecessary formats. IN Excel 2010 to do this:

You can turn off this feature in each of these tables by right-clicking and selecting the PivotTable Options option. Then go to the Data tab and uncheck the Save original data with file checkbox. When you copy data from the internet to a spreadsheet, sometimes a lot of unused objects are downloaded, but they are weighted in the spreadsheet. Then select the "Objects" option and confirm the operation.

If there is no object, the program will display an on-screen notification with this information. Another alternative could be hidden spreadsheets with data. To check this, click on any tab and select Show. If there is a hidden sheet, follow the previous procedures to make it cleaner.

1. Select a block of cells.

2. On the "Home "(Fig. 2) press the button" Clear ".

3. From the proposed menu, select the desired option " Clear formats"(Fig. 2). In a programme Excel 2003 for this purpose, you can use the menu "Edit -> Clear -> Format».

When you open the directory tree on the left side of this screen, you will see a list of worksheets. A new screen will open and you should go to the "Visible" property. These procedures do not always provide a solution to the problem, but it is these small configurations that are often responsible for freeing up space that was overused.

With your table or table ready for printing, choose the File menu. Go to the "Print" option. In the options to be displayed, select the last field, which by default means No Scaling. Select Set All Columns On Page to prevent columns from resting on multiple sheets of paper.

We set the values
instead of formulas

Second important factor abbreviating a document is replacing formulas with their values. Of course, such a technique is not always possible. Nevertheless, in real work, there are often situations when at some point the formulas in the table are no longer needed. But they remain, they are stored with the document. And not only take up extra space, but also slow down the processing of the table. To get rid of unnecessary formulas, do this:

There are other options that allow you to make a table fit on a single page. Select these options according to the number of columns and rows, as a very large table may be unreadable on a single page. Just click the "Portrait Orientation" button and change it. Observe the results of your settings in preview mode. If you are satisfied preview, just submit it for printing.

Page format refers to the area of your composition. Whether it's paper or sheet, size is the size of your paper used for printing. Orientation refers to Portrait or Landscape layout. In chapter. You can change the page format and orientation and preview your changes in the Page Setup group on the Page Setup tab.

1. Select the section of the table where you can delete formulas.

2. Copy the fragment to the clipboard (combination "Ctrl + C").

3. Without removing the selection, click on the drop-down list icon of the "Insert »The main menu of the program. A menu will open, as in Fig. 3.

In the "Print" section. By setting the size and orientation of your page and paper separately, you can control the position of the page on the paper and avoid misaligned printouts. You can also create bleeding from the edge of the page by printing on paper that is larger than your design and then cutting it to size. In addition, you can print multiple pages on one sheet.

Page size, paper size, or custom page orientation

You cannot change the page size, paper size, or orientation of individual pages in a multi-page publication. To create a multi-page publication containing different individual pages, you must create separate compositions for each format, page size, or orientation and assemble the printed publication manually.

4. Select the option " Paste special ...". The window “Paste special"As in fig. 4.In Excel 2003 Paste Special window can be opened by the command "Edit → Paste Special».

5. In this window, set the "Insert" radio button to the "Values" position.

6. Click OK.

Now save the document and estimate the size of the resulting file. Surely it will become smaller. And how much less depends on the number of formulas that were stored in the selected block. But in any case, updating the table will now be faster. For large databases, this speedup can be quite noticeable. By the way, it makes sense to replace formulas with values for another reason: in this case, there will be fewer potential problems with addressing cells during complex table conversion using data processing methods.

Delete notes

Even an experienced accountant can get confused in complex tables. Not to mention situations when a document is developed by one person, and others will use it. Therefore, a good style of work in Excel is to add notes to non-obvious parts of the document. And here, as they say, everything is good in moderation. Extra notes also take up space, without them the table will be more compact. To delete notes, do this:

1. Open the document, find the cell with the note.

2. Click on this cell with the right mouse button, a context menu will open, as in fig. five.

3. From this menu, select “ Delete note».

Usually, the effect of deleting notes is small - the amount of information in comments, as a rule, does not exceed several kilobytes. But there are also exceptions. For example, when a cell with a note has been copied to a large area of the worksheet. With such copying, the note will be duplicated along with the contents of the original cell. And accordingly, the total amount of information in all notes can increase tens and hundreds of times.

Pivot tables are one of the most common reasons for file size increases in MS Excel. Here the situation is as follows. When you create a pivot table, Excel creates a special database (the so-called "internal cache" or "pivot table cache") and saves it with the document. In fact, the internal cache is a template for generating final reports of a very different structure. It stores information about the available fields in the source database, possible relationships between them, subtotals and other calculated items. The main advantage of working with the cache is high performance. When adjusting the structure of the consolidated Excel report does not do all the work from scratch - it takes most of the information from the cache ready-made. But that's not all. In accounting practice, several pivot tables according to the same initial data. In such a situation, it is possible to use the same cache for each new table. It is very convenient. For example, when updating the pivot Excel tables will automatically refresh all reports built on one cache. In addition, the use of a common working base can significantly reduce the volume of the document.

However, working with the cache also has its drawbacks. The fact is that the cache size can exceed the size of the report itself, and several times! And this leads to a sharp increase in the document. At the same time, the power modern computer allows you to recalculate a summary report of several tens of thousands of records in a matter of seconds. So the performance gains from using the cache are relatively small. Therefore, in many cases it makes sense to completely abandon the work with the cache, and thereby reduce the size of the document. Let's see what needs to be done to do not save cache in the pivot table. We do this:

1. Open the document, stand on any cell inside the summary report.

2. Click the right mouse button, a context menu will open, as in fig. 6.

3. In this menu, select the item " Table parameters ...". A settings window will open, as in Fig. 7.

4. In it, go to the “Data " and turn off checkbox "Keep the original data along with the file».

5. Click "OK".

That's it, now only the summary report will be stored in the document. A few words about the effectiveness of this method. For the database shown in Fig. 1, I built a summary report on the volume of sales of goods by date. That is, there were three fields in the report: the date of sale, the name of the product and the amount. The document, together with the pivot table, took up 1489 KB. After I rebuilt the report and deleted the cache from it, the document was reduced to 982 KB. IN this case With just one pivot table, we saved about 35% of the total volume of the document. Not a bad result.

Speaking about the optimization of summary reports, I would like to draw your attention to the following point. An important role in this issue is played by how the source data area was specified for building the report. For example, in the base in Fig. 1 I can specify exactly the part of the worksheet by which the pivot table will be formed. For example, it can be block "A1: G5001 "(I remind you that I left 5000 records in the database, plus one line per title). But in this case, when you add records to the main database, they will not be included in the summary report. And this is a clear disadvantage. On the other hand, there is a positive point in this approach: the pivot table will take up little space. In practice, they most often act differently - indicate as initial data column area... For example, for the base in Fig. 1 these can be columns " A: G ". This is done so that when new rows are added to the table, they will automatically be included in the summary report. In this case, the pivot table will take up a lot more space.

One final point regarding summary reports. I would like to return to the question when there are several pivot tables in one workbook and they are all built on the same set of raw data. In this case, you can specify that pivot tables (except for the first one) are built on the basis of an existing cache. To do this, at the first stage of working with the Table Wizard, you need to set the switch "" to the position " in another pivot table or pivot chart"(Fig. 7). And then MS Excel will create a cache for only one table, and the rest of the pivot report instances will simply use this cache. At a large number space savings tables can be truly colossal.

Important! The shared cache trick can only be applied to a group of pivot tables that are based on a common source data.

By disabling the use of the cache for any report from such a group, Excel will automatically disable this option for all remaining tables.

And further. Access the parameter " Create a table based on the data found:»Is only possible when using the PivotTable Wizard. IN Excel 2003 is the primary tool for creating summary reports. But this is not the case in Excel 2010. You can insert a pivot table in different ways. For example, through the menu “ Insert ”(icon“ Pivot table "). But there is no separate button for calling the Wizard, it must be added to the ribbon manually. The easiest way to do this is:

1. Click on the panel quick access right mouse button.

2. From the context menu, select the item " Customizing the Quick Access Toolbar ..."(Fig. 8). The window “ Excel Options "as in fig. nine.

3. Switch " Select teams from:"Put in position" All teams. "

4. In the list of commands, highlight the " PivotTable and Chart Wizard».

5. Click the "Add >>" button (Fig. 9).

6. In the Excel Options window, click OK.

An icon for calling the PivotTable Wizard will appear on the quick access panel.

Pictures

The presence of graphic images in the workbook significantly increases the size of the document. Especially when there are a lot of images and they are presented with high resolution... For example, after a normal scan of a document, it may turn out that the picture was recorded at a resolution of 300 dpi (300 dpi). A camera or a modern camera is capable of creating images much large sizes... But most importantly, in office work, this is absolutely useless. Therefore, it makes sense to compress the pictures in the document, reducing the resolution to 150 or even 96 dpi. On the screen (and even during normal printing), the loss in quality will be invisible, and the file will become much smaller. To compress pictures in Excel 2010, do this:

1. Open a document, go to the sheet with pictures.

2. Select any picture by mouse click. An additional item will appear in the main menu of the program. Working with pictures", And the tape will look like in fig. 10.

3. In the group "Change »Click on the button« Compress Drawings ". The window “ Compress Pictures ", as in fig. eleven.

4. Switch " Output quality:"Put in position"Screen (150 ppi): ..." or " Email (96 PPI): ..."- depending on the desired compression ratio.

5. If your document contains multiple pictures, disconnect checkbox " Apply to this drawing only».

6. Check the box " Remove cropped areas of pictures". In this case, all parts of the image that are not visible on the screen will be deleted.

7. In the Compress Pictures window, click OK.

Important! In a programme Excel 2003, the Compress Pictures button is on the toolbarImage Adjustment". This panel is activated after selecting any picture in the workbook.

Removing named ranges

As far as the use of named ranges is concerned, I would say so. On the one hand, it is a very convenient tool for writing formulas. Names make formulas simpler and easier to understand. But, as you might expect, the abundance of named ranges increases the size of the file. Especially if the boundaries of the range dynamic, that is, they are determined using formulas. In any case, it makes sense to carefully review the list of ranges and discard those that do not play a key role in your particular document. To adjust the list of ranges, do this:

1. Open the document, press the combination "Ctrl + F3 ". The window “ Name Manager ".

2. In this window, select the items to be deleted and click the "Delete" button.

Disable the journal
changes

In certain situations, documents MS Excel is announced with sharing options. Let me remind you that in MS Excel 2010, sharing control is located on the tab “ Review ", icon" Access to the book ". In MS Excel 2003, such access can be opened through the menu “Service → Book Access».

By itself, such an opportunity is very useful, since in this case several people can correct and view the document at the same time. In the same time general access can cause a significant increase in the volume of spreadsheets. The point is that for shared files Excel remembers the entire history of document changes: who, when and how corrected cells workbook... By default, this information is retained for 30 days. Therefore, with active work with the table, its volume can rapidly increase. In my practice, for example, there was a table with a size of about 8 MB, which in a couple of weeks grew to a size of about 240 MB!

Advice Do not share a document unless it is clearly necessary.

If you still need to share and cannot be turned off, reduce the number of days of data retention in the history log. To understand the sharing options, do this:

1. Enter the menu "Review ", Click on the icon" Accessing the workbook "(in Excel 2003, this is the menu" Service → Book Access»).

2. In the window " File access control» disconnect checkbox "Allow multiple users to modify a file at the same time ..."(Fig. 12). This will disable sharing of the document. Change logExcel will not keep track of. The volume of the document will become smaller.

To limit the number of entries in the change log, do this:

1. Through the menu "Review " Open the window "File access control"(In Excel 2003 it is" Service → Book Access»).

2. Go to the "More details "And reduce the number of days of keeping the history of changes (Fig. 12).

Removing macros and forms

Another way to reduce the size of the workbook is to remove unnecessary (!) Macros and forms from it. The question arises: where can they come from? There are many reasons. The most common is to create a workbook from an existing template or document, which already had these same macros and forms. Since they are absolutely useless for your work, I propose to part with such a “gift” without regret. Moreover, any macro is an additional source for virus attacks... To remove unnecessary macros and forms, do this:

1. While on the worksheet, press Alt + F11 ". The Visual Basic Editor will open.

2. In the main menu, select the command “View → Project Explorer ". The window “ Project explorer »With a list of all modules and forms that are in the workbook.

3. Click the button on the module or form you want to delete.

4. Select “Remove” from the context menu.

5. In the window offering to export the text of the module to separate file select "No".

IN Excel program 2010 there is an easier way to remove macros and forms. Do this:

1. Open a document.

2. In the "File "Select the command" Save as… ". The Explorer window will open.

3. In this window, click on the option “file type: "And indicate the option" Excel workbook (* xlsx) "(Document without macro support).

4. Save your document. Everything! Macros and workbook forms will die automatically.

As alternative option you can disable macros when loading a file, and re-save the document.

Applying new formats
MS Excel 2010

This method of optimizing a workbook is relevant only for MS Excel 2007 (2010) users. The fact is that these programs have new formats for saving files that allow you to compress a document. I would pay attention to three such formats:

- "* .xlsx » - standard way storing documents in Excel 2007 (2010). Files in this format are packed. Their size in comparison with the same documents in Excel 2003 can be reduced by 5 - 7 times;

- "* .xlsm "- an extended version of the"* .xlsx "With the ability to save macros;

- "* .xlsb "- a binary way to represent a document. This format provides the smallest file size while maximizing data access speed. The only drawback of the “* .xlsb "- its incompatibility with other applications. Although, if you think about it, is this compatibility always needed? So, be bold, use the listed table formats in your work, and the volume of documents on your computer will be significantly reduced.

That's all for today. Successful work! I am waiting for your letters, suggestions and comments on , or on the editorial forum.

2017-02-06T17: 39: 57 + 00: 00

In this tutorial, I will tell you in what ways you can set and change the row height in Excel.

How to change line height with mouse?

To change the line height with the mouse, you need to move the cursor to its lower border (that is, to the border with the line that goes below). The cursor changes to a black double-headed arrow. Press the left mouse button and move the cursor up and down to the desired distance:

If you need to set the exact value of the line height in points, do not select it first with the left button. Move the cursor to the row header, and when it changes to a black thick arrow, right-click. In the drop-down menu, select "Row Height ..." and enter a value.

Right-clicking immediately saves time on line selection.

How do I change the height of multiple lines?

Excel allows you to change the height of several lines at once. If they go in a row, select them by headings and set the desired height manually (or through the "Line Height" menu item). Manually drag the bottom of the header of any of the selected lines.

To select several lines that are separated from each other by other lines, you can use in the process with the Ctrl key... First, select the "first" line with the left button, then hold down Ctrl and select the remaining lines. You can also drag the bottom border of any of the selected lines.

Auto-fit row heights based on their content

Excel allows you to automatically set the height of the lines based on the contents of the cells. If you need to auto-fit one line, move the cursor to the lower border of its number (that is, to the border with the line below), the cursor will change its appearance to a black double-headed arrow. Double click with the left mouse button: