Microsoft Excel Tips
A collection of tips and tricks for Microsoft Excel, on how to password protect a document, add comments to cells, insert symbols and special characters, fill date ranges, and jump to a different cell.
Password Protect A Document
To prevent prying eyes from accessing and/or changing a spreadsheet, password protect it. Select File/Save As, then (depending on your version of Excel) click the Tools menu or Options button within the Save As dialog box, and pick General Options. Excel 2007 users can click the circular "Office" button in the top left to find Save As.
"Password To Open" sets a password so no one else can view the spreadsheet. If you leave that blank and enter a "Password to Modify" then anyone can open the document but it will always open read-only unless they have the password.
Cell Comments
When exchanging spreadsheets it can be handy to include comments that mark a particular cell. To do so, right click the cell and choose Insert Comment (on older versions this is found under the Insert menu). Type a comment and add formatting. Drag the box "handles" to make the comment box bigger. When you're done, click elsewhere and the comment disappears, leaving a small triangular marker in the upper right corner of the cell. Hover the mouse over the triangle to view the comment, or right click and pick Edit Comment to modify it.
Always Display Page Breaks
Excel normally displays page breaks only after a worksheet is previewed, which is inconvenient at times. To have Excel always show page breaks, open Tools/Options (Edit/Preferences on Macintosh). Click the View tab, and select the Page Breaks checkbox.
Excel 2007 users can click the circular "Office" button in the top left and click Excel Options, then Advanced, then under Display Options For This Worksheet click Show Page Breaks.
Text On Multiple Lines Within A Cell
When typing into a cell, press Alt+Enter to jump to a new line and continue typing. That saves splitting text across multiple cells.
Insert Symbols
Excel 2002 and later added a convenient way to insert symbols and other special characters into cells. From the Insert menu or tab, choose Symbol. The Symbols tab shows a grid of characters, with Subsets like Currency Symbols and Mathematical Operators. The Special Characters tab shows a list of commonly used characters, like the trademark symbol. Select a character and click the Insert button to add it at your cursor location. Click Cancel or Close to close the dialog box and return to typing.
Jump To The Last Cell
If you are navigating a large spreadsheet and want to jump to the end of a column or row, move the mouse cursor to the border of the selected cell (it will turn into a four-headed arrow) and double click. Excel will jump to the last non-blank cell in that direction. The trick also works via the keyboard: press End and then press an arrow key to jump cells, or hold down Ctrl and press an arrow key.
If you happen to know the number of the cell you want (e.g., "D14"), type that into the "name box" in the upper left corner and press Enter to jump to that cell.
Auto-Fill Dates
Excel will automatically fill in number series or dates for you, but it can also fill partial date ranges, such as weekdays. Normally if you type "10" and "20" into two cells, select them both with the mouse, and grab the handle towards the lower right of the selection outline, you can drag that to extend the range, and Excel will fill in "30," "40," and so on. If you enter in a date, and use the right mouse button to drag the handle down, a popup will appear that allows the section of choices like "Weekdays" or "Months." The Weekdays option, for example, will fill in sequential dates but skip weekends.
April 2008
Send this article to a friend!
Subscribe to The ITS Connection
Related articles