Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. In the example shown, the formula in G5 is: = ADDRESS ( ROW ( data ), COLUMN ( data ), 4 ) & ":" & ADDRESS ( ROW ( data ) + ROWS ( data ) -...Read more
To count columns in an Excel table, you can use the COLUMNS function. In the example shown, the formula in I4 is: = COLUMNS ( Table1 ) How this formula works This formula uses structured referencing , a syntax that allows table parts to be referred to by name. When a table is referred to by the...Read more
To get the last relative position (i.e. last row, last column) for text data (with or without empty cells), you can use the MATCH function. In the example shown, the formula in E5 is: = MATCH ( REPT ( "z" , 255 ), B4:B11 ) How this formula works This formula uses the MATCH function in...Read more
To split a text string at a certain character, you can use a combination of the LEFT, RIGHT, LEN, and FIND functions. In the example shown, the formula in C5 is: = LEFT ( B5 , FIND ( "_" , B5 ) - 1 ) And the formula in D5 is: = RIGHT ( B5 , LEN ( B5 ) - FIND ( "_" , B5 )) How...Read more
To lookup and retrieve the first text value across a range of columns, you can use the HLOOKUP function with a wildcard . In the example shown, the formula in F5 is: = HLOOKUP ( "*" , C5:E5 , 1 , 0 ) How this formula works Sometimes, you may want to check a range of several columns and...Read more
Videos
In this video, we'll look at how to build a two-way lookup with INDEX and MATCH, using approximate matching. This is a classic solution for non-exact lookups like income tax, shipping...
In this Exceljet video tip, we show you how to group a pivot table by day of week. We also show you how to calculate a true day name (like Sunday) using a helper table and the VLOOKUP...
The data labels in Excel charts are flexible and powerful. While you can easily use them to display values from source data, you can also display values from other cells. The video below...
What is a Pivot Table anyway? This short video answers the question by using a pivot table to analyze chocolate sales in several different ways. Learn what a pivot table is in less than 3...
In this brief video, we look at how to debug a formula using the F9 key. If you work with formulas a lot, this is one of the most useful skills you can learn.
Blog
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.
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.
Feedback
Thank you very much for the information. It worked fantastically! I have been trying to figure this out for more than a year, you had the answer for me very quickly. -Paul
You product is phenomenal! Easy to understand and gets to the meat of the function quickly. -Jennifer
Your courses are excellent...Despite all my years of experience, every clip that I have seen so far has been very helpful and a time saver. - Robert
We offer free resources and paid training.