What formatting is applicable to cells. What formatting is applicable to cells in Excel

1). framing and filling

2). text alignment and font format

3). data type, width and height

4). all options are correct

6. Which operator is not included in the group of arithmetic operators?

7. Which of the following is not a characteristic of a cell?

3). size

4). Meaning

What value can a cell take?

1). numeric

2). text

3). returned

4). all listed

9. What can be a function argument?

2). constant

3). function

4). all options are correct

Specifying a cell address in a formula is called...

2). function

3). operator

4). cell name

Excel is used for.

1). creating text documents

2). creating spreadsheets

3). creating graphic images

4). all options are correct

What character does a formula start with in Excel?

3). space

4). no matter from which one

13. What is any diagram based on?

1). Excel Workbooks

2). graphic file

3). text file

4). table data

14. Which option correctly indicates the sequence of execution of operators in the formula?

1). + and - then * and /

2). comparison operators then reference operators

3). reference operators then comparison operators

4). * and then %

The minimum component of the table is...

1). cell

2). formula

4). there is no right answer

16. What is the SUM function used for?

1). To obtain the sum of squares of the specified numbers

2). to get the sum of the specified numbers

3). to obtain the difference between the sums of numbers

4). to obtain the square of the specified numbers

How many types of cell addressing are there in Excel?

4). four

18. What does Excel do if there is an error in the formula?

1). returns 0 as cell value

2). displays error type message as cell value

3). corrects an error in the formula

4). deletes a formula with an error

19. What is the "Form..." command window used for?

1). to fill table entries

2). to format the table

3). to check spelling on a sheet

4). to filter table records by condition

Which link is absolute?

Arranging the values ​​of a range of cells in a specific sequence is called...

1). formatting

2). filtration

3). grouping

4). Sorting

Cell addressing in spreadsheets that maintains a reference to a specific cell or area is called....

1). specific

2). relative

3). absolute

4). Combined

4). True

26. The range of cells A1:D3 of the MS EXCEL spreadsheet is selected. Range contains...

1). 6 cells

2). 9 cells

3). 2 cells

4). 12 cells

The range of criteria is used in MS Excel when...

1). using an advanced filter

2). applying an autofilter

3). data consolidation

4). building pivot tables

When working with spreadsheets, the format of a table cell is of particular importance, since each cell is associated not only with the information that is entered into it, but also with a specific format. The format determines the way data is processed and the form in which it will be presented in the cell: the font used, the size of the characters, the alignment method; for numbers – representation form (fixed or floating point), number of decimal places, etc.

The cell format, unlike the value or formula, is not displayed on the screen. The cell format is set separately and is saved even if the cell is empty.

Cells in which no information has yet been entered already have certain format parameters associated with them, for example, aligning text to the left edge of the cell, numbers to the right, etc. Excel recognizes data types when they are entered into a cell and formats the data in accordance with the parameters , set by default.

Using special commands, you can change the format of both the entire table and an individual cell or block of cells.

To change the initial formatting of cells you can use:

toolbar buttons Standard And Formatting;

window menu command FORMAT Cells;

context menu command Cell format.

Window menu commands FORMAT Cells and context menu command Cell Format displays a dialog box Cell Format. Dialog window Cell Format contains six tabs with which you can define all the parameters of a cell or a selected range of cells: number, alignment, font, border, appearance, protection.

On the tab Number You can select the desired format for the data entered in the active cell or range of cells - general, numeric, monetary, etc.

On the tab Alignment parameters for placing text in a cell are set: horizontally, vertically, orientation (text rotation, vertical position of text in a cell).

On the tab Font sets character parameters for the selected text. If you only need to format part of a cell's contents, you can select that part using the mouse or key Shift and cursor arrow.

Use the tab to decorate spreadsheet cells with lines and borders. Border, as well as a toolbar button Borders.

Using the tab View, as well as buttons Fill color And Text color You can customize the filling of table cells with the background: choose a color and pattern.

On the tab Protection You can hide the display of formulas entered into a cell, and you can also set cell protection—prohibit changing the data placed in it. Please note that cells for which these parameters are set may be locked or hidden only after setting the protection of the entire Worksheet (this table) or the entire Book. This protection (setting access passwords and canceling them) is performed using the command SERVICE Protection.

Analysis and processing of spreadsheet data

In the Excel spreadsheet processor, there is a wide range of tools for analyzing and summarizing data in a spreadsheet. These tools are usually accessed using menu commands in the Excel window. Service And Data.

To perform many table processing operations using menu commands Data It is enough to place the cursor in any cell of the table list. To work with part of the list, you need to select the required area.

Excel provides a variety of ways to automatically summarize and analyze data:

automatic calculations;

tools for working with databases and lists, allowing you to create, add, delete and find records in databases, sort and filter (select) records, work with databases in form mode and using special functions;

means for automatically summing up general and subtotals;

the ability to create and use table structures;

tools for consolidating (summarizing) data;

the ability to create and modify pivot tables, reports and charts;

conditional analysis tools (what-if analysis) (parameter selection, solution search add-in, scenarios, lookup tables);

various add-ons, wizards, templates, for example, the Wizard of summation, substitutions, templates, the Wizard of updating links, the Analysis Package add-on, etc.

We will limit ourselves to considering only two methods for automatic generalization and analysis of data (clause 2, clause 3).

A spreadsheet in which all rows contain the same information is considered a list or database. Terms database And list in Excel they are used as synonyms. In doing so, you need to keep the following in mind:

each line of the list is treated as record Database;

list columns are considered fields Database;

column headings are considered field names Database.

Excel has a set of functions that make it easier to process and analyze the data in a list. In order for a spreadsheet to be processed using these functions, the data in it must be organized as follows:

all rows of the table must contain homogeneous information - all rows in the same columns must contain the same type of data;

column headings must be in the first line of the list;

the row names must be in the left column of the list;

there should be no empty rows or columns in the table, including between the headers and the first row of data. The first empty line is considered the end of the list.

You can apply category-specific functions to data organized as a list. Working with the database, and perform the following operations:

add, change and delete entries;

find records;

sort records;

filter (and select) data using Autofilter And Advanced filter;

summarize general and intermediate results, etc.

Conditional formatting allows you to apply colors to cells under certain conditions, such as having duplicate values ​​or values ​​that meet certain criteria (for example, greater than 100 or equal to a specified income). To do this, the rules for selecting cells and the rules for selecting the first and last values ​​are applied. You can also rank individual cells by range of values ​​using histograms, color scales, and icon sets. Conditional formatting is dynamic and automatically adjusts as values ​​change.

Note: Before Excel 2007, conditional formatting had to be created using a formula, and only three levels were supported. All versions since Excel 2007 have many ready-made rules that are easy to apply and support up to 64 levels. If none of the pre-made formats suit your situation, you can use a formula to apply conditional formatting.

In this article, we'll demonstrate several ways to use ready-made conditional formatting rules. There are thousands of possible combinations, so feel free to experiment to find the best option. But keep your audience in mind, so that an overly complex presentation does not hinder your understanding.

Highlight duplicate values ​​with color

To highlight duplicate elements in a data column,

The o range will be formatted and repeating elements will be highlighted in the selected color.

Sorting by color

Follow the steps below.

Click anywhere in the data range and then on the tab Datain GroupSorting and Filterclick the buttonSorting.

In the dropdown list Sort by select column Full name of the employee, in the drop-down list Sorting- value "Cell Color", in the drop-down list Order- color, and in the last drop-down list - value Above. Click the button OK to apply the specified formatting.

In a sorted table, duplicate values ​​will be grouped at the top.

Selecting the first 10 elements in a range

Rule First 10 elements allows you to quickly identify the leaders in the range, for example, the top 10 clients on the list based on metrics. Likewise, you can select a rule Last 10 items, First/Last 10% or Above/Below Average. Once you select a rule, you can change the number 10 to a different value.

To identify the top 10 earners, follow the steps below.

Displaying variance using histograms

If you are applying conditional formatting using histograms Excel displays a bar in each cell whose length corresponds to the cell's value relative to other cells in the selected range.

To repeat the instructions in the example, follow these steps:

Highlight positive, neutral and negative values ​​using icon sets

Using a set of icons, you can visually mark values ​​that fall within certain number or percentage ranges. In this section, we indicate whether regional performance meets standards using positive, neutral, and negative icons.

To find out how to do this, follow the steps below.

Displaying dispersion using color scales

Using the parameter Color scales you can highlight values ​​to show a range or compare highs and lows (in this case, January to June).

Follow the steps below.

Cell borders and grid lines represent different table elements. Gridlines are defined for the entire worksheet. Borders apply to individual cells or a range of cells. Of course, a border is not the only way to format a cell. You can also change text alignment, background pattern, font, number representation, and cell security attributes. There are also conditional formatting and pop-up comments (appear when you hover over a cell).
You can use the toolbar to format cells Formatting , but the most comprehensive set of cell formatting commands is in the menu Format .

2.1.1. Text alignment.

When you enter text into a cell, it is usually aligned to the left edge of the cell. Numeric values, on the other hand, are aligned to the right edge of the cell. To change how a cell's contents are aligned, select the cell and click the corresponding button on the toolbar Formatting . List of these buttons:

§ Left . Aligns text to the left edge of the cell. If the text is wider than the cell it will extend beyond its right border. If the cell on the right is not empty, then the text is truncated and will not be completely visible.

§ Centered . Centers text in a cell. If the text is wider than the cell, it will extend into the adjacent cells to the left and right, provided they are empty. If adjacent cells are not empty, the text is truncated and will not be fully visible.

§ Right . Aligns text to the right edge of the cell. If the text is wider than the cell, it flows into the cell to the left. If the cell on the left is not empty, the text is truncated and will not be fully visible.

§ Combine and place in the center . Centers text in selected cells and also merges them into one cell.

2.1.2. Boundaries.

Another type of formatting is borders -- these are lines drawn around all or just some sides of the selected cells. Button Borders located on the toolbar Formatting . If you click on the arrow next to it, a palette will appear, which is a miniature toolbar and contains 12 icons. You can drag this toolbar by its title bar anywhere in the workbook window. To add a border to a selected cell or group of cells, just click on the icon corresponding to the type of border. The icon located in the upper left corner of the palette allows you to remove all frames around the selected cells.

2.1.3. Colors.

Tool Fill color allows you to change the background color of selected cells, and the tool Font color -- the color of the text characters in these cells. The working principles of these tools and tools Borders are similar, and their palettes can also be dragged to another location on the screen.

2.1.4. Fonts and text formatting.

The default font in Excel is Arial. This is not a monospaced font; the “ttt” characters have a different width than “ooo”. In a monospaced font, all printed characters, including spaces and punctuation, are the same width. Courier and Courier New Cyr fonts are examples of such fonts. To set a font other than Arial as the default, select the menu Service > Options, click on tab Are common and select the desired standard font.

2.1.5. Formatting numeric data in cells.

You can use different formats for representing numeric data within the same spreadsheet. The most common formats for representing numerical data.

Basic format is the default, ensuring that numeric data is recorded in cells exactly as it is entered or calculated.

Format with a fixed number of decimal places provides the representation of numbers in cells with a given precision, determined by the number of decimal places after the decimal point (decimal point) set by the user. For example, if the formatting mode is set to two decimal places, then the number you enter 12345 will be written as 12345.00, and the number 0.12345 will be written as .12.

Percentage format ensures that the entered data is presented in percent format with a % sign (according to the specified number of decimal places). For example, if the precision is set to one decimal place, then 12.3% will appear on the screen if you enter 0.123, and 12300.0% will appear if you enter 123.

Currency format provides a representation of numbers where every three digits are separated by a comma. In this case, the user can set a certain precision of presentation (rounded to a whole number or two decimal places). For example, the entered number 12345 will be written in the cell as 12.345 (rounded to the nearest whole number) and 12.345.00 (to two decimal places).

Scientific format, used to represent very large or very small numbers, provides a representation of the input numbers as two components:

A mantissa having one decimal place to the left of the decimal point, and a certain (defined by user-specified precision) number of decimal places to the right of it;

Order of numbers.

Applying conditional formatting.

Sometimes you may need to reformat cells based on their content. For example, a negative number contained in a cell should be shown in bold on a red background. There is a special type of formatting for this in Excel: conditional formatting . To apply conditional formatting to a cell or range of cells, you must select the range and then select the command Format > Conditional Formatting . This will open a dialog box Conditional Formatting . This dialog box allows you to define three conditions for selected cells. The conditions can be determined by a value in a cell or a user-specified formula. From the drop-down list you need to select one of the options: Meaning or Formula . If you select the option Meaning , then you need to set a condition on the numeric value. For example, you can specify a range from 0 to 100. You can enter values ​​or cell references. If you select the option Formula , then you need to specify a link to the formula. Then you need to click on the button Format and indicate what formatting should be applied if the condition is met.

Using a formula as a condition. If the option was selected Formula , then the specified formula should take the values True (1) or Lie (0). The formula must begin with an equal sign (=). Calculations using this formula can only be performed on the active sheet.

You can also use the formula to evaluate criteria that are not based on worksheet data. For example, the formula =WEEKDAY("12.5.99")=1 takes the value True , if the date May 12, 1999 is a Sunday. The value of the cell does not affect the truth of the condition in any way only if the formula does not contain special references to the selected cells to be formatted. If the formula refers to selected cells, then you must enter cell references into it.

If you need to set different conditional formatting for this range, you need to click the button And . When everything is done, click on the OK button.

You can change the settings so that a number in a cell appears green if it is positive and red if it is negative. To do this, you need to set the cell format as additional and write:

[Green] # , ##0_) ; [Red] # , ##0

Conditional formatting acts like an alarm clock. Onocontrol values.

1). framing and filling

2). text alignment and font format

3). data type, width and height

4). all options are correct

6. Which operator is not included in the group of arithmetic operators?

7. Which of the following is not a characteristic of a cell?

3). size

4). Meaning

What value can a cell take?

1). numeric

2). text

3). returned

4). all listed

9. What can be a function argument?

2). constant

3). function

4). all options are correct

Specifying a cell address in a formula is called...

2). function

3). operator

4). cell name

Excel is used for.

1). creating text documents

2). creating spreadsheets

3). creating graphic images

4). all options are correct

What character does a formula start with in Excel?

3). space

4). no matter from which one

13. What is any diagram based on?

1). Excel Workbooks

2). graphic file

3). text file

4). table data

14. Which option correctly indicates the sequence of execution of operators in the formula?

1). + and - then * and /

2). comparison operators then reference operators

3). reference operators then comparison operators

4). * and then %

The minimum component of the table is...

1). cell

2). formula

4). there is no right answer

16. What is the SUM function used for?

1). To obtain the sum of squares of the specified numbers

2). to get the sum of the specified numbers

3). to obtain the difference between the sums of numbers

4). to obtain the square of the specified numbers

How many types of cell addressing are there in Excel?

4). four

18. What does Excel do if there is an error in the formula?

1). returns 0 as cell value

2). displays error type message as cell value

3). corrects an error in the formula

4). deletes a formula with an error

19. What is the "Form..." command window used for?

1). to fill table entries

2). to format the table

3). to check spelling on a sheet

4). to filter table records by condition

Which link is absolute?

Arranging the values ​​of a range of cells in a specific sequence is called...

1). formatting

2). filtration

3). grouping

4). Sorting

Cell addressing in spreadsheets that maintains a reference to a specific cell or area is called....

1). specific

2). relative

3). absolute

4). Combined

4). True

26. The range of cells A1:D3 of the MS EXCEL spreadsheet is selected. Range contains...

1). 6 cells

2). 9 cells

3). 2 cells

4). 12 cells

The range of criteria is used in MS Excel when...

1). using an advanced filter

2). applying an autofilter

3). data consolidation

4). building pivot tables

 

It might be useful to read:

• Walkthrough of the game - Tricky test;
• The most terrible horrors. Creepy stories. The most terrible horrors Camp horror story coffin on wheels;
• Watches for children: types, their features;
• Cannot save the document form (invoice) in Excel format - the "Save" and "Save As" buttons are not available;
• How to resize cells in Excel and make them the same How to change the width of all columns in Excel;
• Basic settings of the AutoCAD workspace How to create a custom coordinate system in AutoCAD;
• Translation of numbers in positional number systems;
• Voltage resonance in a series oscillatory circuit;