Entering, Editing, and Managing Data

3.4 - ENTERING, EDITING AND FORMATTING DATA

Excel treats different types of data differently. You enter all kinds of data in a cell in the worksheet. An Excel workbook can hold any number of worksheets and each worksheet is made up of more than seventeen billion cells. Each cell can hold any of the following three types of data:

A numeric value : It can be numbers (example 300.40), dates (example 4-Feb-2011) or times (example 3:35 am). There are many different format options available in Excel for the display of numerical values.
Text : Text in Excel can be used as labels for values, headings for columns or worksheet or for any kind of instructions. Text that begins with a number is still considered as text.
A Formula Spreadsheet : Formulas can be entered in a cell where eventually the result of the formula is displayed. We will study more about formulas later in this unit.

A worksheet can also hold charts, diagrams, pictures and other objects. These objects aren’t contained in cells. Rather, they reside on the worksheet’s draw layer, which is an invisible layer on top of each worksheet.

In order to enter or edit data in a cell, that cell must be current. Excel indicates that a cell is current in following ways:

A dark black border (called the cell cursor) appears around the cell.
The cell address appears in the Name box of the formula bar. A cell address is combination of Column Letter(s) and Row number that intersect at that cell position. For example, if the cell address is A3, it means it is at the intersection of column A and row 3.
The cell column heading (letters) and row heading (number) is shaded for that particular cell.

3.4.1 Moving around the Worksheet

Excel has many ways to move the cell cursor around the worksheet to the cell where you want to enter new data or edit existing data:

Click the desired cell, provided the cell is displayed within the visible section of the worksheet area.
In case, cell is not visible, then you may use horizontal or vertical scroll bars to move to that part of the worksheet that contains the desired cell.
Press F5 to open the Go To dialog box. Type the cell address in the reference and press Enter or click OK. The cell cursor moves to the desired address.
Press CTRL+G. This again opens Go To dialog box.
Click in the Name box of formula bar and enter the address of the desired cell. Preas Enter. Cursor moves to the specified cell.
You can also use the arrow and tab keys as specified below to move the cell cursor to the desired cell:

3.4.2 Selecting Cells, Rows or Columns

If you wish to perform a function on a group of cells, you must first select those cells by highlighting them.

3.4.2.1 Selecting Cells

You can either use a mouse or a keyboard to select the desired group of cells:

Using Keyboard: To select cell A1 to C5 –

Go to cell A1.
Press F8. This anchors the cursor and ‘Extend Selection’ is displayed in the Status bar.
Use arrow keys to select the desired cell area
Press Escape (Esc) to come out of the Extend mode.

Using Mouse: To select cells A1 to C5, as shown in figure 8 –

Click on cell A1.
Keep the left mouse key pressed and drag the mouse to C5.

You can also select noncontiguous area of the worksheet using mouse. Press Ctrl Spreadsheet key along with the left mouse key while dragging to select the cells.

To select a particular row or a column, just click on that particular row or column heading. For example, if you want to select row number 3, then just click on number 3 in the row heading and the entire row will be highlighted. When you take the cursor over the row heading, then it changes to a right arrow. Similarly, when you take the cursor over the column heading, then it changed to a down arrow.

3.4.3 Entering Data

There are different ways to enter data in Excel: in an active cell or in the formula bar.

To enter data in an active cell:

Click in the cell where you want the data. Begin typing. Note that the text appears in formula bar also.

To enter data into the formula bar:

Click the cell where you would like the data
Place the cursor in the Formula Bar
Type in the data in the formula bar

Please note that you can use Alt+Enter to go to next line within a cell. Alt+Enter in a cell works similar to Enter key in a word document.

3.4.4 Deleting Data

Select the cell(s).
Press the Delete key to delete the entire contents of a cell(s). OR
Double click in a cell. The insertion point appears in the cell. Press Backspace to delete one character at a time. Press Enter to confirm changes.

You can also make changes to and delete text from the formula bar. Just select the cell and place your insertion point in the formula bar and use backspace or select the whole text and use delete.

3.4.5 Editing Data

To change entire contents of a cell:

Select the cell and start typing the new data.
Press Enter to confirm the change.

To modify a part of the cell,

Select the cell and switch to edit mode. You can switch to edit mode by following ways: 3
Press F3 once you have selected the cell. The Status changes to ‘Edit’ from ‘Ready’ in the status bar. OR-
Double click in the cell to switch to edit mode.

Once you have made your changes, press Enter to confirm changes or press Esc to cancel changes.

You can also make changes in the Formula bar. Select cell. Click in the formula bar. Make the required changes. Press Enter to confirm or press Esc to cancel changes.

3.4.6 Working with Cells, Rows and Columns

Copy/ Cut and Paste

If you need to duplicate data in some cell(s), you can use copy & paste option. In case you need to move the data from one cell to another, then you use cut & paste option.

To copy data:

Select the cell(s) that you wish to copy. This is the source location.
On the Clipboard group of the Home tab, click Copy OR use Ctrl+C OR select Copy option from menu that appears when you right mouse click on the selected cell(s). The border of the selected cell(s) will change appearance and the data from the selected cell(s) is copied onto the clipboard.

To cut data:

Select the cell(s) that you wish to cut. This is the source location.
On the Clipboard group of the Home tab, click Cut OR use Ctrl+X OR select Cut option from menu that appears when you right mouse click on the selected cell(s). The border of the selected cell(s) will change appearance and the data from the selected cell(s) is copied onto the clipboard.

To paste data:

Once you have copied or cut data from the source location, you paste it to the destination location.

Select the cell(s) where you would like to paste the data. This is the Spreadsheet destination location.
On the Clipboard group of the Home tab, click Paste OR use Ctrl+V OR use right mouse click menu option. The source information will now appear in the new destination cells.

If you use cut, then the information at the source location is removed automatically after the paste operation has been performed. If you use copy, then you have same information at both source and destination locations. Also, in case of copy, the copied information remains selected with changed border (even after the paste operation), until you perform next action or press Esc or double click the selection to deselect it.

Drag and Drop

Drag and drop works similar to cut and paste that you move information from one cell(s) to another. To drag and drop data from one point to another:

Select the cell(s) that you wish to move.
Position your mouse pointer near one of the outside edges of the selected cells. The mouse pointer should change from a white, block cross to a black, thin cross with 4 arrows.
Click and hold the mouse button and drag the cells to the new location. As you drag the selected cells, the outline of the cells will change.
Release the mouse button and the information appears in the new location.

Please note that for drag and drop to work, it should be enabled in Excel Options.

Insert Cell

You can insert a cell either above a cell or to the left of a cell. Keeping this in mind,

Select the appropriate cell.
Click arrow on Insert command from Cells group in the Home tab. If you click on the Insert button, a cell is inserted above the selected cell. But, if you click the arrow then a menu opens.
Choose Insert Cells option. Insert dialog box opens.
Choose the appropriate option. OR-
Select the appropriate cell.
Right mouse click on the cell. A menu opens.
Select Insert… option from the menu. Insert dialog box opens
Choose the appropriate option

Insert Row or Column

You can insert a row above a particular row or a column to the left of a particular column. While keeping this in mind,

Select a cell in the appropriate row/ column.
Either use right mouse click OR Insert command in Cells group of the Home tab on the Ribbon (as done to insert a cell above).
In the Insert dialog box choose the appropriate option for row/ column.
Press right mouse button on the row number (above which you want to insert a row) in row heading on left of the worksheet OR press right mouse button on the column letter in column heading (left of which you want to insert the column) at the top of the worksheet. A menu opens.
Choose insert option from the menu. A row is added above the selected row OR a column is added to the left of the selected column.

Delete Cell, Row or Column

To delete cells, rows, and columns:

Place the cursor in the cell, row, or column that you want to delete
Click the Delete button on the Cells group of the Home tab
Click the appropriate choice: Cell, Row, or Column OR
Use right mouse click on the cell, row number in row heading or column letter(s) in column heading.
Choose Delete option from the menu.

Modify Column Width

There are various ways that you can use to modify column width:

Position the cursor over the column line (line that divides the two columns) in the column heading. A horizontal double arrow will appear.
Click the mouse and drag Spreadsheet the cursor to the right to increase the column width or to the left to decrease the column width. OR-
Release the mouse button.
Click the column heading of a column you wish to modify. The entire column will be highlighted.
Click the Format command in the Cells group on the Home tab. A menu will appear.
Select Column Width to enter a specific column measurement
select AutoFit Column Width to automatically adjust the column so all the text will fit.
OR- Right mouse click the column heading. A menu will appear
Select Column Width… from the menu
Enter the specific column measurement

Modify Row Height

There are multiple ways that you can use to modify row height:

Position the cursor over the row line (line that divides the two rows) in the row heading for the row you want to modify. A vertical double arrow will appear.
Click the mouse and drag the cursor upward to decrease the row height or downward to increase the row height.
Release the mouse button. OR
Click the row heading of a row you wish to modify. The entire row will be highlighted.
Click the Format command in the Cells group on the Home tab. A menu will appear.
Select Row Height to enter a specific row measurement OR select AutoFit Row Height to automatically adjust the column so all the text will fit.
OR- Right mouse click the row heading. A menu will appear.
Select Row Height… from the menu.
Enter the specific row measurement.

Hide or Unhide Rows or Column

To hide or unhide rows or columns:

Select the row or column you wish to hide or unhide.
Click the Format button on the Cells group of the Home tab. A menu appears.
Under Visibility heading, click on Hide & Unhide option.
Choose the appropriate option from sub menu that appears: Hide Rows or Hide Columns or Unhide Rows or Unhide Columns as per the requirements.

3.4.7 Data and Formatting .

Auto Fill

Auto Fill feature fills cell data or series of data in a worksheet into a selected range of cells. If you want the same data copied into the other cells, you only need to enter data in one cell. If you want to have a series of data (for example, serial number) fill in the first two cells in the series and then use the auto fill feature. To use the Auto Fill feature:

Enter the required data in the cell. For example, if you wish to enter 1 in all cells from A1 to A10, then just type 1 in cell A1. Similarly, if you wish to enter numbers 1 to 10 in cells A1 to A10 then enter 1 in A1 and 3 in A3.
Now select the cell(s) with value(s) (just A1 OR both A1 and A3 depending on the case)
Bring your cursor at the bottom right corner of the selection so that it changes from large white cross to a small, thin, black cross. Now the cursor is positioned over the fill handle.
Click your mouse at the fill handle and drag it till all the cells you want to fill are selected (till A10 in our example).
Release the mouse button and all the selected cells are automatically filled.

You can use the fill handle to fill cells horizontally or vertically.

Aligning Values

In Excel, the data in a cell can be aligned both horizontally and vertically. The default horizontal alignment is left for the text data and right for the numerical data. Vertically, both text and numerical data are bottom aligned. You can change the default alignments as per your requirement:

The steps to change alignment are:

Select the cell(s) for which alignment needs to be changed.
Choose horizontal/ vertical alignment command from the Alignment group in the Home tab.

In the Figure 3.9, you can see the how the data can be positioned in a cell by choosing the appropriate combination of horizontal and vertical alignment values.

Wrap Text

Wrap text command enable multiple text line inside a single cell. When you type text that is too long to fit in the cell, either the text is not displayed completely or it overlaps the next cell if it is empty. If you do not want it to overlap the next cell, you can wrap the text.

Select the cell(s) where you wish to wrap the text.
Click on Wrap Text command from Alignment Group in Home Tab. The text would change to multiline text.
Set the column width and row height as needed.

Merge Cells

Sometimes, you may want to center a piece of text over several columns or rows. This is specially the case when you want to give a title to a section of your worksheet. This can be done through merging the cells (shown in Figure 3.11).

Select the cell you want to merge. In our example in Figure 3.11, we have merged cells A1 to E1 for the title ‘Student Marks’.
Click Merge and Center Command in Alignment group of Home tab. The selected cell will be merged together to become one cell (the cell address now will be A1).

In Figure 3.11, we have also merged cells, G5 to I6 for the Note. To do this, again

Select the cells.
Click the arrow for Merge and Center command. The menu opens.
Choose Merge Cells option from the menu.

In case, you want to unmerge the merged cell for any reason:

Select the merged cell.
Click the arrow for Merge and Center command. The menu opens.
Choose Unmerge Cells option from the menu. The cells will be unmerged.

Format Data as Bold, Italicized, Underlined or Double Underlined

The data in the spreadsheet can be formatted to make it more presentable. For formatting:

Select the cell(s).
Either click the appropriate command(s) (Bold, Italic, Underline, Double Underline ) in Font group of the Home tab OR use keyboard shortcuts as below:

Command Keyboard Shortcut

Bold Ctrl+B I

talicize Ctrl+I

Underline Ctrl+U

For double underline format, click the down arrow next to Underline command. Choose Double Underline from the menu that opens.

In our example in figure 11, the Headings are in bold and Note is in italics.

Change Font Style

To change the font style,

Select the cell(s).
Click the drop-down arrow next to the Font Style box on the Home tab.
Select a font style from the list.

As you move over the font list, the Live Preview feature previews the font for you in the spreadsheet.

Change the Font Size

To change the font size,

Select the cell(s) you want to format.
Click the drop-down arrow next to the Font Size box on the Home tab.
Select a font size from the list.

Change the Text Colour:

To change the Text Colour,

Select the cell(s) you want to format
Click the drop-down arrow next to the Font Color command. A color palette will appear
Select a color from the palette. OR
Select More Colors…. A dialog box will appear.
Select a color.
Click OK.

Add a Border

To add border(s),

Select the cell or cells you want to format.
Click the drop-down arrow next to the Borders command on the Home tab. A menu will appear with border options.
Click an option from the list to select it.
You can change the line style and color of the border

In Figure 3.11, we have added thick border to the title and borders to the table, column heading and the note.

Add a Fill Color

To change the Text Colour,

Select the cell or cells you want to format.
Click the Fill command. A color palette will appear
Select a color OR
Select More Colors…. A dialog box will appear.
More Colors
Click OK.

You can use the fill color feature to format columns and rows, and format a worksheet so that it is easier to read.

Format Numbers, Dates and Times

Excel treats dates and times as special type of numeric value. You can format any of these data as per your requirements:

Select the cell(s) you wish to format.
Click the drop-down arrow next to the Number Format box in Number Group in Home tab.
Select one of the options for formatting numbers.

By default, the numbers appear in the General category, which means there is no special formatting.

In the Number group, you have some other options. For example, you can change the another currency format, set numbers to percents, add commas, and change the decimal location.

When you click on the dialog box launcher (arrow at the bottom right corner of the group), the Number tab of the Format Cells dialog box opens as in Figure 3.13. From here you can choose the specific format for number, date or time.

Please Note: For any of the above formatting options, you can also right mouse click in the selected cell(s) and then choose Format Cells… option from the menu that opens. The Format Cells dialog box will appear with different tabs for number formatting, alignment, font, border, fill and protection.

You can also use Format Painter in Clipboard group of Home Tab to copy format from one cell to another.

Entering, Editing, and Managing Data