GudAnuf, Inc.™ (Where only the BEST is Good Enough)

Looking for Software at GREAT Prices? Click Here

Useful productivity enhancers for the Microsoft Excel Users

Splitting numbers:

When you need to split a number into separate cells (example: XJR123 needs to be XJR in one cell, and 123 in the next), use these functions:
To extract from the left: =Left(source_cell,number_of_characters) example =Left(A1,3) (XJR is the result)
To extract from the middle: =Mid(source_cell,start_position,length) example =Mid(A1,3,3) (This would produce R12 in our example)
To extract from the right: =Right(source_cell,number_of_characters) example =Right(A1,3) (123 is the result)

Auto Days:

If you have a column of dates, and wish to have another column listing the days associated with them (Mon, Tues, etc), select the column where the days are to be displayed. Then right-click and choose Format Cells. Click the Number Tab and select Custom. Choose any of the Date formats, and change the Type box to read dddd. Click ok. Now in your worksheet, start with the top cell of the Day column and type =B1 (where B1 holds the date- 1/1/2000) Press Enter, and the Day appears. Use Auto-Fill to fill the rest of the column.

Summing calculated currencies:

You make a calculation, which gives you results stretching to 4 digits. Changing the format to currency, Excel merely loses the last two digits. (Example, 35.6848 and 24.3239 become $35.68 & $24.32) However, when you autosum these figures, Excel still sees the last two digits, so you end up with a sum that is not "correct" (In the above example, you'd get a sum of $60.01, instead of the $60.00 you'd expect) To change this, and get the "correct" sum: Choose Tools/ Options. In the Calculation tab, under "Workgroup Options," select "Precision as displayed" and then OK. Click OK to confirm that accuracy will be lost. This only affects the current workbook.

Printing color spreadsheets on a black and white printer:

You can still use color for your spreadsheets, even without a color printer. To print your beautiful spreadsheet in blacks and grays, click File | Setup. Then click the Sheet tab. Under Print, select Black and White. Click OK. Then print.

Named ranges:

To make formulas or finding a range of cells easier, you can select a range of cells (using Ctrl if they are not adjacent) and then type in a name in the Name Box (directly above cell A1- it usually shows the current cell reference in it.) For instance: create a column of numbers. Select them and type "test" in the Name Box (without quotes) In a blank cell, type sum(test) All the numbers in the test range will be summed. You can use this named range instead of cell ranges (like a1:a25) to make your formulas easier to understand.

Lost your named ranges?:

If you forget where all your named ranges are (or what they're named), you see them all at once by clicking in the Zoom box on your toolbar (the one that says 100% or some other percentage) and type in a number from 10-39 and press enter.

Define a multiplier:

Sometimes, you need to use a certain number repeatedly. Instead of hiding it off the printable area, define it, so that you can use it in formulas. For example, the tax rate... In a worksheet, click Insert | Name | Define. Type in the name you'll use (tax, in this example) and in the Refers to: box, type the tax rate and click OK. Now, to multiply cell A1 (or any other cell) by tax, click an empty cell and type =A1*tax.

Select all cells that contain formulas:

To see which cells contain formulas, go to Edit | Go To. In the Go To dialog box, choose Special. Select Formulas and click OK. Every cell which contains a formula will be selected.

Protecting cells:

When a cell contains formulas that you don't want deleted by accident, use the protection feature. 

1. Press Ctrl + A to select the entire sheet.
2. Choose Format + Cells; in the dialog box that appears, click the Protection tab.
3. Deselect Locked and click OK.
4. Choose Edit + Go To.
5. Click Special.
6. Under Select, choose Formulas; then click OK. Excel selects all the cells that contain formulas.
7. Choose Format + Cells; in the dialog box that appears, click the Protection tab.
8. Select Locked and click OK.
9. Choose Tools + Protection + Protect Worksheet.
10. In the dialog box that appears, click OK (Passwords can cause more trouble than they solve).
11. Save the workbook.

If you need to change a formula in the future, choose Tools | Unprotect, change the cell, and then choose Tools | Protect Worksheet to protect your formulas again.

Formatting 4-digit dates:

First, select the cells that need the special date format. Right-click and choose Format Cells. Now click the Number tab and from the Category list, click Custom. In the Type text box, type mm/dd/yyyy. Click OK. Now you should see all four digits of the year in those cells.

Positioning objects:

To make your drawn objects (text boxes, circles, etc.) position themselves more neatly, click on the Draw button of your Draw toolbar. Then choose Snap to | Grid.

Entering "special" numbers:

Excel makes it easier to enter "special" numbers (phone numbers, zip codes and Social Security numbers.) Select the range you will use for one of these types, then right click. Choose Format Cells. On the Number tab, choose Special. Choose one of the options. Now enter your data. Phone numbers will be formatted as (nnn)nnn-nnnn, although you only type the numerals. Social Security will add the hyphens for you. The main advantage in zip codes is that when you enter one beginning with zero, the zero will not be deleted. To start a number beginning with "0," you may also type an apostrophe before the 0. Only use this with numbers that will not be included in any calculations.

Flipping your spreadsheet:

Even the most proficient Excel users sometimes create a spreadsheet that really would work better if it were "flipped," putting the column headings in rows, and vice versa. Here's how: Select the range to transpose. Use Edit | Copy. Click on a cell outside the range you just copied (maybe even a different worksheet in the same workbook...) Then choose Edit | Paste Special. Select Transpose and click OK. Voila!

Change column widths from Print Preview:

If you check your spreadsheet in Print Preview, and notice that the column widths need adjusting, click the Margins button. You can use the handles that appear to adjust column widths. When you close Print Preview, your changes will be saved. Note: By clicking the Page Setup button in Print Preview, you can quickly access other features you may need, such as headers and footers, or fit on one page.

Evaluating part of a complex formula:

If you've written a complex formula that isn't quite working, you can check the values of its "pieces" individually to find the problem. Click on the cell with the formula. Then in the formula bar, select the part you wish to evaluate. Then Press F9. The value of that expression will show in the formula. Now press ESC to return to normal.

To find links in your spreadsheet:

Use Ctrl-Home to go to cell A:1. Then choose Edit | Find. In the find box, type an exclamation pt. (!) Check your settings: "Look in" should be on Formulas, and "Find entire cells" should not be checked. Click OK to find the first link, then Find Next to find the others.

See your formulas in the cells:

Instead of clicking on one cell to see its formula, then clicking on another to see its formula, you can see all formulas in a spreadsheet at once, in their cells, just by pressing Ctrl-` (That mark is on the button with your ~ at the top left of your keyboard.) To get back to normal, press Ctrl-` again. If you print with this setting, the formulas will print out. Note: this only affects the current spreadsheet- it does not change settings for any others.

Minimize data entry errors:

Select a cell or cells in a blank spreadsheet. Go to Data | Validation and choose the settings tab. The Allow list shows all the limits you can set for valid data in these cells. To try it, choose "Date" Set the date limit for 1/1/97-12/31/97. Click OK and then try to enter a date before 1997. You'll get an error message.
Go back and check out the other validation options. You can limit entry to a list, customize error messages, etc.

To change the number of sheets that appear in a new Excel workbook

Go to Tools/Options and pick the General tab. Change the "Sheets in new workbook" number.

To add a blank worksheet

To an existing workbook, open the workbook and choose Insert/Worksheet.

Inserting dates:

To insert today's date into a cell, click on the cell and then press CTRL+; (The control key and semicolon) This date remains static; it will not change to reflect the current date. If you need the date to change, type TODAY() into a cell. Now the date will change according to your system date, each time you open the workbook.

Change a range of numbers by a multiplier:

For instance, a price sheet needs all prices increased by 15%... Type the multiplier (in our case 1.15) in a blank cell. Right click and choose Copy. Now select the range of cells to be changed (remember, use your control key to select non-adjacent cells). Now choose Paste Special from the Edit menu. Under Operation, pick Multiply. Then click OK. You're done.

Tracing formulas:

You can easily see which cells affect, or are affected by, which other cells with the Auditing Toolbar. To use it, choose Auditing from the Tools menu. Click Show Auditing Toolbar. You can leave the toolbar floating, or drag it to the top or bottom of the window. To use it, click a cell with a formula. First click the Trace Precedents button. This will create arrows to the cells that are used to calculate the value of this cell. Now click the Trace Dependents button. This will point to cells that use this cell in their formulas. Use the Clear button when you need to clear the arrows. Use this trick when a formula is not calculating as expected, or you need to know if it's safe to clear a cell.

Adding a comment to a cell:

Sometimes, you need to add a comment to a cell to remind yourself or others of special circumstances. (For example, John's sales were down, because he was out sick that week.) To add a comment, right click a cell and choose Insert Comment. Type your comment in the text box, and then click outside the text box. Now your cell will have a red triangle in the top right corner. When your mouse is over that cell, the comment will appear. To edit or delete a comment, right click on the cell, and choose either Edit Comment or Delete Comment.

More on comments:

To print comments, go to File | Page Setup. Click the Sheet tab. In the Print section, along with the checkboxes, you'll see a Comments: and a drop-down list. You can choose to print the comments as they appear in the sheet, or at the end, with cell references.

Even more on comments:

To get rid on a single comment, use the Reviewing Toolbar (Right click any toolbar & pick Reviewing if it is not already visible.) Now click the cell with the comment and click the Delete comment on the Reviewing toolbar. To delete more than one comment: Start at the beginning (Ctrl-Home takes you there) and use the Next Comment button to find each comment & the Delete comment button to delete them.

Inserting multiple rows or columns:

If you need to insert one row, select a row (by clicking on the gray number to its left), right click and choose Insert. To insert multiples, (for example three rows), select three rows, right click and choose Insert. Three rows will be inserted. This works for columns also.

Checking formulas:

To see what range a formula includes, click in the formula (either in the formula bar, or in the cell itself) The cells included in the formula will be outlined in blue.