Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To lookup and return the sum of a column, you can use the a formula based on the INDEX, MATCH and SUM functions. In the example shown, the formula in I7 is: = SUM ( INDEX ( C5:F11 , 0 , MATCH ( I6 , C4:F4 , 0 ))) How this formula works The core of this formula uses the INDEX and MATCH function in a...Read more
To calculate workdays per month, use the EOMONTH function together with the NETWORKDAYS function. In the example shown, the formula in C4 is: = NETWORKDAYS ( B4 , EOMONTH ( B4 , 0 ), holidays ) Where "holidays" is the named range E3:E13. How this formula works First, it's important to understand...Read more
To remove specific unwanted characters in Excel, you can use a formula based on the substitute function. In the example shown, the formula in C4 is: = SUBSTITUTE ( B4 , CHAR ( 202 ), "" ) Which removes a series of 4 invisible characters at the start of each cell in column B. How this...Read more
If you need to compare two text strings in Excel to determine if they're equal, you can use the EXACT function. For example, if you want to compare A2 with B2, use: = EXACT ( A2 , B2 ) If the two strings are identical, EXACT will return TRUE. If not, EXACT will return FALSE. You can use this result...Read more
If you want to count the total words in a range of cells, you can do with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. In the generic form of the formula above, rng represents a range of cells that contain words. In the example above, we are using: = SUMPRODUCT ( LEN...Read more
Videos
In this video, we use conditional formatting to create an interactive search box to highlight data. This is a nice alternative to filtering, because the information you're looking is...
How to generate an expiration date in the future, how to calculate "days remaining", and how to highlight rows that are expired or expiring soon with conditional formatting.
Excel Pros use keyboard shortcuts to power though big worksheets with blazing speed. In this video, we look at some key shortcuts for selecting cells.
Because tables support structured references, you can learn a lot about a table with basic formulas. In this video, we run through about a dozen examples. It's a nice demo of how...
In the video, we build one set of SUMIFS formulas with a table, and one set without. This makes it really easy to see the nice benefits you get when you combine tables and formulas.
Blog
The double negative coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. This is a useful technique in many advanced formulas that work with cell ranges.
Excel contains over 450 functions. Where should you start? This guide is a walkthrough of over 100 important functions in Excel. Click function names for details and linked examples.
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.
Feedback
LOVE your site... wow... you have helped me quickly do a lot of things that would have taken me forever to figure out on my own. And I love that your search feature actually knows what I'm looking for... lol -Morgan
I just wanted to say thank you for all the help you've been to me through your website. I absolutely love the way you breakdown formulas...I can honestly say, there have been projects that would have been significantly less successful if it wasn't for the assistance you provided. -Marc
I sincerely want to register my profound gratitude to Dave and Lisa. There are many features so unique about the tutorials in Exceljet: the simplicity, down-to-earth approach and the connection between the instructor and the learner. -John
We offer free resources and paid training.