Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
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
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
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
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
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
In this video we look at how to group sample voting data into 10 year buckets, using a pivot table.
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.
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,...
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...
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.
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.