Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Get information corresponding to max value
To lookup information related to the maximum value in a range, you can use a formula that combines the MAX, INDEX, and MATCH functions. In the example shown, the formula in I8 is: = INDEX ( F3:F11 , MATCH ( MAX ( C3:C11 ), C3:C11 , 0 )) Which returns the number 3920, representing the square footage...Read more
Excel formula: Count cells over 100 characters
To count cells that contain more than a certain number of characters you can use a formula based on the SUMPRODUCT, LEN, and N functions. In the example shown, the formula in C2 is: = SUMPRODUCT ( N ( LEN ( B5:B11 ) > 100 )) How this formula works Working from the inside out, the LEN function...Read more
Excel formula: Sum by week number
To sum by week, you can use a formula based on the SUMIFS function. In the example shown, the formula in H5 is: = SUMIFS ( total , color , $G5 , week , H$4 ) where "total" is the named range D5:D16, "color" is the named range B5:B16, and "week" is the named range E5:E16. How this formula works The...Read more
Excel formula: Subtotal by color
If you need to subtotal numbers by color, you can easily do so with the SUMIF function. In the example shown, the formula in G5 is: = SUMIF ( $B$4:$B$11 , F5 , $D$4:$D$11 ) How this formula works The SUMIF function takes three arguments: range, criteria, and sum_range. In this case, we are using:...Read more
Excel formula: Get nth match with VLOOKUP
To get the nth MATCH with VLOOKUP, you'll need to add a helper column to your table that constructs a unique id that includes the count. If this isn't practical, you can use an array formula based on INDEX and MATCH instead. = VLOOKUP ( id & "-" & I6 , data , 4 , 0 ) How this...Read more

Videos

Tip: How to group a pivot table by age range
In this video we look at how to group sample voting data into 10 year buckets, using a pivot table.
Run time: 2:47
Tip:  Shortcuts for Excel Tables
Excel has great support for shortcuts in tables. Shortcuts for inserting, selecting, and deleting rows and columns all work much better! Watch the video to see how.
Run time: 3:16
Tip: Shade groups of rows with conditional formatting
In this video, we'll look at how to use conditional formatting to shade alternating groups of rows. For example, you can use this approach to shade groups of 3 rows, groups of 4 rows,...
Run time: 3:01
Tip: How to build a simple summary table
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...
Run time: 3:01
Tip: How to clone a pivot table
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 quickly base one pivot table on another.
Run time: 3:11

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

I also wanted to let you know that I learn a ton a get a lot of value from your tutorials. Very much appreciated. -Patrick
A really nice clean website that answers excel questions beautifully and understandably. One of the very best I have seen. keep up the good work. -Geoff
I often Google to get Excel help, such as formulas, help with errors, how-tos etc. This is the most complete and easy to understand website I have come across. Very well organized. You're directions and examples on how to use formulas are very easy to understand. Thank you so much. -Jodi
We offer free resources and paid training.