Features of data entry and editing. Entering data into Excel cells
Annotation: The lecture examines ways to enter and edit data in documents. Microsoft Excel 2010. Basic rules for data entry are given. Features of entering numbers, calendar dates and times are indicated. The possibility of using auto-completion when entering data is shown. The possibility of entering data using auto-completion with standard lists is shown. The procedure for creating a custom list is provided. Methods for editing the contents of cells are considered. Shows the ability to check spelling in a document.
Entering data using the keyboard
General data entry rules
You can enter data directly into a cell or into the formula bar.
If you enter an incorrect character, you can delete it. To delete a character to the left of the text cursor, press the key Back Space.
When entering data, you cannot move the text cursor in a cell using the keyboard keys. This can only be done using the mouse. You need to move the mouse pointer and left-click once.
By default, all data in a cell is entered on one line. To move to a new line (new paragraph) in a cell, press the key Enter when the key is pressed Alt(Fig. 4.2).
Also, text in several lines in a cell can be placed when designing the cell.
To refuse to enter data into a cell, press the key Esc or button Cancel(cross) in the formula bar (see Fig. 4.1).
A cell can contain up to 32767 characters. The exception is formulas. The length of the entry for the formula is 8192 characters.
The same data can be entered simultaneously into several different cells of the same sheet. To do this, select the cells in which you need to enter data (not necessarily adjacent), enter the data and press the keys Ctrl + Enter or, with the key pressed Ctrl, click on the button Enter in the formula bar (see Fig. 4.1).
The same data can be entered simultaneously into cells of the same name in different sheets. To do this, select the sheet labels in which you need to enter data, enter the data and confirm the entry.
To fill the active cell with the contents of the cell above (fill down), you can press the key combination Ctrl+D. To fill the contents of the cell on the left (fill to the right), you can press the key combination Ctrl+R.
Microsoft Excel usually recognizes the data entered into a cell (text, numbers, dates, times) and automatically sets the appropriate data format.
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 integer and fractional parts of the number.
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 Microsoft default Excel sets all table columns to be the same width. Therefore, when entering large numbers and long text, the last characters either “disappear” (if the neighboring cell is full) (cell B13 in Fig. 3), or “creep” onto the neighboring cell (if it is empty) (cell E6 in Fig. 3). But the entered data is in the cell and is completely 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 left button mouse, drag the right border of 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 List Selector is used if the user knows that required text has already been entered into one of the cells in this column. To do this, just right-click on the active cell and from context menu select Select from the drop-down list. 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, select the New list element in the Lists field;
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. Create your own autocomplete list.
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
1. Enter data into Excel cells
Data is always entered into the active cell that has a black border. On first launch Excel programs By default, cell A1 is active. To activate another cell, you must place a selection frame on it. This can be done by left clicking the mouse or moving the frame to the desired cell using the cursor keys.
Once you select a cell, you need to type text, a number, or a formula into it.
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. 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.
Features of entering numeric values using special characters are as follows.
1. If you need to enter negative number, then you must put a “–” (minus) sign in front of the number.
2. Symbol E or e 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 in front of the number. Those. (40) – for Excel means -40.
4. When entering large numbers, for ease of presentation, you can enter a space (23,456.00) between groups of digits. 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. Use the dollar sign ($) to enter the currency format.
6. To enter percentage format, use the percent sign (%).
7. Use the forward slash (/) to enter dates and fractions. 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 represent such a number as a fraction, then you need to 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. However, to display it in the formula bar, exponential notation with no more than 15 significant figures is used. 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 wide enough, Excel either rounds the value or displays ### characters. In this case, you can try increasing the cell size.
When entering decimal fractions, you must use a comma. Excel treats numbers containing a period, hyphen, or slash as dates. So, if you type 1.5, 1/5 or 1–5 in a cell, the system will recognize this entry as the first of May of the current year, transforming it into 01.May. The full date (in the format day, month, year – 05/01/2007) can be seen in the formula bar by highlighting the cell.
If you need to enter a date containing another year, you need to type the number, month and year sequentially separated by a dot, hyphen or slash, for example, 7.8.99, 25/6/0, or 12–12–4. As a result, Excel will place the dates 08/07/1999, 06/25/2000 and 12/12/2004 in the cells. The colon is used to enter the time. So, if you type 19:15 in a cell, Excel will recognize this entry as the time 19:15:00. To complete your entry and move to the next lower cell, you must press the Enter key, or use the mouse or cursor keys to move to other cells.
If the width of the entered text exceeds the width of the cell, it will overlap the empty cells to the right, but will not fill them. If there is data in the cells on the right, then the typed text will not overlap with them. When you remove a selection frame from a cell, the text will be “cut off” in width, but you can see it in full in the formula bar by clicking on the cell. However, there is an easy way to get rid of the overlap by changing the width of the column with the “offending” cell. To do this, hover your mouse over the right border of the column header and, when it looks like , click and drag the border to the right until all the text is displayed. To set the exact column width, you need to watch the tooltip as you drag the border. An even easier way to set the desired width is to do it on the border of the column header double click.
You can visualize text that does not fit the width of the cell in another way - by word wrapping by increasing the line height. To do this, click on the problematic cell and on the Home tab, in the Alignment group, click the Text Wrap button. In this case, the height of the row in which the cell is located will be increased so that its hidden contents are fully displayed. To move text, according to words, the height of the cell can be changed manually by dragging it beyond the border of the header, as in the case of a column.
To enter simple formulas containing only arithmetic signs (+, –, *, /), you need to do the following:
1. Click on the cell in which you want to place the formula.
2. Enter the equal sign = (this should always be done when typing formulas).
3. Next, you need to enter the addresses of the cells whose values will take part in the calculation. To do this, you need to click on the first one. In this case, the cell will be highlighted with a running frame, and its address will appear in the input cell (Fig. 1, left).
4. After this, type the arithmetic sign from the keyboard and click to select the second cell to insert its address (Fig. 4, right) or type the address from the keyboard, switching to the English layout. Next Press Enter to complete the entry. As a result, the calculation result will be displayed in the cells.
5. It is possible to combine several arithmetic operations in one formula. If necessary, you can use parentheses, as with standard notation for mathematical expressions. So, for example, if you need to add the values of two cells, and then divide the result by the number in the third cell, in the form of a formula it will look like this: =(B2+C2)/D2. When entering a formula, cell addresses must be specified by clicking or typing manually.
Rice. 1 – Entering a simple formula
2. Editing data in a cell
Editing data in a cell is carried out as follows. To delete the entire contents of a cell, select it by clicking and press the key. If you need to enter new data in a filled cell, it is not necessary to delete the previous ones. Just select it and start typing. Old data will be automatically replaced.
If a cell contains a large text fragment or a complex formula, then it is irrational to delete them completely to make changes. Double-click on the cell and place the cursor in right place to edit, make the necessary changes and press Enter.
If you need to stop editing a cell, but editing has already started, just press the Esc key. This will restore the original data in the cell. To cancel an action that has already been completed, use the standard keyboard shortcut +Z or the Undo button on the Panel quick access.
When you change values in cells referenced by a formula, the result of the calculation in the cell containing the formula will be automatically recalculated.
3. Autocompletion and autofill cells
Often, when filling out a table, you have to type the same text. Excel's AutoComplete feature helps speed up this process significantly. If the system determines that the part of the text being typed matches what was entered earlier in another cell, it will substitute the missing part and highlight it in black (Fig. 2). You can agree with the proposal and move on to filling out the next cell by clicking, or continue typing required text, not paying attention to the selection (if the first few letters match).
Rice. 2 – Autocomplete when entering text
The autofill mechanism is convenient to use in cases where you need to enter a sequence of data into cells. Suppose you need to fill a row or column with a sequence of numbers, each of which is greater (or less) than the previous one by a certain amount. To avoid doing this manually, you can do the following:
1. You need to type the first two values from a series of numbers in two adjacent cells so that Excel can determine their difference.
2. Select both cells.
3. Place the mouse pointer over the marker located in the lower right corner of the selection frame. At the same time, it will take the form of a black plus.
4. Click and, while holding down the mouse button, drag the frame until the final value appears on the tooltip near the mouse pointer, which will be placed in the last cell of the row. You can drag the frame in any direction.
5. Release the mouse button until the range of covered cells is filled.
The AutoFill feature is very useful when copying a formula into a series of cells. Thanks to the AutoFill feature, you can enter formulas into cells in a few seconds. You need to type only the first formula, and then drag the frame by the bottom marker to copy it to the entire row.
In this case, the cell addresses in the formulas will be automatically replaced with the required ones (by analogy with the first formula).
List of used literature
table editor cell autocomplete
1. Electronic resource: http://www.5byte.ru/excel/edit.php
2. Electronic resource: http://www.on-line-teaching.com/excel/lsn002.html
3. Electronic resource: http://www.kolomna-school7-ict.narod.ru/st40201.htm
Data Types
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 entry
- Click the cell.
- Enter data, for example "Report 2001".
- Press the key Enter.
- 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 on 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 width of the column (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. Active cell is always part of the selected 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.
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 in a variety of formats: general, monetary, financial, percentage, etc. Date and time can also be thought of 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 entry
- Click the cell.
- Enter details, e.g. Report 2001.
- Press the key Enter.
- 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 on 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 width of the column (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 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 the following rows |
Non-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 in 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 over current line.
Multiple columns and rows can be added when selecting an area that includes more than one column or row.
- Select as many columns or rows as you need to insert.
- Select Insert, Rows or Insert, Columns or press the key combination Ctrl and + on the numeric keypad.
To delete rows or columns:
- Select the rows or columns to delete.
- Select Edit, Delete (Edit, Delete) or press a combination Ctrl keys 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) in 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, they can be pasted into a single cell or into an area of cells repeatedly. 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 ( 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
- Select the area of cells to move.
- Move the mouse pointer over the selection frame.
- Drag the selection using the pointer to a new location. The cell area will be moved to a new place.
- 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.
- Select the cell or cells to copy.
- Select the cell or cells where the source data will be placed.
- Select Edit, Paste Special. The Paste Special dialog box contains several options for pasting data (Figure 83).
Rice. 83. Special insert
- Set the necessary options, such as formats (when you colorize formats, only the formatting changes, not the meaning of the cells).
- 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 of 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., if you make a special copy, 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
- Select the area to copy A2:A4.
- Select Edit, Copy.
- Click cell B2 (upper left corner of the area where the data will be placed).
- Select Edit, Paste Special.
- Set the Multiply option.
- Click OK. Please note that the border of the selection area remains on the screen.
- Click cell C2, which will be the start of the insertion area.
- Select Edit, Paste Special and set the Transpose option.
- 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