Excel tips and tutorials
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”
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”
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”
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.
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?
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”
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”
If you have buttons or shapes on an Excel worksheet, you can get their caption text from a worksheet cell, so the text changes, based on a formula. See how to add the button, create its text, then link the button to cell text instead.
In a perfect world, nobody would ever make a mistake in Excel. Every formula would be flawless, and every bit of data would be valid. Unfortunately, that’s not the case. When mistakes happen, there are built-in errors, like #N/A, but why not create your own Excel formula error values, and show those too?
When you first start to use Excel, you probably work with files that somebody else set up. Eventually, you’re ready to build a workbook of your own, and this video shows how to make an order form in Excel, starting from a blank workbook.
