42. Enter Simple Data into a Spreadsheet
BEFORE YOU BEGIN
40 Create a New Spreadsheet
41 Open an Existing Spreadsheet
46 Edit Cell Data
47 Print a Spreadsheet
Often, entering worksheet data requires nothing more than clicking the correct cell to select it and then typing the data. The various kinds of data behave differently when entered, however, so you should understand how Calc accepts assorted data.
Calc works with the following kinds of data:
Labels Text values such as names and addresses, as well as date and time values.
Numbers Numeric values such as 34, 291, 545.67874, and 0.
Dates and times Calc accepts date and time values that you type in virtually any format.
Formulas Expressions that compute numeric results. (Some formulas work with text values as well.)
This task walks you through a short editing session just to give you a feel for entering data into a Calc sheet. Keep in mind that Calc's interface is different from most other programs you may have worked with, unless you've worked with electronic spreadsheet programs before. Having said that, some of the editing skills you acquire in one OpenOffice.org program apply to the other OpenOffice.org programs as well. For example, both Calc and Writer offer the capability to display or hide the Standard toolbar. The Standard toolbar changes slightly depending on which program you use, but most of its functions are similar across the OpenOffice.org programs.
Move the Active Cell
Click cell D5 to make D5 the active cell. The cell's dark outline indicates that the cell is selected. Also, the cell name appears in the Sheet Area .
| | Type Text in the Cell
Type ABC Co
. into cell D5
. To enter the text, simply type the text, and it appears both in the cell as well as in the Input
line toward the top of the screen. When you press Enter
, the active cell moves down one row. Instead of Enter
, you can press the right arrow
button or Tab
, and the cell to the right of the cell becomes active next .
42. Enter Simple Data into a Spreadsheet
By default, text always appears left-justified in a cell, although you can click one of the justification buttons to center or right-justify text in a cell. To correct a mistake, press Backspace
and type the corrected text.
You can change the action of the Enter key from the Options, OpenOffice.org Calc, General dialog box page.
If you press the Esc
button at any point during text entry but before you move to another cell, Calc erases the text you typed in the cell and restores the original cell. In addition, you can press Ctrl+Z
(for undo) or click the Undo
button to back up to a cell's previous state.
| | Type a Long Title
Replace the text in cell D5
by typing Fiscal Year Report for the 1st Quarter
. As you type, the text spills into cells E5, F5
, and finally ends in G5
. The important thing to note is that only cell D5
holds the text value Fiscal Year Report for the 1st Quarter
. Although it looks
as though pieces of the title spill into the cells to the right, Calc is showing the full text in cell D5
because no values appear to the right of D5
. If any data resided in cells E5, F5
, or G5
, Calc would not
have shown the full value in D5
You'll see that when you type 1st , Calc's AutoCorrect feature converts it to 1 st .
| | Type the Long Title Again
Move to cell D10
and begin typing the same title, Fiscal Year Report for the 1st Quarter
. As with cell D5
, the full text shows because nothing appears in the cells to the right of D10
Calc automatically completes the entry thanks to a feature called AutoInput
, which is similar to Writer's AutoComplete feature (see 31 About Writer's Automatic Correction Tools
). AutoInput matches your text to input found elsewhere in the column. You can accept the proposed completion by pressing Enter
or ignore the suggestion and continue typing.
To turn the AutoInput feature on and off, choose Tools, Cell Contents and then click the AutoInput check box to enable or disable the option.
Instead of typing the title twice, you could select the title and click Copy (or press Ctrl+C ). You then could paste the title where you want it by clicking the cell and then clicking Paste (or pressing Ctrl+V ).
If you ever want to see the full content of a cell that contains more data than can be displayed, click the cell and view its contents in the Formula bar.
Type the following numeric values into cells E10 and E11 , respectively: 56 and 45 . You'll see that as soon as you type cell E10 's value, the long text in cell D10 no longer displays. The long text is still in cell D10 , but Calc doesn't display the text because then you would be unable to see the number in cell E10 . Instead, it displays a small red triangle at the end of the cell to indicate that the cell contains more data than there is room to display.
Calc usually recognizes any entry that begins with an alphabetical character as text. Some textual data, such as price codes, telephone numbers, and ZIP Codes can fool Calc into thinking you are entering numeric data because of the initial numeric value. Calc treats numeric data differently from text data when you type the data into cells. If you want Calc to treat a number (such as a ZIP Code) as a text entry so that it does not perform calculations on the cell, precede the contents with a single apostrophe ( ' ). For example, to type the ZIP Code 74137, type '74137 ; the apostrophe lets Calc know to format the value as text. Knowing this enables you to enter text-based numbers such as ZIP Codes and product codes that require a leading zero. Without the quote, Calc interprets a value with a leading zero as a number and removes the zero and right-justifies the number.
Also notice that, unlike text values, Calc right-justifies numeric values. You'll also see when you enter formulas that Calc right-justifies the results of those formulas.
Type an Underline
You can put a line, composed of dashes, below the two numbers you just entered. Type - in cell D7 and press Enter . The dashed line doesn't look correct because Calc left-justified the line since the line is not numeric but text.
Edit the Underline
Click cell D7 to make it active. Then click the Align Right button that resides in the group of justification buttons and Calc right-justifies the underline.
| | Type a Formula
in cell E13
. You've just typed your first formula in Calc. The moment you press Enter
, Calc displays the formula's answer instead of the formula itself.
Click cell E13
to make it active again. You'll see the formula in the Input
line. So when you click a formula's cell, Calc shows you both the formula and the value. If you want to change the formula, either press F2
to display the formula once again inside the cell itself or click to edit the formula in the Input
For now, do not make changes to the formula but rather click cell E13 and press F2 . Calc colorizes the formula and highlights each formula value's cell so you can easily see the cells that comprise the formula. Press Enter to keep the current value.
Type July 4, 1776
Type July 4, 1776 in cell A4 .
Calc Converts the Date
Press Enter and Calc converts your date to a different format. Calc converts the date to 07/04/76 . Calc does retain the date's full value inside the cell, and only the date's display appears in the mm/dd/yy format.
Display Date Format Options
Calc supports almost every national and international date and time format. To determine which format Calc is to display dates (and times) in, select Format, Cells , click the Numbers tab, and then click the Date entry under Category . You can change the way Calc displays any cell's date by modifying its format. 60 Format Cells goes into detail about how to format cells. Click OK to close the dialog box.
Type 6:15 p.m.
Type 6:15 p.m. in cell A7 .
Calc Converts the Time
Press Enter and Calc converts your time to a different format. Calc converts the time to 06:15:00 PM , the time's full value in the hh:mm:ss AM/PM format.
Type the am or pm designation when entering time values or enter the time using a 24- hour clock. Either 6:15 pm or 18:15 work to enter the same time in the cell.