Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Match first error
If you need to match the first error in a range of cells, you can use an array formula based on the MATCH and ISERROR functions. In the example shown, the formula is: { = MATCH ( TRUE , ISERROR ( B4:B11 ), 0 ) } This is an array formula, and must be entered using Control + Shift + Enter (CSE). How...Read more
Excel formula: Data validation contains specific text
To allow only values that contain a specific text string, you can use data validation with a custom formula based on the FIND and ISNUMBER functions. In the example shown, the data validation applied to C5:C9 is: = ISNUMBER ( FIND ( "XST" , C5 )) How this formula works Data validation...Read more
Excel formula: Round a number
If you need to round a number in a normal fashion (i.e. round values of 5 or more up and values less than 5 down) you can use the ROUND function with a specified number of digits. In the example, the formula in cell D6 is = ROUND ( B6 , C6 ) This tells Excel to take the value in B6 (PI) and round...Read more
Excel formula: Highlight multiples of specific value
To highlight numbers that are multiples of a another number with conditional formatting, you can use a simple formula based on the MOD function. In the example shown, the formula used to highlight multiples of 9 is: = MOD ( B4 , $E$2 ) = 0 Note: formula is entered relative to the "active cell" in...Read more
Excel formula: Count day of week between dates
To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT. In the example shown, the formula in cell E6 is = SUMPRODUCT ( -- ( WEEKDAY ( ROW ( INDIRECT ( B6 & ":" & C6...Read more

Videos

Tip: How to create a mixed reference
A mixed reference is part relative, and part absolute. Mixed references are useful when you want only part of a cell reference to change when you copy a formula.
Run time: 2:34
Tip: Perfect conditional formatting with dummy formulas
Conditional formatting with formulas can be tricky because you can't see what happens to the formula when the rule is applied. Dummy formulas let you visualize how formulas will behave...
Run time: 3:28
Tip: How to sort a pivot table with a custom list
Pivot tables can be sorted in several ways - by rows, by columns, and by value. In today’s Exceljet tip, we show you how to sort a pivot table with a custom list.
Run time: 2:24
Tip: How to group a pivot table with custom buckets
Pivot tables are awesome tools for grouping data, and they do a lot of grouping automatically. But pivot tables also let you group data into your own custom buckets, which is great for...
Run time: 2:45
Tip: How to quickly create a pivot table
In this video, we show you how to create a pivot table fast.
Run time: 2:56

Blog

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.
One problem that comes up a lot in Excel is counting or summing based on multiple OR conditions. For example, perhaps you need to analyze data and count orders in Seattle or Denver, for items that are Red, Blue, or Green? This can be surprisingly tricky, so naturally it makes a good challenge!

Feedback

Just found your site. Totally loving it. Great explanations! -Robert
You are an angel. I have been forever frustrated when searching for help and finding no one gets what you get...teach the basic principle first!! Thank you, Thank you! - Christine
As an undergrad doing radiation materials damage research, I have a lot of data to sift through and analyze. Microsoft's Excel has been a very useful multi-tool, and ExcelJet has been a extremely handy guidebook to using Excel effectively. Thank You! -Zachary