Exceljet

Quick, clean, and to the point

Shade groups of rows with conditional formatting

In this video, we'll look at how to use conditional formatting to shade alternating groups of rows. For example, you can use this approach to shade groups of 3 rows, groups of 4 rows, and so on. This can be a nice way to make certain tables easier to read.

Here we have a table with 3 rows of data for each client, for the months April, May, and June.

Let's use conditional formatting to highlight these rows to match the data. We can do this with a formula that groups rows by three's.

Shortcuts Recipe Remove Blank Rows

In this video, we'll look at a simple shortcut recipe for removing thousands of blank rows in a large set of data. Shortcut recipes are just a series of shortcuts you perform in a certain sequence to solve a specific problem.

In this first example, we have a big set of data that contains a lot of blank rows. If I move to the last cell, you can see that there's over 36,000 rows of data, and, looking at the count, just over 33,000 rows actually contain data, which means over 3000 rows are blank.

30+ Excel Shortcuts in 12 minutes

Excel shortcuts are a great way to save time, conserve energy, and prevent mistakes.

But with more than 200 shortcuts to choose from, where do you start? In this rapid-fire video, we demonstrate more than 30 popular Excel shortcuts, based on a survey we did of over 800 Excel users. These are practical shortcuts you can use to save time every day in Excel.

Shortcuts shown in the video

Two-way lookup with INDEX and MATCH approximate

Two-way lookup with INDEX and MATCH approximate

In this video, we'll look at how to build a two-way lookup with INDEX and MATCH, using an an approximate match.

Here we have a simple cost calculator, which looks up cost based on on a material's width and height. The match needs to be approximate. For example, if the width is 250, and the height is 325, the correct result is $1,800.

If the width is 450, and the height stays at 325, the correct result is $3,600.

We can build a formula that does this lookup using INDEX and MATCH.

How to use boolean logic in Excel formulas

In this video, I'm going to show you the basics of boolean logic. Boolean logic is a great tool for simplifying formulas, especially those with many IF statements.

So, to start off, what's a boolean?

A boolean is a data type with only two possible values, TRUE or FALSE.

You'll often see boolean results, or boolean expressions in Excel.

For example, if I enter the formula =B4>30 here, we'll get the boolean result TRUE.

So, B4>30 is a boolean expression - or logical statement - that returns TRUE or FALSE.

Perfect conditional formatting with dummy formulas

In this video, I'll show you how to quickly test your conditional formatting rules with dummy formulas.

When you apply conditional formatting with formulas, it can be hard to get the formulas to work properly, because you can't see what happens to the formula when the rule is applied. Dummy formulas let you visualize how formulas will behave before you create a rule.

Let me illustrate with a very simple example. Let's say we want to highlight values over 100 in this set of data.

To start, I'll pick an area to the side, lined up with the rows.

CONCAT & TEXTJOIN

Fiddles with new CONCAT & TEXTJOIN functions

I've been playing around with the TEXTJOIN and CONCAT functions this week. These are both new functions in Excel 2016, introduced in the Office 365 subscription service.

Both of these functions let you join (concatenate) text in different cells together. TEXTJOIN lets you join values with a delimiter of your choice, and has an option to ignore empty values. CONCAT simply mashes all values together without options.

Tags 

Paste Special Shortcuts

In this video, we'll review shortcuts and commands for Paste Special. 

As you might already know, Paste special is a gateway to many powerful operations in Excel.

To use Paste Special, just copy normally, then use the shortcut Ctrl + Alt + V in Windows, Ctrl + Command + V on the Mac.

Using this shortcut doesn't actually finish the Paste, it simply displays the Paste Special dialog, where you can choose which options you want.

In Windows, you can type a letter to select options.

On the Mac, you'll need to type the Command key plus a letter.

How to find missing values with COUNTIF

In this video, we'll take a look at how to use the COUNTIF function to solve a common problem: how to find values in one list that appear in another list. Or, how to find values in a list that don't appear in another list.

Let's take a look.

In this worksheet, on the left, I have a list of 20 names.

On the right, I have a much larger list of over 1000 names.

How can I quickly figure out which names in the smaller list also appear in the bigger list?

Don't hard code assumptions into your formulas

In this video, we'll take a look at a few reasons why you shouldn't hard-code values that may change into your formulas.

Here we have a simple table for a driving trip across the United States. The route begins in San Francisco and ends in New York City. The route is divided into segments, with each segment starting and ending in a larger city.

Let's add formulas in columns E and F to calculate the estimated fuel usage and fuel cost. 

First, in column E, to estimate fuel consumption we need a formula that divides the distance by the estimated miles per gallon.

Pages

Subscribe to Front page feed