81
University of Salford 31
Entering Labels and Values
Now that you’re familiar with worksheet navigation in
Excel,
you’re ready to start entering data. There are two
basic types of information you can enter in a cell:
Labels: Any information not used in calculations.
Labels are used for headings in columns and rows,
and as data in columns and rows. Excel treats cell
data containing letters or non-numerical punctuation
as text and automatically left-aligns it inside the cell.
Values: Numerical data, including: numbers,
percentages, fractions, currencies, dates, or times,
usually used in formulas or calculations. Excel treats
information that contains numbers, dates or times as a
value and automatically right-aligns it in the cell.
Excel even treats dates as values, makes it possible to
perform calculations and formulas on the labels. For
example, you can subtract one date from another to
find how many days are between them. You can
control how dates appear with cell formatting.
1. Select the cell where you want to enter data.
2. Type the data in the cell.
3. Press the <Enter> or <Tab> key.
The cell entry is confirmed and the next cell becomes
active.
Other Ways to Confirm a Cell Entry:
Click the Enter button on the Formula Bar.
If the contents do not fit in the cell, the text spills into
the empty cell to the right. If that cell is not empty,
Excel truncates the data so only part of it is visible.
Tips
If you want to start a label with a number, type an
apostrophe at the beginning of the label. This tells
Excel that the cell contents are a label, not a value.
AutoComplete can help you enter labels. Enter the
first few characters of a label; Excel displays the
label if it appeared previously in the column. Press
<Enter> to accept the entry or resume typing to
ignore the suggestion.
You can reformat dates after entering them. For
example, if you enter 4/4/12, you can easily reformat
to April 4, 2012. This is covered in another lesson.
Exercise Notes
•
Exercise File: Sales2-1.xlsx
•
Exercise:
Type the label “Sales and Expenses” in cell A1.
Enter the following labels in cell range A7:A11:
Supplies, Office, Salaries, Utilities, Total
Enter the following values in the cell range D4:F4:
18500, 16500, 15500
Enter today’s date in B13.
Figure 2-2: A worksheet with labels and values.
Table 2-2: Examples of Valid Date and Time Entries
October 17, 2010
5:45 PM
10/17/10
5:45 AM
10-17-10
5:45
(Excel assumes 5:45 AM)
17-Oct-10
17:45
(5:45 PM on a 24-hour clock)
Oct-17
(Excel assumes the current
year.)
17:45:20
(5:45 PM and 20 seconds)
Getting Started with Worksheets
Excel treats dates as values, but
the dates appear differently in the
cell in which they are entered.
77
32
© 2010 CustomGuide, Inc.
Selecting a Cell Range and
Entering Data in a Cell Range
Selecting a cell range
To work with a range of cells, you need to know how to
select multiple cells.
1. Click the first cell you want to select in the cell range
and hold the mouse button.
2. Drag to select multiple cells.
As you drag, the selected cells are highlighted.
3. Release the mouse button.
The cell range is selected.
Other Ways to Select a Cell Range:
Select the first cell of the cell range. Press and
hold the <Shift> key and select the last cell of the
cell range.
Tips
To select all the cells in a worksheet, click the Select
All button where the row and column headers come
together, or press <Ctrl> + <A>.
To select multiple non-adjacent cells, select a cell or
cell range and hold down the <Ctrl> key while you
select other cells.
Entering data in a cell range
When you have to enter a lot of data, selecting the range
makes data entry easier and faster. Selecting a range of
cells restricts the cell pointer so it can only move inside
the selected range.
1. Select the range of cells in which you want to enter
data.
By selecting a range, you restrict where the cell
pointer can move and can concentrate on data entry
instead of moving the cell pointer back and forth.
2. Enter the data in the first cell. Press <Tab> or
<Enter> to move on to the next cell.
When the active cells reaches the end of a column or
row, the next time your press <Tab> or <Enter>, the
cell pointer moves to the next cell in the selected
range.
3. Click any cell in the worksheet to deselect the range.
Exercise Notes
•
Exercise File: Sales2-2.xlsx
•
Exercise: Select the cell range E7:F10 and then enter the
data below in cell range E7:F10.
E
F
7
2500
1500
8
400
400
9
7000
7000
10
3000
3000
Figure 2-3: Entering text in a selected cell range.
Table 2-3: Navigating in a Selected Cell Range
Down
<Enter>
Up
<Shift> + <Enter>
Right
<Tab>
Left
<Shift> + <Tab>
Getting Started with Worksheets
Click to select the entire
worksheet.
Generate and draw Code 39 for Java the Java Code 39 Generation Package and extract the file. Encode data for Code 39 barcode image text in Java LEFT"); //Draw Code 39 images in the file with EPS
copy paste text pdf file; get text from pdf c# Generate and draw UPC-A for Java the Java UPC-A Generation Package and extract the file. Encode data for UPC-A barcode image text in Java Class RIGHT); //Draw UPC-A images in the file with EPS
delete text from pdf preview; extract text from scanned pdf
86
University of Salford 33
Overview of Formulas and Cell
References
This lesson introduces formulas and the different elements
that are required to write a formula.
Values
Values are any numerical data entered in a worksheet.
Once values are entered in the worksheet, they can be
used in formulas.
Formulas
Formulas are values, but unlike regular values, formulas
contain information to perform a numerical calculation,
such as adding, subtracting, or multiplying. A cell with the
formula =5+3 will display the result of the calculation: 8.
All formulas must start with an equal sign (=). Then you
specify more information: the values you want to
calculate and the arithmetic operator(s) or function
name(s) you want to use to calculate the values.
Operators are the basic symbols used in
mathematics: + (plus), - (minus), / (divide), *
(multiply). In Excel, you use these just as you would
to write out a math problem.
Functions are used more often in Excel. Functions
are pre-made formulas that you can use as shortcuts,
or to perform calculations that are more complicated.
Relative and absolute cell references
Formulas can contain numbers, like 5 or 8, but more often
they reference the contents of cells. A cell reference tells
Excel where to look for values you want to use in a
formula. For example, the formula =A5+A6 adds the
values in cells A5 and A6.
Using cell references is advantageous because if you
change the values in the referenced cells, the formula
result automatically updates using the new values. There
are two types of cell references: relative and absolute.
Relative: Relative references refer to cells in relation
to the cell that contains the formula. When the
formula is moved, it references new cells based on
their location relative to the formula. Relative
references are the default type of references in Excel.
Absolute: Absolute references always refer to the
same cell, even when the formula is copied. Absolute
references are indicated with dollar signs ($A$1) in
formulas. Pressing <F4> changes a cell reference to
absolute.
Exercise Notes
•
Exercise File: None required.
•
Exercise: Understand how formulas are used in Excel.
Getting Started with Worksheets
Values
Values in Column A.
Formulas
A formula in cell B1
using the multiplication
operator.
The cell displays the
result of the formula,
while the Formula Bar
displays the formula.
The results of each
formula are the same
because each formula
refers to the same cell.
Absolute cell reference
When the formula in cell
C1 is copied to the rest of
the cells in column C, the
cell references are not
updated.
Relative cell reference
When the formula in cell
B1 is copied to the rest
of the cells in column B,
the cell references are
updated in each row.
The results of each
formula are different
because each formula
refers to a different cell.
Java Imaging SDK Library: Document Image Scan, Process, PDF Using RasterEdge Java Image SDK, developers can easily open and compress a multitude of file formats, such as Gif, Png, Tiff, Jpeg2000, DICOM, JBIG2, PDF, MS Word
get text from pdf into excel; copying text from pdf into word
79
34
© 2010 CustomGuide, Inc.
Entering Formulas
This lesson takes a look at how to enter formulas in a cell.
A formula starts with an equal sign, followed by:
Values or cell references joined by an operator.
Example: =5+3 or =A1+A2
A function name followed by parentheses containing
function arguments. Functions are the most common
way to enter formulas in Excel.
Example: =SUM(A1:A2)
Enter a formula with an operator
1. Click a cell where you want to enter a formula.
2. Type
=
, then type cell references and operators.
You can also enter the formula in the Formula Bar.
3. Press <Enter>.
The formula calculates the result and displays it in
the cell where you entered it.
Enter a formula with a function
1. Click a cell where you want to enter a formula.
2. Click the Insert Function button in the Formula Bar.
If you know the name of the function you want to
use, you can type it out instead of selecting it from
the Function button.
Other Ways to Enter a Function:
Click the Formulas tab on the Ribbon and click
the Insert Function button in the Function
Library group.
3. Select the function you want to use and click OK.
The Function Arguments dialog box appears.
4. Enter the function arguments and click OK.
The result of the formula appears in the cell.
Tips
You can use the Formula AutoComplete feature to
help you create and edit complex formulas. Type an
= (equal sign) in a cell or the Formula Bar and start
typing the formula. A list of functions and names that
match the text you entered appears. Select an item
from the list to insert it into the formula.
Exercise Notes
•
Exercise File: Sales2-3.xlsx
•
Exercise: In cell B11, total the values in B7:B10
In cell G4, multiply F4 by G2, making G2 an absolute cell
reference.
In cell G7, multiply F7 by G2, making G2 an absolute cell
reference.
In cell C11, AutoSum the column C expense values.
Figure 2-4: Entering a formula in a worksheet.
Table 2-4: Examples of Operators and Functions
=
All formulas start with an equal sign.
+
=A1+B1
-
=A1-B1
*
=B1*2
/
=A1/C2
SUM
=SUM(A1:A3)
AVERAGE
=AVERAGE(A2,B1,C3)
COUNT
=COUNT(A2:C3)
Getting Started with Worksheets
The formula of the cell
is displayed in the
Formula Bar.
The value of the formula
is displayed in the cell.
DocImage SDK for .NET: Document Imaging Features file Use annotation of embedded image, text or rubber page in document to image file Allow conversion Type 6 (OJPEG) encoding Image only PDF encoding support.
copy text from pdf in preview; acrobat remove text from pdf DocImage SDK for .NET: HTML Viewer, View, Annotate, Convert, Print moved, resized, and rotated independently from source file. including Microsoft Word, Excel, PPT, PDF, Tiff, Dicom and encode JBIG2 images using Microsoft .NET
copy text from locked pdf; extract text from pdf
47
University of Salford 35
Enter an absolute cell reference in a formula
1. Enter the formula using operators or functions.
2. Click the cell you want to reference and press the
<F4> key.
Dollar signs $ are added to the cell reference in the
formula.
Other Ways to Add an Absolute Cell Reference
in a Formula:
Type the address of the cell with $ (dollar signs)
before every reference heading. (For example,
type $B$4).
Total values automatically with AutoSum
Adding up the values in a range of cells is the most
popular fo
rmula in Excel, so they’ve made this easy to do
with the AutoSum feature. AutoSum inserts the SUM
function (which adds all the values in a range of cells) and
selects the range of cells Excel thinks you want totaled.
1. Click a cell next to the column or row of numbers
you want to sum.
For example, if you want to add up a column of
numbers, click the cell at the bottom of the column.
Or, if you want to add up a row of numbers, click the
cell to the right of the row.
2. Click the Home tab and click the AutoSum button in
the Editing group.
The SUM function appears in the cell and a moving
dotted line appears around the cell range that Excel
thinks you want to sum. If the range is not correct,
click and drag to select the correct range.
Tip: Click the AutoSum button list arrow to
choose from other common functions, such as
Average.
Other Ways to Enter AutoSum:
Press <Alt> + <=>.
3. Press the <Enter> key to confirm the action.
The cell range is totaled in the cell. If you change a
value in the summed range, the formula will
automatically update to show the new sum.
Figure 2-5: A formula with a relative (F4) and an absolute
($G$2) cell reference.
Figure 2-6: AutoSum automatically enters a SUM formula
and selects the cells it thinks you want to total.
Getting Started with Worksheets
AutoSum
button
71
36
© 2010 CustomGuide, Inc.
Entering Content
Automatically
Since entering data is a major task in Excel, this lesson
covers three tools that make are very useful in data entry:
Fill, AutoComplete, and PickList.
Use Fill
Fill is a great way to enter sequential numbers, months or
days quickly. Fill looks at cells that you have already
filled in and makes a guess about how you want to fill in
the rest of the series. For example, if you enter January,
Fill will fill in the following months for you. You can also
use Fill to copy formulas to adjacent cells.
1. Select a cell or cell range that contains the data and
increment you want to use.
Excel can detect patterns pretty easily. A series of 1,
2, 3, 4 is easy to detect, as is 5, 10, 15, 20. It can also
detect a pattern with mixed numbers and letters, such
as UPV-3592, UPV-3593, UPV-3594.
Tip: If you select only one cell, that same value is
copied to the adjacent cells when you Fill
—
unless
Excel recognizes it as a date or time, in which
case it will fill in the next logical date or time
period.
2. Position the mouse pointer over the fill handle (the
tiny box in the cell’s lower
-right corner) until the
pointer changes to a plus sign
.
3. Click and drag the fill handle to the cells that you
want to fill with the information.
As you click and drag, a screen tip appears
previewing the value that will be entered in the cell
once you release the mouse button.
Table 2-5: Fill Series Examples
Selected Cell(s)
Entries in Next Three Cells
January
February, March, April
Jan
Feb, Mar, Apr
5:00
6:00, 7:00, 8:00
Qtr 1
Qtr 2, Qtr 3, Qtr 4
5
10
15, 20, 25
1/20/12
1/21/12, 1/22/12, 1/23/12
UPV-3592
UPV-3593, UPV-3594, UPV-3595
Exercise Notes
•
Exercise File: Sales2-4.xlsx
•
Exercise: Fill in the month labels in row 3. Labels should
start with Jan in column B and end with Jun in column G.
Copy the formula in cell G7 to cells G8:G10. Copy the
formula in cell C11 over to columns D, E, F, and G.
Figure 2-7: Fill fills in months after January into the
selected cells. Notice that a screen tip previews the
content being filled into the cells.
Figure 2-8: Formulas that are copied with Fill are updated
relative to their location. This formula copied from C11 is
updated to use cell references from the D column.
Getting Started with Worksheets
.NET Windows Forms QR Code Bar Code Control & SDK using Rasteredge.WinForms.Barcode; // construct a linear barcode Symbology.qrcode ' set qrcode code text to encode draw barcode and save into image file in gif
extract all text from pdf; get text from pdf image
40
University of Salford 37
Control fill options
If Fill doesn’t enter cell content the way you expected,
you can correct the content using AutoFill Options. This
button appears after using the Fill command, and it offers
valuable control over how the Fill command works.
1. Enter cell content using Fill.
After releasing the mouse button, the Fill Options
button appears.
2. Click the Fill Options button.
A list of ways you can control the cell content that is
entered appears.
3. Select a fill option from the list.
The cell content is changed according to the fill
option you chose.
Repeat values in a column
Excel’s AutoComple
te feature helps speed up data entry,
especially if you’re using repetitive information.
1. Type the first few characters of a label.
Excel displays the label, if it appears previously in
the column.
2. Press <Enter>.
Excel accepts the entry.
If a suggest
ion appears and you don’t want to use it,
resume typing to ignore the suggestion.
Select contents from a list
The PickList is a list of data
you’ve used and helps keep
your information consistent.
1. Right-click the cell where you want to enter a label
and select Pick from Drop-down List from the
contextual menu.
A list appears under the cell.
2. Select an entry from the list.
The data is entered and the list disappears.
Figure 2-9: Click the AutoFill Options button to choose a
different result of the Fill.
Getting Started with Worksheets
107
38
© 2010 CustomGuide, Inc.
G
G
e
e
t
t
t
t
i
i
n
n
g
g
S
S
t
t
a
a
r
r
t
t
e
e
d
d
w
w
i
i
t
t
h
h
W
W
o
o
r
r
k
k
s
s
h
h
e
e
e
e
t
t
s
s
R
R
e
e
v
v
i
i
e
e
w
w
Quiz Questions
14.
Press ______ to move the cell pointer one cell to the left.
A. <Enter>
B. <Shift> + <Tab>
C. The up arrow key
D. <Tab>
15.
Which of these statements is false?
A. Dates are a type of value.
B. Labels and values are both aligned along the left side of the cell.
C. Labels can include numbers.
D. Values include any data that can be used in formulas or calculations.
16.
You can select all the cells in a worksheet at once. (True or False?)
17.
Why is entering data in a selected cell range advantageous?
A. The cell pointer recognizes values and labels correctly.
B. There are no advantages.
C. This makes it easier to format cell contents.
D. The cell pointer stays within the selected cell range.
18.
All formulas start with:
A. /
B. (
C. =
D. &
19.
Relative cell references always refer to the same cell. (True or False?)
20.
Which of the following formulas is NOT correctly written?
A. 5+6
B. =A2-B3
C. =A4/A6
D. =SUM(A1:A6)
21.
You can use the Fill command to copy a formula to adjacent cells. (True or False?)
18
University of Salford 39
Quiz Answers
14.
B. Pressing <Shift> + <Tab> moves the cell pointer one cell to the left.
15.
B. Labels are aligned on the left side of the cell. Values are aligned along the right side of the cell.
16.
True. You can select all cells at once by pressing Ctrl + A.
17.
D. Entering data in a selected cell range is advantageous because the cell pointer stays within the selected cell range.
18.
C. All formulas start with an equal sign (=).
19.
False. Absolute cell references always refer to the same cell.
20.
A. 5 + 6 is incorrect because it doesn't begin with an equal sign.
21.
True. You can use the Fill command to copy formulas to adjacent cells.
86
40
© 2010 CustomGuide, Inc.
E
E
d
d
i
i
t
t
i
i
n
n
g
g
a
a
W
W
o
o
r
r
k
k
s
s
h
h
e
e
e
e
t
t
Editing Cell Contents ........................................ 41
Edit cell contents ...................................... 41
Replace cell contents ............................... 41
Clear cell contents ................................... 41
Cutting, Copying, and Pasting Cells ............... 42
Moving and Copying Cells Using the Mouse
................................................................. 43
Cutting, Copying, and Pasting Cells ............... 44
Using the Office Clipboard ............................... 46
Checking Your Spelling ..................................... 47
Inserting Cells, Rows, and Columns ............... 49
Deleting Cells, Rows, and Columns ................ 50
Using Undo, Redo and Repeat ......................... 51
Undo a single action ................................ 51
Redo an action ......................................... 51
Undo multiple actions .............................. 51
Using Find and Replace .................................... 52
Search options ......................................... 53
Adding Comments to Cells ............................... 54
Insert a comment ..................................... 54
View a comment ...................................... 54
Edit a comment ........................................ 54
Delete a comment .................................... 55
This chapter will show you how to edit
your Excel worksheets. You’ll learn how
to edit cell contents; cut, copy and paste
information; insert and delete columns
and rows; undo any mistakes you might
make; and even correct your spelling
errors.
Using Exercise Files
This chapter suggests exercises to practice
the topic of each lesson. There are two
ways you may follow along with the
exercise files:
Open the exercise file for a lesson,
perform the lesson exercise, and close
the exercise file.
Open the exercise file for a lesson,
perform the lesson exercise, and keep
the file open to perform the remaining
lesson exercises for the chapter.
The exercises are written so that you may
“build upon them”, meaning the exercises
in a chapter can be performed in
succession from the first lesson to the last.
3
3
Documents you may be interested
Documents you may be interested
