Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
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 generate a dynamic series of dates that are weekends only (Saturday and Sunday), you can use the WORKDAY.INTL function . In the example shown, the date in B5 is a hardcoded start date. The formula in B6 is: = WORKDAY.INTL ( B5 , 1 , "1111100" ) This returns only Saturdays or Sundays as...Read more
To remove a file extension from a file name, you can use a formula based on the LEFT and FIND functions. In the example shown, the formula in C5 is: = LEFT ( B5 , FIND ( "." , B5 ) - 1 ) How this formula works The core of this formula is the LEFT function which simply extracts text from...Read more
To lookup the first entry in a table by month and year, you can use and array formula based on the INDEX, MATCH, and TEXT functions. the LOOKUP function with the TEXT function. In the example shown, the formula in F5 is: = INDEX ( entry , MATCH ( TRUE , TEXT ( date , "mmyy" ) = TEXT ( E5...Read more
To dynamically sort data with both numbers and text in alphabetical order you can use a formula to generate a numeric rank in a helper column, then use INDEX and MATCH to display values based on rank. In the example shown the formula in C5 is : = COUNTIF ( data , "<=" & B5 ) + (...Read more
Videos
In this video, we'll look at how to rank values in ascending or descending order using the RANK function. When values change, the rank will automatically update.
Setting up a running total in an Excel table is a bit tricky because it's not obvious how to use structured references. This video explores some options.
Once you have one pivot table set up, you might want to see a different view of the same data. In this video, I'll show you how to base one pivot table on another.
In this video, we find your way around a an Excel workbook, and how to see where you are now.
Because tables support structured references, you can learn a lot about a table with simple formulas. In this video, we'll look at some formulas you can use to query a table.
Blog
Pivot tables are one of the most powerful and useful features in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.
Formula errors are useful, because they tell you clearly that something is wrong. This guide shows examples of each of the 9 formula errors you may run into, with an information on how to investigate and correct the error.
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.
Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. This article introduces the basic concepts you need to know to be proficient with formulas in Excel.
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.
Feedback
Thank you, great tip. I actually look forward to your emails, they're concise, straight to the point and very helpful! -Tammeshin
Dave and Lisa, can't imagine enough how much effort you take to give us these free tips online. Really wonderful thought of yours to help people like me for quick tips. -Durga
I would like to thank you for the great website. As you mentioned in it's title, it is truly "Quick, clean and to the point". It looks impossible to delivery information shorter and more informative. -Nikita
We offer free resources and paid training.