CTS130 Spreadsheet

Lesson 4

Working with Cells, Columns, Rows, and Sheets

A new workbook opens with three blank worksheets, enabling you to separate parts of your work.

You can insert, delete, and rename worksheets as needed.

Worksheet tabs can be rearranged and placed in a different order.

Working with Worksheets

Examples:

Inserting a Worksheet A new worksheet that is inserted is placed to the left

of the active sheet. Two ways to insert a worksheet:

Insert Menu > Worksheet option Right-click the tab and choose INSERT

Deleting a Worksheet If a worksheet is the only

one in the workbook, it cannot be deleted.

Two ways to delete a worksheet: Edit Menu > Delete Sheet Right-click tab and choose

Delete

RENAMING A WORKSHEET

Right-click on a sheet tab - select the RENAME option. The original title will be highlighted – key new name.

Worksheet

MOVING A WORKSHEETEasier way is to click and drag the worksheet to thenew location.

COPYING A WORKSHEETRight-click on tab and select “Create a copy”

CUTTING Contents (removes the original copy)1. Edit Menu > Cut

2. CUT button on toolbar

3. CTRL + X

4. Right-click and select CUT from the menu

5. Drag and Drop - Select the cell(s) – cursor turns to a

four-headed arrow - drag to new location

COPYING Contents (leaves original in place)

Edit Menu > Copy

COPY button on toolbar

CTRL + C

Right-click and select COPY from the menu

Select the cell(s) – cursor turns to a four-headed arrow - hold down CTRL - drag to new location

PASTE cut/copied cells

Edit Menu > Paste

PASTE button on toolbar

CTRL + V

Right-click and select PASTE from the menu

PASTE Options dropdown menu

After you have used the PASTE options, a down menu will appear.

This is a list of options that establishes “how” the selection is pasted.

Using the “Office Clipboard” on the TASKBAR•The Office Clipboard is a

temporary memory area that can hold up to 24 copied items.

•It is separate from the Windows clipboard.

•To see the Office Clipboard, go to the VIEW Menu > Task Pane option.

•Once the items have been cut or copied, all you have to do is click and drag on the items in the clipboard to pull them into the spreadsheet.

INSERT – DELETE columns

INSERT Insert Menu > Column Right-click > Insert > Column

DELETE Edit Menu > Delete > Column Right-click > Delete > Column

Position cursor in desired location first, then:

NOTE:

If you already have the column highlighted, you will NOT see these dialog boxes. The action will automatically take place.

INSERT – DELETE rows

INSERT Insert Menu > Row Right-click > Insert > Row

DELETE Edit Menu > Delete > Row Right-click > Delete > Row

Position cursor in desired location first, then:

NOTE:

If you already have the row highlighted, you will NOT see these dialog boxes. The action will automatically take place.

Inserting/Deleting MULTIPLE Rows and Columns at one time You can insert or delete multiple rows or columns

at one time. Highlight the rows/columns BEFORE you select

the insert/delete option.

Rows highlighted Columns highlighted

HIDE and UNHIDE columns & rows You can hide columns and rows that include data you need

for your file but don’t need to see or print. Even though a row or column is hidden, its values are used in calculations.

You can see when a column or row is hidden, because their headings are also hidden.

FORMAT Menu > Row > Hide

Column C is “hidden.”

FREEZE and UNFREEZE In large worksheets, seeing two related columns or

rows on screen at the same time can be difficult if they are not next to each other.

You can keep the data in view by freezing one of the columns or rows.

WINDOW > FREEZE (or unfreeze)

Here the cursor was in column B when the “freeze” option was applied. Then you can scroll to the right to see all the other columns. Note: the line to the right of column B.

Horizontal Alignment The formatting toolbar

contains three horizontal alignment buttons: Left – Center – Right

You can also go to the FORMAT menu > Cells > Alignment Tab. Click the down-pointing arrow to select alignment.

Center Across Selection Option

The “Center Across Selection” option allows you to horizontally center multiple cells across a part of the worksheet.

Format Menu>Cells>Alignment Tab>Center Across Selection

Change Vertical Alignment

Vertical AlignmentThe cells were merged first and then the vertical alignment changed.

Wrap Text Option

Before “Text Wrap” After “Text Wrap”

The Wrap Text setting allows the label to split into more than one line in the cell.

Format Menu > Cells

Change Indents (Format Menu > Cells)

This feature moves the text away from the left edge of the cell.

This cell has an indent of 2.

Using Merge and CenterYou can merge cells to combine a range of cells into one cell that occupies the same amount of space.

You can also use the Merge and Center button on the toolbar.

Change Cell Orientation (Format Menu > Cell)

You can either enter the numerical degrees or drag the red diamond.

Applying Cell Borders Using the Borders button

You must highlight the cell(s) you want to have a border around before you select the border type.

A border is a line around a cell or a range of cells.

You must highlight the cell(s) you want to have a border before you select the border type.

Applying Cell Borders Using the Format Cells Dialog Box

When you click the DRAW button, your cursor will turn to a hand holding a pencil. You simply click the line(s) where you want a border added.

Applying Cell Borders Using the Borders toolbar

View Menu > Toolbars > Borders

Apply Shading Using the FILL button

The background of a cell or range of cells can be filled or shaded with a solid color of pattern.

The color on the button is the current color. You must click the down-pointing arrow to view the color palette shown here.

When you place your pointer over these you will see 52%, 40%, and 25% as a percentage of 100% black.

Apply Patterns Using the Format Cells Dialog Box

Number Formatting Using buttons on toolbar

Number Style buttons – inserts a dollar sign, a percent sign, or a comma.

Increase/Decrease Decimal buttons – to add or delete decimal places.

Number Formatting Using Format Cells dialog box

NUMBER formatting CURRENCY formatting

Examples of different formats:DATE formatting TIME formatting