3 Data entry and editing
Entering data (text, numbers, formulas) is one of the main activities in Excel. In short it comes down to:
- Select cell.
- Start typing.
- End with Enter key.
Beware, if the cell already contains content, this content will be overwritten
After using the Enter key the cell pointer moves automatically one cell downwards.
3.1 Language-dependent number formats
Separators for decimals and thousands depend on the Windows language, but can also be changed in Excel itself. By default, Excel uses the number formats as defined in Windows with the following characters.
- English
-
Decimal separator point:
12.34 -
Thousands separator comma:
12,345 - Dutch
-
Decimal separator comma:
12,34 -
Thousands separator point:
12.345
When you want to change these settings permanently, it must be done through the regional settings in the control panel of Windows.
Fortunately, you can temporarily change these settings in Excel. To do this, follow these steps.
- Click File > Options > Advanced
- Under “Editing options” clear the checkbox Use system separators.
- ype new separators in the Decimal separator and Thousands separator boxes.
When you want to use the system separators again, select the Use system separators checkbox.
3.2 Data entry
A simple exercise in data entry.
Task 3.1
Select cell A1.
Type
homeand press Enter.
The word home is left-aligned in the cell and the cell pointer has moved one cell down so now cell A2 is the active cell.
- Type
Sales 1e quarterand press Enter.
The text is left-aligned in the cell, does not fit within the cell boundaries, and therefore seems partly to be in B2, which is not true. It only looks like that because cell B2 is empty. The active cell is now A3.
- Type
89and press Enter.
The number is right-aligned in cell A3. The active cell is now A4.
- Type
12.34and press Enter.
The number is right-aligned in the cell A4. The active cell is now A5.
- Type
5,6and press Enter.
The content of the cell is left aligned. This is because of the comma between 5 and 6. Excel interprets the entry as text and therefore is left-aligned. The active cell is A6.
- Type
1/2/2011and press Enter.
The date is right-aligned in the cell. That’s because Excel interprets dates as numbers, treats them as numbers, but gives them a special date format. The active cell is now A7 .
- Type
=2+3and press Enter.
The cell now displays the number 5. That’s because Excel treats all data that starts with= as a formula, calculates the result of the formula, and displays the result. The content of the cell is always the formula and not the result. The active cell is now A8.
- Select cell B2, type
10%and press Enter.
The content in cell B2 is right-aligned. Because of the percent sign, Excel has interpreted the data as a number. The real content of this cell is therefore 0.1 and this content is displayed as a percentage. And because cell B2 is no longer empty, the text in cell A2 is not fully shown.
- Select cell A2.
In the formula bar, you can see the fully entered text is still in the cell.
It is possible to adjust the column width so that all the text in cell A2 will be displayed.
Summary
- By default, the text is left-aligned.
- By default, numbers are right-aligned.
- There is an important difference between commas and points in numbers.
- Formulas always start with the
=character. - An input of a date will be treated as a number and formatted as a date.
- An input of a number with a percent sign will be treated as a number and formatted as a percentage. The cell content is the hundredth part of the number entered.
- A cell has a content and a format. What is displayed is not always real content.
3.3 Changing cell content
The data in a cell can be edited in one of the following ways:
- Double click the cell. The cursor will be placed in the middle of the cell and the content can be edited directly within the cell.
- Select the cell and press F2. The cursor will be placed at the end of the cell content and the content can be edited.
- Select the cell and click in the formula bar on the place where you want to start editing.
When the cell is in editing mode, the mode on the status bar (bottom left) has been changed from Ready to Edit. And furthermore, three icons on the left of the formula bar are activated:
The first to undo, the second to confirm the entry and the third to enter a function.
The operation must be terminated with the Enter button to change the mode in Ready.
3.4 Undo changes
From almost every action you perform in Excel it is possible to undo it. This is done using the following buttons on the Quick Access Toolbar.
Undo- Reverses the last action. By clicking this button again you will undo the previously performed task. Besides this button there can be an arrowhead, clicking on it shows a list of the most recent actions. By clicking on an action in this list, all changes up to and including this action will be undone.
Redo- Can redo the actions you undid. Besides this button, there can be an arrowhead with a list of the most recent actions.
3.5 Inserting rows and columns
It often happens that you want to insert a row or a column somewhere in between data in a worksheet. The process is the same for rows and columns.
- Right-click on the header of the row or column.
- Choose Insert.
To insert multiple rows or columns, select the desired number of rows/columns, right-click, and choose Insert. Then the number will be inserted before the first row or column of the selection.
3.6 Copy and paste
Copying the content of cells is a common action in Excel. You can copy one cell or a whole area. By default, both the content and the format of the cell will be copied. However, it is also possible to copy only the content or only the formatting. If the cell contains a formula with cell references then the cell references in the copied formula will be adapted to the new situation.
Task 3.2 File: Cellformat.xlsx
Open the file.
Select the area A1:B13.
Choose Home > Copy (group Clipboard).
Select the starting cell of the destination, e.g. D20.
Choose Home > Past (group Clipboard).
3.7 Auto fill
How to enter data that has a pattern quickly in your worksheet.
Excel has several options to quickly fill a row or column with data that follows a pattern or is based on lists. For example, Excel has built-in lists for the days of the weeks and the months of the year. But you can also make your custom lists through the options of Excel. Another possibility is to create a pattern that Excel recognizes. When filling the cells with new values Excel makes use of this pattern. Often two values for pattern recognition are sufficient.
To quickly fill in data series, you can use the fill handle. This is the black square in the lower right corner of the selection. When you move the mouse pointer over the fill handle, the shape of the pointer changes from 
in 
and then you can drag.
In the next exercise, a built-in list is used first and then a pattern recognition. At the end are examples with which you can experiment.
Task 3.3 Built-in list
Start with a new worksheet.
Select cell A1 and type the word January.
Select cell A1 again. Position the mouse over the fill handle of cell A1. Press the left mouse button and drag to cell A12 and then release the left mouse button.
.
The result is that the area A1: A12 contains the months of the year. You can start with every month. If you keep on dragging, the sequence will repeat itself.
Pattern recognition
Select cell B1 and type the number 1.
Select cell B2 and type the number 2.
Select the area B1:B2 and drag the fill handle some cells downwards.
.
The result is that the area is filled with the numbers 1, 2, 3, 4, 5, …
In Table 3.1 you see some examples to experiment with.
| Starting values | Auto fill series |
|---|---|
| 1, 3 | 5, 7, 9, 11, … |
| 2, 4 | 6, 8, 10, 12, … |
| 3, 6 | 9, 12, 15, 18, … |
| 2010, 2011 | 2012, 2013, 2014, … |
| 09:00 | 10:00, 11:00, 12:00, 13:00, … |
| Jan | Feb, Mar, … |
| Jan, Apr | Jul, Oct, Jan, Apr, … |
| Monday | Tuesday, Wednesday, … |
| Mon | Tue, Wed, … |
| Q 1 | Q 2, Q 3, Q 4, Q 5, … |
| 1e period | 2e period, 3e period, … |
| article 1 | article 2, article 3, article 4, … |