Entering Data in a Spreadsheet

EDS 2101/6423 Page 1 Learning Objectives

You will become familiar with text and data entry and several tools for manipulating this information. In this activity you will:

 Learn spreadsheet text and value entries and distinguish between these entries

 Enter various types of data including numbers, text, dates, and times

 Manipulate data by cell, row, and column

 Use some simple formulas

Start Excel Cells

Where a row and a column meet, a cell is formed. For example, at the meeting place of Column C and Row 4 is cell C4.

You can enter data into a cell by first selecting, or highlighting it. The cell that is currently selected is known as the active cell. When you select a cell, Excel surrounds it in with a thick black border:

Select a Cell

There are many ways to select a cell. The most common are:

 Click in the cell with the mouse.

 Move to the cell using the keyboard keys, such as the Tab and arrow keys.

 On the Home tab, in the Editing group, click the Find & Select drop-down arrow, then click Go To….

Enter Data

1. Select cell G5.

2. Type Test and on the keyboard, press Enter.

Delete Data from a Cell To delete data from a cell:

3. Select cell G5.

EDS 2101/6423 Page 2 4. On the keyboard, press Delete.

How Many Cells are in a Worksheet?

You will enter some text and formulas into Excel to practice data entry. Do not worry about the formulas at this stage. Simply follow the instructions below:

1. Highlight cell A1.

2. Type Columns and press the Enter key. Cell A2 should now be highlighted.

3. In cell A2 type Rows and press the Enter key. Cell A3 should now be highlighted.

4. In cell A3 type Cells and press the Enter key.

5. Highlight cell B1.

6. Enter =COLUMNS(1:1) (include the = sign).

7. Press Enter. This formula returns the number of columns in row 1.

8. In cell B2, enter =ROWS(A:A) (include the = sign) and press Enter. This formula returns the number of rows in column A.

9. In cell B3, enter =B1*B2 (include the = sign) and press Enter. This formula returns the product of cells B1 and B2. Your worksheet should look like this:

You have just entered some text and formulas into Excel. In the process you have determined that a single Excel worksheet has 17,179,869,184 cells!

Select a Range of Cells

The easiest way to select a range of cells is to use the mouse to drag the selection over the cells.

1. Highlight cell A1.

EDS 2101/6423 Page 3 2. Hold down the left mouse button.

3. Drag the pointer to cell F20. You should see a highlight box around the entire cell range.

Select all Cells in a Column

To select all cells in a column, you can click the column header.

 Click the column B header.

Note: To select the cells in multiple columns drag the selection over the column headers.

Select all Cells in a Row

To select all cells in a row, you can click the row header.

 Click the row 2 header.

Note: To select the cells in multiple rows drag the selection over the row headers.

Select all Cells in a Worksheet

To select all cells in a worksheet, you can click the Select All button (the rectangle at the upper left corner where the row and column headers meet):

EDS 2101/6423 Page 4 Move through a Range of Cells

You will often wish to enter data into, or change, a range of cells. Excel assists you to do this by allowing you to tab through the currently selected range.

1. Highlight the range A1 to B3.

2. Enter 1 and then, on the keyboard, press Tab.

3. Enter 2 and then press Tab.

4. Enter 3 and then press Tab.

5. Continue to enter 4, 5 and 6 in the same fashion.

Delete Data from a Range of Cells

The simplest way to delete data in a range of cells is to select the cell range and press the Delete key.

Text and Values

You can enter two types of data (information) into a cell in Excel – text and values. There is a difference between text and 'text values'. If you type ABC into a cell, then it is text data. If you type ="ABC" into a cell, then it is text value data. This will be discussed in more detail later.

Excel Values

Excel values include:

 Numbers. In cell A1, enter 23

 Formulas. In cell A2, enter =A1+4

 Text Values. In cell A3, enter ="This is a text value"

 Dates. In cell A4, enter 5/8/06

 Times. In cell A5, Enter 10:00 p (leave a space before the p). Excel adjusts this automatically to 10:00 PM, using a 12-hour clock. You could also type 22:00 to use a 24-hour clock.

Excel Text – Anything that is not a Value

When you enter data into a cell, Excel checks to see if it is a value. If not, the data is considered text. Excel text includes:

 Plain text. In cell B1, enter Phone 1234 5678

 Invalid numbers. In cell B2, enter 12 34 (space between 2 and 3)

 Invalid formulas. In cell B3, enter A1+4 (missing = sign)

 Invalid dates. In cell B4, enter 2/198/06 (198 instead of 19)

 Invalid times. In cell B5, enter 24:00 p (p is not used for 24 hour clock)

Tip: Follow the rules carefully when entering values or Excel will treat the data as text.

EDS 2101/6423 Page 5 Fundraiser

You are going to set up a small report showing fundraising sales for the first three months of 2012.

The first thing you need to do is to clear all data currently in the worksheet:

1. Click the Select All button to select all cells in the worksheet.

2. On the keyboard, press Delete key.

What Use is Text Data

Text data is useful for entering labels. Labels might include:

1. Titles for Reports. In cell A1, enter Funds Raised Report

2. Labels for Cells. In cell B1, enter For Month Ending:

3. Column Headings. In cell B3, enter Jan, in cell C3, enter Feb, and in cell D3, enter Mar

4. Row Headings. In cell A4, enter Sandwiches, in cell A5, enter Ice Creams, and in cell A6, enter Drinks

Force Data to be Text

Sometimes you will want to enter text data that Excel would normally treat as a value. You can do this by entering an apostrophe before the entry:

1. Highlight cell D1.

2. Enter 'March 2012. (Don't forget the apostrophe).

How Long Can Text Be

A single cell can contain up to 32000 characters! So you can also use text for general descriptions. For example, you could include detailed descriptions of your work in a single spreadsheet cell.

Overhanging Text

If you type more text than can be displayed in a cell then it will appear to extend outside the cell. Note that the text is still located only in the cell you entered it in.

Overhanging text is a useful feature. If there is data in an adjoining cell, however, the text will usually be cut off, as happened with the Fundraiser Sales Report title.

Wrap Text

One way to solve the problem of text being cut off is to wrap the text in the cell.

EDS 2101/6423 Page 6 1. Highlight cell A1.

2. On the Home tab, in the Cells group, click the Format drop-down arrow, then click Format Cells….

.

3. In the Format Cells dialog box, click the Alignment tab.

4. In the Text control section, select the Wrap text option.

3. Click the 0K button.

Now your text entry should look like this:

Sales Figures

Now it's time to enter some sales figures:

1. Select the range B4 to D6.

2. Tab through the 6 selected cells, entering the following data -$13.50, $14.20, $18.30, $8.90,

$7.60, $7.60, $26.50,$24.15, $22.80.

3. In cell E3, enter Totals.

EDS 2101/6423 Page 7 Change the Way Excel Displays Numbers

So that you do not spoil your Cafeteria Report you will do the following work on a separate worksheet:

1. At the bottom of the worksheet, click the Sheet2 tab:

You can include special non-numeric characters to tell Excel how to treat the number. We have just used the currency sign ($ sign). This tells Excel to format the number as currency (money). You can also:

 Use commas to separate thousands. In cell A4, try entering123,456

 Percent Sign (%). Tells Excel to treat the number as a percentage. In cell A5, try entering 22%

Large Numbers

Remain in the Sheet2 worksheet.

2. In cell A1, enter 123456789

3. Move the cursor over the dividing line between column A and column B.

4. When the pointer changes to a double headed arrow, hold down the left (upper) mouse button and shift the dividing line to the left:

5. Adjust the width of column A to slowly make it smaller. When the column width becomes too small to display the number, Excel will automatically adjust the display to scientific notation, which looks like this:

6. Keep making the column width smaller until Excel fills the cell with # signs:

EDS 2101/6423 Page 8

When Excel cannot display a number in a cell it displays # signs. To display the number you can either make the column wider, or format the size of the font until it is small enough to fit in the cell.

Make Sheet1 Active Again

7. At the bottom of the worksheet, click the Sheet1 tab. Resize any columns, as necessary using the steps above

Formulas

A formula is used to make a calculation.

This formula was used to subtract the value in cell C6 from the value in cell B6. The result of the calculation will appear in the same cell as the formula.

Equal (=) Sign

All Excel formulas must begin with an equal (=) sign or Excel will treat the data as plain text.

Parts of Formulas

Formulas can consist of numbers, text values, functions, and references to cells, combined with

mathematical operators such as Add (+), Subtract (-), Multiply (*), and Divide (/).

EDS 2101/6423 Page 9 Formulas with a Function

To help you make calculations with numbers in lots of cells, you can use a formula like this one:

Notice that it includes:

 Cell references, which tell Excel which numbers to work on AND

 A function, to tell Excel what to do with them What Does the Formula Mean?

The formula in this example tells Excel to add the values (i.e., find the SUM of the numbers) in cells B3 to B14.

Practice

1. In cell E4, enter =SUM(B4:D4)

2. In cell E5, enter =SUM(B5:D5)

3. In cell E6, enter =SUM(B6:D6)

The SUM function tells Excel to add the values in the range. For example, SUM(B4:D4) in cell E4 tells

Excel to add the values in the cell range B4 to D4 and store the value (the sum) in cell E4.

EDS 2101/6423 Page 10 Text Value Formulas

You can create formulas that produce text values rather than a number. A common operation performed with strings is to add more than one string together. This is done using the & operator.

4. In cell A8, enter ="Total Sales for "&D1

5. Then press Enter

This formula tells Excel to take the text in the cell D1 and add it to the end of the text Total Sales as of.

The result is stored in cell A8.

Formulas: Text Values, Functions, and Cell References

You can enter powerful formulas in Excel. Change the formula in cell A8 to:

="Total Sales as of "&D1&" : "&DOLLAR(SUM(E4:E6),2) and press Enter.

The DOLLAR function tells Excel to display the value in the parentheses as a currency. The 2 tells Excel that the currency has two decimal places.

Completed Worksheet

-End