Posts

Filtering to extract data based on matching criteria is a traditionally hard problem in Excel. However, the new FILTER function makes this task much easier. The FILTER function is designed to extract data from a list or table using supplied criteria. In this worksheet, we have data that contains names, scores, and groups. Our goal is to use the FILTER function to filter the data by group. I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “filter”....

The FILTER function is designed to extract data from a list or table using supplied criteria. In this worksheet, we have data that contains an order number, amount, name, and state. Our goal is to use the FILTER function to extract orders from Texas with an amount of at least $100. To explain how this logic works, I’m going to test the criteria first in a helper column, then copy it into the FILTER function....

Here we have data in an Excel Table called “data”. In cell J2, I’ll set up a dropdown list we can use to filter data by color. First, I’ll type “Red” in J2, so we have something to filter on. Next, I’ll enter the FILTER function in cell I5. For array, we want the full table. For the include argument, we use an expression to compare values in the color column to cell J2....

In the example shown, the formula in cell F5 is: where data is a named range for B5:D10 {5;6;7;8;9;10} If you want only the first row number, you can use the MIN function to extract just the first row number, which will be the lowest number in the array. Simple version Entered in a single cell, the ROW function will display only the first row number, even though it returns an array....

The idea is to enter a new weight each day, and calculate the difference from the previous day. When every day has an entry, the formula is straightforward: The difference is calculated with a formula like this, entered in D6, and copied down the table: However, when one or more days are missed, things go awry, and the calculated result doesn’t make sense: No, you did not gain 157 pounds in one day The problem is the formula uses the blank cell in the calculation, which evaluates to zero....

To get the last day of the prior month, use: To get the last day of the next month, use: Alternative formula You can also write a formula using the DATE, YEAR and MONTH functions to return the last day of the month: The trick with this formula is supplying zero for the day. When you supply zero as the day argument to DATE, the date function will “roll back” one day to the last day of the previous month....

For example, if you have dates in the range C4:C10, and want to weekend dates, select the range C4:C10 and create a new conditional formatting rule that uses this formula: Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be C5 in this case. Once you save the rule, you’ll see all dates that are a Saturday or a Sunday highlighted by your rule....

Excel is a great spreadsheet tool, but there are a few things about it that may irritate you sometimes. For example, if you add a plus sign before a number in a cell in Excel, that plus sign would just go away. The same happens when you try and add leading zeros before a number. The reason for this is that Excel considers these as redundant (which is correct). But sometimes, you may need to be able to add a plus sign before a number (especially when you’re showing changes as shown below):...

When working with text data, a common task is to make the data consistent by capitalizing the first letter in each cell (or to capitalize the first letter of each word in all the cells) In this tutorial, I will show you a couple of methods to capitalize the first letter in Excel cells. So let’s get started! Capitalize First Letter Using Formula There can be two scenarios where you want to capitalize:...

After you’ve created a chart, you can select the chart and resize manually using any one of the 8 drag handles. To constrain the chart to the cell grid as you drag, hold down the Alt key. You’ll then see the chart snap to the gridlines as you go. If you know what size chart you want exactly, you can double click the chart area, and enter a height and width manually in the Format Task Pane....

While the dollar sign is right there on the keyboard, cent symbol is a little more difficult to find and insert. How to Insert a Cent Symbol in Excel In this tutorial, I’ll show you three different ways to insert the Cent symbol in Excel: Let’s begin. Using a Keyboard Shortcut The keyboard shortcut to insert the cent symbol in Excel is ALT + 0162. You need to hold the ALT key and then press 0162 on the number keypad of your keyboard....

This is the same example we looked at previously: a table which contains first, middle, and last names. In column E, I’ll add a formula that uses the CONCATENATE function to join these names together into a full name. With the CONCATENATE function, just enter each value you want to join together as a separate argument. If I use B5 for the first argument, and D5 for the second argument, we’ll get the name “SusanBrown” without spaces....

Example Generic Formula What It Does This formula will return a random selection from a given list with each item in the list having an equal probability of being selected. How It Works ROWS(List) will return the number of rows in the List. In our example there are 4 rows in the list. RANDBETWEEN(1,4) will return a random integer number between 1 and 4 where each number has an equal probability of being selected....

Imagine working on an Excel workbook with 15 worksheets, and having to constantly switch between worksheets #3 and #12. There are multiple ways to switch between worksheets in Excel, and in this tutorial, I’m going to show you some really cool and advanced tricks that will help switch between tabs a little easier and faster. Keyboard Shortcut to Switch Between Sheets (Page UP/DOWN) If you need to move between sheets in the same workbook that are not far apart (i....

Can you easily unpivot the data? Yes! I have created a pivot table based on some source sales data. Rows area – I’ve added Customer Name, Customer Country and Sales Channel.Column area – I’ve added the Product sold.Values area – I’ve added the Total. We can see this creates a new set of data where each product has its own column heading. This is pivoted data. This the original source data....

In the example shown, we give MODE the range B4:K4, so the formula is solved like this: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

The formula in this example is purposely more verbose that necessary in order to “show” all possible options and results in a way that is easier to understand and maintain. The trick is to structure the formula with line breaks to show each IF on a separate line along with the “true result” for that IF. The “false result” is the following IF statement. Notice the final false result will “catch” any case that fails all previous tests....

Data The data in this pivot tables comes from the Excel Table in column B. Excel Tables are dynamic and will automatically expand and contract as values are added or removed. This allows the Pivot Table to always show the latest list of unique values (after refresh). Fields The pivot table shown in the example has just one field in the row area, as seen below. Steps Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

In this example, an annuity pays 10,000 per year for the next 25 years, with an interest rate (discount rate) of 7%. The PV function is configured as follows in cell C9: The inputs to PV are as follows: rate - the value from cell C7, 7%. nper - the value from cell C8, 25. pmt - the value from cell C6, 100000. fv - 0. type - 0, payment at end of period (regular annuity)....

which returns 10 random values from the named range names (B5:B104). However, the trick in this case is that we don’t want a single name at a known location, we want 10 random names at unknown locations between 1 and 100. This is an excellent use case for the RANDARRAY function, which can create a random set of integers in a given range. Working from the inside out, we use RANDARRAY to get 10 random numbers between 1 and 100 like this: The COUNTA function is used to get a dynamic count of names in the list, but we could replace COUNTA with a hardcoded 100 in this case with the same result: In either case, RANDARRAY will return 10 numbers in an array that looks like this: Note: these numbers are random only and do not map directly to the example shown....