When working with spreadsheets, the format of the 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 certain format. The format determines how the data is processed and how it will be presented in the cell: the font used, the size of the characters, the alignment method; for numbers - the form of representation (fixed or floating point), the 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 retained even if the cell is empty.
Cells that have not yet entered any information already have certain formatting options associated with them, such as aligning text to the left of the cell and numbers to the right, and so on. Excel recognizes data types when they are entered into a cell and formats data according to the settings. set by default.
With the help of special commands, you can change the format of both the entire table and a separate cell or block of cells.
To change the original cell formatting, 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 display 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, currency, etc.
On the tab alignment the parameters of the text arrangement in the cell are set: horizontally, vertically, orientation (text rotation, vertical arrangement of text in the cell).
On the tab Font character parameters for the selected text are set. If you need to format only part of the contents of the cell, this part can be selected using the mouse or the key Shift and arrow cursor.
To decorate the cells of a spreadsheet with lines and a frame, use the tab Border, as well as a toolbar button Borders.
Using the tab View, as well as buttons Fill color and Text color you can arrange the filling of the table cells with a background: choose a color and a pattern.
On the tab Protection you can hide the display of the formulas entered in the cell, as well as set cell protection - to prohibit changing the data placed in it. However, keep in mind that the cells for which these parameters are set can be blocked or hidden. only after protection settings for the entire Worksheet (this table) or the entire Book. This protection (setting access passwords and canceling them) is performed using the command SERVICE Protection.
Analyzing and processing spreadsheet data
Excel spreadsheet provides a wide range of tools for analyzing and summarizing data in a spreadsheet. Access to these tools is carried out, as a rule, using the menu commands of 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 a 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 that allow 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 of automatic summing up of general and intermediate results;
the ability to create and use the structure of the table;
means for consolidating (generalizing) data;
the ability to create and modify pivot tables, reports and charts;
means of conditional analysis (analysis "what if") (selection of a parameter, add-on Search for a solution, scripts, substitution tables);
various add-ons, wizards, templates, for example, Summarization Wizard, Substitution Wizard, Template Wizard, Link Update Wizard, Analysis Pack add-on, etc.
We restrict ourselves to considering only two methods for automatic generalization and analysis of data (item 2, item 3).
A spreadsheet, all rows of which contain homogeneous information, is treated as a list or database. Terms database and list are used interchangeably in Excel. In doing so, keep in mind the following:
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 easy to process and analyze data in a list. In order for a spreadsheet to be processed using such 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 row of the list;
line names should be in the left column of the list;
the table should not have empty rows and columns, including between the headers and the first row of data. The first empty line is considered to be the end of the list.
You can apply special functions related to the category to data organized as a list. Working with the database, and perform the following operations:
add, modify and delete records;
find records;
sort records;
filter (and select) data using Autofilter and advanced filter;
sum up general and intermediate results, etc.
Cell borders and grid lines are different elements of a table. 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 the 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 with the mouse).
You can use the toolbar to format cells Formatting
, but the most complete set of cell formatting commands is in the menu Format
.
2.1.1. Text alignment.
When text is entered into a cell, it is usually aligned to the left of the cell. Numeric values, on the contrary, are aligned to the right edge of the cell. To change the alignment of the contents of a cell, you need to select this cell and click the corresponding button on the toolbar Formatting . List of these buttons:
§ Left . Aligns text to the left of the cell. If the text is wider than the cell, it will go beyond its right border. If the cell on the right is not empty, then the text is truncated and will not be fully visible.
§ Centered . Centers the text in the cell. If the text is wider than the cell, it will go into adjacent left and right cells, 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 of the cell. If the text is wider than the cell, it goes to the cell on the left. If the cell on the left is not empty, the text is truncated and will not be fully visible.
§ Merge and center . Centers the text in the selected cells and also merges them into one cell.
2.1.2. Borders.
Another type of formatting is borders are lines drawn around all sides or just some sides of 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 with 12 icons. This toolbar can be dragged by the title bar to 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 -- color of text characters in these cells. How these tools and tools work 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, in it the characters "ttt" have a different width than "ooo". In a monospaced font, all printable characters, including spaces and punctuation marks, are the same width. The Courier and Courier New Cyr fonts are examples of such fonts. To make the default font other than Arial, you need to select the menu Service > Options, click tab General and select the desired standard font.
2.1.5. Formatting numeric data in cells.
You can use different formats for presenting numeric data within the same spreadsheet. The most common formats for representing numeric data.
Basic format is used by default, ensuring that numeric data is written to cells as it is entered or calculated.
Format with a fixed number of decimal places provides the representation of numbers in cells with a specified precision, determined by the user-defined number of decimal places after the decimal point (decimal point). For example, if the formatting mode is set to include two decimal places, then the entered number 12345 will be written as 12345.00, and the number 0.12345 will be written as.12.
Percent Format provides the representation of the entered data in percent format with a % sign (according to the set number of decimal places). For example, if the precision is set to one decimal place, then entering 0.123 will display 12.3%, and entering 123 will display 12300.0%.
Money format provides a representation of numbers, where every three digits are separated by a comma. In this case, a certain representation accuracy can be set by the user (with rounding to an integer or two decimal places). For example, the entered number 12345 will be written in a cell as 12.345 (rounded to an integer) and 12.345.00 (correct to two decimal places).
scientific format, used to represent very large or very small numbers, provides the input numbers as two components:
Mantissa, which has one decimal place to the left of the decimal point, and some (defined by the precision specified by the user) number of decimal places to the right of it;
Order of numbers.
Apply conditional formatting.
Sometimes you may need to reformat cells based on their content. For example, a negative number contained in a cell should be displayed in bold on a red background. To do this, Excel has a special type of formatting: conditional formatting . To apply conditional formatting to a cell or range of cells, 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 the selected cells. Conditions can be defined by a value in a cell or by a user-specified formula. From the drop-down list, select one of the options: Meaning or Formula . If you select the option Meaning , then you need to set a condition on a 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 set a link to the formula. Then you need to click on the button Format and specify what formatting should be applied if the condition is true.
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 (=). This formula can only be calculated on the active sheet.
You can also use a formula to evaluate criteria that are not based on sheet data. For example, the formula =WEEKDAY("5/12/99")=1 takes the value True if May 12, 1999 is a Sunday. The value of the cell does not affect the truth of the condition in any way only if there are no special references to the selected cells to be formatted in the formula. If the formula refers to selected cells, then you must enter cell references into it.
If you want to specify a different conditional formatting for this range, you must click on the button As well as . When everything is done, click on the OK button.
You can change the settings so that the number in the cell is displayed in green if it is positive, and in red if it is negative. To do this, you need to set the cell format as optional and write:
[Green] # , ##0_) ; [Red] # , ##0
Conditional formatting works like an alarm clock. Onocontrol values.
Conditional formatting in Excel is the ability to set your own style when a certain condition is met. This feature is required for visual data analysis. Highlighting table cells will help you quickly analyze information.
Excel file format
When there are a lot of rows and columns in an Excel spreadsheet, it's hard to see all the information in the cells at a glance. In this case, there is a danger of missing important data and analyzing it incorrectly. If the table is automatically colored in different colors (conditional formatting), then this will help to see the full picture without peering into each cell.
Let's analyze how to set the cell format in Excel using the example of comparing two columns - the number of products planned for release and actually released.
We mark in red those lines where the plan of 2000 units was not fulfilled. To do this, you must specify a criterion for setting the style. Let's click on cell C2 - it is the first one and stores information about the released products. Conditional formatting is performed using the icon of the same name on the toolbar.
After clicking on it, we will see a menu in which you can implement conditional formatting. Use the "Less" option. 
When selected, a window will open where you can set the value and select colors. 
Let's enter the boundary parameter 2000 and choose the red color. Click "OK". The cell will become colored because the value in it is less than the specified one. 
To perform an action for the entire column at once, select it. 
So we will immediately see the months in which the minimum plan was not fulfilled.
Conditional cell formatting in Excel based on values in another range
It is often necessary to make a style dependent not on a specific value, but on data from another cell. In this case, we specify the address, not the number. 
Note that the box contains the absolute address of the cell ($E$3). In this case, this is valid because the comparison is specified for a fixed cell.
After clicking "OK", we will see a highlight in those cells where the value is less than the specified one. 
If you enter a new value in cell E3, then the design in the Excel table will change. 
To compare with a range, you need to slightly change the condition. Select the column with the actually released products and click the conditional formatting button again. If you select the "Less than" option in the window, specify the first cell in the "Planned" column. This can be done with the mouse. 
We again see absolute addressing, which is set by default. We need to remove the "$" sign if we want to get a conditional formatting offset when setting styles. 
In this case, the style will be calculated based on the comparison of two paired cells. 
Instead of numbers, you can use any values: string, date, etc. For example, let's select all the cars at the rental point that are in reserve. The value “p” means that the car is free, and “c” means that it has been handed over. 
Set the green color for free cars. We need the "Equals" condition. 
Let's use "r" as the value. 
Now we can easily see which car is available. 
To check the rule, you need to use the "Manage Rules" item in the conditional formatting menu. 
When selected, a window with a list of rules will open. 
You can adjust the Excel conditional formatting rule or set a new one.
Conditional formatting rules in Excel with multiple conditions
Often one condition is not enough. It may be necessary to select cells depending on two, three or more values. In such a case, you can apply conditional formatting rules sequentially to the same range. For example, in the last example, let's denote busy cars in red.
Select the range again and select the "Equals" rule. Specify the value "c" and set the color to red. 
In this case, the old conditional formatting rule will remain and will be in effect. You can see it in the controls. 
Another way is to use the Create Rule option. Select the range and select it from the conditional formatting menu. 
Then set a rule for cells that contain a specific value. 
Next, select colors for decoration. To do this, click the "Format" button. 
Create another rule. 
Date format in Excel
In cases where you need to highlight a specific date, Excel can use conditional formatting of temporary values. To do this, select "Date" in the "Cell Formatting Rules" section. 
In the drop-down menu, you can see all the possible options. 
It is noteworthy that when a document is opened, the date values are compared with the current one, and if any rule has changed (for example, it has ceased to be “Yesterday”), then the style for it changes. This is very convenient when re-working with a document.
For example, selecting the condition "In the current month" for November, we will get red highlighting only those rows that fall under this condition. 
Excel spreadsheet format using formulas
You can also make the style dependent on formulas and functions. Such conditional formatting may be needed if you are counting balances, or amounts, etc.
For our first example, we select those cells in which the value of output is greater than the average for the year.
Select the range and set a new rule using the formula. 
The AVERAGE function calculates the average value for the specified range.
This way you can set the style for any cell.
Setting the style for an entire line
To apply the appearance to the entire table, select it as the range. For example, for our cars, we will set the following rule: 
And apply it to the entire range. 
This will colorize the entire table. 
Notice the first dollar in the formula. It means that the column is fixed, and only the row number will change. It must be set, otherwise the style will not be applied to the entire line.
Number format in Excel
In the end, we will tell you how to quickly set the number format in Excel. Sometimes your number suddenly turns into text or a date. If this happens, convert the value to the correct format.
Position the pointer over the number cell and click the square in the lower right corner of the Number panel on the Home tab. 
In the window, select the desired value and set the number of decimal places. You can also choose how a negative number is displayed. 
The "Digit group separator" checkbox will allow you to set a space between the digits of the number. 
You can also set the currency or financial format. 
Use conditional formatting to make your tables more visual. This will speed up the analysis and processing of data.
Have a great day!
The Cells command of the Format menu controls the display of numeric and alphabetic characters on the screen. However, you should always remember that there are certain differences between stored and displayed values in cells. For example, if you enter a number with six significant digits after the decimal point in a cell configured to display only two significant digits, you will not see the last four digits as a result. However, the value stored in the cell will be correct, and Excel will use the number with the precision you specified when calculating.
ADVICE
When copying a cell, its format is taken into account. By pasting data into another cell, you transfer the formatting of the original cell to it. Thus, to save time, it makes sense to apply the commands Copy (Copy), Paste (Paste) and Fill (Fill) to data that already has a finished look. For more information on copying and pasting, see Chapter 7, Editing Sheets.
Formatting individual characters
If you select a cell and apply a format to it, the changes will affect the entire contents of the cell. But if necessary, you can also format individual characters of the text written in the cell (not numbers or formulas). To do this, it is enough to select the necessary characters or words and set the necessary attributes for them. To see the result of your actions, press the Enter key. An example of such formatting is shown in Fig. 8.6.
SEE ALSO–
For more examples of formatting individual characters, see the "Changing the font" section of this chapter.
Rice. 8.6. If desired, you can format individual characters in a cell in different ways.
NOTE
If you try to apply formatting to individual characters (digits) of a numeric value, after pressing the Enter key, the assigned attributes will be ignored, since they will be overridden by the format applied to the number as a whole.
Format numbers as you type
Adding special characters to numeric values, such as the dollar sign, percent sign, comma, or slash (forward slash), lets you format numbers as you type. Upon seeing such a symbol representing some recognizable format, the program immediately applies it to the cell. The correspondence between the most commonly used special characters and number formats is described below:
If you enter $45.00 into a cell, Excel interprets the entered value as 45 in currency format with two decimal places. In this case, the stored value - the number 45 - will be displayed in the formula bar, and the formatted value ($45.00) will be displayed in the cell.
When entering the character sequence 1 3/8 (with one space between 1 and
3) the value 1 3/8 will appear in the cell, and the number 1.375 will be displayed in the formula bar. However, if you enter just 3/8, you will see the date in the cell as August 3rd, because the date format takes precedence over the fractional format. To see in a cell
fraction (3/8) and a number (0.375) in the formula bar, you must enter the string 0 3/8, with a space between the digits 0 and 3. For more information about entering dates and date/time formats, see Entering Dates and Times » chapter 15.
If you enter a value of 23% in the cell, then in the cell itself, as well as in the formula bar, the number will be displayed in this form, but in Excel calculations it will use the decimal value - 0.23.
When you enter the number 123456 in a cell, the number format with a thousands separator without decimal places is automatically applied to it. If you type 123,456.00, the value will be assigned a number format with a separator and two decimal digits after the decimal point.
Creating the Super Formatting Toolbar
The Formatting toolbar contains many useful tools, but there are also a number of other buttons that are not displayed on the screen. Each of us has our favorite formatting methods, and if there is a way to make them easier to implement, we should use it. For example, we created the Super Formatting toolbar for ourselves, shown in the figure below, on which we placed several useful buttons, including those missing on the built-in toolbars.
You can repeat our steps as follows:
1. On the View menu, select Toolbars and then Customize.
2. In the Customize dialog box, go to the Toolbars tab and click the New button. Enter a Super Formatting name in the Toolbar Name field and click OK. A small empty toolbar will appear on the screen next to the Customize dialog box.
3. Go to the tab Commands (Commands), in the list Categories (Categories) select the item Format (Format) and drag the following buttons in turn to the new panel: Style (Style Box), Merge cells (Merge Cells), Unmerge cells (Unmerge Cells), Increase Font Size, Decrease Font Size, Vertical Text, Rotate Text Up, Rotate Text Down, Clockwise Text (Angle Clockwise), Angle Counterclockwise Text, Cycle Font Color, Dark Shading, Light Shading and Auto Format;
4. When you are done completing the toolbar, click the Close button
Until the Customize dialog box is closed, you can swap the buttons listed above, remove or add new ones. For more information on custom toolbars, see Chapter 3, Customizing the Excel Workspace.
General format
The General format is the first category in the Categories list on the Number tab of the Format Cells dialog box. Until you explicitly change the cell format, all entered text and numeric values will be displayed by Excel in General1 format. With the exception of the three cases described below, in cells that have been assigned
General format, showing exactly what was entered in them. For example, if you enter the number 123.45, 123.45 will appear in the and cell. Here are the exceptions to this rule:
1. Long numeric entries are displayed in exponential format or rounded. For example, in a cell with a standard width in the format General, the integer 12345678901234 would look like 1.234567E+13. If you enter 123456.7812345 in the same cell and apply the General format to it, the rounded number 123456.8 will appear in the cell. In this case, all calculations will be performed with the initially entered value stored in the cell, regardless of how the number looks on the screen.
2. The General format does not display trailing zeros. For example, the number 123.0
output as 123.
3. Before a decimal fraction typed without a leading zero, it is added automatically
tomato. That is, for the entered value, 123, the cell will display the number 0.123.
Numeric formats
The options in the Number category (Number formats list on the Number tab of the Format Cells dialog box) allow you to display numbers as integers, fixed-point, and thousands separators, as shown in Figure 1-3. 8.7. Unlike the general format, here you can control the number of decimal places displayed, add thousands separators, and highlight negative numbers. This format can be used for any numbers that do not fall into any other category.
The following rules apply when working with the Numeric format:
Select the number of decimal places displayed in the cell (from 0 to 30) by entering the desired number from the keyboard or by scrolling the value in the Decimal Places field;
When the Use 1000 Separator checkbox is checked, the specified digit separators are inserted between all triads (hundreds and thousands, thousands and millions, etc.);
In the Negative Numbers list, you can choose one of the options for displaying negative numbers: with a minus sign, in red, in brackets, or in brackets in red.
1 For the Russian version of Excel, dashes are not added. - Note. transl.
2 When entering data in any of the additional formats, do not forget to uncheck the Fixed decimal format when entering checkbox on the Edit tab of the Options window, because otherwise the program will necessarily attribute the entered value to numeric and separate some of the last digits of the number or index with a decimal point. - Note. ed.
one). 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). the size
4). Meaning
What value can a cell take
one). numerical
2). textual
3). returned
4). all of the above
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
The Excel program is used for.
one). 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 what
13. On the basis of what is any diagram built?
one). Excel Books
2). graphic file
3). text file
4). table data
14. In which variant is the sequence of execution of operators in the formula correctly indicated?
one). + and - then * and /
2). comparison operators then reference operators
3). reference operators then comparison operators
4). * and then %
The minimum component of a table is...
one). cell
2). formula
4). no right answer
16. What is the SUM function used for?
one). To get the sum of the squares of the given numbers
2). to get the sum of the specified numbers
3). to get the difference of sums of numbers
4). to get the square of the given numbers
How many types of cell addressing are there in Excel
4). four
18. What does Excel do if a formula contains an error?
one). returns 0 as cell value
2). prints error type message as cell value
3). fixes an error in the formula
4). removes the formula with an error
19. What is the "Form..." command window used for?
one). to populate 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
Ordering the values of a range of cells in a particular sequence is called...
one). formatting
2). filtration
3). grouping
4). Sorting
Cell addressing in spreadsheets that maintains a reference to a specific cell or area is called....
one). specific
2). relative
3). absolute
4). Combined
4). True
26. The range of cells A1:D3 of MS EXCEL spreadsheet is selected. The range contains....
one). 6 cells
2). 9 cells
3). 2 cells
4). 12 cells
Criteria range is used in MS Excel when...
one). applying an advanced filter
2). applying an autofilter
3). data consolidation
4). building pivot tables
