This is an advanced formula that requires a number ofnestedfunctions. However, it is an excellent example of the power ofdynamic array formulas in Excel. For convenience,datais thenamed rangeB5:B104. This range contains 100 random color names. This might impact performance in larger sets of data. To streamline the formula, we can use theLET function. The LET function is used to declare and assign values to variables. Then we replace UNIQUE(data) withuwhere it occurs in the formula....

Abstract Transcript Hey, Dave here from Exceljet. In the first video, I talked about how fast they are. Let’s go take a look. And when I hit OK we’ll get 26 properties, so that’s the new set of data. I’m going to undo that… undo, undo, undo. And let’s look at another way to do it. So Ctrl + T will do that and, when I click OK, we’ll get the table....

This tricky chart bang out can be quite hard to read.

Like a pie chart, a 100% stacked bar chart shows a part-to-whole relationship.

A 100% stacked column chart is an Excel chart throw in meant to show the relative percentage of multiple data series in stacked columns, where the total (cumulative) of stacked columns always equals 100%. The 100% stacked column chart can show the part-to-whole proportions over time, for example the proportion of quarterly sales per region or the proportion of monthly mortgage payment that goes toward interest vs. principal. Although stacked column charts can show the change over time of a part-to-whole relationship, it’s generally difficult to compare the relative size of the components that make up each column....

We also have alarge list of example formulas, amore complete list of Excel functions, andvideo training. If you are new to Excel formulas,see this introduction. Note: Excel now includesDynamic Array formulas, andalmost 50 new functions. Date and Time Functions Excel provides many functions to work withdatesandtimes. If you want a static value, usedateandtimeshortcuts. HOUR, MINUTE, SECOND, and TIME Excel provides a set of parallel functions for times. DATEDIF can also be configured to get total time in “normalized” denominations, i....

TheIF functionis one of the most heavily used functions in Excel. With IF, you’re free to bring your spreadsheet to life. Are they sometimes necessary? What are the alternatives? Read on to learn the answers to these questions and more… 1. Note the square brackets…these mean the arguments are optional. However, you must supplyeithera value for true, or a value for false. The MONTH function takes that date and returns the current month....

We ran a survey on Excel shortcuts. Here are the results, based on over 800 replies. I’ve also compiled a list of the most 20 popular Excel shortcuts below. So I’ve had to “interpret” many of the shortcuts people mentioned. That said, the patterns are clear, and the best shortcuts naturally bubble up again and again. It’s a great list. Although people who take a shortcut survey are naturally interested in shortcuts, this is surprising!...

Abstract Transcript Hey, Dave here from Exceljet. We’ll look at how to use named ranges to make your formulas easier to read. Okay so let’s go do the training. Notice that you don’t need to enter a closing parentheses. you’re able to also use a feature called the INSERT function. you could also ask Excel to insertfunction argumentsfor you, using the shortcut Control-Shift-A. Then you’re able to opt for arguments, and replace them with the values you need....

When you want to pull information from a table, the Excel VLOOKUP function is a great solution. And yet, although VLOOKUP is relatively easy to use, there is plenty that can go wrong. Which you probably aren’t. This can cause results thatlook completely normal, even though they aretotally incorrect. How VLOOKUP works VLOOKUP is a function to look up and retrieve data in a table. (For horizontally structured data, seeHLOOKUP)....

Formulas are the bread and butter of Excel. If you use Excel on a regular basis, I bet you use a lot of formulas. But crafting a working formula can take way too much time. In this article, I share some good tips to save you time when working with formulas in Excel. Video:20 tips to save time with Excel formulas 1. Don’t add the final parentheses to a function...

Abstract Transcript In this video we’ll look at three basic array formula examples. In this first example, we have high and low temperatures for seven days. We want to calculate the biggest change on any given day. I’ll start by subtracting the lows from the highs. This is an array operation, and we get all 7 results in a dynamic array. Now all I need to do is to wrap theMAX functionaround this formula....

We put together this list based on a survey of more than 800 Excel users. Another very useful shortcut you’ll see me use often in this video is undo, control Z. On a Mac, add the function key. To cut instead of copy, use control X. Before we try it, let’s first check the formulas on this worksheet using control + grave. The shortcut toggles the worksheet to show all formulas, and numbers without any formatting....