Apr 21 2012

Basics of working in Excel. Data entry and editing.

1. MS Excel window structure. Working with books: opening, creating, saving. Working with sheets (adding, copying, moving, deleting)
2. Selecting table elements and moving around the sheet. Entering and editing data in cells.
3. Copying and moving table elements. Inserting and deleting table elements. Merging cells

The Excel 2010 interface is further development user interface, which was first used in the release Microsoft systems Office 2007. When creating the interface, the developers were given the task: to make the main applications Microsoft Office more convenient to use. The result is the Microsoft Office Fluent user interface, which makes it easier for users to work with Microsoft Office applications and get better results, faster.

Previous releases of Microsoft Office applications provided users with a system of menus, toolbars, dialog boxes. This interface worked well as long as the applications had limited number commands Now that programs perform much more features, it becomes difficult for users to work with old system: Too many software options and hard to find.

Office has redesigned its user interface to make it easier to find and use the full range of features these applications provide. In addition, it was intended to prevent clutter in the work area so that users would be less distracted by searching and could spend more time and energy on their work.

Main UI element Microsoft Excel 2010 introduces a ribbon that runs along the top of each application window, replacing traditional menus and toolbars (Figure 1.1).

The main difference from Word is the presence, instead of a document window, of a so-called book window, in other words, a spreadsheet.

Excel workbook divided into several sheets (tables). Sheets can be deleted or new ones added. Like any table, an Excel sheet consists of rows and columns, the intersections of which form cells.

At the bottom of the workbook window there are several buttons that you can use to move from one sheet to another. If not all sheet tabs are visible, you can use the four buttons located in the lower left corner of the window to view the contents of the workbook.

Excel Cells are the basic building units of a worksheet. Each cell has its own strictly defined coordinates, or cell address, where information can be stored and displayed. The cell located at the intersection of column A and row 1 has the address A1.

Often the boundaries of the table data extend beyond the monitor screen. In this case, to view the contents of the worksheet, you must use the scroll bars located along the right and bottom sides of the workbook window.

Below the toolbar is a formula bar.

Contents of the active (highlighted in this moment) Excel cells always appear in the formula bar. As you enter or edit data in a cell, three buttons appear in the formula bar:

• cancel button (red cross);
• enter button (green checkmark);
• formula change button (function sign).

Information can be entered either directly into a cell or into the formula bar.

At the very bottom of the workspace window there is a status bar showing the operating mode of the program.

Selecting table elements, moving around the sheet

Selecting with the mouse

The simplest and most common way to select a single cell is to left-click. Move the cursor to the desired Excel cell and click left button mice. In this case, a frame will appear around the cell, which indicates that this cell is active, i.e. You can enter or edit data in it.

To speed up operations, it is often necessary to select a group of cells or a range. To select a range, you need to place the cursor on the upper left cell of the selected range, press the left mouse button and, without releasing the button, “drag” the mouse to the lower right cell. When you reach the desired cell, you must release the mouse button. In this case, the selected range will be highlighted in a different color.

To select a large range of Excel cells, you can use the selection enhancement technique. To do this, select the top left cell of the range, then hold down the Shift key and click on the bottom right cell; if the cell is not visible, you can use the scroll bars. If you know the coordinates of the cells, you can do the following: select the top left cell, then in the name field in the formula bar, enter the coordinates of the bottom right cell separated by a colon and click Enter key.

To reduce/enlarge the image scale, you can use the “Scale” command in the “View” menu.

If you need to select a group of cell ranges, then for this purpose you need to use the Ctrl key.

If you need to select an entire column or row, you need to click on the row or column header.

Working with a block of cells

Block is a range of Excel cells surrounded on all sides empty cells or column and row headings.

Active area is a rectangle that encloses all the columns and rows of the worksheet that contain non-empty cells.

The figure shows three blocks of cells: A3:E8, A10:E10, G3:G8. The active area is A3:G10. If you place the mouse pointer on the bottom border of the active cell and double-click the left mouse button, the bottom cell of the block will be selected. If the lowest cell of the block is active, then double click at the bottom border of a cell will highlight the cell below the block. Cells are similarly selected when you click on the right, left, or top border of the active cell. If you double-click on the border of a cell while pressing Shift key, then all cells will be selected, starting from the current one to the edge of the block.

You can move through adjacent cells using the cursor keys. To move to the edge of a block of cells, use the arrow keys while holding down the Ctrl key.

It is convenient to use to move around an Excel sheet and select cells. Home keys,End

Home Move to the first cell of the current row Ctrl+Home Move to cell A1 Ctrl+End Move to the last cell of the last column of the active area End Enable/Disable End Scroll Lock+Home Move to the first cell of the current window Scroll Lock+End Move to the last cell of the current window

For fast travel and selecting a cell or range, you can use the command “Edit” - “Go” (F5)

Entering data into Excel cells

Numeric values

Numeric values ​​can contain numbers from 0 before 9 , as well as special characters: + — E e () . , $% /

To enter a numeric value into a cell, you need to select the desired cell and enter the required combination of numbers from the keyboard. The numbers you enter appear both in the cell and in the formula bar. When you have finished entering, you must press the Enter key. After this, the number will be written into the cell. By default, after pressing Enter, the cell located one line below becomes active, but using the “Tools” - “Options” command, on the “Edit” tab, you can set the required direction of transition to the next cell after entering, or eliminate the transition altogether. If, after entering a number, you press any of the cell navigation keys (Tab, Shift+Tab...), the number will be fixed in the cell, and the input focus will move to the adjacent cell.

Let's look at the input features numerical values using special characters.

1. If you need to enter a negative number, then you must put a “-” (minus) sign in front of the number.
2. The symbol E or e is used to represent a number in scientific notation. For example, 5e3 means 5*1000, i.e. 5000.
3. Numbers enclosed in parentheses are interpreted as negative, even if there is no minus sign before the number. Those. (40) - for Excel means -40.
4. When entering large numbers, you can enter a space (23,456.00) between groups of digits for ease of presentation. In this case, the space will not be displayed in the formula bar, but in the cell itself the number will have a space.
5. To enter the currency format, use the dollar sign ($).
6. To enter percentage format, use the percent sign (%).
7. To enter dates and fractions, use the forward slash (/). If Excel can interpret the value as a date, such as 1/01, then the date in the cell will be January 1. If you need to imagine similar number as a fraction, you must enter a zero before the fraction - 0 1/01. A fraction will also represent a number that cannot be interpreted as a date, such as 88/32.

Sometimes you need to enter long numbers. Moreover, to display it in the formula bar, exponential notation is used with no more than 15 significant figures. The precision of the value is chosen so that the number can be displayed in the cell.

In this case, the value in the cell is called entered or displayed meaning.

The value in the formula bar is called stored meaning.

The number of digits you enter depends on the column width. If the width is not sufficient, Excel either rounds the value or displays ### characters. In this case, you can try increasing the cell size.

Text values

Entering text is completely similar to entering numeric values. You can enter almost any characters. If the length of the text exceeds the width of the cell, then the text overlaps the adjacent cell, although it is actually in the same cell. If there is also text in an adjacent cell, then it overlaps the text in the adjacent cell.

To customize the cell width long text, you need to click on the border of the column in its header. So if you click on the line between the headings of columns A and B, the cell width will be automatically adjusted to the longest value in that column.

If you need to enter a number as text value, then you must put an apostrophe in front of the number, or enclose the number in quotation marks - ‘123 “123”.

You can tell which value (numeric or text) is entered into a cell by its alignment. By default, text is left aligned while numbers are right aligned.

When entering values ​​into a range of cells, input will occur from left to right and top to bottom. Those. When entering values ​​and completing the entry by pressing Enter, the cursor will move to the adjacent cell located on the right, and when it reaches the end of the block of cells in the line, it will move to the line below in the leftmost cell.

Changing values ​​in a cell

To change values ​​in a cell before committing the input, you must use it, as in any text editor, Del and Backspace keys. If you need to change an already fixed cell, you need to double-click on the desired cell, and a cursor will appear in the cell. After this, you can edit the data in the cell. You can simply select the cell you want, then position the cursor in the formula bar where the contents of the cell are displayed and then edit the data. After finishing editing, you must press Enter to commit the changes. In case of erroneous editing, the situation can be “rewinded” using the “Undo” button (Ctrl+Z).

Copying and moving table elements.

Using commands Cut, Copy And Insert V Microsoft application Office Excel You can move and copy entire cells or their contents. You can also copy specific portions of the contents or properties of cells. For example, you can copy the value calculated by a formula without copying the formula itself, or you can copy just the formula.

This article does not cover moving or copying a worksheet to another location in a workbook or to another workbook. additional information For information on moving and copying sheets, see the links in the section see also.

Note Excel highlights cells that you cut or copy with an animated, moving border. To clear this selection, press the ESC key.

Moving and copying entire cells

When you move or copy a cell, Excel moves or copies the entire cell along with formulas, calculated values, cell format, and comments.

1. On the tab home in Group Clipboard

Advice

Keys quick access You can also press CTRL+V.

Notes

Move and copy entire cells using the mouse

By default, drag-and-drop mode is enabled, so you can move and copy cells using your mouse.

1. Select the cells or range of cells you want to move or copy.

Notes

• When you move cells in Excel, it replaces the existing data in the insertion area.
• When you copy cells, the cell references change automatically. However, when you move cells, the references do not change, and the contents of those cells and any cells that reference them may be returned with an addressing error instead. In this case, you must change the links manually.
• If the selected copy area contains hidden cells, Excel also copies them. You may want to temporarily display hidden cells that you don't want to copy.

If the insertion area contains hidden lines or columns, you may need to display them to see all the copied cells.

Pasting copied and moved cells between existing cells

1. Select the cell or range of cells that contains the data you want to move or copy.

1. On the tab home in Group Clipboard Do one of the following:

Shortcut Keys You can also press CTRL+X.

Shortcut keys You can also press CTRL+C.

1. Click right click mouse in the upper left cell of the insertion area and select the item in the context menu Cut cells or Copied cells.

Advice To move or copy a selection to another sheet or workbook, click the other sheet's shortcut, or select another workbook and select the top-left cell of the insertion area.

1. In the dialog box Pasting copied cells select the direction in which you want to move the surrounding cells.

Note When you insert entire rows or columns, the surrounding rows and columns are shifted down and left, respectively.

Copy cell values, cell formats, and formulas only

When pasting copied data, you can do any of the following:

• convert formulas in cells to calculated values ​​without overriding existing formatting;
• paste only cell formatting options, such as text color or fill color (but not cell contents);
• paste only formulas (not calculated values).

1. Select a cell or range of cells that contains the values, formats, or formulas you want to copy.

Shortcut keys You can also press CTRL+C.

Note If the copied formulas contain relative cell references, those references (and the relative portions of mixed references) in the copied formulas are adjusted. For example, cell B8 contains the formula =SUM(B1:B7). When you copy this formula to cell C8, the new formula will reference the corresponding cells in that column: =SUM(C1:C7). If the copied formulas contain absolute cell references, the references in the copied formulas will not change. If copying does not achieve the intended result, you can change the references in the original formulas to relative or absolute, and then copy the cells again.

Copying cell width settings

When you paste copied data, the width settings of the target cells are applied to it. To change the width of cells to match the settings of the original cells, follow these steps:

1. Select the cells you want to move or copy.

1. On the tab home in Group Clipboard Do one of the following:

Shortcut Keys You can also press CTRL+X.

Shortcut keys You can also press CTRL+C.

1. Select the top left cell of the insertion area.

Advice To move or copy a selection to another sheet or workbook, click the other sheet's shortcut, or select another workbook and select the top-left cell of the insertion area.

Inserting and deleting table elements.

Blank cells can be inserted above or to the left of the active cell in a worksheet. In this case, the remaining cells will be shifted down in the same column or to the right in the same row. In the same way, you can insert rows above the selected row or columns to the left of the selected column. Cells, rows, and columns can also be deleted.

NOTE. Microsoft Excel has the following row and column limits: 16,384 (A to XFD) columns wide by 1,048,576 rows high.

Inserting blank cells in a worksheet

1. Select the cell or range of cells where you want to insert new empty cells. Select as many cells as you want to paste. For example, to insert five empty cells, select five cells.

1. On the tab home page in Group Cells Insert, and then select the command from the list Insert Cells.

ADVICE. You can also right-click the selected cells and select Insert.

1. In the dialog box Adding cells select the direction in which the surrounding cells will be offset.

NOTES

• When you paste cells in a worksheet, all references affected by the paste will be adjusted accordingly, regardless of whether they are relative or absolute. The same applies to deleting cells, unless the cell being deleted is directly referenced by a formula. To automatically correct references, it is recommended that whenever possible, you use cell range references in formulas rather than specifying individual cells.
• To paste cells containing data and formulas, you can copy or cut them, right-click where you want to paste them, and choose Paste copied cells or Paste cut cells.

Adviсe

Inserting Rows in a Worksheet

1. • To insert a single row, select the entire row or cell in the row above which you want to insert a new row. For example, to insert a new row above row 5, click the cell in row 5.
   • To insert multiple rows, select the rows above which you want to insert new rows. Select as many rows as you need to insert. For example, to insert three new rows, you need to select three rows.
   • To insert non-adjacent rows, hold down the CTRL key while selecting non-adjacent rows.

1. On the tab home page in Group Cells click the arrow next to the command Insert, and then select Insert Sheet Rows.

ADVICE. You can also right-click the highlighted lines and select the command Insert.

NOTE. When you insert rows in a worksheet, all references affected by the insertion will be adjusted accordingly, regardless of whether they are relative or absolute. The same applies to deleting rows, unless the cell being deleted is directly referenced by a formula. To automatically correct references, it is recommended that whenever possible, you use cell range references in formulas rather than specifying individual cells.

Adviсe

Inserting Columns in a Worksheet

1. Do one of the following:
   • To insert a single column, select the column or cell directly to the right of where you want to insert the new column. For example, to insert a new column to the left of Column B, click a cell in Column B.
   • To insert multiple columns, select the columns directly to the right of where you want to insert new columns. Select as many columns as you want to insert. For example, to insert three new columns, you must select three columns.
   • To insert non-adjacent columns, hold down the CTRL key while selecting non-adjacent columns

1. On the tab home page in Group Cells click the arrow next to Insert and then select the command Insert Worksheet Columns.

ADVICE. You can also right-click the selected cells and choose Insert.

1. On the tab home in Group Cells click the arrow to the right of the button Delete and do one of the steps below.

• To delete selected cells, click the button Delete cells.
• To delete selected rows, click the button Remove rows from a sheet.
• To remove selected columns, click the button Remove columns from a sheet.

ADVICE. You can right-click the selected cells, expand the menu Delete and choose the right option. You can also right-click the selected rows or columns and select Delete.

1. When deleting a cell or range of cells, select in the dialog box Removing cells option cells, shifted left, cells, shifted up, line or column.

When you delete rows or columns, the following rows or columns are automatically moved up or to the left.

Adviсe

• To quickly repeat deleting cells, rows, or columns, select the next cell, row, or column to delete, and press CTRL+Y.
• If necessary, deleted data can be restored immediately after deletion. On quick access panels click the button Cancel deletion or press CTRL+Z.

NOTES

• When you press DELETE keys Only the contents of the selected cells are deleted, not the cells themselves.
• IN Excel formulas are automatically updated by adjusting the references to the shifted cells to reflect their new location. However, a formula that contains a reference to a deleted cell returns a #REF! error value.

Merging cells

You can't split a single cell, but you can make it appear split by merging the cells above it. For example, let's say you want to split cell A2 into three cells that appear below cell A1 (perhaps using cell A1 as a header). Cell A2 cannot be split, but a similar effect can be achieved by merging cells A1, B1, and C1. You can then enter data into cells A2, B2, and C2. These three cells will appear as if they are separated under one large cell (A1), which acts as a heading.

When you merge two or more adjacent cells horizontally or vertically, you create one large cell that spans multiple columns or rows. When merged, the contents of one of these cells (top left for left-to-right languages, or top right for right-to-left languages) appears in the merged cell. The example below centers the text in a merged cell using the command.

IMPORTANT. The contents of other cells being merged are deleted.

The merged cell can be split again into individual cells. If you don't remember which cells were merged, you can quickly find them using the command Find.

Merge and center adjacent cells

1. Select several adjacent cells that you want to merge.

NOTE. The data you want to display in the merged cell must be in the top left cell of the selected range. Only this data will remain in the merged cell. Data from other cells in the selected range will be deleted.

1. On the tab home page in Group Alignment select team Combine and place in the center.

The cells will be merged by row or column, and the contents of the merged cell will be placed in the center of the cell. To merge cells without centering them, click the arrow next to the button Combine and place in the center and select a team Merge by row or Merge cells.

NOTE. If the button Combine and place in the center is not available, the selected cells may be in edit mode or located in Excel spreadsheet. To exit editing mode, press ENTER. Cells in an Excel table cannot be merged.

1. To change the alignment of text in a merged cell, select it and in the tab Home Home in Group Editing click the button Find and highlight.

1. Click the button Find.
2. On the tab Find click the button Options, and then the button Format.

Note If the button Format not displayed, click the button Options.

1. On the tab Alignment in Group Display check the box Merging cells and press the button OK.
2. Do one of the following:
   • To find the next occurrence of the merged cell, click Find next.

The next merged cell in the worksheet will be highlighted.

• To find all merged cells, click the button Find all.

At the bottom of the dialog box Find and Replace A list of all merged cells will be displayed. If you select a merged cell from the list, it will be highlighted on the worksheet.

Excel allows you to enter three types of data into cells: numbers, text, and formulas. Text can be used for table headings, explanations, or notes on a worksheet. If Excel does not recognize the data type as numeric or as a formula, then the data is treated as text.

Numbers are used to represent digital information and can be entered into various formats: general, monetary, financial, interest, etc. Date and time can also be considered as numbers.

Formulas entered into a cell perform calculations, control database operation, check cell properties and values, and are used to define relationships between cells and arrays using address references.

Any formula begins with a (=) sign. If a formula is entered into a cell, then by default the cell will show the result of the calculation.

Entering data into a cell

Data is typed directly into the active cell, and it is displayed in the formula bar. Data can also be entered in the formula bar.

Data input

1. Click the cell.
2. Enter details, e.g. Report 2001.
3. Press the key Enter.
4. To cancel your entry, press Esc.

As you type in the formula bar, click the button Enter- √ to confirm, and to cancel the entry, press the button Cancel - ×.

Pressing the cursor keys or clicking another cell will always cause the typed data to be saved in the active cell before moving to the next one.

Text that is too wide to fit in the current cell will appear to overlap adjacent cells, although it will actually be contained within a single cell. Excel limits text or formulas in a cell to 255 characters.

Numbers that are too large to be shown inside the current cell will be shown as a sequence of characters # # # #. To show a numeric value in a cell, you need to increase the column width (see the section "Formatting Cells").

You can delete the contents of a cell using the key Delete.

Selecting cells

Many operations, such as inserting rows or columns, deleting, copying, or moving cells, require selecting one or more cells before starting the operation.

The selection area can be either a separate cell or occupy an entire workbook. The active cell is always part of the selection area. The selection area must be rectangular and can be defined as:

• one or more cells;
• one or more columns;
• one or more lines;
• one or more worksheets.

Table 17 illustrates some of the possible combinations.

Table 17. Designations of table areas

Cells can be selected using the mouse or keyboard, or a combination of both (Table 18). The selected cells will differ in color.

Selection area	Selection method
Single cell	Click in a cell
Cell group	Click the mouse in the cell; Without releasing the mouse, drag from the first cell to the last, or click the first cell and, while holding down the Shift key, click on the last cell
Column	Click on the column header
Adjacent Columns	Click on the first column heading; Without releasing the mouse, drag from the first column heading to the last, or click on the first column heading and, while holding down the Shift key, click on the last column heading
Line	Click on the row header
Adjacent lines	Click on the row header; Without releasing the mouse, drag from the first row heading to the last, or click on the first row heading and, while holding down the Shift key, click on the last row heading
All cells in the current worksheet	Click the button at the intersection of the row and column headings
Non-adjacent columns	Select the first column, press Ctrl and select the following columns
Non-adjacent lines.	Select the first row, press Ctrl and select following lines
Not adjacent cells	Select the first group of cells, press Ctrl and select the next group of cells

Table 18. Selecting table areas

To deselect, click on any cell. To select an area using the mouse if the selection area exceeds the size of the window, as in other applications, autoscrolling is used. This means that if the mouse pointer moves outside the window, the sheet will automatically scroll in that direction.

Editing cell contents

Editing the contents of a cell can be done either in the cell or in the formula bar. Input and Edit modes are shown in the status bar.

To edit, double-click the cell you want to edit or click in the formula bar.

The mouse pointer can be used to move to the edit location. In addition, the following keys can be used in edit mode:

Just like in Word, pressing the Insert key switches between insert and replace modes.

Undo and redo actions

Excel allows you to undo changes made to a workbook. Although this feature is applicable to to the majority commands, there are exceptions for it (for example, you cannot undo deleting and renaming a sheet).

Team Undo in the Edit menu context dependent. When a user types or edits data in the formula bar, menu Edit The command corresponding to the last performed operation will be offered.

On standard panel to cancel the last one teams you must click a button or cancel several commands by selecting them from the list.

After choosing a team Undo from the Edit menu The command will change to the Redo command.

Inserting Rows and Columns

Additional rows or columns can be inserted as needed anywhere in the table. Team Insert (Insert) in the Edit menu (Edit) can be used to insert a new column left from the current column or new line above current line.

Multiple columns and rows can be added when selecting an area that includes more than one column or row.

1. Select as many columns or rows as you need to insert.
2. Select Insert, Rows or Insert, Columns or press the key combination Ctrl and + on the numeric keypad.

To delete rows or columns:

1. Select the rows or columns to delete.
2. Select Edit, Delete (Edit, Delete) or press the key combination Ctrl and - on the numeric keypad.

Inserting and deleting columns or rows shifts the addresses of the remaining data in the table, so you need to be especially careful when inserting or deleting.

Moving and copying data

Moving and copying data is one of the main operations used when working with tabular data; in this case, not only the contents of the cells are copied to a new location, but also their formatting.

Moving and copying cell contents can be done in two ways:

• Edit menu commands;
• dragging with the mouse.

Once the user selects the cell and selects the command Cut or Copy (Cut or Copy) on the menu Edit Excel will copy the contents of the cell to the clipboard.

When you move, the data from the original cells will be pasted into the new location.

Data copying is used to duplicate information. Once the contents of one cell are copied, it can be pasted into a single cell or into an area of ​​cells multiple times. In addition, the selected area is surrounded by a moving dotted border that will remain until the operation is completed or cancelled.

The border appears as a pulsating dotted frame surrounding the selected object. Pasting the contents of cells is only possible when this border exists.

Using the command Paste in the Edit menu after selecting the Cut command, the border will be disabled.

Using the command Paste after the Copy command (Soru) will not disable the border, i.e. the user can continue to specify other destinations for data insertion and use the command Insert again.

Keystroke Enter will paste the selected area to the new location specified by the mouse and turn off the border.

Keystroke Esc will cancel the copy to buffer operation and disable the border.

When pasting data from more than one cell, you only need to specify the left one top corner the area of ​​cells on the worksheet into which the pasting is performed.

Moving and copying using the menu

Opportunity Drag and Drop allows you to move or copy the contents of selected cells using the mouse. This feature is especially useful when moving and copying to short distances(within the visible area of ​​the worksheet).

Move and copy by dragging

1. Select the area of ​​cells to move.
2. Move the mouse pointer to the selection frame.
3. Drag the selection using the pointer to a new location. The cell area will be moved to a new place.
4. If you hold down the key while dragging Ctrl, the area of ​​cells will be copied to the new location.

Special data copying

Special copying of data between files includes the command Special paste (Paste Special) in the Edit menu (Edit). Unlike simply copying data using the Paste command, the command can be used to calculate and transform information, as well as to link workbook data (these capabilities will be discussed in the next chapter).

Team Special insert(Piste Special) often used to copy cell formatting attributes.

1. Select the cell or cells to copy.
2. Select the cell or cells where the source data will be placed.
3. Select Edit, Paste Special. The Paste Special dialog box contains several options for pasting data (Figure 83).

Rice. 83. Special insert

1. Set the necessary options, such as formats (when you colorize formats, only the formatting changes, not the meaning of the cells).
2. Select OK.

First group of dialog box parameters Paste Special Allows you to select the content or formatting attributes that you want to paste. Selecting All (AI) pastes the contents and attributes of each copied cell to the new location. Other options allow you to insert different combinations content and/or attributes.

The second group of parameters applies only when inserting formulas or values ​​and describes the operations performed on the information being inserted into cells that already contain data (Table 19).

Parameter	Insertion result
Fold	The inserted information will be added to the existing values
Subtract	Inserted information will be subtracted from existing values
Multiply	Existing values ​​will be multiplied by the inserted information
Divide	Existing values ​​will be divided by the inserted information
Skip empty cells	You can only perform actions on cells that contain information, i.e. special copying empty cells will not destroy existing data
Transpose	The orientation of the pasted area will be switched from rows to columns and vice versa

Table 19. Paste Special command options

Choice No means that the copied information simply replaces the contents of the cells. By choosing other operation options, we get that the current contents will be combined with the inserted information and the result of such a combination will be the new contents of the cells.

Exercise

Perform calculations using the Paste Special command

Enter the data as shown in the table. 20.

	A	IN	WITH	D	E	F	G	N
1
2	5	2					1	2
3	12	3					10	3
4	8	2					15	4

Table 20. Initial data

1. Select the area to copy A2:A4.
2. Select Edit, Copy.
3. Click cell B2 (upper left corner of the area where the data will be placed).
4. Select Edit, Paste Special.
5. Set the Multiply option.
6. Click OK. Please note that the border of the selection area remains on the screen.
7. Click cell C2, which will be the start of the insertion area.
8. Select Edit, Paste Special and set the Transpose option.
9. Copy the formats of column G into column H yourself and get a table. 21.

	A	B	C	D	E	F	G	N
1
2	5	10	5	12	8		1	2
3	12	36					10	3
4	8	16					15	4

Table 21. Result of the Paste Special command

Lab. Slave. No. 10

Creating and formatting tables in MS Excel

1. Purpose and content: learn how to carry out basic operations with worksheets in MS Excel containing economic indicators And financial results activities of enterprises; perform calculations using given formulas using a spreadsheet processor.

Theoretical background

Excel is a spreadsheet processing program. It can be used to organize complex calculations, presenting tabular data in the form of charts, sorting and manipulating large volumes information. Spreadsheets are designed to store and process information presented in tabular form - this is two-dimensional arrays, consisting of columns and rows. MS Excel allows you to automate the processing of tabular data. Using MS Excel tables, you can perform various economic and accounting calculations, build various types of diagrams, conduct analysis, simulate and optimize solutions to various production situations, etc.

2.1 Key concepts of spreadsheets

Workbook- is the main MS Excel document. It is stored in a file with the xlc extension. Each workbook contains 16 worksheets by default. Sheets designed for creating and storing tables, charts, macros. Sheet workbook Excel has a matrix structure. The column name and row number to which the cell simultaneously belongs uniquely determine its address: A1 is the address of the cell located in column A and the first row. Cell is the smallest structural unit for placing data within a worksheet. Each cell can contain data in the form of text, numeric values, formulas, or formatting options. When entering data, MS Excel automatically recognizes the type of data and determines the list of operations that can be performed with it. There are two ways to write cell addresses in a table:

1) Indicating the column letter and row number of the table, which can be preceded by a $ sign indicating absolute addressing, for example: C5, J$1, $K$3. This is the default method and is called A1 style.

2) By indicating the row number and column number following the letters R and C, respectively. This method is called "R1C1".

MS Excel interface

Microsoft program Excel can be started in three ways: using the START menu Windows systems; using the Excel shortcut on your desktop Windows desktop; through the program Windows Explorer. The view of the main Excel window, which is shown to the user immediately after starting the program, is shown in Fig. 1.1.

The Excel window structure consists of the following elements.

Header Line – top line of the screen; This line contains the application name - Microsoft Excel, followed by the workbook name - Workbook 1. The number 1 indicates that Excel can work with multiple workbooks.

Among the main window interface elements may be called:

- Menu bar and the main mode menus located on it (hierarchical drop-down lists). The menu bar is located below the title bar. The menu bar lists the commands: File, Edit, View, Insert, Format, Tools, Data, Window, Help. Each team combines a set of subteams that have a common functional focus.

- Toolbar, containing icons, buttons, and drop-down lists that allow quick access to frequently used commands and settings. By default, unless certain settings are made, Excel displays panels Standard And Formatting. Toolbar Standard contains buttons with icons. Badges- these are commands that can also be found in the menu. For quick access to frequently used commands, it is convenient to use the toolbar.

Toolbar Formatting contains abbreviations for the most common operations performed when formatting text.

Figure 1 Standard view of the spreadsheet window

To find out the name of a button or icon, you need to move your mouse pointer over it - Excel will display a hint. To display additional toolbars, you need to use the VIEW command >Toolbars, where to select the required panel. Or bring up a list of toolbars using the context-sensitive menu.

Data entry line . IN Excel program the data entry line is called formula bar. Formula bar located under the panel Formatting and is used to enter and edit data in a table cell. It displays the contents of the active cell. Status bar- displays information about the selected command or operation being performed, as well as background information about enabled function keys.

Many operations in Excel (for example, deleting, pasting, copying) are performed using Context-sensitive menu, which can be called by right-clicking on the active object.

Move around the sheet

At any given moment, only a certain part of the spreadsheet data may be visually available to us. In this regard, the navigation capabilities of the program are of particular importance.

You can navigate through a workbook sheet using: the keyboard (cursor control arrow); mouse (scroll bars); EDIT menu commands >Go . In the Go to Field dialog box Link specify the address of the desired cell to go to a specific cell of the desired worksheet. Basic features for moving inside separate sheet are described in Table 1.

To move between sheets of a workbook, you can either use the navigation buttons or directly select the tab of the desired sheet with the mouse.

Table 1

Move around the worksheet. Active cell is the cell into which information is entered (text, number, formula). The active cell can be changed by: moving the mouse pointer to the cell that should be active and clicking on it; or select the EDIT command from the menu> Go.

Column selection – carried out by clicking on the column name. Selecting a line - carried out by clicking on the line number. Selecting multiple cells: in order to highlight adjacent cells, holding down the left mouse button, move the mouse pointer from one corner of the range to another; to highlight non-adjacent cells select the first cell or first range of cells, and then hold down Ctrl, select the remaining cells or ranges one by one.

To select the entire table– click the button located at the intersection of the row and column headings.

Data entry technology in MS Excel

The cell is designed to store different meanings various types. It has a unique address, can have a name, and can have different values.

Interval (block, range) of cells- a rectangular area of ​​adjacent or non-adjacent cells. It is specified by specifying the address of the upper left and lower right cells, separated by a colon. For example: A2:G4.

Note: Selecting a non-adjacent range of cells is done using the mouse while holding down the key Ctrl.

Cells have a specified format, which can be set using the FORMAT> command Cells(Fig. 2). This command has several tabs. You can enter data of various types into cells: text, numbers, dates, times, formulas (which may include numbers, signs of arithmetic, logical or other operations performed with data from other cells, cell addresses or their names, names of built-in functions) . By default, text is left aligned and numbers are right aligned.

Data input – one of the main operations when working with a spreadsheet. Each table cell can be filled with data of a different nature: number, text, formula.

Figure 2. Controlling cell format

To enter data into a table cell, you must do the following: activate the cell; enter data (text, numbers, formulas) using the keyboard; confirm the entry: by pressing the enter key Enter; pressing the button before Formula bar; click on another cell; exiting the current cell using the cursor keys.

Entering numbers. Excel interprets the entered cell contents as a numeric value if it consists only of numbers. Numeric values ​​can use some Special symbols: “-” - input negative number. If a numeric value is enclosed in parentheses as you enter it, the program automatically converts it to a negative number; “+” - when entering positive numbers, this sign is omitted; “,” - if one comma is entered before a number or in a number, the program will perceive it as a decimal point; “%” - when entering a number, you can assign a percentage format to the cell. To do this, you must specify a percent symbol after the number. When performing calculations, Excel will divide the value displayed in the cell by 100; "R." - when entering a number, you can assign a currency format to the cell. To do this, the currency symbol is indicated after the number. At correct input character in numeric Excel value aligns it to the right edge of the cell.

Entering text. Any input data that the program does not perceive as a numeric value, date, time value, or as a formula is interpreted by the program as text. Text, unlike numbers, is displayed left-aligned. Long text can be split into several lines in a cell by pressing a keyboard shortcut Alt+Enter go to new line.

Excel has several ways to make it easier and faster. data entry process: re-entry(copying) existing data; autocomplete; entering progressions.

For copying data should: select the cell; move the mouse pointer to the autofill marker (the “+” sign) and drag the selection frame over all the cells into which you want to copy the data.

Similar result can be achieved by using the EDIT command >Fill in and indicate the direction of copying.

Autofill- fills the selected cells with selected (or additionally created) sequences.

The function for entering progressions allows you to quickly create sequences of numbers, dates, etc. increasing (decreasing) according to an arithmetic or geometric law. For creating a progression necessary: ​​enter the value of the first element of the progression in the selected base cell; select a block of cells that will be occupied by members of the progression (or in the future you will have to specify the value of the last element); execute the EDIT command >Fill in > Progression; in the dialog box that appears Progression indicate the type and parameters of the generated sequence of values.

Correction of cell contents. Before confirming your data entry: using the key backspace; using the key Del; button – cancel.

Confirmed data, Having activated the cell, you next need to: press the key F2 and make corrections directly in the cell; set mouse cursor to Formula bar and make corrections.

Deleting the contents of a cell. A cell on the screen has several levels: Image, Format, Formula, Name.For removing content cells you need: run the EDIT command > Clear, and then select one of the levels; use key Del; using the command context menu Clear contents.

For removing row/column: execute the EDIT command >Delete; using the context menu command Delete.

Copying data. If necessary, the contents of a cell can be copied and pasted to another location. To do this, you need to activate the cell, copy it to the clipboard, then select the area (cell or range of cells) to paste and paste the contents of the clipboard. There are several ways to copy and paste: through the menu commands EDIT> Copy, then EDIT> Insert; via context menu commands Copy And Insert; using the appropriate buttons on the toolbar; drag and drop method: Move the mouse cursor under the lower right corner of the cell, and the cursor will change its shape (+), while holding the left mouse button, move the mouse cursor to adjacent cells and release the mouse button.

Formulas

Formula is an expression consisting of operands that is used to perform calculations on the given data. Each formula begins with the equals symbol "=". The operands can be: numbers, logical values, references to cell addresses (range of cells), built-in functions that are connected using operation symbols - addition, subtraction, multiplication, division, exponentiation. The formula itself is also a value (the result of calculations) that is stored in a worksheet cell.

The main operators in the formulas and their hierarchy are described in Table 2. Excel performs calculations sequentially, depending on their hierarchy. If a formula contains multiple operators with the same precedence level, they will be processed from left to right. If the sequence of calculations, determined by the priorities of the operators used in the formula, must be changed, then parentheses must be used. Expressions in parentheses will be processed first.

Table 2. OPERATORS IN FORMULAS AND THEIR HIERARCHY

If a formula contains several operators, they will be processed in the following sequence: negative number sign (-); percentage operator (%); exponentiation (^); multiplication and division (*,/); addition and subtraction (+,-); concatenate text (&).

Entering formulas. Technical Excel capabilities allow you to create enough complex formulas. You can use formulas to calculate and analyze worksheet data. Any data entry that begins with an "=" sign is interpreted by Excel as a formula. The formula itself is displayed in the input formula bar, and the calculation result is visible in the cell. Formulas almost always contain references to cells whose contents are used in calculations. The formula can use cell range names or headers.

2.2.4 Worksheet design

Fonts.Various types and sizes of fonts offered by Excel allow you to effectively design a table. There are several ways to select a font. select the FORMAT command >Cells > Font; by executing the context-sensitive menu command Cell Format > Font; using the FORMAT toolbar buttons > Font And Size .

Formatting. Align the contents of a cell or cells to the left, right, center, etc. performed using the corresponding buttons on the FORMAT toolbar or using the bookmark Alignment dialog box Format.

Choice of colors allows you to get greater clarity when working with the table. To select and set a color, you must select a tab View dialog box Format

Framing.For the purpose of highlighting individual elements table (header, columns, rows, cells) border is used. To set range boundaries, select the tab Border dialog box Format or the corresponding toolbar buttons.

- 2.2.5 Shutting down the application. There are also several ways to correctly shut down Excel: using the FILE command >Exit; click on the button Close or by running the FILE command >Close.

If it was open spreadsheet and changes were made to it, Microsoft Excel will save them automatically upon exit, however, if these operations affected the structure of any table objects, the program will additionally request confirmation for all changes made.

Related information.

ENTERING AND EDITING DATA IN MICROSOFT EXCEL

1. Data entry

All data entered into Microsoft spreadsheet Excel, placed and stored in cells. Each cell can hold up to 255 characters. Excel uses two types of data: constants and formulas. Constants include text, numeric values, including date and time, which, when entered, are displayed in a table cell and in the input area of ​​the formula bar (Fig. 1). Moreover, one cell can contain either a number or text, but not a number and text together. Formulas define a calculation algorithm, the results of which are displayed in cells, and the formula itself, by which the result is calculated, is in the input area of ​​the formula bar (Fig. 2).

Rice. 1. Constant in a table cell.

Rice. 2. Display the formula in the table.

To enter data into a cell, you need to select it, and then begin entering by pressing the required characters on the keyboard. Moreover, decimal fractions are entered with a comma between the whole number and fractional parts numbers.

You can enter data directly into a worksheet cell or into the input area of ​​the formula bar. In this case, the entered information is displayed simultaneously in these two places. To complete entering data into a cell, do one of the following:

1) press Enter or Tab keys;

2) click on the enter button of the formula bar;

3) press one of the cursor keys.

By default, Microsoft Excel sets all table columns to the same width. Therefore, when entering large numbers and long text, the last characters either “disappear” (if the adjacent cell is full) (cell B13 in Fig. 3), or “creep” onto the adjacent cell (if it is empty) (cell E6 in Fig. 3). But the entered data is in the cell and is fully displayed in the formula bar. In such cases, you should change the column width (row height) automatically or manually.

Additionally, a cell may display ##### if it contains a number that does not fit in the column. To see this number, you need to increase the column width.

Rice. 3. Displaying "long" data in a table.

For automatic change To change the column width, place the mouse pointer on the right border of the column header so that it looks like a cross with a double-headed arrow, and double-click the left mouse button.

To change the column width (row height) manually, you need to place the mouse pointer on the right border of the column header (the lower border of the row header) so that it looks like a cross with a double-headed arrow (Fig. 4) and, while holding down the left mouse button, drag the right border the column header (bottom border of the row header) to a new location.

Rice. 4. Change column width manually.

In this case, the tooltip will display

set column width. It shows the average number of digits 0-9 in the default font that will fit in the cell, as well as the column width in pixels).

In order to change the width of several columns at once, you need to select the columns whose width you want to change, and then drag the right border of the header of any selected column. In a similar way, you can change the height of several lines at once.

2. Accelerated data entry

IN Microsoft Excel input data can be facilitated by using AutoComplete, Select from a list, and AutoFill, as well as using a fill handle.

AutoComplete completes text input for the user. As you type characters in a cell, AutoComplete checks all cells in the column, starting with the current cell and ending with the first empty cell. If

it will detect a data element in the column that begins with the characters entered, then the remaining characters will be entered automatically according to the found pattern.

Let's assume that a column contains a sequence of text data, for example, city names: Kyiv, Chisinau, Kislovodsk. Their first two letters are the same. When you enter the first three letters of Kish in a new cell in a column, AutoComplete will insert the word Chisinau (Figure 5). If you need to enter another word, you need to continue entering the remaining characters. If you need to enter the word Chisinau, you need to press Enter. AutoComplete makes it very easy to enter large amounts of text data, especially when the differences in words begin with the second or third letter.

Rice. 5. AutoComplete tool.

AutoComplete does not work with numeric values.

The Select from List feature is used when the user knows that the required text has already been entered in one of the cells in a given column. To do this, just right-click on the active cell and select Select from the drop-down list from the context menu. A list will open under the active cell, which will present all the text data entered in this column (Fig. 6). The user must left-click on the list item that needs to be placed in the active cell.

Rice. 5. Tool Select from list.

The Select from List tool, like the AutoComplete tool, works

only with the contents of column cells not separated by empty cells and does not work with numeric values.

To fill cells in a row or column with duplicate values, you can use padding with fill marker ( black square in the lower right corner of the cell_ (Fig. 7).

Rice. 7. Fill marker.

To do this you need to do the following:

1) enter into the cell the value with which you want to fill the row or column;

2) select this cell again;

3) move the mouse pointer to the fill marker so that the mouse pointer changes to a black cross;

4) While holding down the left mouse button, drag the pointer, which takes the form of a cross, over the desired cells of the row (column) (Fig. 8).

To fill the cells of a row or column with a sequence of numbers in which each next number differs from the previous one by the same value (arithmetic progression), you can:

1) enter the value of the first term of the arithmetic progression into the cell;

2) enter the second term of an arithmetic progression into the adjacent cell of a row or column;

3) select both cells;

4) holding down the left mouse button, drag the fill marker to the right (if you need to fill row cells) or down (if you need to fill column cells) (Fig. 9)

Rice. 9. Filling column cells with arithmetic progression values ​​using a fill handle.

Also, to set a progression, you can use the Fill button in the Editing group of the Home tab and in the Progression dialog box that appears, set parameters for an arithmetic or geometric progression.

Microsoft Excel allows you to enter frequently repeated lists into a table using the AutoComplete tool. After Microsoft installations Excel already contains lists of days of the week and months. In some of them, their elements are full words, while in others they are generally accepted abbreviations:

- January, February, March, ..., December;

- Jan, Feb, Mar, …, Dec;

- Monday, Tuesday, Wednesday, …, Sunday

Mon, Tue, Wed, …, Sun.

To automatically enter one of these lists:

1) enter the first element of the list into the cell;

2) select this cell;

3) While holding down the left mouse button, drag the fill marker to the right or down (Fig. 10).

Rice. 10. Autofill tool.

If you need to use your own, not built-in lists, then the user can assign them to autocomplete. To do this you need:

3) in the Lists dialog box that opens, in the Lists field, select the New list element;

4) click in the List Items field and enter your list, using the Enter key to separate list items (Fig. 11);

5) Having specified the last element of the list, click on the OK button.

Rice. 11. Creation own list autofill.

A new autocomplete list can also be created based on an already entered one.

V list of values ​​table:

1) select the range of cells containing the list;

2) In the Office button menu, click on the button Excel Options;

3) in the Excel Options dialog box that opens, in the Basic section, click on the Edit lists button;

4) In the Lists dialog box that opens, click the Import button.

5) Click on the OK button.

Rice. 12. Creating an autocomplete list based on the list of values ​​​​already entered into the table.

3. Data editing

Data entered into Microsoft cell Excel, you can edit them directly as you enter them. Editing already entered data can be done in two ways - directly in the cell (to do this you need to go to edit mode) or using the input area of ​​the formula bar. To enter edit mode, you can either double-click the cell or press F2. To edit data using the formula bar, you need to select the cell and click once in the input area of ​​the formula bar. Editing data in a cell is similar to editing text in Microsoft Word. To delete a character located to the left of the cursor, use the Backspace key, and to delete a character located to the right of the cursor, use Delete. When entering and

There are two types of data that can be entered into cells Excel sheet - constants And formulas.

Constants, in turn, are divided into: numeric values, text values, date and time values, logical values ​​and error values.

Numeric values

Numeric values ​​can contain numbers from 0 to 9, as well as special characters: + - E e () . , $% /

To enter a numeric value into a cell, you need to select the desired cell and enter the required combination of numbers from the keyboard. The numbers you enter appear both in the cell and in the formula bar. When you have finished entering, you must press the Enter key. After this, the number will be written into the cell. By default, after pressing Enter, the cell located one line below becomes active, but using the Tools - Options command on the Edit tab, you can set the required direction of transition to the next cell after entering, or eliminate the transition altogether.

The number of digits you enter depends on the column width. If the width is not sufficient, Excel either rounds the value or displays ### characters. In this case, you can try increasing the cell size.

Text values

Entering text is completely similar to entering numeric values. You can enter almost any characters. If the length of the text exceeds the width of the cell, then the text overlaps the adjacent cell, although it is actually in the same cell. If there is also text in an adjacent cell, then it overlaps the text in the adjacent cell.

To adjust the cell width to fit the longest text, click on the column border in its header. So if you click on the line between the headings of columns A and B, the cell width will be automatically adjusted to the longest value in that column.

If there is a need to enter a number as a text value, then you must put an apostrophe in front of the number, or enclose the number in quotation marks - “123 or “123”.

You can tell which value (numeric or text) is entered into a cell by its alignment. By default, text is left aligned while numbers are right aligned.

Changing values ​​in a cell

To change values ​​in a cell before committing the input, you must use, as in any text editor, the Del and Backspace keys. If you need to change an already fixed cell, you need to double-click on the desired cell, and a cursor will appear in the cell. After this, you can edit the data in the cell. In case of erroneous editing, the situation can be “rewinded” using the Undo button (Ctrl+Z).

Hiding cells and sheets

To enable formula hiding mode, you must: select the desired cells; select Format – Cells (Ctrl+1); on the Protection tab, check the Hide formulas box; select Tools - Protection - Protect sheet; In the Protect Sheet dialog box, select the Contents checkbox.

After this, when you activate cells containing hidden formulas, these formulas are not displayed in the formula bar, although the calculation results are visible.

If desired, you can hide the entire sheet. In this case, all sheet data is saved, but it is not available for viewing.

To hide a worksheet, click on the sheet tab and select Format - Sheet - Hide. After hiding the sheet, the Display command will appear in the Sheet submenu, with which you can make the sheet visible again.