This is a great alternative to filtering, because you can see the information you’re looking for highlighted in context. Let’s take a look. Here we have a table that contains of order data. We could add a filter, and use it to explore the data. But filters can be a little clunky. You have keep changing the filter, and you can’t see what you’re looking for in the context of other data....
The DATEDIF function is a “compatibility” function that comes from Lotus 1-2-3. For reasons that are unknown, it’s only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time. DATEDIF takes three arguments: start date, end date, and unit. Unit specifies whether you want the difference between dates in days, months, or years, with several variations. To explain how DATEDIF works, the table below is set up to show all options available for Unit....
Let’s take a look. To illustrate converting formulas to values, let’s look at an example that uses the RANDBETWEEN function to assign a list of people randomly to four different groups. RANDBETWEEN takes two arguments: the first argument is the bottom value, and the second argument is the top value. In this case, We’ll use 1 and 4, because we want four groups. Once I copy the formula down, each person in the list has a group number between 1 and 4....
In this video, we’ll show you how to use a formula with conditional formatting to highlight an entire row in a multi-column list. Let’s take a look. Here we have a task list that contains a number of columns - priority, task, owner, and due date. If we want to highlight all tasks that that are assigned to Bob, we could use the Conditional formatting rule “Text that contains” and then enter “Bob” for the text to match....
A histogram chart displays the count of items grouped into bins using columns. Starting in Excel 2016, the histogram chart is a built-in option. In this worksheet, I’ve got a list of 100 names and ages. Let’s plot this data in a histogram chart. To start out, select a cell in the data. If you have a lot of data, there’s a good chance you’ll find a histogram option in Recommended charts....
Here I have a worksheet that calculates sales commissions based on the commission structure shown in the table. For example, we can see that King sold $124,500 and gets a commission of 5%, worth about $6,000. On the left, I have a small test area with three formulas to calculate commission rates. Each formula returns the same rate when we plug in different sales numbers. So if we look at the first formula, you can see it’s created with a series of nested IFs....
Let’s take a look. As you make changes to your worksheet, Excel tracks these changes in a way that they can be reversed if needed using the Undo command. For example, if you’ve made a list, and you decide you don’t want the last value you typed, you can just click the undo button, and Excel will step back in time before that change was made. If you click the Undo button again, you’ll step back one more step....
When you use a pivot chart, it’s important to understand that the chart won’t update by itself when source data is added or modified. To demonstrate this, let me create a new pivot chart using this order data from a small online surf shop. You can see we have a date, order number, item, total, and state. So, to start off, let’s just see what items are sold. I’ll add item on the axis, and then put total into the value area....
Let’s take a look. As with our previous examples, we have a simple worksheet that contains instructions and several highlighted cell references. Following the instructions in the table, let’s build the formulas needed using multiplication. The first example doesn’t need cell references and can be input directly: = 4 * 5 If you forget the equals sign, you’ll see the formula displayed instead of the result. Just edit the formula and add the equals sign to the front....
Here we have a list of 16 properties with prices and other information. Let’s calculate some averages based on the conditions listed in column K. The AVERAGEIF function calculates an average for cells that meet a single criteria. It takes three arguments: range, criteria, and average_range. Note that average_range is optional and represents the actual range to average. If you’re not using average_range, the range argument will be used instead....
Where text represents a text string. As the formula is copied down, CountWords returns the count of words in column B. Note: the LAMBDA function is available through the beta channel of Excel 365 only. This formula uses three built-in functions: SUBSTITUTE, TRIM, and LEN. Here is the formula in action below. You can read a detailed explanation here. The formula only requires one input parameter, the text in cell B5, so the LAMBDA function will have two arguments, the text from a cell, and the calculation to perform....
where data is an Excel Table in the range B5:E15. Background study What is an Excel Table (3 min. video) Introduction to structured references (3 min. video) How to use INDEX and MATCH (overview) Excel Tables (overview) INDEX and MATCH One of the advantages of using INDEX and MATCH over VLOOKUP is that INDEX and MATCH can easily work with lookup values in any column of the data. In the example shown, columns B through E contain product data with a unique ID in column E....
MATCH return 2 inside the INDEX function as the column_num argument, where the array is set to the range C5:F11, which includes data for all fruits. The tricky part of the formula is the row_num argument, which is set to zero. Setting row to zero causes INDEX to return all values in the matching column in an array like this: The SUM function then returns the sum of all items in the array, 47....
Fields The source data contains six fields, but only two fields are used to build the pivot table, Item and Amount: Item has been added as a Row field. The Amount field has been added twice as a value field. The first instance of Amount has been renamed “Sales” and and set to sum values: The second instance of Amount has been renamed “Rank”. The rank value is created setting “Show values as” to “Rank largest to smallest”, with Item set as the Base field:...
TRUE values represent numeric values. We want to know if this result contains any TRUE values, so we use the double negative operator (–) to force the TRUE and FALSE values to 1 and 0 respectively. This is an example of boolean logic, and the result is an array of 1’s and 0’s: We use the SUMPRODUCT function to sum the array: Any sum greater than zero means at least one number exists in the range, so we use “>0” to force a final result of TRUE or FALSE....
The url comes straight from B5. The start is calculated using the FIND function like this: FIND returns the position of the double slash ("//") in the URL as a number, so we add 2 in order to start extracting at the next character. Chars represents the number of characters to extract. We calculate this using the following expression: The LEN function calculates the length of the original URL, from which we subtract the position of “//” minus 1....
Here we have a worksheet that contains a large set of sales data for a business that sells specialty chocolate to retailers. This data contains columns for date, customer, city, state, region, product, category, quantity, total sales. You can see that there are a lot of rows, almost 3000 rows total, each representing an order for one kind of chocolate to one customer. In its current form, this data is hard to understand, because there’s too much detail....
In this lesson, we’re going to take a tour of the File tab. The File tab is also sometimes referred to as “backstage”. Unlike the main stage where you do your work, the File tab is a place to go for tasks that don’t involve working on your data—for example, sharing files, printing, and changing Excel settings. Let’s take a look. The first thing to notice about the File tab is that, unlike other ribbon tabs, you aren’t able to see the worksheet when you’re on the File tab....
In the example shown, the formula first subtracts the date value for January 1, 1970 from the date value in B5 to get the number of days between the dates, then multiplies the result by 85400 to convert to a Unix time stamp. The formula evaluates like this: How Excel tracks dates and time The Excel date system starts on January 1, 1900 and counts forward. The table below shows the numeric values associated with a few random dates: Notice the last date includes a time as well....
