Excel Tips & Tricks

My Wife & I, share a liking for Excel (& good food). Once in a while, we exchange Excel tips & tricks that we may have picked up. I recognized this common interest a couple of years ago, when she pointed out that the count of rows & columns have thankfully increased since Excel 2003 and marvelled at the improvements in Charts in Excel 2007 with the same excitement that is usually reserved for a special dish. I did not loose a chance then to tell her how to get a Ribbon-less pre-Excel 2007 UI.

Just so that I do not forget the tips & tricks we discuss, I plan to document them here.

1) Find & replace a special character - This trick is adapted from a response on the MrExcel Forum

Let's say you have received an Excel file with a pesky non printing character at several places in a sheet and now you need to replace it with something else. How to go about it?

Get the numeric code of the pesky character using the CODE function. For instance, the formula =CODE("!") will display the numeric code for ! or 33

Next, invoke the Find box using the shortcut Ctrl + F. Click in the Find box, hold Alt key and type 0 followed by numeric code of the mysterious character you have encountered(for example, for "!", you would type 033 instead of 33), on the number keypad and then the release the Alt key. As soon as you release the Alt key the character whose numeric code you typed will appear in the Find box.

Now go to the Replace tab, place the character to substitute in the "Replace with" textbox, and hit Replace All.

Excel Help suggests in the topic "Remove spaces and non printing characters from text" that you can also use a combination of the TRIM, CLEAN, and SUBSTITUTE functions to remove non printing characters

2) Matching row, values of adjacent cells - I find this question frequently being asked on technical Forums. To get a result that is based on values of adjacent cells in a row, use the combination of INDEX & MATCH as explained in the topic "Look up values in a list of data" in Excel Help.

3) (Added: 17-Sep-09) Increment numeric values in a range of cells by a standard value - Jonathan Van Houtte's tip shows you how to add a value of 10 to large number of existing cells containing (let's say) salary amounts at one shot.

1. Type 10 in any empty cell.
2. Copy that cell.
3. Select the range of cells containing Salary amounts
4. Right-Click, and choose Paste Special.
5. On the Paste Special dialog box, click the Add radio button (in the Operation frame)
6. Click "OK"
7. Clear the cell with the 10.

To be continued...

Also see:
HOW TO create an Excel 2007 Macro
HOW TO conditionally format entire row/s based on a cell's text value in Excel 2007