Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
If need to generate a dynamic series of dates with a formula that include only future weekend dates (i.e. Sat and Sun), you can do so with a formula that uses the IF and WEEKDAY functions. How the formula works In the example, B6 is the hard-coded start date and the formula in B7 is: = IF ( WEEKDAY...Read more
To remove text from a cell by position, you can use the REPLACE function. In the example shown, the formula in C6 is: = REPLACE ( B6 , 1 , 24 , "" ) How this formula works The replace function lets you replace text based on its location and length. In this case, we want to strip off the...Read more
To convert a time in Excel's format to a Unix time stamp, you can use a formula based on the DATE function. In the example shown, the formula in C5 is: = ( B5 / 86400 ) + DATE ( 1970 , 1 , 1 ) How this formula works The Unix time stamp tracks time as a running count of seconds. The count begins at...Read more
To filter results of SUMPRODUCT with specific criteria, you can apply simple logical expressions directly to arrays in the function, instead of using the IF function . In the example shown, the formula in H5 is: = SUMPRODUCT ( -- ( color = "red" ), quantity , price ) Named ranges The...Read more
To add a given number of years to a date, you can use the EDATE function. In the example shown, the formula in D5 is: = EDATE ( B5 , C5 ) How this formula works The EDATE function is fully automatic. Simply supply a valid date and a number of months and EDATE will return a new date. To subtract...Read more
Videos
In this video we look at how to group sample voting data into 10 year buckets, using a pivot table.
When you copy and paste a pivot table, a hidden data cache still remains. This video shows a simple way to copy a pivot table without the source data, while preserving all formatting.
Sometimes, you open a spreadsheet and see errors everywhere! Fortunately, Excel contains a cool tool that can automatically trace errors, and pinpoint the exact source.
By their nature, nested IF formulas are hard to read. In this video, we show you how to easily make a nested IF formula more readable, by adding line breaks.
In this video, we look at how to quickly find formulas and trace relationships, using the concept of precedents and dependents. A great way to quickly understand an unfamiliar worksheet.
Blog
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.
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?
Feedback
What a great website. I'm an Excel trainer and your website helped me populate a sample company worksheet with random departments. Thanks. -Lloyd
Great pages. I search for solution, find it. Another week, search for solution, find it again. -Hans
My Lord where have you been all my life?...I've learned more awesome tricks/functions in one afternoon from you than in the last 10 years. - Robert
We offer free resources and paid training.