Exceljet

Quick, clean, and to the point
Tip: How to use data labels in a chart
Excel charts have a flexible system to display values called "data labels". Data labels are a classic example a "simple" Excel feature with a huge range of options just below the surface. This video provides a general...
Tip: How to randomly assign people to teams
In this video, we look at a step by step approach to randomly assign people to groups with teams using formulas. Along the way, we use three functions (RAND, RANK, and CEILING), helper columns, and nesting. We verify results with the COUNTIF...
Tip: 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...
Tip: 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...
Tip: 30+ Excel Shortcuts in 12 minutes
Shortcuts are a great way to save time and energy in Excel, but there are more than 200, so the list can seem overwhelming. In this rapid fire video, we demonstrate more than 30 popular Excel shortcuts you can use to save time every day in Excel. We...
Tip: 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 approximate matching. This is a classic solution for non-exact lookups like income tax, shipping costs, material costs, etc. ...
Tip: How to use boolean logic in Excel formulas
In this video, we introduce the concepts of boolean values and boolean logic. Boolean logic can be a great way to simplify and speed up complex formulas, since it requires no branching.
Tip: Perfect conditional formatting with dummy formulas
Conditional formatting with formulas can be tricky 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. This video shows you how to test...

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 
Tip: Paste Special Shortcuts
Despite the boring name, Paste Special is actually a gateway into a hidden world of powerful features. This video shows how to access Paste Special features using keyboard shortcuts.

Pages

Subscribe to Front page feed