## Conquer Excel

Our approach is quick, clean, and to the point. Join more than 100,000 people who get weekly updates from us.

## Formulas

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

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

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

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

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

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.

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...

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.

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...

In this video, we show you how to create a pivot table fast.

## 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

We offer free resources and paid training.