Newsletter
Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Formulas
This formula relies on the AND function to test two conditions at the same time: Count of words from named range inc is >0 Count of words from named range exc is =0 If both conditions are TRUE, the formula returns TRUE. If either condition is FALSE, the formula returns FALSE. The test for multiple...Read more
This formula uses the named range "key" (C4:G4) for convenience only. Without the named range, you'll want to use an absolute reference so the formula can be copied. In cell I7, we have this formula: = SUM ( -- ( C7:G7 = key )) working from the inside-out, this expression is evaluated first: C7:G7...Read more
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12, and the reference to B4 will change to the address of each cell being evaluated...Read more
This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock. In the example, colors are treated as unique item identifiers – imagine a product available in one size only in just three colors: red, blue, or...Read more
The YEAR function takes just one argument, the date from which you want to extract the year. In the example, the formula is: = YEAR ( B4 ) B4 contains a date value for January 5, 2016. The YEAR function returns the number 2016 representing the year of the date. Note that you can use YEAR to extract...Read more
Videos
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 look at how to use a pivot table to analyze movie data by rating, runtime, genre, and more.
In this video, we show you how and why to use a table as a data source for your pivot table.
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 how to use conditional formatting to highlight rows and columns associated with exact match lookups.
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've been following Exceljet for a while and would wholeheartedly compliment you on your lucid examples and explanations of the most complicated Excel formulas. This is indeed extremely helpful. -Yossi
You certainly provide the clearest explanations of how to use Excel that I have ever seen! Thanks very much for sharing! -Mike
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ë
We offer free resources and paid training.