Newsletter
Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
Formulas
In Excel, the HYPERLINK function can be used to create links. The basic syntax is: = HYPERLINK ( "link" , "link text" ) The link itself is a text string that represents a valid link. The link text (called "friendly name" in Excel) is the text displayed to a user. Ultimately, the...Read more
The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, the ADDRESS function with 1 for both row and column like this: = ADDRESS ( 1 , 1 ) returns "$A$1" as text. The INDIRECT function returns a valid reference from a text string. In the...Read more
For this example, we want to calculate the principal portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: = C6 / 12 per - the...Read more
This is a tricky formula to understand, and it requires that you have a numeric id for each match, and that all matches are sorted by id. They key is in understanding how FREQUENCY gathers numbers into "bins". Each bin represents an upper limit, and generates a count of all numbers in the data set...Read more
For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of...Read more
Videos
In this video, we'll look at how to use conditional formatting to shade every other row in a table. This is sometimes called "zebra striping".
In this video, we'll look at how to create 3D references, an elegant way to analyze multiple worksheets with simple formulas.
In this video, we'll cover the basics of data labels, which can be used to display data values in a chart directly.
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 show how to select visible cells only.
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
Your site is my number one go-to when I need a quick reference OR when I need in-depth clarification. It's also where I send colleagues hoping to learn more about Excel. I especially like the intro/explanation/example style - AND - the "customers shopping for this (function) were also interested in..." at the bottom. Because reading up on the next/related steps can lead to many AHA moments! -Cat
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
Love your website. Your algorithms are clever and innovative. Your examples are easy to follow. And I've never seen a cleaner looking website. It has been extremely helpful to me. -I've never seen a cleaner looking website
We offer free resources and paid training.