Dates can be tricky in Excel, especially in December and January! In those months, you often have to type the year too, because Excel defaults to the current year. There are other Excel date problems too, like dates where you can’t change the formatting, and dates for the GetPivotData function. Here’s a quick look at dealing with those issues.
Category: Excel Formulas
Choose Random Names From Excel List – Formulas No Macros
Do you ever need to choose random names from a list in Excel? Maybe your charity is picking winners for a raffle, or your blog is doing a giveaway, or you need “volunteers” for a special project at work. As long as you have a list of the names, or ticket numbers, or anything else, Excel can do a random draw for you.
Continue reading “Choose Random Names From Excel List – Formulas No Macros”
Find MAX and MIN With Criteria in Excel Formulas
To find the highest and lowest amounts in Excel, use the MAX and MIN function. If you need to use criteria in your calculations, add the IF function to your formula, in older versions of Excel. In Excel 2019 and Excel 365, use the newer MAXIFS and MINIFS functions. You can even get the answer without formulas – use a pivot table instead!
Continue reading “Find MAX and MIN With Criteria in Excel Formulas”
Excel Workbook Formulas Not Calculating: How to Fix Them
Your Excel file worked perfectly yesterday, but when you entered new data today, the totals didn’t update. Why did the formulas suddenly stop calculating, and how can you fix them? Here’s the most common reason for that problem, and a quick way to fix it. Also, see how to avoid that problem in the future.
Continue reading “Excel Workbook Formulas Not Calculating: How to Fix Them”
Excel MATCH Function Error Troubleshooting Examples
Sometimes, a MATCH formula returns an #N/A error, even if the value you’re looking for is in the lookup table. The reason for that could be numbers that Excel sees as text, and here are a couple of ways to fix that problem. And if numbers aren’t the problem, I’ve got a couple of other things to check too!
MATCH Function Examples
Before we start troubleshooting the MATCH function, here’s a short video that shows how the function works. It has four MATCH examples, so you can see different ways to use it.
There are more examples on the INDEX and MATCH page of my Contextures site.
Continue reading “Excel MATCH Function Error Troubleshooting Examples”
Reverse First Last Names with Excel Formula
If names are in Last, First order in an Excel workbook, use this MID formula to reverse them, in another cell. This short video shows the steps, and how the formula works. There’s a longer video too, that shows a different formula you can use.
Continue reading “Reverse First Last Names with Excel Formula”
Excel Nested IF Formula Horror Stories
Is the Excel IF function underused, overused or too often abused? Or some combination of all three? This short video shows how to make a simple IF formula. It checks if a cell is empty, then returns a result, based on that test. Too often though, the IF function is nested about 15 layers deep. Those formulas are confusing, and could usually be replaced by a much simpler solution. What are your nested IF Formula horror stories?
How to Count Specific Words in Excel Text String
How can you count specific words in a text string, using an Excel formula? For example, a cell has this text – air, hair brush ,hot air, hair. The word “air” should be counted once. It shouldn’t be counted if it’s part of other words or phrases.
Continue reading “How to Count Specific Words in Excel Text String”
Combine Text and Formatted Numbers in Excel
In all versions of Excel, you can use a simple formula, with the & operator, to combine values from different cells. If you want the numbers formatted a certain way, use the TEXT function to set that up.
Continue reading “Combine Text and Formatted Numbers in Excel”
Excel Spill Formula Examples
Spill functions can fill neighbouring cells with their results, to create dynamic ranges. Thank you to Dermot Balson, who sent me his Excel workbook with examples for all the new functions (dynamic arrays) in Excel for Office 365. The examples show how VBA functions can spill too, and even some old Excel functions will spill automatically. Continue reading “Excel Spill Formula Examples”