Proper Case Conversion with Exceptions

The PROPER worksheet function allows you to change the case of text so that only the first letter of each word is capitalized. This may not be exactly what you want, however. This tip looks at a couple of ways you could modify the way that PROPER does its work.

Read this tip »

(Thanks to Jacques Raubenheimer, Steve Aprahamian, Jim Williams, Ryszard Raciborski, Dennis Riddle, Ron Solecki, Elliot Penna, Mark Johnson, Bob Beechey, Joe Labellarte, Wayne Edmondson, Colin McCart, Dan McGovern, and Alec Whatmough for contributing to this tip.)

Moving Between and Selecting Sheets with the Keyboard

Hate to take your fingers off the keyboard? Here's how you can move from worksheet to worksheet without touching the mouse.

Read this tip »

Add UserForms to Your Macros

Still relying on message boxes and input boxes to communicate with the users of your Word and Excel macros? Take your macros to the next level by creating your own UserForms. Discover how in the information-packed Creating VBA UserForms course. Low cost; great instruction--check it out today!

Adding a ScreenTip

If you want people to know something about a hyperlink you added to your worksheet, one way to help them is to use ScreenTips. This tip explains what they are and shows you how to create them.

Read this tip »

Converting Mainframe Date Formats

Different industries and different computer systems specify dates in all sorts of strange ways. If you need to convert a date from an off-beat format into one that Excel can understand, you'll like the technique used in this tip.

Read this tip »

Delimited Text-to-Columns in a Macro

The Text-to-Columns tool is an extremely powerful feature that allows you to divide data in a variety of ways. Excel even makes it possible to use the tool in your own macros, as described in this tip.

Read this tip »

Inserting the User's Name in a Cell

Need to understand who is using a particular workbook? There are a number of ways you can find out, 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.

Counting Ports of Call with a PivotTable

We enjoy cruising and I have a worksheet I use to track all of the cruises we've taken. Each row is one cruise. The left-hand columns contain ship name, departure date, booking number, etc. The ten right-most columns are labeled "Port 1" through "Port 10" to record the ports visited, in order. Nassau, for instance, might be the first stop on one cruise and the third stop on another. I can't figure out a simple, straightforward way to use a PivotTable (as I do, e.g., to see how many times we've cruised on a particular ship, etc.) to see how many times we've visited each port. Can a PivotTable be used to count the number of times each unique value appears in a range of cells as opposed to a single row or column?
—Dutch Williams (provide an answer for this Help Wanted question)