Newsletter
Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Formulas
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Minutes Value Time 1 1/24 60 0.04167 1:00 3 3/24 180 0.125 3:00 6 6/24 360 0.25 6:00 4 4/24 240 0.167 4:00 8 8/24 480 0.333 8:00 12 12/24 720 0.5 12:00 18...Read more
The framework of this formula is based on IF, which tests each value in column B to see if its the same as the value in the "cell above". When values match, the formula returns nothing (""). When values are different, the IF function calls SUMIF: SUMIF ( B:B , B5 , C:C ) In each row where SUMIF is...Read more
The ABS function is fully automatic. All you need to do is supply a number and ABS will return the absolute value. Convert negative numbers in place If you only need to convert negative numbers once, you can convert in-place with Paste Special : Add -1 to a cell and copy to the clipboard Select the...Read more
The CONVERT function requires three arguments: number, from_unit, and to_unit. As long as the units specified are in the same category (weight, distance, temperature, etc.) , CONVERT will automatically perform a conversion and return a numeric result in the specified "to unit". In the example shown...Read more
In this example, the goal is to get a count of people that hold shares in whole numbers. For example, Bob holds 100 shares (even), so he should be included in the whole number count, while Cindy holds 50.5 shares, so she should not be included in the whole number count. The first problem is how to...Read more
Videos
In this video, we show you how to make a pivot table fast.
In this video, we'll look at how to use a simple formula to apply conditional formatting. Formulas allow you to create more flexible and powerful conditional formats.
In this video, we'll look at shortcuts that let you work with the Ribbon using only your keyboard.
In this video, we’ll set up the XLOOKUP function with a basic example. Matching on City name, we'll retrieve Country and Population.
In this video, I'm going to show you the basics of Boolean logic. Boolean logic is a great tool for simplifying formulas, especially those with many IF statements.
Blog
In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.
There are eight functions in Excel that work differently than you might realize. The same features that were supposed to make them easier to use, can actually make them harder to use :)
This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook.
A quick example of how to track testing for COVID-19 using Excel and publicly available data. In this project, the data is fetched and "shaped" with Power Query, then dropped back into Excel, where it can be refreshed with a single click.
Dynamic Excel offers 6 brand new functions that solve hard problems in Excel like sorting, filtering, and working with unique values. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel.
Feedback
I love your email and webpage. You have made me look like a hero on several occasions. Thank you very much for all the free learning! -Raulf
I am a software engineer with 10+ years of programming experience, but I had to learn advanced Excel material for a new job that used it primarily and I found the official Microsoft documentation a bit vague and uninstructive until I found your website, which helped me implicitly understand a lot about the internal workings of Excel's formulas and matrix treatments through your explanations and examples. -Jay
Just wanted to drop in and say thank you! You've helped me solve SO MANY PROBLEMS over the years. Today, I learned the difference between Excel's FIND and SEARCH function and was able to write a formula that must have saved me hours of manual work. -Amanda
We offer free resources and paid training.