Abstract Transcript In this lesson we’ll look at the Text format. It may seem strange to format numbers as text, but there are times when it makes sense. The Text format displays numbers exactly as they were entered. Let’s take a look. One solution to this problem is to first format cells in Text format. Note that Excel aligns values to the left. This indicates that Excel is treating these values as text....
Abstract Transcript In this lesson we’ll look at how to use text orientation. Orientation allows you to rotate text in a variety of ways. Let’s take a look. Here we have a simple feature summary table. We can use orientation to spice things up a bit. Before we do that, however, let’s look at what types of orientation are available. Orientation options are in a menu in the Alignment group on the home tab of the ribbon....
Here we have a list of 16 properties with prices and other information. It takes three arguments:range, criteria, and average_range. Note thataverage_rangeis optional and represents the actual range to average. If you’re not usingaverage_range, therangeargument will be used instead. First, I’m going to name some ranges to make the formulas easier to read and copy. Now we can easily refer to “prices,” “size,” and “status.” To start off, let’s calculate a straight average with theAVERAGE function, just for reference....
Here we have a list of 16 properties with prices and other information. TheAVERAGEIFS functioncan calculate an average for cells that meet multiple criteria. The first argument isaverage_range;this is the range that contains the values to average. The criteria is supplied in pairs: first therange, and then thecriteria. To start off, let’s calculate a straight average with theAVERAGE functionfor reference. LikeAVERAGEandAVERAGEIF, theAVERAGEIFS functionwill automatically exclude blank values, but zero values are included....
Let’s look at three examples. In this first example we have some items listed with a numeric color code. When I copy the formula down, we have our color names. This works fine, but we can do the same thing with CHOOSE without a table. WithCHOOSE, the first argument is an index, and the remaining arguments are choices. We get the same result asVLOOKUP, and we don’t need this table at all....
Abstract Transcript TheCOUNT functionis a simple function that counts numbers. Let’s take a look. The COUNT function counts numeric values. The COUNT function ignores blank cells and text values. A good way to check the actual value in a cell is to temporarily apply the General format. you could then easily see which values are numbers. Negative numbers are counted, too. Note that if you want to count text values, you’ll want to use theCOUNTA function....
Let’s take a look. The COUNTIF function counts cells that satisfy a single condition that you supply. It takes two arguments: range and criteria. Excel then returns “1,” since only one cell contains “15”. If I temporarily enter another “15” that result will change. you might add logical operators to the criteria. When the criteria contains a logical operator, you’ll need to enclose it in double quotes. you’re free to use COUNTIF with both text and numbers....
Let’s take a look. In this first set of tables, we have twonamed ranges, “number” and “color.” The first item in the pair is the range, and the second item is the criteria. Note that all ranges that you use must always be the same size. For the second example, we can see two pairs in action. The “number” range appears twice, each time with a different condition. The result is “1”....
The fill handle allows you to fill cells with data that originates from one or more source cells. Let’s take a look. The fill handle is a special box that appears in the lower right-hand corner of a selection. The fill handle can fill cells in four directions: right and left; up, down. It also works when more than one cell is selected. The fill handle will also recognize and repeat certain patterns....
And although formatting tools in Excel are easy to use, applying formatting manually can quickly get tedious. One tool you’re able to use to speed things up is the Format Painter. The Format Painter copies formatting from one group of cells to another. To the right, I have another table with the same kind of data, and no formatting. The question is, how can I quickly format the second table to match the first?...
Abstract Transcript TheMATCH functionfinds the relative position of an item in a list. MATCH can find exact matches or approximate matches. In this video, we’ll look at how to use MATCH to find an exact match. In column E, I have a list of numbers from 1 to 9. I’ve already created anamed rangecalled “numbers” that refers to this list. In cell C7, I’ll enter the MATCH formula. For lookup_value, I’ll use C6, so we can easily change it later....
Ever use Excel’sMOD function? The MOD function performs themodulo operation. It takes a number and a divisor, does the division, and gives you back the remainder. Unless you’re a programmer, this might seem way too nerdy. What can the average person do with that? As it turns out a lot! Note: MOD turns up in many compact, elegant formulas in Excel. You want something like this: Simple, right? But how to begin…what kind of formula does something every 3 months?...
Let’s take a look. By default, the Quick Access Toolbar sits above the ribbon at the upper left of your screen. To customize the Quick Access Toolbar, poke the small arrow to the right of the toolbar. In this menu, you’ll see the currently enabled icons appear with a checkmark. In this case we see Save, Undo, and Redo. To add a command from this menu to the toolbar, just select it in the list....
