| Microsoft Office 2003: Excel Basic Excel Tips, Tricks, and Savage Amusement... |
 |
| [Software Tips] [Microsoft Excel] |
| Text Wrap in Excel Cells | |||
 If you type a long text string into an Excel worksheet cell,
the text will overflow into adjacent cells. If you would like to keep all of the text in a
single cell, first select the cell then pull down the Format menu, and choose the Cells
command.
When the "Format Cells" dialog box opens, click on the "Alignment" tab. Select the "Wrap text" check box and then click OK. Now when you reach the end of a line in your cell, press [Alt]+[Enter] to start a new line. |
|||
| Center Pages in Excel | |||
| If you have an Excel worksheet that occupies less than a full
page, Excel by default will print it in the upper-left corner of your paper. You can make
it look better in print by forcing Excel to print it in the center of the page.
From the top menu choose "File>Page Setup" and, when the dialog box opens, click the "Margins" tab. Now select the two check boxes (Horizontally and Vertically) under "Center on Page". The sample graphic on the dialog box will show you an approximation of what your page will look like. If you want to see exactly what it will look like, click the "Print Preview" button. If you don't like the way your spreadsheet looks when centered you can get back to the Page Setup dialog box, from the print preview screen, by clicking "Setup" on the print preview menu. |
|||
| Keeping Columns Hidden in Excel | |||
| If you send Excel spreadsheet files to others, and want to
insure that certain columns stay hidden you must use password protection on the file.
Before you password the file, first highlight all the cells you want people to be able to change and select "Format">"Cells" from the drop down menu. In the "Format Cells" dialog box select the "protection" tab and un-check the "locked" checkbox. You may have to do this several times if the cells in your spread sheet are not contiguous. Be sure that all the cells in the columns you want to hide have the "locked" option checked. Then hide the columns you don't want the person to see and select "Tools">"Protection">"Protect Sheet". Enter a password (don't forget the password since it CAN NOT be recovered) and click OK. Now when you send the spreadsheet, the person can only make changes to the sections you allowed them to and they can not "un-hide" the hidden columns. |
|||
| Proper Function in Excel | |||
| Version 4.x, 95 Here's an Excel function you may not have
seen. It's called Proper and its job is to render text with initial capitals.
Why would you need to use Proper? Well, if you purchase mailing lists, you know that many of them come in all caps. This is a poor stylistic choice for mailing labels, or embedded in a letter. This is where Proper comes in. Of course, its most efficient use is in a macro, but you can check it out without writing a macro. Press the Caps Lock key and type something into cell A1. Try something like THIS IS ALL CAPS Next, go to cell A2 and enter: =Proper (A1) and you'll get: This Is All Caps Proper deals with any text, uppercase, lowercase, or mixed. Whatever you apply it to will come out with the initial character of each word capitalized. |
|||
| Print to Fit in Excel | |||
| Sometimes it's not easy to guess how many pages an Excel
worksheet will require to print. To see how it's going to look, choose
"File">"Print preview" from the drop down menu. If the sheet will
require more than one page, Print Preview will tell you.
If your worksheet isn't much over a single page, you can choose "File">"Page Setup" and click on the Page tab. Select the "Fit To" check box and tell Excel to make it all fit on one page. Click on OK to close the dialog box and record your changes. You can see how it will look by checking Print Preview again. Note: This is often a valid selection if the worksheet isn't much over one page. If it is larger, then the reduction may make the printout difficult to read. |
|||
|
|||
| Add vertical text to Excel | |||
| Vertical text is a matter of alignment. When you enter data
into an Excel cell, it takes on the format and alignment assigned to the cell. The default
for text is left-aligned and horizontally oriented.
To make text vertical, click the cell and choose Format, Cells. When the Format Cells dialog box opens, click the Alignment tab. Now, click one of the examples under Orientation. Click OK to record your selection and close the dialog box. |
|||
| Date Fill in Excel | |||
| To create a series of dates in Excel, enter the starting date
in the first cell. Select and highlight all the contiguous cells in which you want a date
to appear (Row or Column) then from the top pull down menu choose Edit>Fill> Series.
This will open the Series dialog box. Here, choose "Date Unit" and the type of
date you want to use (Day, Weekday, Month, or Year.) Then select the "Step
value" and Click OK.
Note: The Step value is the value added to each date in succession. For example if you selected a step value of 2 with a starting date of 12/25/96 the next date filled in would be 12/27/96, 12/29/96...etc. |
|||
| Date & Time in Excel | |||
| If you'd like to insert the date into a cell or the formula bar, use the key combination of [CTRL]+[;] (semicolon.) To insert the current time into a cell or the formula bar, use [CTRL]+[:] (colon.) The colon is a shifted character, so you actually press [CTRL]+[SHIFT]+[:]. | |||
| Counting Days in Excel | |||
| Version 4.x, 95 If you'd like to know how many days there are between two dates open Excel and in one of the cells type:
Excel will return the number of days (61) between the two dates.
and you'll get 344 days. The trick here is to remember to use the quotes. If you don't use quotes, you'll get some very strange results. |
|||
| Fun with Dates in Excel | |||
| Version 4.x, 95 You can do more with Excel's date formatting than you might think.
Suppose you'd like a particular cell to show only the month and the day. Click the cell
(to select it) then choose Format, Cells and select Custom. Double-click the entry that's
in Type and press Delete to get rid of it. Now, under Type enter:
mmmm dd and then click OK. Let's say the date is 1/25/98. The cell will display January 25. Suppose now that you'd like to show the day of the week, the month, and the day (numerical.) Choose Format, Cells and select Custom again. This time, type: dddd, mmmm dd to produce a display of Sunday, January 25. Click OK to close the dialog box and record the changes. If you want to add the year, go back to the Format Cells dialog box and type: dddd, mmmm dd, yyy Now click OK. This time, the cell will display Sunday, January 25,1998. |
|||
| More Excel Range Fill Options | |||
| If you want to fill an Excel range with the same value or formula, first select the range, then press the [CTRL] key and simultaneously click on the first cell. Enter the value or formula in that cell, then hold down [CTRL] key and press [ENTER]. Excel will fill in the entire range. | |||
|
|||
Easy Sums in Excel |
|||
| Version 4.x, 95 Do you want to quickly sum a row of numbers in Excel? Once you have all the numbers entered, click on the cell where you want the sum to appear. Now click the sum button in the toolbar (it looks like a capital M on its side.) Use the mouse to select the numbers you want to sum, then press Enter. The sum will appear and you didn't even have to type in a formula. |
|||
| Entering Fractions in Excel | |||
| You can enter fractions into an Excel cell and use them in your calculations. The only
problem is that you have to be careful about how you enter fractions. For example, if you
enter
5/6 and press Enter, Excel will think you're entering a date and convert the number to the current date format. So, 5/6 becomes something like 6-May. To get around this problem, make a habit of entering fractions in the form zero, space, the fraction. It should look like this: 0 5/6 Now Excel knows you want to enter a fraction and will deal with the number correctly. |
|||
Edit Cells in Excel |
|||
| There are two ways to view and edit Excel formulas. If you select a cell that contains a formula, the formula will appear in the formula box right above the worksheet. You can click in the formula there to edit it. You can also view and edit the formula right in the cell. Double-click the cell and the formula will appear, ready to be edited. | |||
| AutoSave in Excel 1 of 3 | |||
| Version 4.x AutoSave is a handy Excel feature--one that saves many of us from disaster. Although Excel doesn't use AutoSave by default, you can easily add it to your system. Choose "Tools">"Add Ins" then select the AutoSave check box and click OK. After you select AutoSave, the command will appear in the Tools menu. Choose "Tools">"AutoSave" and you can tell Excel how often you want to invoke AutoSave, and if you want to be prompted before it saves the file. Un-check the "prompt before saving" box to let Excel save without bothering you. |
|||
| Excel AutoSave 2 of 3 | |||
| By default, when you use Excel's Add-in AutoSave, the program will prompt you when
it's time to save the document. If Excel is minimized when the time for a save
comes, its Taskbar icon will blink. So, if you see a blinking icon, don't worry it
is just Excel asking if you want to save the document. You can disable this prompt if you like. Choose "Tools">"AutoSave". When the AutoSave dialog box opens, deselect the "Prompt Before saving" check box and click OK. Now the icon will no longer blink. |
|||
| Excel AutoSave 3 of 3 | |||
| If you're concerned about losing Excel data during a system crash, or a sudden power
outage, you should be using Excel's AutoSave feature. However, AutoSave does have a
drawback: If you open a workbook to run a few what-if scenarios that you don't necessarily
want to save, AutoSave may save them anyway. If you want to be sure that AutoSave only saves what you want, set it to warn you before every save. Choose "Tools">"AutoSave" (If you don't have an AutoSave on your Tools menu, it's because you haven't installed AutoSave then check the option "Prompt Before Saving" and click OK. |
|||
| Tell Excel How to Save | |||
| Microsoft Excel saves files to the "My Documents" folder by default. You can change this by choosing Tools>Options, then clicking the General tab. Now change the Default File Location to whatever subdirectory you want to use. | |||
| Excel Easter Egg | |||
If you have never heard of the term before, it's the right time of year to learn about
"Easter Eggs". In the context of software (geekspeak), an Easter Egg is a hidden
feature or novelty that the programmers have put in their software. This can be anything
from a hidden list of the developers, to hidden commands, to jokes, to funny animations.
So in the spirit of the season, here are a couple of nicely colored "eggs" for
each operating system:
|
|||
| If you have the DirectX video drivers installed you will get a full screen animations that you can control. Use the mouse to fly around--the right button moves you forward; the left button moves you in reverse. If the drivers are not installed you will get a list of developers. | |||
| Excel Quick Moves | |||
| In Excel, you can copy a group of cells by selecting them and then pressing Ctrl-C. This
copies the cells to the Windows Clipboard. Now you can move to another cell range and
press Ctrl-V to paste the cells to the new location. If you prefer to move the group of cells, simply select them and press Ctrl-V at a new location. In either case, you'll find that you can eliminate the Ctrl-V part of the paste operation by clicking into the first cell of your target range and pressing Enter. This shortcut doesn't actually save any keystrokes, since you press Ctrl and V at the same time to make the paste. But simply pressing Enter has a certain elegance about it. |
|||
| Moving around in Excel | |||
| If you want to move to a new sheet in Excel, press [Ctrl]+[Page Down]. To move to a previous sheet, press [Ctrl]+[Page Up]. To get to the very last data cell in your worksheet, press [Ctrl]+[End]. [Ctrl]+[Home] will take you to cell A1. | |||
| Eight Timesaving Shortcuts | |||
| Excel has a lot of useful shortcut keys. The more you know, the more time you can save. Here are eight shortcuts we hope you find useful: To enter the current date into the active cell, press CTRL + ;. To enter the current time, press CTRL + SHIFT + ;. To enter or edit a cell comment in the active cell, press SHIFT + F2. To quickly copy the contents of a cell to the cell directly to the right, select the cell that will receive the copy, then press CTRL + R. To quickly copy the contents of a cell to a cell directly below it, select the cell that will receive the copy, then press CTRL + D. To copy the contents from a cell directly above the active cell, press CTRL + '. If the cell contains a formula, this shortcut makes an identical copy of that formula, rather than adjusting the cell reference relatively. Pressing CTRL + SHIFT + ' also copies the contents from the cell directly above the active cell. However, if the cell contains a formula, this shortcut copies the result of the formula only rather than the formula itself. An easy way to view all formulas in a worksheet without having to select the cell containing it is to press CTRL + ~. Press the shortcut again to switch back to the Normal view. |
|||
|
