Happy Fourth

For most people in the United States, this is a holiday weekend. Yesterday, July 4, was Independence Day, celebrating the signing of the Declaration of Independence on July 4, 1776.

Because the holiday was on a Friday this year, that means that the festivities will, no doubt, continue through the weekend. In our town, fireworks have been going off for a couple of nights and I expect them for a few more.

I would be remiss if I didn't mention that this past week I opened the doors to my Using Dates and Times online course. I know you may be doing lots of celebrating right now, but you may want to take a minute to check the course out. It provides all the info you need to become a wiz at slicing, dicing, and otherwise manipulating both dates and times in your workbooks.

You can find complete information on the course here:

https://usingoffice.com/dates-and-times/

Plus, you'll note that for a limited time (through this next Tuesday) you can enroll in the course for 25% off the already low price! That means that, during this early bird period, you can enroll in the course for as little as $89. That's a fantastic deal for the "how to" information provided in this course!

I hope your weekend is a safe and happy one and that you enjoy the tips in this week's issue of ExcelTips.

—Allen
     

ExcelTips (ribbon) for 5 July 2025

Date formulas
Assuming Dates are in the Current Century

Type a date into Excel that includes a two-digit year, and you may not always get the year in the century you intended. This tip explains why that occurs and discusses ways you can address the issue.

Read this tip »

(Thanks to Ryszard Raciborski, Dave Newcomer, Michael Avidan (MVP), Neil Woller, James Woolley, Thomas Reeves, Brian Dorey, Tomek Dluzniewski, Dave Onorato, David Shepherd, and Julie Fass for contributing to this tip.)

 
Printing
Black and White Blues

Getting a chart looking its best on a black-and-white printer can be a challenge. This tip examines different ways you can improve the printout.

Read this tip »

 
Enhance Your Excel Skills with Macros

Macros are a great way to increase your productivity with Excel by making the program work the way you want it to work. Discover the true power behind macros; ExcelTips: The Macros puts almost 800 pages of macro-focused information at your fingertips. Discover more...

 
Editing
Copying a Cell without Formatting

When you are copying a cell from one place to another (perhaps even to a different worksheet), you may not want to copy the formatting from the source cell to the target cell. This is where Paste Special comes in handy, as illustrated in this tip.

Read this tip »

 
Printing
Printing Individual Worksheets for Vendors

If you use a worksheet to track data for multiple vendors, you may wonder if there is a way to print individual information specific to those vendors. This tip looks at four distinct ways that you can get the reports you want.

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.

 
Calculating the Average of Five Values Near the End of a Range

I have a column of values for which I need an average of the last five values, but one. For instance, if there is data in cells A5:A36, I need the average of the values in A31:A35, ignoring cell A36 (the "but one"). The formula needs to be dynamic, as data will be routinely added to the column.
—Alex Whatmough (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