Excel Tips
Introducing The Boring Use Of SUMPRODUCT »
February 1, 2021
Your IT department sends you a file with unit price and quantity sold. You need to calculate total revenue. You usually add a new column and total that column, but there must be a way to total the 5,000 line items with only one formula.
Why the Minus Minus? Coerce Numbers from True/False »
January 29, 2021
While IF and other functions that expect logical tests can easily convert TRUE and FALSE values to 1s and 0s, the SUMPRODUCT function cannot do this. Why do you sometimes use a minus minus in SUMPRODUCT?
Sum Every Other Row or Every Third Row »
January 27, 2021
Someone set up a worksheet with dollars in rows 2, 4, 6, 8, and so on and percentages in rows 3, 5, 7, 9, and so on. You want to sum only the dollars, which are stored in the even rows. While you’re at it, you’d like to know how to sum the odd rows or every third row.
Store Holidays in a Named Range »
January 25, 2021
The NETWORKDAYS and WORKDAY functions can take a list of company holidays as the third argument. If you store the list of holidays in AZ1: AZ10, there is a chance that someone will inadvertently delete a row, so you want to move the range of company holidays to a named range.
Calculate Workdays for 5, 6, and 7 Day Workweeks »
January 22, 2021
Calculate how many workdays fall between two dates. Excel’s NETWORKDAYS function does this if you happen to work the five days between Monday and Friday inclusive. This topic will show you how to perform the calculation for a company that works 5, 6, or 7 days a week.
Find the First Non-blank Value in a Row »
January 20, 2021
You want to build a formula to return the first non-blank cell in a row. Perhaps columns B:K reflect data at various points in time. Due to the sampling methodology, certain items are checked infrequently.
January 6, 2021
When you hand the world’s most flexible software to 750 million people, everyone finds interesting things to do or interesting ways to use Excel. I love traveling to do the in-person Excel seminars because I get to meet these people. At every seminar, someone has an interesting story of how they are using Excel.
Excel 2020: Settings in the Excel Options Menu »
December 24, 2020
Consider a few of these settings after you choose File, Options.
Excel 2020: A Great April Fool’s Day Trick »
December 14, 2020
Do you want a harmless prank to pull on a co-worker? When he leaves his desk to grab a cup of coffee, add the fifth icon to his Quick Access Toolbar: Speak Cells on Enter. Click the icon once, and the computer will say, “Cells will now be spoken on Enter.”