Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To count rows in a table that meet internal, calculated criteria, without using a helper column, you can use the SUMPRODUCT function. Context Imagine you have a table of sales figures for several products. You have a column for sales last month and a column for sales in the current month. You want...Read more
To configure COUNTIFS (or COUNTIF ) with a variable range, you can use the OFFSET function . In the example shown, the formula in B11 is: = COUNTIFS ( OFFSET ( B$5 , 0 , 0 , ROW () - ROW ( B$5 ) - 1 , 1 ), "<>" ) This formula counts non-blank cells in a range that begins at B5 and...Read more
To FILTER and extract the first or last n values (i.e. first 3 values, first 5 values, etc.), you can use the FILTER function together with INDEX and SEQUENCE . In the example shown, the formula in D5 is: = INDEX ( FILTER ( data , data <> "" ), SEQUENCE ( 3 , 1 , 1 , 1 )) where data...Read more
If want to extract the name part of an email address, you can do so with a formula that uses the LEFT and FIND functions. In the generic form above, email represents the email address you are working with. In the example, we are using this formula: = LEFT ( C4 , FIND ( "@" , C4 ) - 1 )...Read more
To check if a date is within the last n days of today's date, you can use a formula based on the TODAY and AND functions. In the example shown, we are checking for dates in the last 7 days. The formula in D5, copied down, is: = AND ( B5 >= ( TODAY () - 7 ), B5 < TODAY ()) You can use a...Read more
Videos
In this video, we'll use MATCH to find the position of an item in a table, and the INDEX function to retrieve the value at that position in a two-way lookup.
In this video, we'll look at how to use the SUMIFS function with an Excel table, with a side-by-side comparison without a table.
In this video, we'll look at how use Boolean algebra in array formulas for AND and OR logic. This is a key skill when building dynamic array formulas.
In this video, we’ll set up the XLOOKUP function to perform an approximate match in order to calculate a quantity based discount.
In this video we look at how to group sample voting data into 10 year buckets, using a pivot table.
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
Many thanks.This site is pinned to my home page and has proven itself very useful. -Glynn
I felt compelled to send my thanks for your clear, informative, concise, and just very helpful videos. I have been thrust a data management assignment without training and far outside my usual duties, so these have been invaluable. -Zoë
Just found your site. Totally loving it. Great explanations! -Robert
We offer free resources and paid training.