Working with Cells and Ranges

1
Working with Cells and Ranges

• Applications of Spreadsheets

2
Understanding Cells Ranges

• Cell a single element in a worksheet that can
  hold
• a value
• some text
• a formula
• Range a group of cells designated by
• specifying the upper-left cell address and the
  lower-right cell address and
• separating these values with a colon ()
• A5E5 refers to the range of cells in row 5 from
  column A through column E.

3
Selecting Cells Ranges

• Select a range by
• Using the mouse to drag and highlight the range.
• Press the Shift key while using the direction
  keys.
• Press F8 and move the cell pointer with the
  direction keys, and then press F8 again.
• Type the cell or range address in the Name box
  and press Enter.
• User the Edit ? Go To command (F5) and enter a
  ranges address manually into the Go To dialog
  box.

4
Selecting the Cell Range A3E3
Notice that the column and row frame areas for
the cell range are highlighted also..
5
Selecting a Cell Range using the Mouse
The row and column frame areas are highlighted,
making it easy to identify the selected cell
range
Use the cross mouse pointer to select a cell or
cell range
6
Selecting Columns and Rows

• Click column heading(s) to select entire columns
• Click row heading(s) to select entire row(s)
• Click Select All button to select entire
  worksheet (CtrlA)
• Use Shift arrow keys to select with keyboard

7
Multiple Selection

• To select nonadjacent or noncontiguous
  cells/columns/rows
• click the first cell/column/row
• press and hold the Ctrl key and click additional
  cells/columns/rows.
• The last cell that you click is the active cell,
  but the others remain selected.

8
Selecting Multiple Cell Ranges using the CTRL Key
The active cell, A12, is the top left-hand cell
in the final cell range selected
9
Selecting Multisheet Ranges

• Ranges can be 3-dimensional, extending across
  multiple worksheets.
• To select a multisheet range
• Select a range on one worksheet.
• Select the worksheets to include in the range by
• pressing the Shift key and clicking another
  worksheet tab for contiguous worksheets.
• pressing the Ctrl key and clicking other
  worksheet tabs for non-adjacent worksheets.
• Any changes you make will effect the specified
  range of cells on every worksheet youve selected.

10
Selecting Multisheet Ranges

• The workbook windows title bar displays Group
  as a reminder that you are in Group edit mode.
• Select another sheet tab to cancel Group mode.
• Can select all sheets in a workbook by
  right-clicking any sheet tab and choosing Select
  All Sheets from the shortcut menu.

11
Copying to Multisheet Ranges

• Can also use this technique to copy a cell or
  range and paste it to other worksheets in the
  same workbook.
• Select the range to copy.
• Press Ctrl and click the sheet tabs for the
  worksheets to which you want to copy the
  information.
• Select Edit ? Fill ? Across Worksheets.
• A dialog box appears that asks what you want to
  copy
• All
• Contents
• Formulas
• Make your choice and click OK.
• The title bar displays Group as a reminder that
  you are in Group edit mode.

12
Selecting Special Types of Cells

• What if you wanted to select every cell in the
  worksheet that contains a formula?
• Use the Go To Special dialog box.
• Choose Edit ? Go To
• Click the Special button to display the Go To
  Special dialog box.
• Make your choice selections.

13
Go To Special Options

• Comments
• Constants
• Formulas
• Blanks
• Current Region
• Current Array
• Objects
• Row Differences

• Column Differences
• Precedents
• Dependents
• Last Cell
• Visible Cells Only
• Conditional Formats
• Data Validation
• See Table 4-1

14
Selecting Cells by Searching

• Use Excels Edit ? Find command (CtrlF) to
  select specific cells by their content.
• Opens the Find Replace dialog box.
• click on the Options button
• enter the text you are looking for
• click Find All
• Excel returns a list of cells that match your
  criteria.
• Click on an item in the list to scroll to that
  cell.

15
Copying or Moving Ranges

• Can copy a cell to another cell.
• Copy a cell to a range of cells the source cell
  is copied to every cell in the destination range.
• Copy a range to another range (must be the same
  size).
• Move a range to another range.

16
Copying and Moving Ranges

• Select the source range (the cell or range to
  copy) and cut or copy it to the Clipboard.
• Move the cell pointer to the destination range
  (the cell or range that will hold the copy) and
  paste the Clipboard contents.
• If copying or moving a range, you dont need to
  select the entire destination range just the
  upper-left cell in the destination range needs to
  be activated.

17
Other Options

• Use the Toolbar buttons.
• Use the menus Edit ? Copy or Edit ? Cut and then
  Edit ? Paste.
• Right click to use the shortcut menu.
• Use shortcut keys
• CtrlC copy
• CtrlX cut
• CtrlV paste

18
Moving By Drag Drop

• Select the cells and position the mouse pointer
  on any border of the cells so the cell pointer
  changes to a white arrow.
• Click and drag the white arrow to the new
  location
• An outline of the cells that you are moving
  appears as you drag
• A ScreenTip shows you the current active cell
  where the information will appear if you release
  the mouse button.
• When you release the mouse button, the cells
  contents appear in the new location.
• Bonus!! If the new location already contains
  information, a dialog box appears, asking whether
  you want to replace the contents of the
  destination cells.

19
Using Drag and Drop to Move Cell Data
The ToolTip helps you to identify the target drop
area for the move operation
20
Using Smart Tags

• Some cell and range operations result in the
  display of a Smart Tag (a small square that, when
  clicked, presents a list of options.)
• Alternative to using some of the options in the
  Paste Special dialog box.
• Can turn off Smart Tags by selecting Tools ?
  Options, choosing the Edit tab, and removing the
  check mark from both the
• Show Paste Options buttons
• Show Insert Options buttons

21
Copying to Adjacent Cells

• Select the cell that you are copying and the
  cells that you are copying to.
• From the Edit menu, issue the appropriate
  command
• Edit ? Fill ? Down (CtrlD)
• Edit ? Fill ? Right (CtrlR)
• Edit ? Fill ? Up
• Edit ? Fill ? Left
• Edit ? Fill ? Series
• displays the series dialog box

22
Extending a Cell's Contents
23
Extending a Cell's Contents Using the Edit, Fill,
Right Command
The top left-hand cell in the cell range contains
the data that you want extended to the other
cells.
24
Filling Multiple Ranges with Formulas
Unlike using the mouse and fill handle to extend
a range, you can use the Fill commands to
complete multiple range selections at a single
time.
25
Using AutoFill

• When using AutoFill, if you select cells that
  contain data, Excel overwrites the data in the
  cells.
• You can reverse the fill action and restore
  original data by choosing Edit, Undo.

26
AutoFill Sequences in Excel

• Days of the week (Monday, Mon)
• Months of the year (January, Jan)
• Any Text with numbers (Product 1, Prod 1, Quarter
  1, Qtr 1, Year 1, Y 1)
• Customized Lists (Tools ? Options)

27
Fill Effects

• You can fill columns as well as rows.
• to fill in increasing order, drag down or to the
  right
• to fill in decreasing order, drag up or to the
  left
• If you want to create a sequence of consecutive
  entries
• provide an example in one or two cells
• select the cell(s) containing the example
• drag the lower-right corner of the selection

28
Fill Effects

• If a fill sequence is to be a set of numbers
  incrementing by the same amount, provide a
  pattern in two adjacent cells, such as the number
  10 in one cell and the number 20 in the next.
• If you select both cells and drag right or down,
  Excel fills the copy range with numbers
  incrementing by 10 (10, 20, 30, 40, and so on).

29
Fill Effects

• The sequences can be text, numbers, or a
  combination of text and numbers.
• Example
• if you enter Jan In a cell and then use the Fill
  handle to drag right or down eleven more cells,
  Excel automatically enters Jan, Feb, through
  Dec in the sequence of twelve cells.

30
Fill Effects

• You can also use the AutoFill feature to enter a
  series of dates.
• If you fill a range of dates based on the
  contents of one cell, each additional date
  increments by one.
• applying AutoFill to a cell containing 1/1/2000
  produces a series of dates 1/1/2000, 1/2/2000,
  1/3/2000 and so forth.

31
Fill Effects

• If you desire a different sequence, such as the
  last day of each month, provide a pattern in two
  cells.
• if you enter 1/31/2000 in one cell and 2/29/2000
  in the next cell, and select and drag those
  cells, Excel continues the sequence with
  3/31/2000, 4/30/2000 and so forth.

32
Specify a Series Using the Shortcut Menu

• If you specify a start value in one cell, and
  click and drag with the right mouse button
  instead of the left, a shortcut menu displays
  with predefined increments including days,
  weekdays, months, and years if the value is a
  date.

33
Using a Cells Fill Handle in AutoFill
Position the mouse over cell A3s fill handle
until it changes to a small black cross.
34
Using AutoFill to Complete Cell Ranges
AutoFill Options button.
35
Extending an Incremental Data Series
36
Creating a Series Using AutoFill
37
Using the Windows Clipboard
38
Entering Totals Using the AutoSum Button
A SUM function is entered into each cell in the
selected range.
39
Selecting and Copying a Range to the Clipboard
The dashed marquee or moving border is used to
signify that the data has been placed onto the
Clipboard.
40
Copying and Pasting Data in a Worksheet
41
Displaying the Clipboard Task Pane
The contents area of the Office Clipboard is
currently empty.
42
Clipboard Task Pane after collecting Items
Clears the collected items appearing in the list
box.
Pastes all of the items appearing in the list box
vertically into the worksheet.
43
Pasting Items from the Office Clipboard into the
Worksheet
44
Pasting the Row Items into the Worksheet
Notice that the rows are ordered differently than
the original cell range in rows 3 through 5.
45
Pasting in Special Ways

• You may not always want to copy everything from
  the source range to the destination range.
• Copy current values not the formulas.
• Copy formatting not the data.
• To control what is copied to the destination
  range, use the Edit ? Paste Special command.

46
Paste ? Special Options

• All (equivalent to Edit ? Paste)
• Formulas
• Values (results of formulas)
• Formats
• Comments
• Validation (copies only the validation criteria)
• All except borders
• Column widths
• Formulas and number formats (no current values)
• Values and number formats (no formulas)

47
Paste ? Special Options

• Using Paste ? Special, you can also
• Perform mathematical operations without using
  formulas.
• Skip blanks
• Transpose a Range (rows become columns and
  columns become rows)

48
Working with Named Ranges

• A range name is a nickname given to a group of
  cells
• can later be used in constructing formulas
• For example, assigning the name Revenue to cell
  C5, and the name Expenses to cell C6 makes the
  formula expression C5-C6 become the more
  understandable Revenue-Expenses.

49
Working with Named Ranges

• Less error-prone
• Easier navigation
• use the Name box
• Edit ? Go To (F5)
• Easier formula creation and recognition
• Rules
• cant use spaces in the name
• must begin with a letter
• no symbols
• limited to 255 characters

50
Creating Named Ranges

• Select a cell or range
• Select Insert ? Name ? Define (or CtrlF3)
• Excel displays the Define Name dialog box
• Type a name in the box labeled Names in
  Workbook.
• The active cell or range address appears in the
  box labeled Refers to.
• Click OK.

51
Faster Way of Creating Named Ranges

• Select a cell or range
• Click on the Name box (a drop-down list that
  shows all the names in the workbook.)
• Type the name you wish to name the range or cell
• Press Enter to create the name
• If the name already exists, you cant use the
  Name box to change the range reference.

52
Using Adjacent Text to Create Named Ranges

• Select the name text and the cells you want to
  name.
• Names must be adjacent to the cells that you are
  naming.
• Choose Insert ? Name ? Create
• Excel displays the Create Names dialog box.
• Change the check marks in this dialog box as
  necessary (Excel has made a best guess).
• Click OK.

53
Creating a Table of Named Ranges

• Excel lets you create a list of all names in the
  workbook and their corresponding addresses.
• Move the cell pointer to an empty area of your
  worksheet.
• Use the Insert ? Name ? Paste command (F3).
• Excel displays the Paste Name dialog box which
  lists all the defined names in the workbook.
• To paste a list of names, click the Paste List
  button.

54
Modifying Existing Named Ranges

• To change the cell or range to which a range name
  refers
• Select Insert ? Name ? Define
• Click the name that you want to change
• Edit the cell or range address in the Refers To
  edit box.
• To remove a range name
• Select Insert ? Name ? Define
• Choose the name you want to delete from the list
• Click the Delete button.
• Be careful that the deleted range name is not
  used in a formula!

55
Naming Cell Ranges
Define a range name using the Name Box .
56
Creating Range Names from Worksheet Values
Use this dialog box to help Excel identify the
cells containing labels which should be used for
naming ranges.
57
Displaying Range Names in the Name Box
Excel 2003 creates these range names using the
column and row heading labels
58
Managing Range Names using the Define Name Dialog
Box
59
Pasting Range Names into the Worksheet
Range names and their cell references can be
pasted into the worksheet for reference.
60
Adding Comments to Cells

• Use documentation to explain certain elements in
  a worksheet
• Document a particular value
• Explain how a formula works
• Adding a Comment
• Select the cell
• Choose Insert ? Comment (ShiftF2)
• Excel inserts a comment that points to the active
  cell.
• Enter text for the cell comment
• Click anywhere in the worksheet to hide the
  comment.

61
Adding Comments to Cells

• Comment Indicators
• Cells that have a comment attached display a
  small red triangle in the upper-right corner.
• Use the Reviewing Toolbar to navigate between the
  comments in the worksheet.
• Changing Comments
• Adjust the comment size by clicking and dragging
  any of its borders.
• Delete a comment by activating the cell with the
  comment, right-clicking, and choosing Delete
  Comment from the shortcut menu.
• Select Tools ? Options and click the View tab to
  control how cell comment indicators are displayed.