Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Next business day 6 months in future
Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015. Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the range B9:B11...Read more
Excel formula: Get pivot table grand total
To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. In this case, we want the grand total of the "sales" field, so we simply provide the name the field in the first argument, and supply a reference to the pivot table...Read more
Excel formula: NPV formula for net present value
Net Present Value (NPV) is the present value of expected future cash flows minus the initial cost of investment. The NPV function in Excel only calculates the present value of uneven cashflows, so the initial cost must be handled explicitly. One way to calculate Net Present Value in Excel is to use...Read more
Excel formula: Data validation number multiple 100
Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When the formula returns FALSE, validation fails and the input is rejected. In this case, the MOD function is used to perform a modulo...Read more
Excel formula: Reverse VLOOKUP example
Starting at the beginning, the formula in H5 is a normal VLOOKUP formula: = VLOOKUP ( G5 , B5:D8 , 3 , 0 ) // returns 3000 Using G5 as the lookup value ("C"), and the data in B5:D8 as the table array , VLOOKUP performs a lookup on values in column B, and returns the corresponding value from column...Read more

Videos

In this video, we'll look at how to create a list of random text with the RANDARRAY function and INDEX function.
Run time: 2:58
One of the more confusing aspects of pivot tables is layout options. When you create a pivot table, you have your choice of three layouts. In this video, we'll take a look at each...
Run time: 3:05
Some Excel formulas are complex, but all complex formulas are built from simple steps. In this video we build a more complex formula step-by-step.
Run time: 3:07
In this video, you find your way around an Excel workbook, and how to see where you are now.
Run time: 2:32
In this video, we'll look at how to compare two lists using conditional formatting. This is a great way to visually highlight missing items in a list.
Run time: 2:28

Blog

What is an array formula?
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.
Excel's RACON functions
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.
Tracking COVID-19 with Excel
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 emails….I learn so much….even if frequently the suggestions are not immediately applicable it opens my eyes to all the things excel can do. -Lydia
I use your site almost daily. All your content is extremely helpful, clear, and easy to follow. -Joyce
I am an IT Trainer, based in London and working internationally worldwide, mostly Pharma, Banking and Law Firms. I subscribed to your newsletter and regularly visit your website. I am completely blown away by your mails and explanations. They are by far the best one can find on the web. I look forward to your weekly letter. I refer to your website every single Excel training and to every customer I have! -Avi