Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To get the position of the nth match (for example, the 2nd matching value, the 3rd matching value, etc.), you can use a formula based on the SMALL function. In the example shown, the formula in G5 is: = SMALL ( IF ( list = E5 , ROW ( list ) - MIN ( ROW ( list )) + 1 ), F5 ) This formula returns the...Read more
If you want to use conditional formatting to highlight dates occur on weekends (i.e. Saturday or Sunday), you can use a simple formula based on the WEEKDAY function. For example, if you have dates in the range C4:C10, and want to weekend dates, select the range C4:C10 and create a new conditional...Read more
To extract the last two words from a cell or text string, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN. In the example shown, the formula in C5 is: = MID ( B5 , FIND ( "@" , SUBSTITUTE ( B5 , " " , "@" , LEN ( B5...Read more
To get the number of days in a given month from a date, you can use a formula based on the EOMONTH and DAY functions. In the example shown, the formula in cell B5 is: = DAY ( EOMONTH ( B5 , 0 )) How this formula works The DAY function returns the day component of a date. The EOMONTH function...Read more
To get "first in, last out" times from timesheet data, you can use the MAXIFS and MINIFS functions, or an array formula as described below. In the example shown, the formula in H6 is: = MINIFS ( times , names , H5 , actions , "in" ) with the following named ranges : actions = C5:C22...Read more
Videos
In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer...
In this video, we look at a way to fill in missing data when the structure is not consistent, using formula created with IF and ISBLANK functions.
In this short video, we look at how to replace a typical nested IF formula with a VLOOKUP formula. Compared to nested IF statements, VLOOKUP is simpler and more transparent.You can change...
Pivot tables can be sorted in several ways - by rows, by columns, and by value. In today’s Exceljet tip, we show you how to sort a pivot table with a custom list.
Excel charts have a flexible system to display values called "data labels". Data labels are a classic example a "simple" Excel feature with a huge range of options just...
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
This is the best learning resource I have stumbled upon in many years during the process of my Excel learning. -Anchery
Dave this is amazing. I wish I got this message a year ago, so glad I signed up for your tips!! Thank you!
I just wanted to say thank you for the useful tutorials...This info is really useful for me at the moment because I'm just starting my first job while I'm not yet graduated from college. -Andrea
We offer free resources and paid training.