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.
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.
To select one of the other two worksheets, simply click on the sheet tab of Sheet2 or Sheet3.
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.
3. For example, type Sales 2010.
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.
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.
To delete a worksheet, right click on a sheet tab and choose Delete.
1. For example, delete Sheet4, Sheet2 and Sheet3.
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…
The ‘Move or Copy’ dialog box appears.
3. Select (move to end) and check Create a copy.
4. Click OK.
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).
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.
To zoom to a specific percentage, execute the following steps.
2. On the View tab, click Zoom.
3. Enter a number (between 10 and 400) 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.
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.
2. Drag it down to split your window.
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.
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.
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.
To freeze the top row, execute the following steps.
1. On the View tab, click Freeze Panes, 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.
Note: to keep the first column visible while scrolling through the right of the worksheet, click Freeze First Column.
To unlock all rows and columns, execute the following steps.
1. On the View tab, click Freeze Panes, Unfreeze Panes.
To freeze panes, execute the following steps.
1. Select row 3.
2. On the View tab, click Freeze Panes, Freeze Panes.
3. Scroll down to the rest of the worksheet.
Result. All rows above row 3 are frozen.
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.
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.
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.
4. Go to the other two worksheets and you’ll see that these worksheets have been edited as well.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
2. Select a suggestion.
3. Click Change to correct the misspelling in cell A2.
4. Click Change All to correct all instances of this misspelling (cell A2 and cell A7).
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.
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.
7a. Click AutoCorrect to add this word to your AutoCorrect list (every time you type anwer, Excel will replace it with answer).
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.