MICROSOFT EXCEL 2011 MANAGE WORKSHEETS

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

1

Insert, move, and delete cells and cell ranges ......................................... 4 Insert blank cells ........................................................................................................ 4 Insert blank rows or columns.................................................................................... 5 Delete rows or columns ............................................................................................. 6 Move cells ................................................................................................................... 6 Delete cells ................................................................................................................. 7

Split and freeze rows and columns ........................................................... 8 Freeze top rows and first columns ........................................................................... 8 Freeze a specific range of cells................................................................................. 9 Unfreeze frozen rows and columns ........................................................................ 10 Split rows and columns ........................................................................................... 10 Remove the split between rows and columns ....................................................... 12

Manage worksheets .................................................................................. 13 Create a worksheet in the workbook ...................................................................... 13 Rename a sheet tab.................................................................................................. 14 Move or Copy a worksheet to another workbook.................................................. 14 Rearrange worksheets within a workbook ............................................................. 16 Color sheet tabs ....................................................................................................... 16 Delete worksheets .................................................................................................... 17

Create, edit, and delete Headers and Footers ........................................ 18 Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

2

Insert a standard or customized Header or Footer ............................................... 18 Insert an image to a Header or Footer .................................................................... 21 Remove a Header and Footer .................................................................................. 24

The following training document is using information from Lynda.com and Microsoft Office Excel 2011 Help

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

3

Insert, move, and delete cells and cell ranges Insert blank cells When you insert blank cells, you can choose whether to shift other cells down or to the right to accommodate the new cells. Cell references adjust to match the location of shifted cells Select the cell, or range of cells, where you want to insert additional cells NOTE

Select the same number of cells as you want to insert. For example, to insert five blank cells, select five cells

Form the Ribbon, click Home tab  Cells  click the down arrow next to Insert  choose Insert cells

In the Insert dialog box, select a desired option  Shift cells right – move the selected cells to the left one column  Shift cells down – move the selected cells down one row

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

4

Insert blank rows or columns When you insert additional rows or columns, they appear above or to the left of the selected row or column on your sheet. Cell references adjust to match the location of shifted cells Move the mouse pointer to row headers or column headers to make it change to a right-pointing black arrow or a down-pointing black arrow

Select the row headers or column headers where you want to insert additional rows or columns NOTE

Select the same number of rows or columns as you want to insert. For example, to insert five blank rows, select five rows.

Hold down Control + Left click and click Insert from the shortcut menu

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

5

Delete rows or columns Move the mouse pointer to row headers or column headers to make it change to a right-pointing black arrow or a down-pointing black arrow Select the row headers or column headers where you want to delete Hold down control + Click and click Delete from the shortcut menu

Move cells NOTE  When you move or copy a cell in Excel, the content of the moved or copied cell overwrites any data in the destination cell. Likewise, any formatting applied to the cell (including Conditional formatting and Data validation), overwrites any formatting that existed in the destination cell.  If the cell contains a formula, the cell references are not adjusted. Therefore, the contents of the moved or copied cell and of any cells that point to them might display the #REF! error value. If that happens, you will have to adjust the reference manually Select the cells or range of cells that you want to move Point the mouse cursor to the border of the cell that you selected to make it become a grabbing hand

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

6

Drag the cells to another desired location

Delete cells Select the cells that you want to delete From the Ribbon, click Home tab  Cells  click the down arrow next to Delete  choose Delete Cells

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

7

In the Delete dialog box, click the direction in which you want to shift the surrounding cells

Split and freeze rows and columns Freeze top rows and first columns You can prevent important rows or columns from scrolling off the screen by freezing them. For example, you can freeze a top row that contains column headings so that the labels remain visible as you scroll in the sheet.

From the Ribbon, click Layout tab  View  select Normal

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

8

Select any cell in the worksheet From the Ribbon, click Layout tab  Window  click the down arrow next to Freeze button choose either one or both the below options  Freeze Top Row  Freeze First Column

Freeze a specific range of cells From the Ribbon, click Layout tab  View  select Normal

Do one of the following:

From the Ribbon, click Layout tab  Window  click the down arrow next to Freeze button choose Freeze Pane

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

9

Unfreeze frozen rows and columns From the Ribbon, click Layout tab  View  select Normal

Select frozen rows or columns that you want unfreeze From the Ribbon, click Layout tab  Window  click the down arrow next to Freeze button choose Unfreeze

Split rows and columns If you have a huge worksheet and you need to work with two separate pieces of data at the same time but they are not on the screen at the same time, you can split the worksheet into two scrollable areas instead of scrolling back and forth From the Ribbon, click Layout tab  View  select Normal

Do one of the following:

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

10

From the Ribbon, click Layout tab  Window  click Split button

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

11

Remove the split between rows and columns From the Ribbon, click Layout tab  View  select Normal

Select any cell on the worksheet From the Ribbon, click Layout tab  Window  click Split button

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

12

Manage worksheets A worksheet is like a folder in a drawer that can store a lot of data and span more than one printed page. Like file folders, the best way to organize data is to create separate worksheet for data in a specific set. For example, if you create a workbook to summarize yearly sales data, you could create a separate worksheet for each month In a workbook, the worksheet name tabs are located at the bottom left of the Excel program window.  The active worksheet is white  Inactive worksheets are gray  To switch between worksheets, click the desired sheet tab

Create a worksheet in the workbook Move the mouse to sheet tabs area at the bottom left of the Excel program Click the Insert sheet button NOTE

Excel names the worksheets by an incrementing numerical value

TIP There are no limits to the number of worksheets you can put in a workbook. However, if you have more than 15 or 20 sheets in a workbook, you should split your data into more than one workbook

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

13

Rename a sheet tab By default, Excel names the worksheet by incrementing numerical values. In order to indicate what data that is currently on the worksheet, you should rename sheet name tab, to give you an idea what is inside each worksheet Double click a sheet tab that you want to rename to highlight the sheet name

Type in the new name

Press return to rename the sheet name tab

Move or Copy a worksheet to another workbook Select a desired worksheet Do one of the following:  From the Menu Bar, click Insert  choose Move or Copy Sheet

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

14

 Hold control + click and choose Move or Copy… from the shortcut menu

In the Move or Copy dialog box, do the following  Click the list down arrow To book and select (new book) to remove the entire selected worksheet from the workbook and move to another workbook

 Select Create a copy check box if you want to copy the worksheet to a new workbook

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

15

Rearrange worksheets within a workbook Drag the sheet tab that you want to reposition NOTE

The mouse pointer will change to a file icon and there is a small downward-pointing black triangle which tells you the position the worksheet will be in when you release the mouse button

Place it in a desired position

Color sheet tabs Select a desired sheet tab Hold control + click and choose Tab Color… from the shortcut menu

Select a desired color in the Tab Color dialog box

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

16

Delete worksheets Select a desired worksheet Hold control + click and choose Delete from the shortcut menu NOTE

Deleting a worksheet is nonreversible. Therefore, you cannot go back and all of the data is gone

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

17

Create, edit, and delete Headers and Footers Headers and Footers can help to determine what kind of data that the worksheet contains, when it was last updated, including information related to the file name, date the file was printed, and the page numbers, at the bottom or top of each printed page NOTE  Headers and Footers don't show on the worksheet in normal view — they are only visible in page layout view and on the printed pages.  Each sheet in a workbook can have its own headers and footers  When you customize your header or footer, you can add images, page numbers, date, time, sheet name, file name and path, and more.

Insert a standard or customized Header or Footer From the Ribbon, click Layout tab  View  choose Page Layout

From the Ribbon, click Layout tab  Page Setup  choose Header & Footer

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

18

In the Page Setup dialog box, select Headers & Footers tab  To add a pre-made header or footer, click the list down arrow under Header section or Footer section and select a desired style from the list

 To add a customized header or footer, click Customize Headers or Customize Footers button



Use the controls in the Headers dialog box or Footers dialog box to create a custom style



Select desired positions to insert a custom header or footer in separate sections: Left, Center and Right section



Enter the text you want to appear in the header or footer

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

19

TIP

To create a header or footer with the style “Page 1 of 5”, do the following:  Select a desired position to insert a header or footer  Click Insert Page Numbers button (displayed as & [Page] in the section box)  Press space key on the keyboard  Type the word “of”  Press space key on the keyboard  Click Insert Page Numbers button  Click OK button

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

20

Insert an image to a Header or Footer From the Ribbon, click Layout tab  View  choose Page Layout

From the Ribbon, click Layout tab  Page Setup  choose Footer & Header

In the Page Setup dialog box, do the following:  Select Headers & Footers tab  Click Customize Headers or Customize Footers button

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

21

In the Headers dialog box or Footers dialog box, do the following:  Select desired positions to insert a custom header or footer in separate sections: Left, Center and Right section  Click Insert Pictures button

In the Choose a Picture dialog box, browse a desired picture and click Insert button

NOTE

To format an image inserted in the header or footer of the document, do the following:

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

22

 Select the image by high the code in the section you want to insert it into  Click Format Picture

 Use the controls in the Format Picture dialog box to edit the picture

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

23

Remove a Header and Footer Form the Ribbon, click Layout tab  Page Setup  click Header & Footer In the Page Setup dialog box, select Headers & Footer tab and do the following:  To remove a standard header or footer, click the list down arrow under Headers section or Footer section and select (none)

 To remove a custom header or footer, click Customize Header button or Customize Footer button and text in the sections box

Microsoft Excel 2011 Essentials – Manage Worksheets Last Edited: 2012-07-10 Technology Training and Education © Metropolitan State University of Denver

24