Enter the number 1 in a cell somewhere away from the range of numeric values. Copy that cell to the Clipboard. Highlight the entire range of text numbers and select Paste Special from the Edit menu. Check the box titled Multiply, then click on OK. This will replace the value of each cell in the range with each value multiplied by 1.
If the cell held a text number, it will be converted to an actual number. Cells containing non numeric text will not be changed. Note that you can use the same technique to multiply, divide, add, or subtract the values in the target range by the value in the clipboard. For example, you can use this method to raise or lower a list of prices by 10 percent.
Monday, 5 July 2010
Excel: Row based page breaks
Suppose you want page breaks every 40 lines. Select the first few cells in the 40th line and change the background color, so it will stand out even when reduced in size. From the File menu, select Print Preview and note where your 40th line falls on the page. Click the Setup button and increase the bottom margin by an inch or two, then click OK. Note where the marked line falls. Go into Setup as many times as needed, adjusting the bottom margin until the marked line is the last line on the page. Click the Page Break Preview button to view your worksheet with all page breaks marked and scan through it to verify that the data is laid out as you wanted.
Excel: Highlight formula cells
First, select the entire range of cells and apply the format you want to use for cells without formulas. With the range still selected, choose Go To from the Edit menu and click on the Special button. In the Go To Special dialog, check the Formulas box, and click on OK. All cells containing formulas will be highlighted, regardless of whether they form a continuous area. Right-click on any one of them, choose Format Cells, and apply the desired formatting. You must repeat this process if you change the contents of the cells.
Excel: Find unique rows
Problem: Each row contains info in a consistent format and layout for a record in a database, with a unique ID in the first column.
Resolution: Assuming the data starts in cell A1, click in that cell and choose Format | Conditional Formatting from the menu. Change the drop-down to Formula is. Enter the formula =COUNTIF(A:A,A2)=1. Click the Format button, click the Pattern tab, and select a color, say, green. Click OK, OK. Now click the Format Painter button on the toolbar and highlight all the rest of the data in column A. Any ID that appears only once will now be green.
In Excel 2007, highlight all the cells in the unique ID column and click the big Conditional Formatting button on the ribbon. Select Highlight Cells Rules and choose Duplicate Values. In the little window that appears, change Duplicate to Unique and select the desired formatting.
Resolution: Assuming the data starts in cell A1, click in that cell and choose Format | Conditional Formatting from the menu. Change the drop-down to Formula is. Enter the formula =COUNTIF(A:A,A2)=1. Click the Format button, click the Pattern tab, and select a color, say, green. Click OK, OK. Now click the Format Painter button on the toolbar and highlight all the rest of the data in column A. Any ID that appears only once will now be green.
In Excel 2007, highlight all the cells in the unique ID column and click the big Conditional Formatting button on the ribbon. Select Highlight Cells Rules and choose Duplicate Values. In the little window that appears, change Duplicate to Unique and select the desired formatting.
Excel: Fill functions
CUSTOM LISTS
As with the months of the year and days of the week, you can create your own custom fill lists. Click the Office button and choose Excel Options | Popular | Edit Custom Lists to display the Custom List dialog. You can copy a list from a range on the worksheet or click New List and type a list of entries in the aptly named List entries box, one per line. Click Add to add the list and OK twice to finish. Now type any entry from your list in a cell and drag the fill handle to create the sequence.
MORE-COMPLEX SERIES
To create a nonlinear series, type the first value in the series, right-click, and drag on the fill handle. When the Context menu appears, select Series to open the Series dialog. Here you specify a Growth series, for example. To test this, type number 1 in a cell, right-click it and drag, choose Series | Growth and set the step value to 2, and click OK. This creates a sequence in which each number is followed by itself multiplied by two.
DROP-DOWN EASE
A drop-down list is handy when you have a set of options you want your user to choose from—and it conveniently averts typos. On a new sheet, type a list of entries, select it, and type a name for the list in the Names box on the left of the Formula bar. On a new worksheet, select the cells in which the data is to appear. Click the Data tab on the Ribbon and choose Data Validation. From the Settings tab choose Allow | List and in the Source box type = and the name you gave to the selected list (for example, =employees). Click OK. Now when you click a cell you can select the cell entry from the drop-down list.
FORMS
The Form button is not on the Excel 2007 Ribbon, but you can add it to the Quick Launch bar. Click the Office button, choose Excel Options | Customize, and in the Choose commands from list pick Commands Not in the Ribbon. Locate and select the Form command. Click Add and then OK. To display the form, click in a table of data and click the Form button. You can now enter data into your table using the Form dialog, which is quicker and more convenient than navigating around a very large table.
As with the months of the year and days of the week, you can create your own custom fill lists. Click the Office button and choose Excel Options | Popular | Edit Custom Lists to display the Custom List dialog. You can copy a list from a range on the worksheet or click New List and type a list of entries in the aptly named List entries box, one per line. Click Add to add the list and OK twice to finish. Now type any entry from your list in a cell and drag the fill handle to create the sequence.
MORE-COMPLEX SERIES
To create a nonlinear series, type the first value in the series, right-click, and drag on the fill handle. When the Context menu appears, select Series to open the Series dialog. Here you specify a Growth series, for example. To test this, type number 1 in a cell, right-click it and drag, choose Series | Growth and set the step value to 2, and click OK. This creates a sequence in which each number is followed by itself multiplied by two.
DROP-DOWN EASE
A drop-down list is handy when you have a set of options you want your user to choose from—and it conveniently averts typos. On a new sheet, type a list of entries, select it, and type a name for the list in the Names box on the left of the Formula bar. On a new worksheet, select the cells in which the data is to appear. Click the Data tab on the Ribbon and choose Data Validation. From the Settings tab choose Allow | List and in the Source box type = and the name you gave to the selected list (for example, =employees). Click OK. Now when you click a cell you can select the cell entry from the drop-down list.
FORMS
The Form button is not on the Excel 2007 Ribbon, but you can add it to the Quick Launch bar. Click the Office button, choose Excel Options | Customize, and in the Choose commands from list pick Commands Not in the Ribbon. Locate and select the Form command. Click Add and then OK. To display the form, click in a table of data and click the Form button. You can now enter data into your table using the Form dialog, which is quicker and more convenient than navigating around a very large table.
Excel: Enter date and time formats
To set up a worksheet so that you can enter dates as MMDDYYYY with no slashes between, first click the column header for the column that will contain the dates. Now right-click the column header and select Format Cells from the pop-up menu. On the Number tab choose Custom and enter the following custom format in the Type box: 00\/00\/0000. The forward slash has special meaning in a custom format, so you must precede it with a backslash to tell Excel that you want the literal character, not its special meaning.
Now insert a column just after this column to hold the actual date for use in calculations. Let's say the eight-digit dates were in column A, starting in cell A2. Then you would enter this formula in cell B2 and copy it down the whole column: =DATE(MOD(A2, 10000), TRUNC(A2 / 1000000), MOD(TRUNC(A2 / 10000), 100)). The MOD function returns the remainder when dividing its first argument by its second. Thus the first sub-expression returns the last four digits of your number—the year. For the month, we divide by 1,000,000 and use the TRUNC function to throw away any fractional part. And for the day, we do a combination of the two.
To enter HH:MM time values without the colon, you'll simply use a different custom format. The format 0\:00 will give one- or two-digit hours, while the format 00:\00 will always give you two-digit hours, left-padding with a zero if necessary. A formula like this one would convert the typed value into an actual time value for use in calculations: =TIME(TRUNC(A2/100),MOD(A2,100),0).
Now insert a column just after this column to hold the actual date for use in calculations. Let's say the eight-digit dates were in column A, starting in cell A2. Then you would enter this formula in cell B2 and copy it down the whole column: =DATE(MOD(A2, 10000), TRUNC(A2 / 1000000), MOD(TRUNC(A2 / 10000), 100)). The MOD function returns the remainder when dividing its first argument by its second. Thus the first sub-expression returns the last four digits of your number—the year. For the month, we divide by 1,000,000 and use the TRUNC function to throw away any fractional part. And for the day, we do a combination of the two.
To enter HH:MM time values without the colon, you'll simply use a different custom format. The format 0\:00 will give one- or two-digit hours, while the format 00:\00 will always give you two-digit hours, left-padding with a zero if necessary. A formula like this one would convert the typed value into an actual time value for use in calculations: =TIME(TRUNC(A2/100),MOD(A2,100),0).
Thursday, 1 July 2010
Recovering from virus / malware
Foolishly pasted a link into my browser which turned out to be malware. Nasty.
But recovery back to previous state was helped tremendously by the following:
But recovery back to previous state was helped tremendously by the following:
- SQLyog queries being batch backed up using XCOPY -
- Outlook rules exported
- Use of FEBE in Firefox
- Shortcuts previously copied to network (so can be copied back)
- Use of del.icio.us and Google Bookmarks
- Don't click on things that look dodgy!
- Back up the internals of Firefox extensions, such as Tab Mix Plus preferences, sites held in Morning Coffee
Subscribe to:
Posts (Atom)
Posts
