Posts

Lets take a look. Here we have an empty pivot table using the same source data weve looked at in previous videos. Lets add Product as a Row Label, and Total Sales as a Value. Now lets sort the pivot table by Total Sales in descending order. This will eliminate all of the products below White Chocolate. Value Filters are located in the same drop-down menu as Label Filters. Instead of filtering the label itself, Value Filters work on the values associated with a Label Field....

Lets take a look. By default, a new pivot table will summarize all of the source data. For example, this pivot table shows Total Sales by product. By default, a new report filter shows all items. When we select West, we see only sales for cities in the West region. If we select East, we see sales for Cities in the East. The Select Multiple Items setting, allows you to select more than one item at a time....

Lets take a look. Here is the same pivot table weve looked at previously, showing Sales and Orders by product. Lets add a Value Filter on the Product field that limits products to the top 5 products by Sales. The option we want is called Top 10. Now simply adjust the controls to display the top 5 items for the Sales field. The result is a pivot table with 5 rows only - the 5 top products by Sales....

By default, a pivot table wont allow multiple filters on the same field. Lets take a look. Here we have a pivot table that shows Sales and Orders by product, sorted by Sales. Lets filter the products to show only the top 3 products by Sales. Notice the Value Filter is now active. Now, lets add a Label Filter to show only products that end with the word chocolate. By default, a pivot table does not allow multiple filters on the same field....

When plotting unsummarized data, the chart filter may not be convenient. If I insert a line chart, Excel automatically builds a condensed horizontal axis and plots all the data. And I can easily use the Chart Filters menu to exclude everything but the close price. But what if I only want to show data from 2016 and 2017? The way the entries are listed in the filter, I’ll have to uncheck a lot of checkboxes....

Let’s take a look. Here we have a table that contains 300 property listings. Let’s use Excel’s filter tools to explore this information. First, we need to enable filter mode. Select any cell in the table, then tap the Filter button on the Data tab of the ribbon. you’re free to also use the keyboard shortcut Control-Shift-L to toggle filter mode on and off. Let’s start by filtering to show only properties below $500,000....

Let’s take a look. Here’s the table of names we looked at previously. Let’s add some formatting, then look at how to find this formatting. Let’s fill several cells that contain the nameAnnwith yellow. Let’s also bold two of these cells. Now let’s add the yellow fill to our search criteria. The Format button brings up the Format Cells dialog box, where we can select a yellow fill. When we click OK, the formatting is added to our search criteria....

Let’s take a look. To find a value in Excel, use the Find and Replace dialog box. Let’s try looking for the nameAnn. Nothing happens until we poke the Find Next button. Then, each time we click, Excel finds another match. Note that Excel finds other names that containAnnas wellAnnie, Ann, Danny, and then Hannah. If we continue clicking Find Next, Excel will eventually return to the first match. By default, Excel searches left to right, first through rows, then columns....

Let’s take a look. In this case, there are 19 cells that match the search forAnn. it’s possible for you to resize the window to show more files in the list. If we press any item in this list, Excel will immediately select that cell in the worksheet. We can also step through each item in the list using the up and down arrow keys. The “Find All” results list respects the current search options....

Knowing where formulas are is the first step in understanding how a spreadsheet works. This key is just below the Escape key on US keyboards. This shortcut will cause Excel to display the formulas themselves instead of their results. Using this shortcut, you could quickly and easily switch back and forth. The next way you might find all formulas is to use Go To Special. Go To Special is based on the Go To Dialog....

Abstract Transcript Excel keeps track of the workbooks you’ve opened recently. Let’s take a look. Here you’ll see a list of workbooks, with the most recently opened workbooks at the top. When you pin a file, it stays at the top of the list until you unpin it again. This number represents the files at the top of the list, and includes pinned files. it’s possible for you to adjust this value to whatever makes sense for you....

Find and Replace is a powerful tool when making global changes to worksheets. Let’s take a look. Here we have the worksheet we looked at previously. Let’s run through a few examples to explore how Find and Replace work. First, let’s replace every occurrence ofAnnwithAnna. We need to enterAnnin the “Find what” box, and enterAnnain the “Replace with” box. At this point, we have two options. Let’s replace one cell at a time first....

you might even ignore values and just work with the format itself. Let’s take a look. Before we replace any values, let’s add an orange fill to all cells that containAnn. Now let’s initiate the Find and Replace dialog box and set up our Find options. Let’s look forAnn, and select “Match entire cell contents.” Let’s also add a format to match the orange fill we’ve just applied. Now, let’s set up Replace options....