Troubleshooting
Resetting the Last Cell
| Q1: | I pressed Ctrl+End to go to the last cell in my worksheet, but I ended up with the insertion point in a blank cell below and to the right of the actual end of the sheet. How do I convince Excel to jump to the actual end of the sheet? |
| A1: | When you select the last cell in a worksheet, either by using the Go To Special dialog box or by pressing Ctrl+End, Excel actually jumps to the last cell that has ever contained data or formatting. As you've seen, that can produce unexpected results, especially if you've deleted a large number of rows or columns (or both) from a list or worksheet model, or if you once placed a range of data in an out-of-the-way location and then moved or deleted it. In that case, selecting the Last Cell option might position the insertion point in a cell that's far beyond the actual end of the sheet. To reset the sheet so that you can truly jump to the last cell, delete all rows that are between the actual end of the sheet and the location that Excel insists on identifying as the last cell, and then repeat the process for all columns that match that definition.
If this is a common occurrence, you can create a one-line macro that will reset the last-cell location in the current sheet. Press Alt+F11 to open the Visual Basic Editor and enter the following code: Sub ResetRange()
ActiveSheet.UsedRange
End Sub
Be sure to save the ResetRange macro in an easily accessible location, such as your Personal macro workbook; then run it whenever you encounter a worksheet that needs this type of cleanup. |
Adjusting Header and Footer Margins
| Q1: | I created a complex custom footer for a worksheet, but when I try to print, the footer runs into data at the bottom of the sheet. |
| A1: | By default, Excel positions headers and footers a half-inch from the edge of the page and another half-inch from the worksheet's data. That's ideal for a one-liner, but if you try to add too much information in either place—for example, if you insert a long boilerplate paragraph required by a government agency at the bottom of each sheet—you'll quickly overrun that margin. If you decrease the Top or Bottom margins without also adjusting the Header or Footer margins, your data might also collide. You can enter an exact measurement for any of these margins by using the Margins tab on the Page Setup dialog box. If you've already created the header and footer, however, it's much easier to set the margins visually. Choose File, Print Preview; click the Margins button, if necessary, to display the margin markers along each edge of the preview window, and drag the indicators up or down until the preview looks right. |
Passwords Don't Work on Web Workbooks
| Q1: | When I try to save a workbook as a Web page, I get an error message warning that the workbook or sheet is password-protected. |
| A1: | For security reasons, Excel won't let you save a password-protected workbook or worksheet in HTML format. If the entire workbook is protected, you can still save an individual sheet. If any sheet is protected, however, you cannot publish that sheet or even a selection from it in HTML format. Temporarily remove the password protection by choosing Tools, Protection, Unprotect Sheet. After entering the correct password, you can publish the Web page and then restore the protection. |
Data Validation Limitations
| Q1: | I created a set of validation rules to protect data entry, but when users returned the filled-in worksheet, I found invalid data in those cells. I've triple-checked the data-validation rules, and I'm certain they're working properly. What's the problem? |
| A1: | Validation settings apply only when the user types data into a cell. If the user copies or cuts data from another source and pastes it into the cell via the Clipboard, Excel ignores the rule. There is no workaround for this problem, so you'll have to train your users not to use the Clipboard when filling in forms. Also, if any cell contains a formula as well as a data-validation rule, Excel ignores the rule.
If you want to triple-check the values in cells protected by data-validation rules to make sure they're correct, use the Go To dialog box. Press F5 and click the Special button, and then check the Data Validation option. Click All to see all cells with data-validation rules, or Same to see only cells whose rules match the currently selected cell. |
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.