Enjoying Easter

Welcome to Easter weekend 2019. For many this is a time celebrating new life and renewal of life, rooted in Christian theology. For others it is a time to mark the coming of Spring and the new life that is exhibited by the changing of the seasons. For still others it is a time to join with family and friends and celebrate whatever the Easter Bunny may bring.

This year, in the Wyatt household, it is also a time of reuniting, as we celebrate our biannual family reunion. We are in Mesa, Arizona, this weekend, getting together with my parents, siblings, and everyone's families. I mentioned the reunion in this space last week, but now the festivities are in full swing.

However you choose to celebrate this weekend (and whatever you choose to celebrate this weekend), I hope that it is a joyous time for you and yours.

My best to you.

—Allen
     

ExcelTips (ribbon) for 20 April 2019

Editing
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.)

 
Excel can calculate date in lightening time
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!

 
Online and web
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 »

 
Date formulas
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 »

 
Macros extend Excel
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 »

 
Excel can calculate date in lightening time
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 »

     

Help Wanted

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)

 
ExcelTips is a free service of Sharon Parq Associates. You can find thousands of tips at our website and a whole lot more. ExcelTips is part of the Tips.Net network.
Sharon Parq Associates, Inc.  •  PO Box 1187  •  Mountain View, WY  82939  •  307-200-0450