What's the Current Date or Time?
You may be familiar with Excel's NOW function, which plugs the current date and time into your spreadsheet. Just enter =NOW() into any cell, and Excel displays the date and time formatted according to the regional options you've set for Windows. If you just want to see the date, use the TODAY function, =TODAY(), instead.
But if you want to use either of these functions as a type of "timestamp" you'll find that they don't do the job, because they're automatically updated whenever your worksheet is calculated or re-opened. The cell that displays today's date will display tomorrow's date when you open the spreadsheet tomorrow.
To insert a fixed date into a cell--a date that you don't want to change--hold down Ctrl while you press the semicolon ( ;) key. To insert the current time (without a date), press Ctrl-Shift-Semicolon.
These commands are useful if you like to document your work. For example, you might want to keep track of when you added new data to your worksheet.
Showing posts with label Tricks. Show all posts
Showing posts with label Tricks. Show all posts
Tuesday, June 8, 2010
Shade Alternate Rows
Shade Alternate Rows
Looking for a way to simulate that nerdy green-banded computer paper on screen? Look no further. Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy. Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility.
Here's how to do it:
1. Highlight the range of cells or rows or columns that you want to format.
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.
4. Click the Format button to bring up the Format Cells dialog box.
5. Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). For example, you might select a dark blue background, accompanied by a mellow yellow text color.
6. Click OK twice to return to your worksheet.
The best part is that the row shading is dynamic: The alternate row shading persists even if you insert or delete rows within the original range. By the way, if get tired of this new look and want to get things back to normal, just select the range, choose Format, Conditional Formatting, and click the Delete button in the Conditional Formatting dialog box.
Looking for a way to simulate that nerdy green-banded computer paper on screen? Look no further. Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy. Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility.
Here's how to do it:
1. Highlight the range of cells or rows or columns that you want to format.
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.
4. Click the Format button to bring up the Format Cells dialog box.
5. Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). For example, you might select a dark blue background, accompanied by a mellow yellow text color.
6. Click OK twice to return to your worksheet.
The best part is that the row shading is dynamic: The alternate row shading persists even if you insert or delete rows within the original range. By the way, if get tired of this new look and want to get things back to normal, just select the range, choose Format, Conditional Formatting, and click the Delete button in the Conditional Formatting dialog box.
Add a Drop-Down List
Add a Drop-Down List
Inserting a drop-down list to a cell is a slick trick that can add a touch of professionalism to your worksheets--and also ensure that erroneous data is not entered into the cell. And best of all: Macros are not required!
Assume that you have an input cell in which the user is supposed to enter a month name: January, February, and so on. Here's how to add a drop-down list to that cell to make data entry a breeze--and save some keystrokes.
1. Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet. In Excel terminology, a rectangular group of cells (such as E1 to E12) is called a range.
2. Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)
3. Choose Data, Validation to display the Data Validation dialog box.
4. Click the Settings tab.
5. In the Allow field, select List.
6. In the Source field, specify the range that contains the list items. In this example, the items are in cells E1 to E12, so type =E1:E12 into the field.
7. Click OK.
After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.
If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.
If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into the Source field in Step 6. Just separate each list item with a comma.
Inserting a drop-down list to a cell is a slick trick that can add a touch of professionalism to your worksheets--and also ensure that erroneous data is not entered into the cell. And best of all: Macros are not required!
Assume that you have an input cell in which the user is supposed to enter a month name: January, February, and so on. Here's how to add a drop-down list to that cell to make data entry a breeze--and save some keystrokes.
1. Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet. In Excel terminology, a rectangular group of cells (such as E1 to E12) is called a range.
2. Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)
3. Choose Data, Validation to display the Data Validation dialog box.
4. Click the Settings tab.
5. In the Allow field, select List.
6. In the Source field, specify the range that contains the list items. In this example, the items are in cells E1 to E12, so type =E1:E12 into the field.
7. Click OK.
After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.
If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.
If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into the Source field in Step 6. Just separate each list item with a comma.
Sunday, May 30, 2010
Removing Extra Breaks in Word Documents
Removing Extra Breaks in Word Documents
Follow these steps:
1. Press Ctrl + H to open the Find and Replace dialog box
2. In the first box, enter ^p^p (the "p" must be lower case)
3. In the second box, enter ^p
4. Click Replace All
Note: This will replace two paragraph breaks with one. You can specify other options, depending on the number of paragraph breaks you'd like between paragraphs. You can also replace a paragraph break with another character, if you choose.
If you copied the text from the Internet, this might not work for you. That's because there are different types of breaks in HTML files. Not to worry, there is a solution:
5. Press Ctrl + H to open the Find and Replace dialog box
6. In the first box, enter ^l (the "l" must be lower case)
7. In the second box, enter ^p
8. Click Replace All
You can then replace double breaks as necessary.
Follow these steps:
1. Press Ctrl + H to open the Find and Replace dialog box
2. In the first box, enter ^p^p (the "p" must be lower case)
3. In the second box, enter ^p
4. Click Replace All
Note: This will replace two paragraph breaks with one. You can specify other options, depending on the number of paragraph breaks you'd like between paragraphs. You can also replace a paragraph break with another character, if you choose.
If you copied the text from the Internet, this might not work for you. That's because there are different types of breaks in HTML files. Not to worry, there is a solution:
5. Press Ctrl + H to open the Find and Replace dialog box
6. In the first box, enter ^l (the "l" must be lower case)
7. In the second box, enter ^p
8. Click Replace All
You can then replace double breaks as necessary.
Thursday, May 27, 2010
display formulas so you can troubleshoot or debug them
Press [Ctrl]~ to display formulas so you can troubleshoot or debug them
you can display your formulas by pressing [Ctrl]~. If you want to view the dependent cells for a particular cell, select it before you press [Ctrl]~.
you can display your formulas by pressing [Ctrl]~. If you want to view the dependent cells for a particular cell, select it before you press [Ctrl]~.
Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)
Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)
If you have a long list of data that contains blank rows that you want to delete, you have several options.
* You could select each row, one by one, and delete them individually. If you have dozens or hundreds of rows, this could be time consuming.
* You could sort the rows alphabetically, which would put all the blank rows together, and then delete that group of blank rows. However, there will be times when you may want to maintain the order of your list, so this would not be an option.
* You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.
* You could use a little-known shortcut to quickly select the blanks in your list and delete them.
This week I will cover option number 4 and next week I will cover option number 3. The first two are self-explanitory and usually are not your best choice, so I won't discuss those.
Suppose you receive a list of data that contains blank rows that you want to remove without affecting the order of the list. This technique will allow you to delete all rows that have blank cells in a particular column. Here's the fastest way to do it.
1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
2. Now, select the cells in one column from the top of your list to the bottom.
3. Make sure that all the blank cells in this selected range are the rows you want to delete.
4. Press the F5 key on your keyboard (or select Edit, Goto).
5. Click the Special button.
6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
7. Now choose Edit, Delete, select the Entire Row option and click OK.
If you have a long list of data that contains blank rows that you want to delete, you have several options.
* You could select each row, one by one, and delete them individually. If you have dozens or hundreds of rows, this could be time consuming.
* You could sort the rows alphabetically, which would put all the blank rows together, and then delete that group of blank rows. However, there will be times when you may want to maintain the order of your list, so this would not be an option.
* You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.
* You could use a little-known shortcut to quickly select the blanks in your list and delete them.
This week I will cover option number 4 and next week I will cover option number 3. The first two are self-explanitory and usually are not your best choice, so I won't discuss those.
Suppose you receive a list of data that contains blank rows that you want to remove without affecting the order of the list. This technique will allow you to delete all rows that have blank cells in a particular column. Here's the fastest way to do it.
1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
2. Now, select the cells in one column from the top of your list to the bottom.
3. Make sure that all the blank cells in this selected range are the rows you want to delete.
4. Press the F5 key on your keyboard (or select Edit, Goto).
5. Click the Special button.
6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
7. Now choose Edit, Delete, select the Entire Row option and click OK.