One of the newest Excel functions is TEXTJOIN — use it to combine several text items, quickly and easily. It’s a big improvement over the old CONCATENATE function, and the ampersand (&) operator. There’s a simple example below, and a fancier one, which combines TEXTJOIN with a few other functions.
Category: Excel Formulas
Show List of Matching Items in Excel
Today’s video shows how to set up a matching items list – select a region name, and the list shows all employees who work in that region. There are no macros, just a data validation list and a few formulas.
NOTE: If you have a version of Excel with dynamic arrays (Excel for Office 365), use dynamic arrays to create the list of matching items instead. (video below) Continue reading “Show List of Matching Items in Excel”
Excel Multiplication Table Practice Workbook
Do you remember the number grids from your schools days, where you filled in the multiplication answers? What’s 4 times 8? Or 9 times 7? If you need a refresher, or you’re helping someone learn them, I’ve got an Excel multiplication table practice workbook for you.
Continue reading “Excel Multiplication Table Practice Workbook”
How to Copy Excel Formulas with Table References
If you create Excel formulas that include table references, and then try to copy those formulas to adjacent columns, you might get incorrect results. See why that happens, and how to avoid those problems, when you copy Excel formulas with table references.
Continue reading “How to Copy Excel Formulas with Table References”
Excel Formula to Sum for Odd or Even Weeks
Here’s an Excel formula challenge for you, based on an email question that someone sent to me. They wanted help with an Excel formula to sum for odd and even weeks. See my solution, and let me know how you’d solve the problem.
Continue reading “Excel Formula to Sum for Odd or Even Weeks”
Find the Nth Weekday in a Month in Excel
Some events occur on the Nth weekday of a specific month each year. Maybe your book club meets on the 4th Tuesday every month, and you don’t want to miss any meetings. In Excel, you can use a formula to find the Nth weekday in a month, and there’s an example shown below. Continue reading “Find the Nth Weekday in a Month in Excel”
Excel Hyperlink to Next Empty Cell
Last week, someone asked me how to make an Excel hyperlink to next empty cell. See how to set that up, in a couple of different situations.
Count Duplicate Number Sets in Excel
Recently, someone asked me how to count duplicate number sets. There were hundreds of rows, with 6 numbers in each row. Did any of the number sets appear in the list more than once? How would you solve this problem, and count duplicate number sets?
Show Loan Payments in Excel with PMT and IPMT
With the PMT function, you can enter a few numbers, and calculate the monthly payments for a loan amount. But when is each payment due, and how much goes to interest and principal? This PMT and IPMT functions example shows how to calculate that too.
Continue reading “Show Loan Payments in Excel with PMT and IPMT”
Excel Lookup Formula for Last Item in Column
How can you get the last number in an Excel column? I needed to do that in one of my sample files, so here’s the formula that I used. Then, someone asked me how to get the last item (text or number) from a column, so there’s a formula for that too.
Continue reading “Excel Lookup Formula for Last Item in Column”