Posts

Transcript In this video, we’ll look at how filters work in an Excel Table. When you first create an Excel Table, you’ll see filter buttons automatically added to the top of each column. Each filter is customized to match the contents of the column. For example, when I click the filter for Color, Excel displays a checkbox for each color that appears in the data. This list is dynamic. If I change Purple to Yellow in a row, Yellow appears automatically in the filter menu....

Ever use Excel’s MOD function ? The MOD function performs the modulo 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. Modulo? Seriously? 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. In fact, if you use MOD in one of your formulas, people will automatically assume you’re an Excel pro, so take care :)...

Transcript In this lesson, we’ll look at how to use the Quick Access Toolbar. The Quick Access Toolbar is a small toolbar that makes it easy to add your own commands to the Excel interface, so they are always immediately available. Let’s take a look. By default, the Quick Access Toolbar sits above the ribbon at the upper left of your screen. It displays a small set of icons that represent common, useful commands that you’d like to keep handy....

Explanation This formula uses the MATCH function in approximate match mode to locate the last text value in a range. Approximate match enabled by setting by the 3rd argument in MATCH to 1, or omitting this argument, which defaults to 1. The lookup value is a so-called “big text” (sometimes abbreviated “bigtext”) which is intentionally a value “bigger” than any value that will appear in the range. When working with text, which sorts alphabetically, this means a text value that will always appear at the end of the alphabetic sort order....

About This Shortcut If the active cell is empty, Excel will stop on the first non-empty cell to the right. If the active cell is non-empty, Excel will stop on the last non-empty cell to the right. On a Mac, the control key (⌃) can be used instead of the command key (⌘). About This Shortcut If the active cell is empty, Excel will stop on the first non-empty cell to the left....

Purpose Return value Syntax =PRICEMAT(sd,md,id,rate,yld,[basis]) sd - Settlement date of the security. md - Maturity date of the security. id - Issue date of the security. rate - Security interest rate at date of issue. yld - Annual yield of the security. basis - [optional] Day count basis (see below, default =0). Using the PRICEMAT function The Excel PRICEMAT function returns the price per $100 face value of a security that pays interest at maturity ....

Explanation The ROUNDDOWN function rounds a number down to a given number of places. The number of places is controlled by the number of digits provided in the second argument ( num_digits ). For example, these formulas round the number 5.89 down to 1 and zero places: =ROUNDDOWN(5.89,1) // returns 5.8 =ROUNDDOWN(5.86,0) // returns 5 In the example shown, the formula in cell D7 is =ROUNDDOWN(B7,C7) This tells Excel to take the value in B7 (1999....

Transcript In this video, we’ll look at how to use conditional formatting to shade alternating groups of rows. For example, you can use this approach to shade groups of 3 rows, groups of 4 rows, and so on. This can be a nice way to make certain tables easier to read. Here we have a table with 3 rows of data for each client, for the months April, May, and June....

Transcript In this video, we’ll cover shortcuts for extending selections to include more cells. The most basic way to extend a selection is to use the shift key + any arrow key. From a single cell, this lets you add additional cells in any direction. If you begin with a larger selection, shift will let you extend the edges of that selection. To change which edges of a selection you can move, you can reposition the active to a different cell to a different corner using control + period....

Explanation In this example, the goal is to sum by group, where each group is represented by a different color: Blue, Red, Green, and Purple. The worksheet shown contains two different approaches. In the range F5:G8, we have created a summary table to summarize counts by color. In column D, we are using a modified formula that reports a total per group the first time the group appears in the data....

Explanation The goal is to sum numbers that appear inside a single cell as seen in column B. Technically, the numbers in each cell are a single text string, and the numbers are separated by commas, which is referred to as a “delimiter”. In the current version of Excel, the easiest way to solve this problem is with the TEXTSPLIT function. It is possible to sum numbers separated by other delimiters as well....

Purpose Return value Syntax =SWITCH(expression,val1/result1,[val2/result2],...,[default]) expression - The value or expression to match against. val1/result1 - The first value and result pair. val2/result2 - [optional] The second value and result pair. default - [optional] The default value to use when no match is found. Using the SWITCH function The SWITCH function compares one value against a list of values and returns a result that corresponds to the first match found. You can use the SWITCH function when you want to perform a “self-contained” exact match lookup with several possible results....

Purpose Return value Syntax =TBILLEQ(settlement,maturity,discount) settlement - Settlement date of the security. maturity - Maturity date of the security. discount - Discount rate of the security. Using the TBILLEQ function The Excel TBILLEQ function returns the bond-equivalent yield for a Treasury bill, based on a settlement date, a maturity date, and a discount rate. In the example shown, the settlement date is 5-Feb-2019, the maturity date is 1-Feb-2020, and the discount rate is 2....

Welcome to Exceljet! Your use of the content on our site comes with certain terms and conditions. If you continue to browse and use this website, you are agreeing to comply with and be bound by the following terms and conditions of use. If you disagree with any part of these terms and conditions, please do not use our website. Copyright This website contains material created and owned by us. This material includes, but is not limited to, articles, pages, videos, screenshots, PDFs, and images....

Purpose Return value Syntax =TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with]) text - The text string to split. col_delimiter - The character(s) to delimit columns. row_delimiter - [optional] The character(s) to delimit rows. ignore_empty - [optional] Ignore empty values. TRUE = ignore, FALSE = preserve. Default is FALSE. match_mode - [optional] Case-sensitivity. 0 = enabled, 1 = disabled. Default is 0. pad_with - [optional] Value to pad missing values in 2d arrays. Using the TEXTSPLIT function The TEXTSPLIT function splits a text string with a given delimiter into multiple values....

Transcript In this video, we’ll introduce Pivot Charts. Pivot charts let you rapidly analyze large amounts of unsummarized data in different ways. Unlike normal charts, Pivot charts can be used to plot data with hundreds or thousands of rows. For example, on this worksheet, I have order data from a wholesale chocolate company over a period of 2 years, in almost 2000 rows of data. To introduce the idea of a pivot chart, I’ll first create a normal chart....

Explanation In this example, the goal is to look up a price using XLOOKUP with multiple criteria. To be more specific, we want to look up a price based on Item, Size, and Color. At a glance, this seems like a difficult problem because XLOOKUP only has one value for lookup_value and lookup_array . How can we configure XLOOKUP to consider values in multiple columns? The trick is to construct the lookup array we need using Boolean logic, then configure XLOOKUP to look for the number 1....

Explanation In this example, the goal is to add minutes in decimal format (i.e., 1, 5, 10, etc.) to an existing Excel time. The complication is that Excel stores time as fractional values. The number 0.0104167 makes sense when you consider that 15 minutes is 1/96th of a day, and a day in Excel equals 1. But it isn’t the way most people think about time. To add decimal minutes to a time, we need to first convert the minutes to an equivalent fractional value....

About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a border added or removed from the border preview area. On the Mac, this shortcut works directly on the worksheet, and adds a border to each cell in the selection. About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab....

Explanation This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock. In the example, colors are treated as unique item identifiers – imagine a product available in one size only in just three colors: red, blue, or green. The key to this approach is to use Excel Tables , because Table ranges automatically expand to handle changes in data....