Posts

Abstract Transcript Excel provides a simple way to horizontally indent cell content. Indents work in steps, allowing you to use more than one level of indentation. Let’s take a look. Here we have a simple budget worksheet. Let’s apply some indentation to make the categories a little more readable. One button increases the indent by one step, and the other button decreases the indent by one step. Once you reach zero, clicking decrease indent has no further effect....

When you insert cells, you have the option to shift the cells down or to the right. When you delete cells, you have the option to shift cells up or to the left. Let’s take a look. Then, hit the Insert menu on the ribbon and choose “Insert Cells”. you’ve got the option to also use the right-click menu to insert cells. To delete a cell horizontally, first pick the cell you want to delete....

You may want to insert columns to make room for new information. You may want to delete columns to remove information you no longer need or want. No matter how many columns you add or delete, the number of columns in the worksheet never changes. When you insert columns, columns are pushed off the worksheet at the far right edge. When you delete columns, new columns are added to the far right edge....

It’s common to insert rows to make room for more information. Deleting rows is an easy way to remove information you no longer want or need. No matter how many rows you add or delete, the number of rows in the worksheet never changes. When you insert rows, rows are pushed off the worksheet at the bottom. When you delete rows, new rows are added to the bottom. Let’s take a look....

Let’s take a look. No matter which tab is selected, the new worksheet goes to the end of the list. Note that there is a keyboard shortcut for adding a new worksheet: Shift F11. This command always inserts new worksheets to the left of the currently selected worksheet. you’re free to add as many worksheets to a workbook as you like. The number of worksheets is limited only by available memory....

Let’s take a look. There are several different ways to insert symbols and special characters. One way, that’s not obvious, is to use AutoCorrect. AutoCorrect is a feature built into Excel that automatically substitutes one bit of text for another. Let’s try it. We can do the same thing for trademark. This launches the Symbol window which contains two tabs. The Special Characters tab is a rather short list of commonly used characters....

With the CONCATENATE function, just enter each value you want to join together as a separate argument. As usual, all “literal text” needs to be surrounded in double quotes. you’re free to use this trick to quickly add all the arguments you need. We’ll use the same approach to fix that problem. By usingIFwithISBLANK, we can test for a middle initial. As you might see, the CONCATENATE function and the ampersand are both valid options for joining together values....

Let’s take a look. TheTEXT functionhas just one purposeto convert a number into a text value using a specific number format. In the first table, we have some random numbers in the left column. I’ll add a formula in column C to show just the raw values in the General number format. In column E, there is already another formula that points back to column B. you’ve got the option to see this clearly if I temporarily switch to the General number format....

Abstract Transcript Often, you’ll need to join together values in Excel. This is referred to as “concatenation.” In this example, we have first, middle, and last names shown separately in a table. I’ll use simple concatenation to join these separate names together into a single name. The easiest way to concatenate is to use the operator for concatenation: the ampersand character. For example, I can join Susan and Brown using the formula =B5&D5....

This is part 3 of a 3-part series on Excel formulas. you’re able to find Parts 1 and 2 here:

Abstract Transcript What does theINDEX functiondo? Let’s take a look. INDEXis a powerful and flexible function that can be used for advanced features like dynamic ranges and two-way lookups. In its simplest form, INDEX takes just two arguments: an array and a row number. INDEX correctly retrieves Chicago from the list of cities. If I put in “13” we’ll get Austin, the 13th city in the list. Now let’s expand on this to use INDEX with multiple columns....

Abstract Transcript In this video we’re going to combineINDEXandMATCHtogether to look things up. Here we have the city population data we looked at before. We used theINDEX functionto retrieve information about a city with a hard-coded position value. When we supply a number, INDEX retrieves information for the city at that position in the list. Now instead of fetching information by position, let’s convert this worksheet to retrieve information by city name....

Depending on your needs, FILTER might be a better option than first or last match. Read on for the answer and more interesting examples. It doesn’t matter if data is sorted or not. In the screen below, the lookup value in E5 is “red”. In the screen below, VLOOKUP is set to approximate match mode, and colors are sorted. With unsorted data, you may see normal-looking results that are totally incorrect....