Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
Case-sensitive lookup By default, standard lookups with VLOOKUP or INDEX + MATCH aren't case-sensitive. Both VLOOKUP and MATCH will simply return the first match, ignoring case. However, if you need to do a case-sensitive lookup, you can do so with an array formula that uses INDEX, MATCH, and the...Read more
To sum by week, you can use a formula based on the SUMIFS function. In the example shown, the formula in F4 is: = SUMIFS ( amount , date , ">=" & E4 , date , "<" & E4 + 7 ) This formula uses the named ranges "amount" (C4:C21) and "date" (B4:B21). How this formula...Read more
To count rows in an Excel table , you can use the ROWS function. In the example shown, the formula in I4 is: = ROWS ( Table1 ) Note: with just the table name, ROWS will count data rows only. How this formula works This formula uses structured referencing , a syntax that allows table parts to be...Read more
To highlight dates that are "past due" you can use a conditional formatting rule that checks if the variance between two dates is greater than a certain number of days. In the example shown, three conditional formatting rules have been applied to the range D5:C12 with these formulas: = ( D5 - C5...Read more
To return the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) with a given start date, you can use a formula based on the WEEKDAY function. In the example shown, the formula in D6 is: = B6 + 7 - WEEKDAY ( B6 + 7 - 2 ) Where B6 contains the date Friday, January 16, 2015,...Read more
Videos
Some Excel formulas are complex. But all complex formulas are built from simple steps. In this video, we built a more complex formula step by step.
In this video, we’ll look at a basic example of filtering with the FILTER function.
In this video, we show you how to filter your pivot table to show top and bottom values.
In this video, we look at how to configure a slicer to control more than one pivot table at once.
In this video, we'll use the FILTER function, with the LARGE and SMALL functions, to show the top or bottom results in a set of data.
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 would just like to say, that being a high level excel user, your site has been a godsend for me. I have taken to using Exceljet rather than excel help as I can more readily find what I am looking for with clearer guidance on how to implement. -Michelle
When I was a Network Engineer, your site was a big help for the 5% of my time spent in Excel. Now as an IT Security Engineer, 95% of my time is spent in Excel and I would be incredibly hard-pressed to do my job without your site. -Mark
I frequently use the resources you provide here on your website, and wanted to thank you for the awesome work you are doing. It is very well done, has great breadth, and lays out complex topics in an understandable way. -Chase
We offer free resources and paid training.