Posts

In the example shown, the formula in C5 is: Working from the inside out, we use the SUBSTITUTE function to find all line breaks (char 10) in the text, and replace each one with 200 spaces: After the substitution, the looks like this (with hyphens marking spaces for readability): With 200 spaces between each line of text. Next, the RIGHT function extracts 200 characters, starting from the right. The result will look like this: Finally, the TRIM function removes all leading spaces, and returns the last line....

In the example, the formula cell B4 of the helper column looks like this: This formula picks up the value in D4 and uses concatenation to add a hyphen, and the result of a COUNTIF function. The COUNTIF function uses an expanding range (the mixed reference $D$4:D4) to generate a running count of the id in the data. On the lookup side, VLOOKUP is used to fetch values form the table, taking into account the “nth” occurrence....

The result is a decimal value with percentage number format applied. However, since the price change is not in the table as a separate column, we need to add a step: Applying this approach to the worksheet as shown, the formula in cell E5, copied down, is: For each item in the table, Excel returns a calculated percentage. Formatting percentages in Excel In mathematics, a percentage is a number expressed as a fraction of 100....

Given a date, what formula will highlight other dates in the same month and year? What formula would you use to apply conditional formatting? Please post your answers in the comments section below. Like so many things in Excel, there are many ways to solve this problem. Need some ideas or inspiration? You can find Excel’s date functions here. There’s a practice file attached below so you can easily try out your ideas....

If you want to highlight duplicate rows in an unsorted set of data, and you don’t want to add a helper column, you can use a formula that uses the COUNTIFS function to count duplicated values in each column of the data. For example, if you have values in the cells B4:D11, and want to highlight entire duplicate rows, you can use rather ugly formula: Named ranges for a cleaner syntax The reason the above formula is so ugly is that we need to fully lock each column range, then used a mixed reference to test each cell in each column....

Let’s take a look. Here we have a simple table meant to summarize the features of a hypothetical service plan. One thing you might want to do with a table like this is combine cells with like values. For example, Feature Y is not offered in Plans A, B, or C, and feature Z is not applicable to Plan A or B. So, we could combine these cells to visually simplify the layout....

Table styles control the look and feel of an Excel Table. Tables styles allow you to format an entire table with a single click, and the style is applied continuously to a table as new rows and columns are added. You can find table styles listed on the Design tab of the Table Tools ribbon, whenever you have at least one cell in a table selected. You’ll see a large number of pre-built styles organized in several categories....

So, the first step is going to be to calculate the number of characters in the cell. We’ll do that with the Length or LEN function. And then, we need to figure out the number of characters without spaces. To get the number of characters without spaces we need to strip the spaces out. I’ll use SUBSTITUTE, and we’ll point at this text here. We’ll look for spaces and replace that with “nothing....

Let’s take a look. Near the bottom of the border menu on the home tab of the ribbon is a group of commands for drawing borders. Excel has two basic modes for drawing borders: Draw Border and Draw Border Grid, and another mode for erasing borders called Erase Border. To draw a border, first select a color and a line style. Notice that when we select either of these options, Excel shifts into draw border mode; the Draw Border menu item is selected, and the cursor changes into a pencil....

Let’s take a look. Here we have some customer data. To illustrate how to extract text using the LEFT and RIGHT functions, I’ll use them both to pull out just certain parts of this information. For the first example, I’ll extract the area code from the phone number. This is a perfect application of the LEFT function. LEFT takes two arguments: the text you’re working with, and the number of characters to extract, which is optional....

Example #1 - Number too big or small Excel has limits on the smallest and largest numbers you can use. If you try to work with numbers outside this range, you will receive the #NUM error. For example, raising 5 to the power of 500 is outside the allowed range: Example #2 - Impossible calculation The #NUM! error also can appear when a calculation can’t be performed. For example, the screen below shows how to use the SQRT function to calculate the square root of a number....

Let’s take a look. Here we have a table that contains 12 months of sales data for a team of salespeople. Let’s use Conditional Formatting to quickly highlight the highest and lowest figures. To do this, we need to first select all of the data. Then we’ll use presets in the Top and bottom rules category of the Conditional Formatting menu. The presets in the Top and bottom rules category apply formatting based on the current selection....

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. The easiest way to indent is to use the Indent buttons in the Alignment group on the home tab of the ribbon. 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....

Let’s take a look. Here we have an Excel table that contains almost 3000 rows. This is sales data, and each row represents one order, for one kind of chocolate, to one customer. Using this data, let’s build a pivot table and see what happens if we remove the source data. The moment a blank pivot table is created, Excel generates the pivot cache. As we add fields to the pivot table, we are actually working with the pivot cache....

Let’s take a look. As you work in Excel, one of the first questions you need to answer is “where am I now?” You can always tell what workbook you’re in by looking at the name in the window title bar. In this case, we can see that we’re in a workbook called Forecast 2013. All workbooks have at least one worksheet, and worksheets are listed as tabs at the bottom of the workbook....

Let’s take a look. When you use Find, there are three options for the “look in” setting on the Find tab: formulas, values, and comments. Comments are self-explanatory, so let’s focus on formulas and values. You can think of formulas as whatever is stored in the cell—the underlying formula—and values as whatever is displayed in the cell. The naming is a little confusing, so let’s take a look at some examples....

Note: this is an array formula, and must be entered with control + shift + enter. The formulas in F7 and F8 return the same result, but have different approaches. In cell F7, we have the following formula, using a nested IF approach: This is how Excel evaluates the IFs inside SUM: In essence, each IF “filters” values into the next IF, and only quantities where all three logical tests return TRUE “survive” the operation....

In the example shown, the formula in cell D6 is: When this formula is copied down column D, it multiplies the value in B6 by a number that starts with 1 and increments by one at each step. We want to start with 1, however, so we need to subtract 5, which yields 1. As the formula is copied down column D, ROW() keeps returning the current row number, and we keep subtracting 5 to “normalize” the result back to a 1-based scale: If you are copying a formula across columns, you can use COLUMN() function the same way....

Which returns the sum of the named range “data” on Sheet1. Once the string is assembled using values in B6 and C6, INDIRECT evaluates and transforms the string into a proper reference. Note you can refer to a named range in a formula without using INDIRECT. For example, the formula in D6 could be written: However, if you want to assemble the reference as text, and have Excel treat the text as a reference, you need to use INDIRECT....

where project (B5:B16) and data (C5:F16) are named ranges. With a lookup value of “Neptune” in cell H5, the result is all four quarterly values for the Neptune project, which spill into the range I5:L5. Note: this problem can also be solved with INDEX and MATCH, as described below. Although this example shows off the simplicity of the XLOOKUP function, it can also be solved with a straightforward INDEX and MATCH formula, as described below....