Excel2010

2.2 Cell Operations

2.2.1 Selecting a Cell or an Area

When you see the Select type of mouse pointer and click a cell, Microsoft Excel makes it the Active cell. When you click a cell and drag the mouse to another cell, all the cells between them will be selected as a range.

Press Ctrl+A on an empty area to select all the cells in a worksheet. If the active cell indicator is in a list (table), Ctrl+A will only select the table.

2.2.2 Entering data in a cell

In order to enter data or formula in a cell, select it and type what you need followed by Enter key. If you need to type multiple lines of text in the same cell, press <Alt+Enter> at the end of each line.

By default, when you press the Enter key, Excel automatically moves the cell pointer to the next cell down. To change this setting, choose Office Excel Options and click the Advanced tab. The check box that controls this behavior is labeled: “After Pressing Enter, Move Selection”. You can also specify the direction in which the cell pointer moves (down, left, up, or right). Your choice is completely a matter of personal preference.

If you want to cancel your data entry and return the cell to the previous state, press Esc key.

Figure 2.2: Moving selection, after entering data

2.2.3 Using arrow keys instead of pressing Enter

When you’re finished making a cell entry, you can also use any of the direction keys to complete the entry. Not surprisingly, these direction keys take you in the direction that you indicate. For example, if you’re entering data in a row, press the right-arrow key rather than Enter. The other arrow keys work as expected, and you can even use PgUp and PgDn.

Worksheet and Cell Operations

21

You can also use the F8 key to select a range. First, activate the first cell of the selection and press F8. Then, using your keyboard or mouse, select the last cell. Press F8 again to finish selection.

Press [Ctrl + Spacebar] to select the active column and [Shift + Spacebar] to select the active row.

Figure 2.3: AutoComplete

2.2.4 Moving Through a Selected Area

In order to move throughout a selected area, after entering the data or formula, without changing the selected range, four combinations can be used;

1.<Enter> : Normally; the active cell moves to the next cell down.

2.<Shift+Enter>: the cell above becomes the active cell.

3.<Tab> : the right cell becomes the active cell.

4.<Shift+Tab> : the left cell becomes the active cell.

On all of these options, after you reach to the end of a column or row, if you again press the same key, the active cell goes automatically to the beginning of the next column or row.

2.2.5 Selecting multiple cells, rows or columns

Using the SHIFT key and mouse (or with arrow keys), you can select multiple consecutive cells, rows, or columns. For this, first you select the initial cell, then hold down the SHIFT key, using arrow keys or mouse, select the final cell. Excel will automatically select all the cells between the first and last locations.

Using the CTRL key and left mouse click, you can select multiple cells from different places. You can use the same method with rows or columns.

2.2.6 Entering data in an area

After you select your data range, using the four combinations that we discussed in Section 2.2.4, you can move through and enter your data. In place of ENTER, use <Ctrl+Enter> to fill all the selected range with the same text.

When you enter information in the same column Excel provides an easy tool: AutoComplete. When you enter an item in a list Excel checks the rows above it. If there is another item starting with the same characters, it completes the rest of the word for you. If you want to enter a different word just continue. But, if you want to enter the suggested word here, just press enter.

Example 2.1: Prepare your class list for informatics marks. Select the marks as a range and enter marks for all students using Ctrl+Enter.

22	Microsoft Excel

2.2.7 Entering numbers with fractions

To enter a fractional value into a cell, leave a space between the whole

number and the fraction. For example, to enter

(three and a half), enter

3 1/2 and then press Enter. When you select the cell, 3.5 appears in the

Formula bar, and the cell entry appears as a fraction.

If you have a fraction only (for example, 1⁄8), you must enter a zero first, like

this: 0 1/8—otherwise, Excel will likely assume that you’re entering a date.

When you select the cell and look at the Formula bar, you see 0.125. In the

cell, you see 1⁄8.

2.2.8 Modifying Cell Contents

After you enter a value or text into a cell, you can modify it in several ways:

Erase the cell contents

Replace the cell contents with something else

Edit the cell contents

To erase the contents just select the range of cells and press the Del button on the keyboard. Or for replacing, just select the cell and type your new data. But for modifying the contents we have something more to talk about.

If the cell contains only a few characters, replacing its contents by typing new data is usually easier. But if the cell contains lengthy text or a complex formula and you need to make only a slight modification, you probably want to edit the cell rather than re-enter information. When you want to edit the contents of a cell, you can use one of the following ways to enter cell-edit mode:

Double-clicking the cell

Selecting the cell and pressing F2

Selecting the cell that you want to edit and then clicking inside the Formula bar

enables you to edit the cell contents. You can use whichever method you prefer. Some people find editing directly in the cell easier; others prefer to use the Formula bar to edit a cell.

Figure 2.4:

Entering numbers with fractions

Worksheet and Cell Operations

23

Figure 2.5a

Figure 2.5b:

Resizing columns

Figure 2.6: Delete dialog box

2.3Cell, Row or Column Operations

2.3.1 Resizing and auto sizing rows columns:

Using the Resize mouse pointer, you can change the width of columns and height of rows. First select the column(s), then, when you see the Resize mouse pointer drag to the width that you want to.

If you select multiple rows (columns) with either the CTRL or SHIFT keys, and then you change the height of one row, MS. Excel automatically applies the same height to all other selected rows.

For auto sizing rows or columns, after you select your range, move your mouse pointer to the right border of a row or column, when you see the Resize mouse pointer, double click on it. You can also AutoFit the column width for only the selected range: Home Cells Format AutoFit Column Width.

To set the row height precisely, select the rows first and then select Row Height from Format group. It will show you an input box. Instead of trying an approximate value, you can write a fix value in pixels for the height or width.

2.3.2 Hiding and Unhiding Cells

When hiding rows or columns, they physically exist but their height or width is made zero, so that they are not visible. Using the Resize mouse pointer, you can set the width of a column to zero and hide it. Or, from the popup menu, you can select the Hide command to do the same operation. Later, they can be shown, using Format Hide & Unhide button in Cells group in Home Tab.

2.3.3 Deleting – Inserting

a. A cell or a group of cells

When you want to delete a cell itself (not the content of that cell, all the cell itself and contents physically) right click on it and select Delete from the popup menu. Because the cell will be deleted physically, like a wall of bricks, the space cannot be empty, other cells will fill in the space.

In this case, after deleting the selected range, you will have four options, in order to fill the space. It will,

1.Shift cells left: move the cells on the right to left

2.Shift cells up: move the bottom cells up

3.Entire row: delete the entire row(s) and move all of the bottom rows up.

4.Entire column: delete the entire column(s).

24	Microsoft Excel

When inserting cells, the process is similar to deletion. In order to add / open new physical space, some cells need to be moved. You can move right, or down, or you can insert an entire row or column. If you insert a row then all the rows will be moved one down. If your last row contains data, it will ask you to move this data into a different location or clear it and try again.

b. Rows or columns
There is another method to delete rows. After selecting the rows heading that	Figure 2.7: Insert cells dialog box
you want to delete, right click the selected area and select Delete. It will
directly delete the selected rows or columns.

2.4 Cut, copy, paste operations

Cut, Copy, and Paste operations are similar to other Windows applications. After you select a range, right click on the selected area. From the popup menu, select Cut or Copy. The cells are copied into the office clipboard and ready to be pasted. Just select the starting cell of the destination, and then from the popup menu select Paste. This will paste all data and formats of the source to the destination.

2.4.1 Paste Special

Paste special is one of the most efficient features of Excel. In many conditions, you cannot copy all: formats, data, formulas, etc. to the destination. Sometimes, you want to copy only the values or formulas or comments. For this purpose, Paste Special offers many useful options for users. Most of them are clear in the meaning and don’t need any further explanation.

1.All: Pastes all, which is the same as regular paste.

2.Formulas: Pastes just the formula while adjusting the formula according to the destination (See Absolute and Relative Reference in Section 5.3.2).

3.Values: Pastes only the resulting values of formulas.

4.Formats: Paste only the format.

5.Comments: Paste only the comments.

6.All using Source theme: Pastes all cell contents in the document theme formatting that is applied to the copied data.

Operation

When having numerical values, you can use Paste special to make arithmetic operations. For example, you can copy a range over another range and select the Multiply operation. Excel multiplies the values in the source and the destination ranges and replaces the destination with the new values.

Figure 2.8: Paste special dialog box

Figure 2.9:

Paste special button options

Worksheet and Cell Operations

25

Figure 2.10: Deleting a worksheet

1

2

3

Figure 2.11: Move or copy worksheet dialog box

1.None: No operation

2.Add: the source value is arithmetically added to the destination.

3.Subtract: The source value is subtracted from the destination.

4.Multiply: Multiplies the source and destination values.

5.Divide: Divides the destination by the source value.

Skip blanks: It doesn’t paste anything over the destination if the source cell is empty.

Transpose: Shifts the vertical and horizontal orientation of the cell range. If your cells are horizontally adjacent, it will rotate them to a vertical list.

Paste Link: Pastes the cell link formula to the destination so that when you change the source, the destination is also changed.

2.5 Worksheet Operations

2.5.1 Deleting a worksheet:

If you are sure to delete the Entire worksheet, right click on the worksheet name then select Delete from the popup menu. There is no undo after you delete a worksheet and you cannot get your data back, Excel will ask whether you are sure to delete or not.

2.5.2 Renaming a worksheet:

In order to rename a worksheet, right click on the worksheet name and then select Rename from the menu. Or you can also double click on the worksheet name and make your change.

When renaming worksheets you should know these details:

Sheet names can be up to 31 chars (characters)

Spaces are allowed and each is counted as one char

Following chars are not allowed in sheet names: (?) Question mark, (*) Asterisk, (:) Column, (/) Slash, (\) Backslash

2.5.3 Moving or copying a worksheet

This option lets you Move or Copy the selected worksheet to a different location in the same file or in another file. To Move or Copy a worksheet, right click on its name, then select Move or Copy… The Move or Copy dialog box

will open. From To Book: combo box 1 , you can select to which workbook

to copy. If you check Create a copy 3 , the source worksheet will be copied to the new location. Otherwise, it will be moved to.

The list box in the middle 2 shows the worksheets of the selected workbook. The source worksheet will be inserted before the selected sheet.

26	Microsoft Excel

2.5.4 Inserting an empty worksheet

Sometimes, you may need a new worksheet. Right-click on a worksheet name, and from the popup menu select Insert. Excel will open the Insert window.

There are two tabs in this window. The general tab shows general options: Dialog, Chart, Macro or Worksheet. If you select the Worksheet option, it will insert an empty worksheet. If you select the Chart option, it will show necessary tools to prepare a chart and so on. The chart, macro and dialog box options will be studied later.

The Spreadsheet solutions are ready to use, predesigned Excel workbook templates. Like: Personal monthly budget, etc. They are from MS. Office and you can find many more on

Office Online. Figure 2.12: Insert dialog box

2.5.5 Selecting Multiple Worksheets

Similar to selecting multiple cells, using the CTRL and SHIFT keys you can select multiple worksheets. When selecting an adjacent group of worksheets, first select the starting worksheet. Then while holding down the SHIFT key, select the last sheet of the adjacent list. All of the worksheets between these two will be selected.

When selecting nonadjacent worksheets, select the first worksheet and then hold down CTRL and click the other worksheets one by one.

After selecting multiple worksheets, your formatting and cell entries or column row operations are applied to all selected sheets.

2.5.6 Changing the active worksheet

Using the <Ctrl+PgUp> or <Ctrl+PgDn> keys, you can activate different sheets. Or, using the navigation buttons, you can move through the worksheets and use the mouse to activate the worksheet that you want.

If you want to select multiple worksheets, hold down the CTRL or SHIFT keys and use the Page Up or Page Down and Space bar keys.

2.5.7 Hiding or Unhiding a worksheet

Hiding sheets in some situations can be useful. When you design a workbook and you don’t want users to see your program details, you can hide it. You can (un)hide worksheets from Home Cells Format Hide&Unhide. When you select Hide, selected sheet(s) are hidden. Later, they can be shown from the same place using the Unhide command. (Or, right click on the Sheet Tabs and select Hide or Unhide.)

When you work on multiple workbooks you can switch between workbooks using Ctrl+Tab or Ctrl+F6.

Figure 2.13: Hiding sheets

Worksheet and Cell Operations

27

Figure 2.14: Sheet properties

If the Developer tab’s not visible, you can show it from Excel Options Customize Ribbon.

In order to not permit others to see and make changes on your hidden sheets, you need to protect your workbook from: Review ChangesProtect workbook, and click the Structure option in the dialog box.

We have another hide option since Excel 2007.

Display the Properties window from the Developer Controls tab.

Select the sheet that you want to fully hide from the combobox above.

Then Select Veryhidden option from the options.

Be careful that the sheets cannot be shown using these normal ways. Because they are made very hidden and will not appear any more in the Properties window. But, you can unhide those using macros that we’ll discuss in Chapter 9, using following statement:

ActiveWorkBook.WorkSheets(“Sheet1”).Visible = True

28	Microsoft Excel

Questions

1.Without using the mouse or the arrow keys, what is the fastest way of getting to cell A1 in a spreadsheet?

a. <Home>	b. <Shift+Home>
c. <Ctrl+Home>	d. <Alt+Home>

2.How do you select an entire row?

a.View>Select>Row from the menu.

b.Click the Row heading.

c.Hold down the CTRL key as you click anywhere in the row.

d.Hold down the SHIFT key as you click anywhere in the row.

3.What is <Shift+Tab> used for?

5.Which key combination is used to change the active worksheet?

a.<Ctrl+Page Up>

b.<Shift+Page Up>

c.<Ctrl+Spacebar>

d.<Alt+Page Down>

6.To select multiple cells, which keys are held down while clicking the mouse?

a.ALT or SHIFT

b.ALT or CTRL

c.SHIFT or CTRL

d.ALT and SHIFT or CTRL

a.Moves the active cell indicator one cell down.

b.Moves the active cell indicator one cell up.

c.Moves the active cell indicator one cell right.

d.Moves the active cell indicator one cell left.

4.To delete a column,

a.Right click on the column heading then select Delete from the menu.

7.Which key is used to modify data in a selected cell?

a. F1	b. F2	c. F3	d. F4
8. To select all cells in a worksheet press,
a. <Ctrl+X>		b. <Alt+V>
c. <Ctrl+B>		d. <Ctrl+A>

b.Click on a column name and then press the DELETE key.

c.Select the cells which you want to delete, and then press the delete key on the keyboard.

d.Select the cells which you want to delete, and then press <Ctrl+Spacebar>

9.Which of the following is not a way to complete a cell entry?

a.Click the Enter button on the formula bar

b.Press any arrow key on the keyboard

c.Press ENTER

d.Press INSERT

Worksheet and Cell Operations

29

10.Which of the following is different from the others?

a. Cell	b. Row
c. Column	d. Gridline

11.Which keystrokes must be used to copy selected cells?

a.<Ctrl+X> and <Ctrl+V>

b.<Ctrl+C> and <Ctrl+V>

c.<Shift+V> and <Ctrl+C>

d.<Ctrl+Z> and <Ctrl+V>

12.What key combination is used when entering a text in all the selected cells?

a.<Ctrl+Insert>

b.<Shift+Insert>

c.<Ctrl+Enter>

d.<Alt+Enter>

13.Which of the following is not a method for adjusting the width of a column?

a.Double-click the column header’s name.

b.Drag the column header’s right border to the left or right.

c.Select the column header and click the Column Width button on Home Cells Format.

d.Right click on the column header, select Column Width from the popup menu, and enter its new width.

14.Which of the following are true for inserting a row?

I.Right-click the row heading where you want to insert the new row and select Insert from the popup menu.

II.Select the row heading where you want to insert the new row then select Rows from the Insert tab.

III.Right click on the cell where you want to insert the new row, select Insert then select the Entire row.

IV. Select the cell where you want to insert the new row and select Home Cells Insert Sheet Rows.

a. I, III, IV	b. II, III
c. II, III, IV	d. I, II, III, IV

15.Which of the followings is not a way of deleting a column?

a.Right-click the column heading you want to delete and select Delete from the popup menu.

b.Select the column heading you want to delete and click Delete from Home Cells.

c.Select the column heading you want to delete and select the Delete Row button on the Developer tab.

d.Select a cell in the column which you want to delete, right click on it and select Delete then select Entire column.

30	Microsoft Excel