One kind of automatic grouping works on numeric data. Lets take a look. This pivot table shows total sales and orders for the sales data weve looked at previously. One field we havent looked at yet is Quantity. Lets break down total sales by order quantity. To start off, lets add quantity to the pivot table. When we first add the quantity field, we get a detailed breakdown by quantity....
Lets take a look. This pivot table shows a breakdown of sales and orders by product. Lets use manual grouping to organize these products into 2 custom groups. With these cells selected, click Group Selection from the Options tab on the PivotTable Tools Ribbon. Excel will put the selected items into their own group and each remaining item into other groups. it’s possible for you to repeat this process to make as many groups as you like....
Let’s take a look. Sometimes you better group values into discreet categories that don’t exist in your data. Well, you could usenested IFstatements as we’ve covered in another video, and this works fine. To do this, we need to first build the helper table. Then we need a complete list of departments. This is a great-use case for Excel’s built-in “remove duplicates” feature. Just copy the values to another worksheet, and use Remove Duplicates on the Data tab of the ribbon....
Hiding rows or columns is a good way to visually simplify a worksheet without removing important information. Let’s take a look. One way to hide a column is to choose Hide Columns from the Format menu on the home ribbon. To unhide a column, you oughta select columns on both sides of the hidden column. Then, choose Unhide Columns from the Format menu. you could also hide and unhide columns using the right-click menu....
Let’s take a look. As you build more complicated workbooks, you might want to hide certain worksheets to keep things simple. The easiest way to hide a worksheet is to right-poke the worksheet tab and choose Hide from the menu. The worksheet will disappear, and the tab will no longer be visible at the bottom. The hidden worksheet won’t appear in the pop-up menu either. To unhide a worksheet, right-punch a visible tab, then choose Unhide from the menu....
Let’s take a look. Here we have a table that contains three test scores for a group of students. Let’s use Conditional Formatting to quickly highlight values that are above or below average. There are two presets in the Top and bottom category under Conditional Formatting: Above average and Below average. Let’s choose Above average, and use the green preset for the format. Excel now highlights all test scores that are above average....
Here we have a simple lookup table that shows material costs for various heights and widths. Note that the lookup is based on an approximate match. Let’s build a conditional formatting rule to highlight the matched row and column. I’ll set up the formula for width first. We need to return TRUE for every cell in row 7, where the matched width is 200. This means we start our formula with $B5=, and we need to lock the column....
In this lesson we’ll look at one way to approach this problem. Let’s take a look. That is, rows that contain all of the same values. Let’s call this column “Key,” as in “unique key.” Now let’s add the formula. This is called “concatenation.” Now that we have this value, we can apply the conditional formatting preset for duplicates. This time, only true row duplicates are highlighted. When you’re done with the key, simply remove the column from the table....
This effect is easy to create with conditional formatting. To start off, I’ll delete the existing conditional formatting rules and then rebuild them step-by-step. Now, to make things easier to explain, I’ll create a set of dummy formulas for testing. Let’s do the country first. K5 needs to be an absolute reference so that it won’t ever change. B4 needs to be a mixed reference, with only the column locked....
Here we have an example we looked at previously. But what if we want to highlight rows based on both priority and owner? In that case, we’ll need to extend the formula to handle 2 conditions. First, I’ll add an input cell for priority. I’ll call the first input “priority” and the second input “owner”. Now we can use a formula that evaluates two conditions inside the AND function. When I update the rule, I can now highlight both a priority and an owner....
However, it’s a little trickier to highlight entire rows in a list that contains multiple columns. Let’s take a look. However this rule only highlights individual cells, and it also catches any cells that just mention Bob. First, select all of the data in the list. Then, choose New Rule from the conditional format menu on the Home tab of the ribbon. For style, choose “Classic”. Then select “Use a formula to determine which cells to format”....
That way, you’re free to change the text whenever you like without editing the rule.
Scanning for high and low values in a large set of data is tedious and error-prone. With conditional formatting, you could highlight these values in an instant. Let’s take a look. Here we have a table that contains 12 months of sales data for a team of salespeople. Let’s use Conditional Formatting to quickly highlight the highest and lowest figures. To do this, we need to first select all of the data....
