# Exceljet

## Formulas

### Odometer gas mileage log

To calculate gas (MPG) based on odometer readings you can build a table with a few simple formulas. In the example shown, the formulas in E5 and F5 are: = [ @ Mileage ] - SUM ( C4 ) // E5 calculate mileage = [ @ Distance ] / [ @ Gallons ] // F5 calculate mpg How this example works Note: example...Read more

### Formula with locked reference

To create a formula with a "locked" reference – a reference that won't be adjusted during copy or paste, or when rows and columns are changed in a worksheet – you can use the INDIRECT function. How this formula works The INDIRECT function accepts text, and evaluates that text as a reference. As a...Read more

### Subtotal by color

If you need to subtotal numbers by color, you can easily do so with the SUMIF function. In the example shown, the formula in G5 is: = SUMIF ( \$B\$4:\$B\$11 , F5 , \$D\$4:\$D\$11 ) How this formula works The SUMIF function takes three arguments: range, criteria, and sum_range. In this case, we are using:...Read more

### Get age from birthday

To calculate a person's age from their birth date, you can use the YEARFRAC , INT , and TODAY functions . In the generic version of the formula above, birthdate is the person's birthday with year, and TODAY supplies the date on which to calculate age. Because TODAY always returns the current date,...Read more

### List contains duplicates

Does a range contain duplicate values? If you want to test a range (or list) for duplicates, you can do so with a formula that uses COUNTIF together with SUMPRODUCT. In the example, there is a list of names in the range B3:B11. If you want to test this list to see if there are duplicate names, you...Read more

## Videos

### How to plot survey data in a bar chart

In this video, we'll look at how to plot over 3000 survey responses to a question in an Excel bar chart.
Run time: 3:42

### Shortcuts to enter data

In this lesson, we cover shortcuts you can use to to enter data in one or more cells at a time. Knowing these tips can really speed up editing in Excel.
Run time: 3:33

### How to use the SUMIF function

In this video, we'll look at how to use the SUMIF function to sum cells that meet a single criteria in a set of data.
Run time: 3:43

### How to quickly create a pivot table

In this video, we show you how to make a pivot table fast.
Run time: 2:56

### How to use drag and drop in Excel

In this video, we show you how to use drag and drop, which can be surprisingly useful.
Run time: 1:56

## Blog

### Excel Pivot Tables

Pivot tables are one of the most powerful and useful features in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.

### Excel Formula Errors

Formula errors are useful, because they tell you clearly that something is wrong. This guide shows examples of each of the 9 formula errors you may run into, with an information on how to investigate and correct the error.

### How to lookup first and last match

If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.

### Excel formulas and functions

Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. This article introduces the basic concepts you need to know to be proficient with formulas in Excel.

### The double negative in Excel formulas

The double negative coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. This is a useful technique in many advanced formulas that work with cell ranges.

## Feedback

Of all the "Excel" sites I have visited, yours is the most friendly and inviting, that I have come across. -Steve
I am just loving exceljet. Honestly THE best money I ever spent! What is so good about this resource is that it is in short, specific video clips, with both audio and text and with practice downloads for hands-on experience. I like the way you highlight what you are talking about in orange, and it isn’t too fast that the eye can’t see it before you move to the next thing! I love how you provide instructions for the absolute beginner right through to really advanced things. -Anne
Just found your site. Totally loving it. Great explanations! -Robert