Converting Text Notation Values to Numeric Values

If you convert a PDF file to an Excel worksheet, you may end up with some text values that need to have some conversion done in order to work with them as numeric values. This tip examines some ways that this transformation can occur.

Read this tip »

(Thanks to Bob Farey, Steve Aprahamian, Michael Avidan (MVP), Dave Newcomer, Brian Dorey, Tony Greaves, Craig Korfanta, Ryszard Raciborski, Michelle Hintz, James Woolley, Peter McNab, Erik Oberg, Murray Belchamber, Yvan Loranger, Henry Arthur, David Watssman, and Omar Cosme for contributing to this tip.)

Generating Double-Digit Random Numbers

Normally you use the RAND and RANDBETWEEN functions to generate random numbers. What if you want to generate random numbers within a certain range but exclude some numbers in that range? Here's some ideas on how to get what you want.

Read this tip »

Baffled by Array Formulas?

Have you heard about the special array formulas you can create in Excel? They make it a snap to perform calculations using large amounts of data and even generate results you can't get with regular formulas. Array formulas may be Excel's best-kept secret. Remove the confusion; discover how to use array formulas today.

Relative Worksheet References when Copying

Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it adjusts everything except the names of any worksheets that may be referenced in the formula. Here's how you can get around that lack of change.

Read this tip »

Coloring Identical Company Names

Want to know where duplicates are in a list of names? There are a couple of ways you can go about identifying the duplicates, as discussed in this tip.

Read this tip »

This section is for those having problems making Excel behave. If Excel is giving you fits, feel free to submit your own Help Wanted question.

If you have a solution for the problems below, click the link after the problem to send us your answer. (All responses become the sole property of Sharon Parq Associates, Inc., and can be used in any way deemed appropriate.) If your response is used in a future issue, you will be credited for your contribution to the answer.

Creating a Static Cell Reference

I have a question about using a static cell reference in a formula. I have a formula that refers to cell U3, and I want it to always refer to cell U3. If I insert or delete columns between the cell containing my formula and cell U3, I don't want the formula to be updated with a new column reference; I want it to always refer to cell U3. Referring in the formula to $U$3 doesn't work because it is updated by Excel when columns are inserted or deleted.
—Judi Pillsbury (provide an answer for this Help Wanted question)