Merry Christmas

This next week (starting tomorrow), we will have our kids and their families start to arrive in anticipation of celebrating Christmas at our house. Games will be played, hugs will be shared, prodigious amounts of food will be consumed, presents will be exchanged, and memories will be made. Our little home will host 17 people and two dogs, all coming and going and bumping into each other.

Central to our family's celebration is Christmas Eve, when we will gather in the living room and I will read the traditional Christmas story from the Bible. Three generations will listen to the story that has been told for centuries, contemplating the import of a babe lying in a manger.

For me, that is what Christmas is about. I really enjoy the decorations, the anticipation, the music, the lights, and the way everyone is just a bit nicer while wishing well to one and all. For me, though, it all pales in comparison to that first Christmas gift, offered in the humblest of circumstances to all the world.

I know that not everyone sees Christmas in the way I do. That's OK. You may not even believe that the gift of a child was necessary or of any real meaning. That's OK, too. I don't fault anyone their beliefs or their celebration.

Regardless of your beliefs or how you celebrate, what I wish for you is happiness, joy, and peace. I wish you a warm hearth and a full pantry. I wish you health and reciprocated love. I wish you the best that life has to offer.

From me to each of you, may you have a merry Christmas.

—Allen
     

ExcelTips (ribbon) for 21 December 2024
Powerful spreadsheets
Dynamically Setting a Print Area

You can define, in your worksheet, an area that you want printed. If you want this print area to be dynamic, based on the contents of a cell, then you'll appreciate the easy approach described in this tip.

Read this tip »

(Thanks to Michael Avidan, Steve Aprahamian, Dave Newcomer, Ryszard Raciborski, Andrija Vrcan, Joe Smith, James Woolley, Fred Potter, Peter McNab, Mike Haley, Jacques Raubenheimer, David Watssman, and Tomek Dluzniewski for contributing to this tip.)

 
Formulas
Deriving Monthly Median Values

When processing huge amounts of data, it can be a challenge to figure out how to derive the aggregate values you need. This tip examines how you can derive median values, month by month, from a huge amount of data.

Read this tip »

 
Serious about Sorting?

Organize your data in the order that makes sense to you. Excel's sorting tools make it easy to get at the data you need. Get a firm grip on sorting today and your data management tasks will be easier than ever!

 
Find and Replace
Searching for Line Breaks

If you need to find where line breaks are located in cells, there are a couple of ways you can proceed. Here's a quick overview of how you can locate those pesky line breaks.

Read this tip »

 
Workbooks
Closing a Read-Only Workbook

When you create a workbook that is read-only, users can still make changes to the workbook. When they exit, they are prompted to save their changes to another workbook (since yours is read-only). If you don't want users to be prompted to save their changes, you can use a simple macro that tricks Excel into thinking the workbook has already been saved.

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.

 
Ignoring Special Characters in COUNTIF

I have a worksheet where column A contains semi-random text strings. In cell D1 I enter a string that I want to search for in column A. In another cell I use the formula =COUNTIF(A:A,D1) to determine how many times a string occurs within column A. However, a string in column A may include what COUNTIF considers logical operators (>,<,<>,=) or wildcards (*,?). This can throw off what I expect COUNTIF to return. For instance, if column A contains both "abc123def" and "abc*def" and I place the latter string in cell D1, then COUNTIF returns 2 because of the asterisk. Is there a way I can force COUNTIF to not pay attention to logical operators or wildcards so that my desired count of text strings is correct?
—Peter McNab (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