Posts

Abstract Transcript In this lesson, we’ll look at how to enterdates in Excel. If you don’t supply a year, Excel will use the current year. Let’s take a look. Many other formats are supported as well.

Abstract Transcript In this video, we’ll look at several ways to enter functions. Let’s take a look. Excel will show a drop-down list of possible functions below by matching the characters you’ve already typed. As you pop in additional characters, Excel will shorten the list. To navigate through the list of suggestions manually, use the up and down arrow keys. Note that Excel will also display a brief description of each function to the right....

When the year is 29 or less, Excel will use the twenty-first century. When the year is 30 or greater, Excel will use the twentieth century. Let’s take a look. Here we have a list of birthdays. Our first three birthdays have a year that’s greater than 29. The next three birthdays all have a year that’s less than 30. In this case, we need to use a 4-digit year....

Let’s take a look. First, let’s look at the problem. In the case of the part code, Excel actually interprets the code as a date. The problem is that Excel is treating these values as numbers or dates. We need a way to tell Excel to treat this input as text. There are two ways we can do this. The first way is to use a single apostrophe when entering the values....

Let’s take a look. you might, of course, enter a time with all components. We can then cleanly format these times in any way we like.

Let’s take a look. Excel’s LEFT andRIGHT functions are easy to use when you know how many characters you want to extract. But what if you want to extract the first name from a full name? But “5” isn’t going to work for names like Maureen, Charles, or Ann. FIND is case-sensitive, but it won’t make any difference in this situation. As you’re free to see, this works. Each name contains a trailing space....

Let’s take a look. Here we have some customer data. For the first example, I’ll extract thearea codefrom thephone number. This is a perfect utility of theLEFT function. If I use LEFT without the second argument, it will extract just the first character. To get thearea code, I’ll need to use “3” for the second argument. For that, we can use the RIGHT function. Without the second argument, RIGHT will extract just the first character from the right....

Here again we have a music collection with some missing data. The twist, in this case, is that the Artist isn’t in the right column. As before, we’ll use formulas to fill in the missing data. But this time, we need to take care of the artist rows as a first step. With all the cells in column B selected, enter an equal sign to start off a new formula....

This isn’t a problem for humans because we can see and understand the overall structure of the data. Let’s try it with this data. To begin with, grab the data. Next, select only the empty cells. The key is starting with the right reference. To do that, verify the active cell in the selection is one of the empty cells. The active cell will be the basis for all formulas we enter....

Abstract Transcript In this lesson we’ll introduce the concept of filtering. Filtering is a great way to quickly explore data in a list or table. Let’s take a look. Each arrow provides a drop down menu that contains tools to filter on that column. Let’s set a filter on the color column to show only silver models. First, smack the arrow in the Color column to open filter controls. Click Select All to deselect all colors, then click Silver....

Let’s take a look. Lets create a pivot chart that shows product sales by Region. Since we have a pivot table already, well create a pivot chart first, and then start customizing. Go to the Options tab of the PivotTable Tools ribbon and hit the PivotChart button. For chart styles, well use a simple column chart. At this point, our chart is plotting the full set of source data. Lets filter the pivot chart to show only certain products: Banana Chocolate and Orange Chocolate....

Lets take a look. Here we have a pivot table that shows Total Sales only. Lets add Customer as a row label, and Region as a column label. Both of these fields contain text, so we see a standard set of filter options for labels. This includes things like Equals, Contains, Begins With, and so on. The options are the same for both fields. Now lets remove Region, and add the Date field....

Lets take a look. This pivot table is displaying just one field: Total Sales. After we add Product as a row label, notice that a drop-down arrow appears in the header area. When we open this menu, we see a variety of filter options. The simplest way to filter is to simply include or exclude items by using the checklist that appears below. This is called “Manual Filtering,” and we can select any combination of items that makes sense....