Excel contains two functions that can help you clean things up. Let’s take a look. Here we have a list of movie titles that were copied in from some other system. you might see that there’s a problem with extra space characters. Excel contains a special text function calledTRIMthat is designed to fix this problem. TRIM takes one argument: the text you want to process. The result is a set of cleaned up titles without extra spaces....
In this video, I’ll show you how to base one pivot table on another. Here I have a set of sales data for chocolate products. I’ve also got another month’s worth of sales data in this second sheet. We’ll get to that in a minute. Now that we have the pivot table set up, we have a nice breakdown of sales by Product. But suppose you want to look at sales by Customer?...
Lets take a look. Well turn off the buttons for now. For example, we can expand and collapse each item in the category field individually, in any combination. We can also expand and collapse the entire field at once. Just select a cell in the field, and use the buttons on the ribbon. These buttons are called Expand Entire Field, and Collapse Entire Field. The same options are available in the right-click menu....
Let’s do that step-by-step. First, I’ll use theRIGHT functionto extract the three characters from the right. This gives us the number. you might see by the left alignment that the number is still in text format. We get a correct result of 2, but we loose the padding with zeros. So let’s add the padding back using theTEXT function. The TEXT function lets us apply a number format inside a formula....
This is a great way to visually highlight missing items in a list. Here we have two lists. Both lists contain the same number of items, but each list is slightly different. We can use conditional formatting with a formula to quickly find and highlight the differences. First, I’m going to name each list. The first list I’ll call list1, and the second list I’ll call list2. For the formula, we can use the COUNTIF function....
One of the most important operations in Excel formulas is concatenation. In Excel formulas, concatenation is the process of joining one value to another to form atext string. The values being joined can be hardcoded text, cell references, or results from other formulas. Then I’ll introduce the three Excel functions dedicated to concatenation: CONCATENATE, CONCAT, and TEXTJOIN. These functions can make sense when you oughta concatenate many values at the same time....
In this video we’ll look at a clever way to make this task easier and less error-prone. A common example of a situation that requires concatenation is assembling a mailing address from data in separate columns. This works, but notice that everything just ends up on the same line. To fix this, I need to insert actual line breaks, and this is where theCHAR functionis useful. On Windows, character 10 is a line break and on the Mac, it’s character 13....
Abstract Transcript By default, a newpivot tableincludes Grand Totals for both rows and columns. But you’re able to enable and disable Grand Totals for rows and columns independently. Lets take a look. However, depending on your needs, you may want to turn these on or off. There are two ways to manage Grand Totals. The first way is to use the Design tab of the PivotTools ribbon. you’re able to enable Grand Totals for both rows and columns....
However, it’s possible for you to turn subtotals on or off on a field-by-field basis. Lets take a look. When you first add Row or Column labels to a pivot table, you wont see any subtotals. Notice we see grand totals, but no subtotals. The same is true of Column labels. you could manage the subtotals that appear in the pivot table globally or field by field. you’ve got the option to disable all subtotals at once....
This is best explained with an example. In this worksheet, I have a list of fruit names. Let’s count all the names with more than 5 characters. I’ll first get the length of each name with theLEN function. If I give LEN the entire range, results spill onto the worksheet into a dynamic array. The result is a set of Boolean values. For each name, we get a result of TRUE or FALSE....
Let’s take a look. In this case, We’ll use 1 and 4, because we want four groups. But notice howRANDBETWEENrecalculates whenever we change anything in the worksheet. With each change, we get a new set of random numbers. The simplest way is to replace the formulas in each cell with the values that have already been calculated. Just edit the cell, put the cursor in the formula, then press F9....
Let me show you how this works. So, how can you copy a pivot table without the data? Well, the simplest way is to copy and paste using Paste Special. First, select and copy the entire pivot table. you’re free to use Control + A to snag the whole table. Next, in a new worksheet, use Paste Special, then Values. This will strip away all the formatting and leave you with just the data....
However, there are a couple easy workarounds. One option is to copy an entire worksheet into another workbook. In a second open workbook, there are no custom table styles. Then check “create a copy”. When I click OK, a copy of the sheet is moved into the other workbook. At this point, the custom style has been imported. As you could see in the Table Styles menu. Then I can apply the custom style....
