Chapter Title :  | Worksheets | Zoom| Split | Freeze Panes | Group Worksheets |Consolidate |View Multiple Worksheets |Spelling

A worksheet is a collection of cells where you keep and manipulate the data. By default, each Excel workbook contains three worksheets.

Select a Worksheet

When you open Excel, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.

Sheet1

To select one of the other two worksheets, simply click on the sheet tab of Sheet2 or Sheet3.

Rename a Worksheet

By default, the worksheets are named Sheet1, Sheet2 and Sheet3. To give a worksheet a more specific name, execute the following steps.

1. Right click on the sheet tab of Sheet1.

2. Choose Rename.

Rename a Worksheet

3. For example, type Sales 2010.

Specific Name

Insert a Worksheet

You can insert as many worksheets as you want. To quickly insert a new worksheet, click the Insert Worksheet tab at the bottom of the document window.

Insert a Worksheet

Result:

Added Sheet

Move a Worksheet

To move a worksheet, click on the sheet tab of the worksheet you want to move and drag it into the new position.

1. For example, click on the sheet tab of Sheet4 and drag it before Sheet2.

Move a Worksheet

Result:

Different Order

Delete a Worksheet

To delete a worksheet, right click on a sheet tab and choose Delete.

1. For example, delete Sheet4, Sheet2 and Sheet3.

Delete a Worksheet

Result:

One Worksheet left

Copy a Worksheet

Imagine, you have got the sales for 2010 ready and want to create the exact same sheet for 2011, but with different data. You can recreate the worksheet, but this is time-consuming. It’s a lot easier to copy the entire worksheet and only change the numbers.

1. Right click on the sheet tab of Sales 2010.

2. Choose Move or Copy…

Copy a Worksheet

The ‘Move or Copy’ dialog box appears.

3. Select (move to end) and check Create a copy.

Dialog Box

4. Click OK.

Result:

Copied Worksheet

Note: you can even copy a worksheet to another Excel workbook by selecting the specific workbook from the drop-down list (see the dialog box shown earlier).

 

Zoom

In most cases, you can use the minus and plus symbols in the status bar to quickly zoom the document. Use the buttons on the View tab to zoom to a specific percentage and to zoom to a selection.

1. To quickly zoom the document, use the minus and plus symbols in the status bar.

Quickly Zoom the Excel Document

To zoom to a specific percentage, execute the following steps.

2. On the View tab, click Zoom.

Click Zoom

3. Enter a number (between 10 and 400) and click OK.

Enter a Number and click OK

To zoom to a selection, execute the following steps.

4. First, select a range of cells.

5. On the view tab, click Zoom to Selection.

Click Zoom to Selection

Split

Split your worksheet to view multiple distant parts of your worksheet at once. To split your worksheet (window) into a upper and lower part (pane), execute the following steps.

1. Click the split box above the vertical scroll bar.

Click Split Box

2. Drag it down to split your window.

Split Worksheet

3. Notice the two vertical scroll bars. For example, use the lower vertical scroll bar to move to row 49. As you can see, the first 6 rows remain visible.

View Distant Parts

4. To remove the split, double click the horizontal split bar that divides the panes (or drag it up).

Note: in a similar way, you can use the split box to the right of the horizontal scroll bar to split your window into a left and right pane. You can even split your window into four panes. Any changes you make to one pane are immediately reflected in the other ones.

 

Freeze Panes

If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

Freeze Top Row

To freeze the top row, execute the following steps.

1. On the View tab, click Freeze Panes, Freeze Top Row.

Freeze Top Row

2. Scroll down to the rest of the worksheet.

Result. Excel automatically adds a black horizontal line to indicate that the top row is frozen.

Freeze Top Row Result

Note: to keep the first column visible while scrolling through the right of the worksheet, click Freeze First Column.

Unfreeze Panes

To unlock all rows and columns, execute the following steps.

1. On the View tab, click Freeze Panes, Unfreeze Panes.

Unfreeze Panes

Freeze Panes

To freeze panes, execute the following steps.

1. Select row 3.

2. On the View tab, click Freeze Panes, Freeze Panes.

Freeze Rows

3. Scroll down to the rest of the worksheet.

Result. All rows above row 3 are frozen.

Freeze Rows Result

Note: to keep columns visible while scrolling to the right of the worksheet, select a column and click Freeze panes.

4. Select cell C3 (unfreeze panes first).

5. On the View tab, click Freeze Panes, Freeze Panes.

Result. The region above row 3 and to the left of column C is frozen.

Freeze Rows and Columns Result

Group Worksheets

You can group worksheets in Excel if you want to edit multiple worksheets at the same time. Our workbook contains 3 similar worksheets (North, Mid and South) and a blank fourth worksheet.

1. To group worksheets, hold down CTRL and click the sheet tabs of the sheets you want to group.

Group Worksheets in Excel

2. Release CTRL.

Now you can edit multiple worksheets at the same time.

3. For example, on the North sheet, change the value of cell B2 to $1000 and delete row 4.

North

4. Go to the other two worksheets and you’ll see that these worksheets have been edited as well.

Mid

South

5. To ungroup, right click one of the sheet tabs and click Ungroup Sheets or click any sheet tab outside the group. For example, the sheet tab of Sheet4.

Consolidate

You can use Excel’s Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet. Below you can find the workbooks of three districts.

Before you start: if your worksheets are identical, it’s probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data.

District1

District2

District3

As you can see, the worksheets are not identical. However, the beauty of the Consolidate feature is that it can easily sum, count, average, etc this data by looking at the labels. This is a lot easier than creating formulas.

1. Open all three workbooks.

2. Open a blank workbook. On the Data tab, click Consolidate.

Click Consolidate

3. Choose the Sum function to sum the data.

4. Click in the Reference box, select the range A1:E4 in the district1 workbook, and click Add.

5. Repeat step 4 for the district2 and district3 workbook.

Add References

6. Check Top row, Left column and Create links to source data.

Note: if you don’t check Top row and Left column, Excel sums all cells that have the same position. For example, cell B2 (in distric1.xls) + cell B2 (in district2.xls) + cell B2 (in district3.xls). Because our worksheets are not identical, we want Excel to sum cells that have the same labels. If you check Create links to source data, Excel creates a link to your source data (your consolidated data will be updated if your source data changes) and creates an outline.

7. Click OK.

Result.

Collapsed Consolidated Data

Expanded Consolidated Data

View Multiple Worksheets

If you want to view multiple Excel worksheets at the same time, execute the following steps.

1. Open a workbook.

2. On the View tab, click New Window.

Click New Window

Excel opens a new window containing another view of the document.

3. On the View tab, click Arrange All.

4. Select the desired arrange setting. For example, click Vertical.

Vertical Arrange Setting

5. Click OK.

The titles (view-multiple-worksheets:2 and view-multiple-worksheets:1) indicate that two windows of the same file are open.

6. In the right window, select the sheet tab of Wk2. You can now view the sales in week 1 and week 2 at the same time.

View Multiple Worksheets

Note: any changes you make to one window are immediately reflected in the other window. On the View tab, in the Window group, click View Side by Side (by default, Synchronous Scrolling is activated), to scroll both worksheets at the same time.

Spelling

Learn how to check the spelling of text in Excel. You can also add words to your custom dictionary or AutoCorrect list.

1. On the Review tab, click Spelling.

Click Spelling

2. Select a suggestion.

3. Click Change to correct the misspelling in cell A2.

Change

4. Click Change All to correct all instances of this misspelling (cell A2 and cell A7).

Change All

5. Click Ignore Once to ignore the misspelling in cell A2. Click Ignore All to ignore all instances of this misspelling.

6a. Click Add to Dictionary to add this word to your custom dictionary.

Add to Dictionary

6b. To edit this list, click Options (or if you don’t have this screen open, click File, Options, Proofing), and then click Custom Dictionaries.

Custom Dictionaries

7a. Click AutoCorrect to add this word to your AutoCorrect list (every time you type anwer, Excel will replace it with answer).

AutoCorrect

7b. To edit this list, click Options (or if you don’t have this screen open, click File, Options, Proofing), and then click AutoCorrect Options.

AutoCorrect Options

 

 

Leave a comment

Your email address will not be published. Required fields are marked *