Transcript In this video, we’ll look at how to use slicer styles to control the look of slicers. Once you have a slicer on a table, you can use styles to quickly change how a slicer looks. You’ll find slicer styles on the Slicer Tools ribbon, which appears whenever a slicer is selected. Just click a thumbnail image to apply a style. Notice each style has a name that appears when you hover with the cursor for a few seconds....

Transcript In this video we’ll look at how to use the COUNTIF function to count cells that meet a single criteria. Let’s take a look. The COUNTIF function counts cells that satisfy a single condition that you supply. It takes two arguments: range and criteria. For example, if I want to count the cells in this range that contain the number “15,” I enter B7:B12 for the range, and “15” for the criteria....

Transcript Before we look at alignment, we need to look at text wrapping. Wrapping text in cells is a key feature in Excel. Wrapping text allows you to fit multiple lines of text in a single cell. Let’s take a look. You’ve probably noticed that longer text will extend right through the cell border into columns to the right, as long as those columns don’t contain data. If those columns do contain data, the text will be cut off at the cell border....

Purpose Return value Syntax =HYPERLINK(link_location,[friendly_name]) link_location - The path to the file or page to be opened. friendly_name - [optional] The link text to display in a cell. Using the HYPERLINK function The HYPERLINK function creates a hyperlink to a given destination with a “friendly name”, which is simply the anchor text. You can use HYPERLINK to construct a clickable hyperlink with a formula. The HYPERLINK function can build links to other cells in a workbook, other sheets, named ranges, other workbooks, pages on the internet, or files on network servers....

Purpose Return value Syntax =IMCSCH(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMCSCH function The Excel IMCSCH function returns the hyperbolic cosecant of a complex number. Given “1+1.5707963267949i” as input, the function returns “-1.72324E-15-0.648054274i” as output. =IMCSC(COMPLEX(1, PI()/2)) // returns -1.72324E-15-0.648054274i Explanation The complex hyperbolic cosecant function is the reciprocal of the complex hyperbolic sine function. In Excel, the function’s output is equivalent to the following formula....

Transcript In this video, I’ll give a brief introduction to structured references. A structured reference is a term for using a table name in a formula instead of a normal cell reference. Structured references are optional, and can be used with formulas both inside or outside an Excel table. Let’s take a look at some examples. On this sheet, I have population data for 25 states in the US, based on the 2000 and 2010 census data....

Purpose Return value Syntax =ISOMITTED(argument) argument - The argument to test for. Using the ISOMITTED function The Excel ISOMITTED function is a helper function for LAMBDA functions to allow optional arguments . Inside a LAMBDA function, ISOMITTED will return TRUE when an argument has not been provided. ISOMITTED takes just one argument, argument , which should be the name of an argument defined in the parent LAMBDA function. Step-by-step example To illustrate how ISOMITTED works, imagine a simple LAMBDA formula that adds 10 any given value....

Purpose Return value Syntax =ISOWEEKNUM(date) date - A valid Excel date in serial number format. Using the ISOWEEKNUM function The ISOWEEKNUM function returns a week number based on ISO standards. Under this standard, weeks begin on Monday and the week number 1 is assigned to the first week in a year that contains a Thursday, following ISO 8601 . ISOWEEKNUM takes just one argument, date , which must be a valid Excel date....

Purpose Return value Syntax =MDETERM(array) array - A square array of numbers only. Using the MDETERM function The MDETERM function returns the matrix determinant of a given array , which must be a square matrix containing numbers only, and no empty values. In mathematics, the matrix determinant is a scalar value (single value) that is a function of the entries in a square matrix. The determinant is a special number that can help characterize some properties of the matrix....

About This Shortcut Moves current selection one cell to the left of the active selection. About This Shortcut Moves current selection one above the active selection.

Purpose Return value Syntax =ODD(number) number - The number to round up to an odd integer. Using the ODD function The ODD function rounds numbers up to the next odd integer. ODD always rounds numbers away from zero, so positive numbers become larger and negative numbers become smaller (i.e. more negative). ODD takes just one argument , number , which should be a numeric value. With positive numbers, ODD rounds number up to the next odd integer....

About This Shortcut This shortcut will display the Ungroup Dialog Box as long as no whole rows or columns are selected. About This Shortcut This shortcut toggles the display of outline symbols on the worksheet. Outline symbols only appear if the worksheet has at least some rows or columns grouped.

Purpose Return value Syntax =PERCENTOF(data_subset,all_data) data_subset - A subset of data as a range or array. all_data - All data as a range or array. Using the PERCENTOF function The PERCENTOF function is designed to return a subset of data as a percentage of a larger data set. The output from PERCENTOF is a decimal number that can be formatted with Excel’s percentage number format. Essentially, PERCENTOF performs a calculation like this:...

Purpose Return value Syntax =PI() Using the PI function The PI function returns the value of π (pi) accurate to 15 digits. The value of π represents a half-turn in radians and appears in many formulas relating to the circle. The PI function takes no arguments : =PI() // returns 3.14159265358979 Circumference of a circle The constant π appears in many math formulas relating to the circle. The formula for the circumference of a circle is 2πr....

Transcript In this example, we’re going to look at sales data by salesperson. In the data, we have the fields: Name, Date, Amount, and Region. To start off, let’s look at total sales by region. This would be easy to graph in a Pivot Chart as well. Next, what are total sales by month and year? To answer this, we’ll need to group by Date. How about total sales by salesperson in 2014?...

Transcript In this example, we’re going to use a Pivot Table to analyze instrument measurements. This data consists of Temperature readings taken in a greenhouse over a period of days. We have Date, Time, temperature in Celsius, and temperature in Fahrenheit. So, first, how many readings do we have? You can see that we have over 9000 readings total. How many reading per day is this? If I add Date as a row label, we get a breakdown by day....

Explanation In this example, the goal is to randomly assign the names in column B to three groups of equal size. The group names are “A”, “B”, and “C”, and these values appear in the named range groups (F5:F7). The solution should automatically count the number of groups to assign and attempt to generate the same count for each group. The worksheet shown contains 18 names, the final result should be that each group includes 6 random names from the list....

Explanation The Excel FLOOR function rounds a number down to a given multiple. The multiple to use for rounding is given as the second argument ( significance ). If the number is already an exact multiple, no rounding occurs. FLOOR works like the MROUND function , but unlike MROUND, which rounds to the nearest multiple, FLOOR always rounds down to the given multiple. In the example shown, the formula in cell D6 is...

Purpose Return value Syntax =ROW([reference]) reference - [optional] A reference to a cell or range of cells. Using the ROW function The ROW function returns the row number for a cell or range. For example, =ROW(C3) returns 3, since C3 is the third row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula. ROW takes just one argument, called reference , which can be empty, a cell reference, or a range....

About This Shortcut This shortcut will select the entire column of the current selection. If the current selection spans multiple columns, all columns that intersect the current selection will be selected. About This Shortcut This shortcut will select the entire worksheet. The behavior changes on subsequent use If the cursor is inside a region with adjacent, non-empty cells: First time - will select the region that contains non-empty cells Second time - will select the entire worksheet