Transcript Hey, Dave here from Exceljet. So, in these last couple of videos, I’ve given you a short introduction to the power of pivot tables. In the first video, I talked about how fast they are. And how you can, using pivot tables, build reports way faster than even an advanced user building the same reports with formulas. In the second video, we looked at how good pivot tables are at answering questions about data, and how you can use pivot tables a little bit like a food processor to slice and dice data any way you want to answer questions and reveal key information....

Explanation In this example, the goal is to categorize various expenses using the categories shown in column F and the keywords shown in column E. This is a case where it seems like we should perform a lookup operation of some kind, but the problem is that the keywords appear embedded in the text and the structure is unpredictable. The article below explains two ways to solve this problem. The first approach, based on the XLOOKUP function is the simplest....

Purpose Return value Syntax =CHAR(number) number - A number between 1 and 255. Using the CHAR function The CHAR function returns a character when given a valid character code. Use the CHAR to translate ASCII code page numbers into actual characters. For example: =CHAR(65) // returns "A" =CHAR(97) // returns "a" the CHAR function takes just one argument , number , which must be an integer between 0-255. The result from CHAR is a text value ....

Explanation The coefficient of variation measures the relative variability of data with respect to the mean. It represents a ratio of the standard deviation to the mean and can be a useful way to compare data series when means are different. It is sometimes called relative standard deviation (RSD). In this contrived example, the standard deviation is calculated in column H with the STDEV.P function: =STDEV.P(B5:F5) Notice that the standard deviation is the same for all data series (1....

Explanation This formula builds the final result in 2 parts, joined by concatenation with the ampersand (&) operator. On the left of the ampersand, we generate the year value. To extract a 2-digit year, we can use the TEXT function, which can apply a number format inside a formula: TEXT(B5,"yy") To extract a full year, use the YEAR function: YEAR(B5) On the right side of the ampersand we need to figure out the day of year....

Explanation The goal of this example is to count rows in the data where the date joined falls between start and end dates (inclusive) and the age also falls into the age ranges seen in column G. The formula is complicated somewhat by the fact that the age range labels are actually text, so we need to extract a low and high number for each age range as a separate step....

Explanation In this example, the goal is to count the number of cells in column D that are not equal to a given color. The simplest way to do this is with the COUNTIF function , as explained below. Not equal to In Excel, the operator for not equal to is “<>”. For example: =A1<>10 // A1 is not equal to 10 =A1<>"apple" // A1 is not equal to "apple" COUNTIF function The COUNTIF function counts the number of cells in a range that meet the supplied criteria:...

Explanation In this example the named range “range1” refers to cells B5:B8, and the named range “range2” refers to D5:D7. We are using named ranges for convenience and readability only; the formula works fine with regular cell references as well. The core of this formula is INDEX and MATCH. The INDEX function retrieves a value from range2 that represents the first value in range2 that is found in range1. The INDEX function requires an index (row number) and we generate this value using the MATCH function, which is set to match the value TRUE in this portion of the formula:...

Explanation The TRANSPOSE function is fully automatic and can transpose cells vertical to horizontal, and vice versa. The only requirement is that there be a one to one relationship between source and target cells. In the example shown, we are transposing a table that is 2 columns by 7 rows (14 cells), to a table that is 7 columns by 2 rows (14 cells). Note that this function creates a dynamic link between the source and target....

Purpose Return value Syntax =FORECAST.LINEAR(x,known_ys,known_xs) x - The x value data point to use to calculate a prediction. known_ys - The dependent array or range of data (y values). known_xs - The independent array or range of data (x values). Using the FORECAST.LINEAR function The FORECAST.LINEAR function predicts a value based on existing values along a linear trend. FORECAST.LINEAR calculates future value predictions using linear regression, and can be used to predict numeric values like sales, inventory, test scores, expenses, measurements, etc....

Explanation In this example, the goal is to group ages into buckets. One way to do this is to prepare a table with age breakpoints in the first column, and the name of the appropriate group or bucket in the second column. Then use a lookup function to find the right bucket or group for each age. In the example shown, we are using the VLOOKUP function for this job, and the formula in cell F7, copied down, is:...

Purpose Return value Syntax =HLOOKUP(lookup_value,table_array,row_index,[range_lookup]) lookup_value - The value to look up. table_array - The table from which to retrieve data. row_index - The row number from which to retrieve data. range_lookup - [optional] A Boolean to indicate exact match or approximate match. Default = TRUE = approximate match. Using the HLOOKUP function The HLOOKUP function can locate and retrieve a value from data in a horizontal table . Like the “V” in VLOOKUP which stands for “vertical”, the “H” in HLOOKUP stands for “horizontal”....

Transcript In this video, we’ll look at how to create a basic column chart in Excel. We’ll also look at custom number formats to show dates as month names, and currency in thousands. Here we have data for monthly sales of a small business for the year 2016. Let’s build a column chart to plot this data. To start off, put the cursor anywhere in the data, then visit the Insert tab on the ribbon....

Transcript The moment you add numbers to your pivot table , you’ll want to adjust the formatting. You could just select the numbers in the pivot table itself, and format normally, but there’s a better way. Let’s take a look. There are two ways you can format values in a pivot table. The first way is to select cells directly in the pivot table and apply a format manually. For example, we can select the sales values in this pivot table and apply a currency format with no decimal places....

Transcript In this video we’ll look at how to convert numbers to text using a function called TEXT. This is useful when you want to convert a number to text, or when you want to join a number with text using a specific number format. Let’s take a look. The TEXT function has just one purpose—to convert a number into a text value using a specific number format. The function takes two arguments: the value or number, and something called “format_text” which is the number formatting you want to use when the number is converted....

Core Formula is now live ! Some thoughts on how to quickly learn Excel formulas and functions, and a sample lesson from our course on Excel formulas, Core Formula . This is part 3 of a 3-part series on Excel formulas. You can find Parts 1 and 2 here: Video 1: 5 ways to use VLOOKUP Video 2: 23 tips to work faster with formulas today Video 3: How to learn Excel formulas and functions <- this video

Transcript In this video, we’ll look at how to update data in a pivot chart. When you use a pivot chart, it’s important to understand that the chart won’t update by itself when source data is added or modified. To demonstrate this, let me create a new pivot chart using this order data from a small online surf shop. You can see we have a date, order number, item, total, and state....

Transcript In this video, we’ll look at how to configure a slicer to control more than one pivot table. One of the nice things about slicers, compared to report filters, is that they can control multiple pivot tables or charts. This makes slicers very useful for building dashboards that are based on pivot tables. In this worksheet, we have a simple pivot table that shows total sales by Product. I’m going to duplicate the pivot table, and set the second pivot table to show sales by City....

Purpose Return value Syntax =IMABS(inumber) inumber - The string representing a complex number. Using the IMABS function The Excel IMABS function returns the absolute value of a complex number. For example: =IMABS("4+3i") // returns 5 Excel handles complex numbers as strings formatted like “x+yi” or “x+yj”. Use the COMPLEX function to get the string representing a complex number. Explanation The absolute value of a complex number goes by several other names: modulus, length, or magnitude....

Purpose Return value Syntax =IMREAL(inumber) inumber - A string representing a complex number. Using the IMREAL function The IMREAL function returns the real part of a complex number. For example: =IMREAL("4+3i") // returns 4 Excel handles complex numbers as strings formatted like “x+yi” or “x+yj”. Use the COMPLEX function to get the string representing a complex number. Explanation A complex number like “2-5i” is drawn as an arrow in the complex plane, where the horizontal axis corresponds to the real part of the number, and the vertical axis corresponds to the imaginary part....