Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is: { = SUM ( -- ( MMULT ( -- ( data = 90 ), TRANSPOSE ( COLUMN ( data ))) > 0 )) } where data is the named range B4:B12...Read more
To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function. In the example shown, the formula in C5 is: { = AND ( COUNT ( SEARCH ( inc , B5 )) > 0 , COUNT ( SEARCH ( exc , B5 )) = 0 ) } This...Read more
If you want to count rows where two (or more) criteria match, you can use a formula based on the COUNTIFS function. In the example shown, we want to count the number of orders with a color of "blue" and a quantity > 15. The formula we have in cell G7 is: = COUNTIFS ( B4:B11 , "blue" , C4:...Read more
To allow only values that begin with certain text, you can use data validation with a custom formula based on the EXACT and LEFT functions. In the example shown, the data validation applied to C5:C9 is: = EXACT ( LEFT ( C5 , 3 ), "MX-" ) How this formula works Data validation rules are...Read more
To test a range and determine if it contains one of many substrings (partial matches, specific text, etc.) you can use use a formula that uses the COUNTIF wrapped in the SUMPRODUCT function. How the formula works All the hard work is done by the COUNTIF function, which is configured to count the...Read more
Videos
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows. For example, you can use this approach to shade groups of 3 rows, groups of 4 rows,...
In this video, we'll look at a simple shortcut recipe for removing thousands of blank rows in a large set of data. Shortcut recipes are just a series of shortcuts you perform in a...
In this video, we demo how to plot results from a survey question in an Excel bar chart. Along the way, we use the COUNTIF function to build a summary table, and show a simple technique for...
This video show how to calculate due dates with the WORKDAY and WORKDAY.INTL functions. You can use WORKDAY to calculate intelligent ship dates, delivery dates, and completion dates that...
If you're building a pivot table from data in an Excel worksheet, you should be using a table for the source data. In this short video, we show you how to use a table as the data...
Blog
Criteria are a key concept in Excel, but building useful criteria for text, numbers, dates, times, etc. is hard because it requires a good understanding of how Excel handles data. This guide will help you build formulas that work the first time with over 50 examples.
Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula. What?
Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables.
In this challenge, the goal is to end up with a text string like "MWF" for Monday, Wednesday, Friday. The problem is that the weekdays are input like as yes/no abbreviations like "NYNYNYN" for "MWF". What formula will translate the "N" and "Y" to weekday abbreviations?
Every day, thousands of questions about Excel are posted on the internet. Many of these questions go unanswered because they are unclear. This article contains some tips on how to write a question that people will quickly understand and answer if they can.
Feedback
When querying functions, I often end up on your site. And, the explanations are always clean and comprehensive. So, I appreciate the work you're putting into this. -Chris
Love your tutorials, they've been awesome in helping me learn all that Excel can do.
I often Google to get Excel help, such as formulas, help with errors, how-tos etc. This is the most complete and easy to understand website I have come across. Very well organized. You're directions and examples on how to use formulas are very easy to understand. Thank you so much. -Jodi
We offer free resources and paid training.