How to format a table in Excel. Assigning and removing formats in Excel. To create an Advanced filter you need

Formatting in Excel used to facilitate the perception of data, which plays an important role in labor productivity.

Purpose of the format

1. Select the command "Format" - "Cells" (Ctrl+1).
2. In the dialog box that appears (the window will be discussed in detail later), enter required parameters formatting.
3. Click "OK" button

A formatted cell retains its format until a new format is applied to it or an old one is deleted. When you enter a value into a cell, the format already used in the cell is applied to it.

Removing a format

1. Select a cell (range of cells).
2. Select the command "Edit" - "Clear" - "Formats".
3. To delete values ​​in cells, select the “All” command from the “Clear” submenu.

Please note that when copying a cell, along with its contents, the cell format is also copied. Therefore, you can save time by formatting the source cell before using the copy and paste commands.

Formatting using toolbars

The most frequently used formatting commands are located on the Formatting toolbar. To apply a format using a toolbar button, select a cell or range of cells and then click the button. To delete the format, press the button again.

To quickly copy formats from selected cells to other cells, you can use the Format Painter button in the Formatting panel.

Formatting individual characters

Formatting can be applied to individual characters text value in a cell in the same way as for the entire cell. To do this, select the desired characters and then select the “Cells” command from the “Format” menu. Set the required attributes and click OK. Press the Enter key to see the results of your work.

Using AutoFormat

Automatic Excel formats are predefined combinations of number format, font, alignment, borders, pattern, column width, and row height.

You can keep statistics and enter into the table all the transactions that will be made. You already know how, but, in addition, if you wish, you can arrange this table more conveniently for your work if you go to the menu item Home - Format as table , and choose one of the options that are there. Just be sure to select at least one cell in the table before selecting, otherwise Excel program will not know which table to format.

In the table headers, after selecting formatting, will appear, by clicking on which you can sort or filter the data, leaving only the data you need.

You can also not use ready-made options, but create your own style formatting Excel tables 2007 if you go to Table Formatting - Create a Table Style . For each element Excel tables You can choose your format.

When you format a table in this way, a new menu item will appear in Excel 2007 Constructor . This item appears when a cell in a formatted Excel table is selected. If a cell is selected outside the table, you will not see this item. It has additional formatting options: You can check or uncheck items Alternating lines And Alternating Columns to change the color of the borders. In point Express Styles You can also choose a completely different formatting option.

You can also in the menu Constructor remove duplicate rows, make a pivot table, or convert a table to a range.

Team Convert to Range returns the Excel table to its normal appearance, but cell formatting (background color, text color, and border color) remains the same. To remove cell formatting, use the command Sample format . Select an unformatted cell outside the Excel table, and in the menu home find the brush icon. It is located on the left under the icons Cut And Copy. Click on it. The cursor will change to a cross with a brush icon. Now select the entire table. You will see that the table format will change - it will become the same as the format of the cell taken as a sample.

Also for formatting tables in Excel 2007 you can use the command Home — Cell Styles : both for the entire table at once and for individual cells. Select the cells that need to be formatted, go to this menu item and click on the style you need. When you hover your mouse over the styles, you can immediately see the result, what it will be like if the style is applied.

In addition, there is another opportunity to change the table formatting in Excel 2007. Go to Page layout . On the left side you will see a subtitle Themes. You can change it Colors, Fonts, Effects for everything Excel sheet straightaway. The changes in fonts are best visible, although the colors will also change if you, for example, use the command before Format as table .

You can get more detailed information in the “All courses” and “Utilities” sections, which can be accessed through Top Menu site. In these sections, articles are grouped by topic into blocks containing the most detailed (as far as possible) information on various topics.

You can also subscribe to the blog and learn about all new articles.
It does not take a lot of time. Just click on the link below:

So, you have created a workbook in Excel full of data. It is clearly organized, the information is up to date, and the formatting is exactly as intended. You decided to print a paper version of this table... and everything went wrong.

Excel spreadsheets don't always look great on paper because they aren't designed to fit on the printed page. They are designed to be as long and wide as required. This is great for editing and on-screen viewing, but can be problematic when printing documents because the data may not always fit perfectly into a standard paper format.

All these difficulties don't mean it's impossible to make an Excel spreadsheet look good on paper. It's actually not difficult at all. The following 5 tricks for printing in Excel will help you solve this problem. They should all work the same in Excel 2007, 2010 and 2013.

1. Preview the page before printing

Using a tool Print Preview (Preview) You can see exactly how the table will look on the printed page. In terms of saving time and paper, Print Preview(Preview) is your main tool when printing. You can even make some changes, such as dragging the borders of the print with your mouse to make them wider or narrower. Use this tool after you've adjusted your printing and layout options to make sure your table looks the way you want it to.

2. Decide what should be printed

If you only need a small part of the data, do not print the entire workbook - print selected data. You can only print the sheet that is in this moment view by selecting in the print settings Print Active Sheets(Print active sheets), or select Print Entire Workbook(Print Entire Book) to print the entire file. In addition, you can print a small section of your data by highlighting the desired area and selecting Print Selection(Print selection) in the print settings.

3. Maximize your available space

You're limited by the size of the sheet of paper you're printing on, but there are ways to get the most out of its space. Try changing the page orientation. The default orientation is good for data that has more rows than columns. If your table is wider than it is tall, change the page orientation to landscape(landscape). Still need more space? You can change the width of the borders along the edges of the page. The smaller they are, the more space is left for data. Finally, if your table is not too big, try playing with the tool Custom Scaling Options(Scale) to fit all rows or all columns, or risk fitting the entire table onto one printed sheet of paper.

4. Use header printing

If a table spans more than one page, then it becomes difficult to understand what specific data belongs to since Excel by default prints column headings only on the 1st sheet. Team Print Titles(Print Headers) allows you to print row or column headings on each page, making the data much easier to read.

5. Use page breaks

If your table spans more than one sheet of paper, we recommend using page breaks to define exactly what data will go on each specific sheet. When you insert a page break, everything below the break is separated from what is above the break and ends up on the next page. This is convenient because... You can break down the data exactly as needed.

Using these tricks, you can make your tables easier to read. You will find more details about the techniques described above in the lessons.

The standard Excel spreadsheet column widths do not always fit the columns in your worksheet.

For example, column widths need to be changed under the following conditions:

• the width is not sufficient to show the text without being truncated;
• the width is not sufficient to show the numeric information (in this case the cell will be filled with # # # # characters);
• The width is too large for this Excel table and needs to be reduced.

If the column width is too small, no data is lost; they just don't show up completely.

Column width and groups columns can be changed using commands Format, Column.

Formatting a table column

1. Select the columns to format.

2. Select the command from the menu Format, and then the required operation (Table 1).

Table 1. Excel table column formatting

Change the width of Excel table columns using the mouse

1. Select the columns to change the width.

2. Move the mouse pointer to the right border of one of the selected columns in the table column headers area. The mouse pointer will change to a double-headed arrow.

3. Drag the right column border to a new location. The column width of the Excel table will be shown in the tooltip field during this process.

To automatically adjust the width of an Excel table column, double-click on the right border of the column.

The height of selected lines can be changed to provide increased spacing between lines.

The row height that Excel automatically assigns is based on the size of the largest font found in the table row. Automatically calculated line height is approximately 25% larger than the font size in points(1/72 inch).

Exercise

Formatting table rows

1. Select the required lines.

2. Select Format, String and required operations (Table 2).

Table 2. Formatting a Row in an Excel Table

The table row height can also be changed using the mouse. Drag the bottom border of the line to a new location. The row height will be shown in the tooltip field during this process.