How to remove table formatting. Purpose and removal of formats in Excel. Display and hiding mesh lines

Formatting cells

Formatting cells The spreadsheets are a prerequisite for working with data in Excel 2007. Formatting cells It is done using the "Numeric format" drop-down list or the "cell format" dialog box. This window has six tabs: number, alignment, font, border, fill, protection (Figure 1). The dialog window opens when you click on the left mouse button on the "Number" group arrow on the Home tab.

Fig. one

On the "Number" tab of the "Cell format" window, you can assign numeric formats of electronic tables. Moreover, the formats of electronic tables can be assigned both before the data entry and after their input in the cell. Numerical formats include: common, numeric, monetary, financial, etc. (Fig. 1).

Typically, the data is entered into Excel 2007 spreadsheets in one of the numeric formats. If the data is entered without a cell format, then by default Excel 2007 assigns the format - general. It should be noted that you can format one cell or several cells at the same time.

To format the cell (cells), it is necessary to select it (them), then open the Dialog Window and the "Numeric Format" dialog window in the "Number" group on the Home tab and assign the necessary numeric format.

The formatting of the cells includes such operations as combining cells, alignment and direction of text in cells, transfer according to words, etc. These operations can be performed in the "Dialog" dialog box on the Alignment tab (Fig. 1) or in the Alignment group on the Home tab (RIS. 2).

RIS. 2.

Formatting a font can be performed in the "Cell format" dialog box using the tools on the "Font" tab or in the Font group on the Home tab. It should be noted that the font and other parameters of Excel 2007, operating in the default mode, can be changed in the "Excel parameters" dialog box. This window can be opened by executing the command "Office" / "Excel parameters" (RIS. 3)

RIS. 3.

The borders, fill and cell protection can be formatted on the corresponding tabs of the Dialogue window "Cell format".

In addition, Excel 2007 has the "Format" tool in the "Cell" group on the Home tab. This intrusion is used to change (formatting) the height of the line or the width of the column, protection or hide cells, rows, columns, sheets, arrangement of sheets (RIS. 4).

RIS. four

Style application

A set of cell formatting attributes stored under a unique name is called style. Styles of cells can be created and applied to cells. Tools of styles of cells are placed in the "Styles" group on the Home tab (Figure 5).

RIS. five

In Excel 2007, you can change the data format depending on their values. Such formatting is called conditional formatting. In addition, conditional formatting can be applied to highlight cells with important information using icons, histograms, color scale, etc. (Figure. 6).

Fig. 6.

Fast formatting of the range of cells and converting it to the table using a specific style selection can be implemented by tools to "format as a table" from the "Styles" group on the Home tab (Figure 7)

One of the most important processes when working in the Excel program is formatting. With it, it is not only the appearance of the table, but also specifies the indication of how the program to perceive the data located in a particular cell or range. Without an understanding of the principles of operation of this tool, it is impossible to master this program well. Let's find out in detail what is formatting in Excele and how they should be used.

Formatting is a whole set of adjustment measures for visual contents of tables and calculated data. This area includes a change in a huge number of parameters: size, type and color of the font, the magnitude of the cells, fill, borders, data format, alignment and much more. We will talk more about these properties below.

Automatting

Any range of sheet with data can be applied automatic formatting. The program formats the specified area as a table and assigns a number of pre-installed properties.

After that, the table will have the selected format. But it can always be edited using more accurate formatting tools.

Transition to formatting

Users not in all cases satisfies the set of characteristics that is presented in autoformatting. In this case, it is possible to format the table manually using special tools.

Go to formatting tables, that is, to change their appearance, you can via the context menu or by performing steps using tape tools.

In order to proceed to the formatting through the context menu, you need to perform the following steps.

Formatting tools on the tape are in various tabs, but most of all in the tab "The main". In order to use them, you need to select the corresponding element on the sheet, and then click on the tool button on the tape.

Data formatting

One of the most important types of formatting is the data type format. This is due to the fact that it determines not so much the appearance of the information displayed, how much does the program indicate how to process it. Excel is completely different from the processing of numerical, text, monetary values, date and time formats. You can format the data type of the selected range by both the context menu and using the tape tool.

If you open the window "Format cells" Through the context menu, the desired settings will be located in the tab. "Number" In the parameter block "Numeric formats". Actually, this is the only block in this tab. There is a choice of one of the data formats:

• Numerical;
• Textual;
• Time;
• Date;
• Monetary;
• General, etc.

After the choice is made, you need to click on the button Ok.

In addition, additional settings are available for some parameters. For example, for a numeric format on the right side of the window, you can set how many marks after the comma will be displayed in fractional numbers and whether the separator is shown between discharges in numbers.

For parameter "Date" It is available to set, in which date the date will be displayed on the screen (only numbers, numbers and names of months, etc.).

Similar settings are available at the format "Time".

If you select item "All formats"In one list, all available data formatting subtypes will be shown.

If you want to format the data through the tape, then being in the tab "The main"You need to click on the drop-down list located in the toolbar "Number". After that, the list of basic formats is revealed. True, it is still less detailed than in the previously described version.

However, if you want to more accurately format, then you need to click on this list "Other numeric formats ...". An already familiar window will open "Format cells" With a complete list of change settings.

Alignment

A whole block of tools is presented in the tab. "Alignment" in the window "Format cells".

By installing the birds near the corresponding parameter, you can combine selected cells, make the width of the width and transfer the text according to the words if it does not fit into the cell boundary.

In addition, in the same tab, you can position the text inside the horizontal cell and vertical cell.

In parameter "Orientation" Setting the text arrangement angle in the table cell.

Block tools "Alignment" There is also a ribbon in the tab "The main". There are all the same features as in the window "Format cells"but in a more truncated version.

Font

In the tab "Font" Formatting window There are ample possibilities for setting the font of the dedicated range. These features include changing the following parameters:

• font type;
• inscription (italics, bold, usual)
• the size;
• color;
• modification (substitution, firm, crossed).

On the tape, there is also a tool block with similar capabilities, which is also called "Font".

The border

In the tab "The border" Formatting window You can customize the line type and its color. Immediately it is determined which border will be: internal or external. You can generally remove the border, even if it is already available in the table.

But on the tape there is no separate block of tools for the border settings. For these purposes in the tab "The main" Only one button is highlighted, which is located in the instrument group. "Font".

Fill

In the tab "Fill" Formatting window You can set the color of the table of the table. Additionally, you can install patterns.

On the tape, as for the previous fill function, only one button is allocated. It is also placed in the toolbar "Font".

If the standard colors presented you lack and you want to add originality into the color color, then you should go on it "Other colors ...".

After that, a window is opened for a more accurate selection of colors and shades.

Protection

In Excel, even protection refers to the formatting area. In the window "Format cells" There is a tab with the same name. In it, you can designate whether a dedicated range will be protected or not, in the case of setting a sheet lock. Immediately you can include the hide formula.

On the tape, similar functions can be seen after clicking on the button. "Format"which is located in the tab "The main" In the tool block "Cells". As you can see, a list appears in which there is a group of settings. "Protection". Where you can not only adjust the cell's behavior in case of locking, as it was in the formatting window, but also immediately block the sheet by clicking on the item "Protect sheet ...". So this is one of those rare cases when a group of formatting settings on a ribbon has a more extensive functionality than a similar tab in the window "Format cells".

.
Lesson: How to protect the cell from changes to Excel

As you can see, the Excel program has a very wide functionality for formatting tables. At the same time, you can use several versions of styles with pre-installed properties. You can also make more accurate settings with a whole set of tools in the window. "Format cells" And on the ribbon. With a rare exception in the formatting window, more wide possibilities of changing the format than on the tape are presented.

Excel sheet is a blank to create tables (one or several). Methods of creating tables are several, and tables created by different ways provide different capabilities with data. You can learn about this from this article.

Creating Tables

First, let's talk about creating a spreadsheet in a broad sense. What do I need to do:

• on the sheet Excel enter column names, rows, data values, insert formulas or functions if it requires a task;
• highlight All filled range;
• turn on everything borders.

From the point of view of Excel developers, what you created is called range cells. With this range you can produce various operations: format, sort, filter (if you specify the header string and turn on Filteron the tab Data) etc. But all of the above you must take care of yourself.

To create a table, as Microsoft programmers understand it, you can choose two ways:

• convert to the table already existing range;
• insert the table using Excel.

Conversion option Consider on the example of the table, which is shown in the figure above. Do the following:

• select cells tables;
• use the tab Insert and team Table;
• in the dialog box, check that the desired range is highlighted, and that the checkbox is checked. Table with headlines.

The same result, but with the choice of style it would be possible if after allocating the range to apply the command Format as a tabletab the main.

What can be noticed right away? The received table already has filters (each header has a selection icon from the list). Appeared tab Constructorwhose commands allow you to manage the table. Other differences are not so obvious. Suppose, in the initial version there were no results under the data columns. Now on the tab Constructor You can turn on line of outcomethat will lead to the appearance of a new row with the options for selecting the option of the results.

Another advantage of the table is that the filter action applies only to its lines, the data that can be placed in the same column, but outside the table area, do not fall under the filter. This could not be achieved if the filter had been applied to the fact that at the beginning of the article was marked as a range. This feature is available for the table as publication in SharePoint..

The table can be created immediately, bypassing the range. In this case, select the range of empty cells and use any higher than the considered option of the table. The headlines at such a table first conditional, but they can be renamed.

Delete tables

Despite the obvious advantages of the tables compared with the ranges, sometimes it is necessary to abandon their use. Then on the tab Constructor Select Team Convert to range (Of course, at the same time, at least one cell table must be highlighted).

If you need to clean the sheet from the data, regardless of whether they are decorated as a range or as a table, select all cells with data and use the key Delete. Or delete the corresponding columns.

Techniques for creating and removing the tables that you learned from this article will use you in Excel 2007, 2010 and older.

Standard column width is not always suitable for columns on the work sheet.

For example, the width of the columns must be changed under the following conditions:

• width is insufficient to show text without truncation;
• width is insufficient to show digital information (in this case, the cell will fill the symbols # # # #);
• the width is too big for this table, and it must be reduced.

If the width of the column is too small, the data is not lost; They are simply not shown completely.

Column width I. groupcolumns can be changed when using command format, column (Format, Column).

Formatting column

1. Select columns for formatting.
2. Select the command in the menu Format, column (Format, Column)and then the required operation (Table 29).

Table 29. Column formatting

Changing columns width using mouse

1. . Volume columns to change width.
2. Move the mouse pointer to the right border of one of the selected columns to the column header area. The mouse pointer will change to the bidirectional arrow.
3. Drag the right border of the column to a new place. The width of the column will be shown in the field of the pop-up tip during this process.

For the firing of the column width, double-click on the right border of the column.

The height of the selected rows can be changed in order to ensure the increased distance between the lines.

The height of the strings is automatically assigned to Excel, based on the size of the greatest font located in the string. Automatically calculated line height approximately 25% more than font size in points(1/72 inches).

Formatting string

1. Highlight the required lines.
2. Choose Format, line (Format, Row) and Required operations (Table 30).

Table 30. Formatting string

The row height can also be changed using the mouse. Drag the bottom limit of the string to a new place. The height of the string will be shown in the field of the pop-up tip during this process.

To automate the height of the string, click twice at the bottom limit of the line.

Formatting cells

The contents of the table cells can be formatted to improve the appearance of the table on the work sheet. All cell format options can be found in the command dialog box. Cellsin the format menu (Format, Cells). In addition, some buttons are available in the formatting toolbar to quickly apply the most common text and digital formats.

Formatting options dialog box Format cells(Fig. 90) are separated by bookmarks and are presented in Table. 31.

Table 31. Bookmarks dialog boxes Format cells

Formatting numbers

Data in Excel tables can be represented in various numeric formats, depending on which calculations are required (Fig. 92).

In field Numeric formatsa list of formats is presented. You can set the desired number of semicad signs and choose a method for representing negative numbers in the corresponding fields.

There is a difference between the fact that the cell "Really" contains, and a way to show its contents. The formula string shows unformatted content, while the cell shows a formatted version. Field Sample Shows the selected formatting.

When working with a new default table, a digital format is installed Common(General), which shows numbers without defined formatting (i.e. 145123; 0.37; 31, etc.).

Enter some characters will automatically install the desired formatting. For example; Input 40 r. Or 5% in the cell will automatically set a monetary or percentage format of the entered data, respectively.

Formatting can also be performed with the introduction of values \u200b\u200bto the work sheet.

Fig. 92. Number Bookmark

Aligning Data in Cell

To set new alignment values \u200b\u200bor change the default values, bookmark options can be used. Alignment (Alignment) in the dialog box format, cells (Format, Cells)(Fig. 93).

Changes will be applied to all selected cells, worksheet areas. By default, the alignment of the text entered into the cell is carried out on the left edge, and the numbers are aligned to the right.

Alignment options horizontally and vertically determine the position of the text in the cell. To change the direction of the text you need to turn the arrow with the word Inscriptionin the orientation field.

If the text in the cell consists of several words, for easy reading, ask opto to transfer according to words.

Fig. 93. Alignment tab

Centering text in columns

The contents of the cell can be aligned in columns in the selected area. This feature is used to center headers on the work sheet. In this case, text shouldlocated in the very left cell of the selected area.

Installation of fonts and their attributes

Bookmark options Font (Font) window dialog box format, cells (Format /Cells) are used to install fonts and their styles for selected cells.

Formatting tools using Fonts in Excel are similar to the Word tools described in the previous chapter.

Borders and type of cell or region

Frames can be used for cell or selected area. Frame options can be installed when using the Border tab (Border) (Fig. 94).

Fig. 94. Border bookmark

Some of the available styles of the frames are presented in the line type field.

1. Select cells for formatting.
2. Open the dialog panel Format, cells (Format, Cells)and select the Border tab (Border).
3. Select the sides for the cells in which the frame will be installed.
4. Select the type and color of the line.
5. Click OK.

The appearance of cells can be improved when filling them with color and / or pattern. Colors and patterns (including pattern color) can be installed when using bookmark View (Patterns)dialog box format, cells (Format, Cells).

The selected fill and pattern are shown in the field Sample.

Protection of cells

Protection is useful in tables containing complex formulas and predetermined constants. Excel uses a two-level protection system. In the work sheet, each default cell is blocked, but if the sheet protection is turned off, the data can be entered into all cells.

Bookmark Protection (Protection)contains the Protected Cell Options and Hide Formulas (Hidden).

To prevent the change in the sheet cells to save formulas or data, you should unlock cells for entering and install sheet protection.

1. Highlight and unlock all cells that need to be changed after leaf protection, removing the protected cell option (Locked).
2. Hide all formulas that should not be visible by installing the Hidden Formula (Hidden) option.
3. On the menu Serviceselect Protection (Tools, Options), and then Protect Sheet (Protect Sheet) command.

So that no one else is able to remove the protection from the sheet, enter the password, but note that when the password is lost, it will be impossible to access protected items.

Format cells as a table is a process that requires a lot of time, but bringing little benefit. A lot of effort can be saved using already ready-made table design styles.

Consider ready-made designer stylish solutions that are predefined in the Excel set of tools. You can also think about creating and maintaining your own styles. This program provides special tools and tools.

How to create and format as a table in Excel?

Format the source table from previous lessons using style design.

Note. Note, Excel itself recognized the sizes of the table, we did not need to highlight the range, it was enough to specify one any cell.



Although styles have a lot sometimes cannot be found suitable. To save time, you should choose the most similar style and format it to your needs.

To clear the format you need to pre-highlight the required range of cells and select the tool on the Home tab - "Editing" - "Clear Formats". To quickly delete only cell boundaries, use the CTRL + SHIFT + hot key combination (minus). Just do not forget to allocate the table itself before that.