Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
(Download the workbook.)
Excel’s VLOOKUP function is more popular than the INDEX-MATCH function combination, probably because when Excel users need to look up data then a "lookup" function...
An Introduction to Excel’s Normal Distribution Functions
(Download the workbook.)
When a visitor asked me how to generate a random number from a Normal distribution she set me to thinking about doing statistics...
How to Use Excel Formulas to Calculate a Term-Loan Amortization Schedule
"How do I calculate cumulative principal and interest for term loans? I have scoured the web for a function that will perform this task,...
Use a ‘Visible’ Column in Formulas to Ignore Hidden Rows in Filtered Tables
Excel Tables, introduced in Version 2007, give us the ability to use column filters to hide rows in a Table.
And slicers for Tables, introduced...
How to Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel
SUMPRODUCT is one of Excel's most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell...
How to Work with Dates Before 1900 in Excel
(Download the workbook.)
If you work with dates prior to the year 1900, Excel's standard date-handling system will be no help. However, there are several...
Excel’s Five Annuity Functions
“Help!” the message said. “I know the payment, interest rate, and current balance of a loan, and I need to calculate the number of...
Use Excel’s INDEX-MATCH or VLOOKUP Functions to Populate Invoices and POs
A visitor asked how to set up a simple invoicing system in Excel.
This is a common problem in many small businesses, divisions, and sales...
Use COUNTIFS, not FREQUENCY, to Calculate Frequency Distribution Tables for Charting Histograms
Because the Texas and California governors have been bickering over the Texan's attempt to poach California employers, I got curious about the distribution of...
Excel’s CLEAN Function is More Powerful Than You Think
The Excel 2016 help file for the CLEAN function provides more information than earlier versions:
“Removes all nonprintable characters from text. Use CLEAN on text imported...