Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To extract the first name from a full name in "Last, First" format, you can use a formula that uses RIGHT, LEN and FIND functions. In the generic form of the formula (above), name is a full name in this format: LAST, FIRST Jones, Sarah Smith, Jim Doe, Jane A comma and space separate the last name...Read more
To highlight cells by quartile, you can apply conditional formatting with a formula that uses the QUARTILE function. In the example shown, we are using 4 different conditional formatting rules. Each rule highlights a quartile in the range B4:F11 using the QUARTILE function. The formulas look like...Read more
To count the number of cells that contain values not equal to a particular value, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells, and X represents the value you don't want to count. All other values will be counted. In the example, the...Read more
To find the closest match with a lookup value and numeric data, you can use an array formula based the INDEX, MATCH, ABS and MIN functions. In the example shown, the formula in E5 is: { = INDEX ( data , MATCH ( MIN ( ABS ( data - E4 )), ABS ( data - E4 ), 0 )) } where "data" is the named range B5:...Read more
To find and replace multiple values with a formula, you can set up one table with one replacement per column, and feed in find/replace pairs from another table. In the example shown, G5 contains this formula: = SUBSTITUTE ( B5 , INDEX ( find , C$4 ), INDEX ( replace , C$4 )) where "find" is the...Read more
Videos
In this video, we show you how to join different values together in one cell with line breaks. We also demo a clever way to add spaces, commas without double quotes.
Although Pivot Tables are fantastic tools for summarizing data, sometimes you just want a simple, lightweight solution based on formulas. The good news is you can build your own summaries...
In this tip, we look at a cool way to remove rows from a big list or table that contain empty values.
A mixed reference is part relative, and part absolute. Mixed references are useful when you want only part of a cell reference to change when you copy a formula.
In this video, we look at how to use Evaluate Formula to solve complex formulas one step at a time. It's a great way to understand how Excel solves a formula.
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
Knowing your site is the best thing that ever happened to me since I started using excel. - MJM
Thank you, great tip. I actually look forward to your emails, they're concise, straight to the point and very helpful! -Tammeshin
Thank you, and thank you so very much for your videos, they are sooooo helpful. - Elizabeth
We offer free resources and paid training.