Editing Your Workbook

1
Editing Your Workbook
2
Editing Your Workbook

• This lesson will cover
• How to
• Change the Contents of a Cell
• Select a Range of Cells
• Copy and Move Cells
• Delete and Clear Cells
• Insert and Delete Columns and Rows
• Add, Delete, Rename and Arrange Worksheets
• Work with Multiple Workbooks

This lesson covers topics from Chapter 3 of the
text...
3
Change the Contents of a Cell

• To change the contents of a cell you can simply
  retype it, but there are some other tricks you
  should know.
• If you click a cell and then press DELETE or
  BACKSPACE on the keyboard, Excel removes the cell
  contents but does not remove comments or cell
  formats.
• Pressing the ESCAPE key in the middle of
  editing a cell will return the cell contents to
  the condition they were in before you started
  making changes.
• If you highlight (or activate) a cell with a
  calculation in it and press F2, all the cells
  involved in that calculation will become
  highlighted.
• The Options dialog provides numerous ways to
  customize the way you edit cells. If something
  doesnt seem to be acting as you think it should
  with regards to editing your data, open this
  dialog box and look at the Edit tab.

4

• Pressing the F2 key when a cell that contains a
  function is active, such as cell F5 shown here,
  highlights all the cells used in that function
• C5
• D5
• E5
• and of course F5

5
Selecting More than One Cell

• Selecting a range of cells can be done in many
  different ways
• Pressing the CrtlA key sequence will select
  every cell in the current worksheet.
• Pressing the Control key and clicking on
  individual cells will select all the cells you
  click on.
• Click on a cell and drag over all the cells you
  want to select.
• Click on a Row heading to select the entire
  row.
• Click on a Column heading to select the entire
  column.
• The status bar shows the sum of all the cells
  that contain numerical data as you make your
  selections.

6
The sum of column E is 655.42, notice that
although the column is summed in cell E13, the
value in the status bar sums every cell including
the number 327.71.
Clicking on a Column or Row heading will select
the entire column or row
7
Copying and Moving Cells

• Cells can be moved in several ways
• Drag and Drop to Move or Copy cells
• Cut, Copy and Paste with a menu selection
• and by using the Paste Special command you can
  Copy or Move all the data, the cell formatting
  or just make a reference to the parent cell

8
Cut, copying and pasting cell data and formulas
is just about as easy as with any type of text in
Windows. There are some major exceptions, but
right clicking on a cell and selecting the
operation, such as Copy, works. You could also
use the Edit menus version of Cut or Copy, too.
Notice the other things you can do from
hereremember, when in doubt, right-click your
mouse
9
Deleting Cells

• To Delete a cell or Range of cells
• Select the cell(s)
• Right click to show the pop-up menu
• Select the Delete command
• Then select how you want Excel to handle the
  empty space

Shift cells left, Shift cells up, Delete the
entire row, or Delete the entire Column
10
Clearing Cells

• To Clear a cell or Range of cells
• Select the cell(s).
• Right click to show the pop-up menu.
• Select the Clear Contents command.
• Be carefulclicking the command proceeds with
  the clearing action without further feedback.
• RememberYou can always Undo the Clear.

11
Clearing Cells

• The other Clear method involves the Edit menu
• Select the cell(s).
• Open the Edit menu.
• Select the Clear command.
• Select the type of clearing you want to do
• Clear All, the cells Formatting, or the
  Contents (and leave the cell formatting as is).
• Notice the shortcut key to Clear Contents is
  Delete.

12
Inserting Columns and Rows

• To Insert a column
• Select the columns(s) or Row(s).
• Open the Insert menu.
• Select the Columns or Rows command (the options
  are limited by whether a column or row was
  selected.)
• A new column(s), or row(s), will be inserted
  before the selected ones.
• Selecting more than one column or row will
  insert that number of columns or rows to the
  worksheet.

13
Inserting Columns and Rows
You can do the same with the pop-up menu that is
displayed by right-clicking on the selected
columns or rows. Remember these pop-up menus
mimic the menu items available from the main
menu. But, you could figure that out by now that
14
Deleting Columns and Rows

• To Delete a column or row
• Select the columns(s) or row(s).
• Open the Edit menu.
• Select the Delete command.
• The column(s) or row(s) are deleted without any
  further warning.
• Selecting more than one column or row will
  delete that number of columns or rows.

15
Deleting Columns and Rows
You can access a Delete command from the pop-up
menu as shown on the right, or just press the
Delete key.
16
Adding a Worksheet

• To Add a worksheet
• Select the worksheet that will come after the
  new one. That is, the sheet you want to place
  behind the new sheet.
• Open the Insert menu.
• Select the Worksheet command
• A new worksheet will be added before the
  selected tab.
• Selecting more than one worksheet insert that
  number of worksheets.

17
Deleting a Worksheet

• To Delete a worksheet
• Select the worksheet to delete.
• (activate it by clicking the worksheets tab)
• Open the Edit menu.
• Select the Delete Sheet command
• The worksheet will be deleted without any
  further warning.
• Selecting more than one worksheet deletes that
  number of worksheets.

18
Renaming Worksheets

• To rename a worksheet
• Double click on the worksheet tab.
• The worksheet name on the tab will become
  highlighted as shown below.
• Type a new name and press the Enter key or
  otherwise move off the tab.
• The worksheet will now be renamed to the text
  you typed.

19
Adding, Deleting and Renaming Worksheetsanother
way
Adding (or Inserting), Deleting or Renaming a
worksheet can be accomplished by accessing the
Shortcut (pop-up) menu. Right-click on the
selected tab to display the menu as shown below
20
Selecting Several Worksheets

• Two or more adjacent sheets
• Click the tab for the first sheet, and then
  hold down SHIFT and click the tab for the last
  sheet.
• Two or more nonadjacent sheets
• Click the tab for the first sheet, and then
  hold down CONTROL and click the tabs for the
  other sheets.
• All sheets in a workbook
• Right-click a sheet tab, and then click Select
  All Sheets on the shortcut menu.

21
Arranging Worksheets

• Arranging the order of your worksheets is simply
  a matter of dragging the tab and dropping in into
  the new location.
• Grab a tab and move it to the new location, a
  small black triangle (pointing down) will appear
  at the currently selected location
• Drop the tab in its new place...

22
Using Multiple Workbooks
Theres more to working with multiple workbooks
then moving back and forth between them. You can
display data in one workbook that resides in
another, and even place a hyperlink that refers
to another file. Formulas can include data from
other worksheets and workbooks. For example, the
following formula gets and sums data from another
Excel file whose path is C\Excel\Budget.xls
and the worksheet is named Annual SUM(C\Exce
l\Budget.xlsAnnual!C10C25). There are,
however, some methods for activating and
toggling between the open workbooks that we
need to look at first
23
Displayed here is the first worksheet, Type, of
the Chapter2.xls workbook.
You can see by looking at the Taskbar that there
are two workbooks open
24
Restore window control button
Recall that you can detach or reduce the size of
the active workbook by clicking the
Restore Down window control button. Doing this
changes the view from the previous slide to
this2 windowed workbooks.
25
You can also toggle back and forth between
workbooks by using the Window menu. Notice the
lower section of the menuit displays the
currently open files. Clicking on Chapter3.XLS
would make that workbook active
26
To copy a range of cells from one open workbook
to another
First select the cells
27
Then select the Copy command from the pop-up menu
or from the Edit menu
28
Then select a cell in the new location (at the
beginning of the range). If you select more than
one cell it must be the exact number of cells as
those about to be pastedso selecting one is
easier and always worksselect the Paste command,
from a menuor CtrlV
29
Now the cells have been pasted, but the column
looks a bit narrow
30
Select Column from the Format menu to change the
column widthuse AutoFit Selection to do it
automatically
31
Now the column matches the size of the contents
(including the label Price Now) the copy is
complete
32
To copy cells with formulas in them, and actually
create a link between the two workbook
filesselect a range of cells (or just one cell)
that includes a formula and Copy the range
33
Next, select a single cell or the correct range
of cells on the destination workbook and right
click to show the pop-up menu or use the Edit
menu. Then select the Paste Special command from
one of the menus
34
Then select the Paste Link command
35
Selecting one of the newly pasted cellsdisplays
the link in the Formula bar.
With the Paste completethe workbook files are
now linked.
36
Links for Excel
Excellent Excel online tutorial http//www.usd.edu
/trio/tut/excel/
another good online tutorial http//www.extension
.iastate.edu/Pages/Excel/homepage.html
Excel tour from Microsoft http//www.microsoft.com
/office/excel/evaluation/tour/default.asp
Tips and tricks from Microsoft http//www.microsof
t.com/office/using/tips/default.asp