Abstract Transcript One of the more confusing aspects ofPivot Tablesis layout options. When you create a pivot table, you have your choice of three layouts. In this video, we’ll look at each layout. By default, each new pivot table you create will use the Compact layout. But you’ve got the option to easily switch to Outline layout, or to Tabular layout. Let’s look at each layout more closely. First, the Compact layout....
Abstract Transcript Pivot table slicers come with a variety of options and options. Lets take a look. There are several ways to get to slicer options. First, you could hit the Option tab of the Slicer Tools ribbon. The Slicer Tools ribbon will appear whenever you select a slicer. you could set the heading or caption for a slicer directly on the ribbon itself. If you want to hide captions altogether, opt for Slicer controls button and uncheck display header....
Lets take a look. Slicer styles are available on the Slicer Tools ribbon. They control the color and style used to display slicers. To apply a slicer style, go for the slicer and then click one of the thumbnail images. To illustrate, lets apply the existing dark orange style. Note that this style uses a yellow gradient with black text to highlight hovered items. Lets make our own version of this style that displays hovered buttons in green with white text....
Let’s take a look. This is a perfect tool for relative cell references. To start off, we’ll create our first formula in cell E6. We want to multiply the quantity times the unit cost, so our formula is = C6 * D6. Note that C6 and D6 are relative references which is Excel’s default behavior. For our first item, we get a total of $25.00 since 2 x 12.50 = 25....
These kinds of totals often use formulas that are identical except for the cell references. Let’s take a look. Here we have a table that shows the number of widgets sold over a 12-month period. There are six different kinds of widgets, so the table has six rows of data. First, let’s add a total for each month on row 13. This is a job for theSUM function. Now let’s do the same thing to get a 12-month total for each widget pop in....
Abstract Transcript In this lesson we’ll look at the Scientific format. The Scientific format is used to display numbers in scientific or exponential notation. Let’s take a look. In column C of our table we have a set of very large and small numbers in Number format. Let’s first copy these numbers to the rest of our table. As we can see, they don’t fit very well. In Scientific format, all values fit easily....
Abstract Transcript One of the options grouped under Alignment is Shrink to fit. Shrink to fit will automatically reduce the font size until text fits in a cell. Let’s take a look. To illustrate how Shrink to fit works, let’s look at a typical layout problem. Here we have a basic feature table that needs some cleanup. Let’s start by applying a horizontal and vertical alignment of center for all cells....
Let’s take a look. Here we have a small list of numbers that need special formatting. These numbers can be entered manually using approaches we’ve looked at previously. Special formats aren’t listed in the number formats menu on the ribbon. To access, select More Number Formats. Excel generates the required format on the fly. We can enter the phone number without spaces or parentheses. The same is true with the social security numberwe only need to enter the number....
To subtract things in Excel, use the “-” sign. Let’s take a look. The first two examples don’t require cell references and can be input directly. As expected, we get thirteen as the answer. In the last example we have H8 minus H11. Notice that we get 27, since subtracting a negative number is the same as adding it. No special formatting is required. Like addition, you could receive a VALUE error if any of the cells you are adding contain text....
On this worksheet, I have two identical sets of order data. With the data on the left, I’ll use standardformulasand no table. On the right, I’ll create an Excel Table, and build formulas that usestructured references. Now I’ll use theSUMIFS functionto sum sales by item. Thecriteria_rangecomes from column D, and also needs to be locked. The criteria itself comes from the adjacent column. When I copy the formula down, we get the correct total for each item....
Each filter is customized to match the contents of the column. This list is dynamic. If I change Purple to Yellow in a row, Yellow appears automatically in the filter menu. I’ll undo that change. To filter on a single value, click Select All, then check the value. Notice a filtered column will display a different icon than an unfiltered column. To clear a filter, use the Clear option in the menu....
Table slicers have a variety of controls. Options appear grouped in 5 main categories 1. Commands to arrange and align slicers4. Controls for slicer buttons, and5. Inputs to set slicer sizeThe Slicer Tools ribbon will appear whenever you select a slicer. The slicer tweaks button opens a window with more tweaks. you’re able to access these same configs by right-clicking on a slicer. you’re able to set the heading or caption for a slicer directly on the ribbon itself....
You’ll find slicer styles on the Slicer Tools ribbon, which appears whenever a slicer is selected. Just click a thumbnail image to apply a style. Notice each style has a name that appears when you hover with the cursor for a few seconds. The currently applied style is highlighted with a thick border. Let’s walk through that process. I’ll first apply the existing dark orange style. Notice this style uses a yellow gradient with black text to highlight hovered items....
