Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Get address of named range
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
Excel formula: Count table columns
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
Excel formula: Last row in text data
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
Excel formula: Split text string at specific character
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
Excel formula: Get first text value with HLOOKUP
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

Tip: Two-way lookup with INDEX and MATCH approximate
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...
Run time: 3:10
Tip: How to group a pivot table by day of week
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...
Run time: 3:39
Tip: Dynamic min and max with data labels
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...
Run time: 3:14
Tip: What is a pivot table?
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...
Run time: 2:44
Tip: How to check and debug a formula with F9
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.
Run time: 3:10

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.
Excel Formula and Functions
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 in Excel formulas
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.
101 Excel Functions
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.