Explanation In this example, the goal is to count rows that are visible and ignore rows that are hidden. This is a job for the SUBTOTAL function . SUBTOTAL can perform a variety of calculations like COUNT, SUM, MAX, MIN, and more. What makes SUBTOTAL interesting and useful is that it automatically ignores items that are not visible in a filtered list or table . This makes it ideal for running calculations on the rows that are visible in filtered data....
Purpose Return value Syntax =DEGREES(angle) angle - Angle in radians that you want to convert to degrees. Using the DEGREES function The DEGREES function takes an angle in radians and converts it to degrees. Radians measure angles using the radius of a circle , as illustrated in this image: To convert degrees back to radians, you can use the RADIANS function . Converting degrees to radians manually Because Pi = 180°, the general formula for degrees to radians is degrees * PI()/180 ....
Purpose Return value Syntax =DELTA(number1,[number2]) number1 - The first number. number2 - [optional] The second number. Using the DELTA function The DELTA function tests two numeric values for equality. When values are equal, DELTA returns 1. When values are different, DELTA returns zero. As a result, DELTA can be used to easily count pairs of equal numbers. For example: =DELTA(5,4) // returns 0 =DELTA(3,3) // returns 1 In the example shown, the formula in D6, copied down, is:...
About This Shortcut Use this shortcut to display the shortcut menu. This is the menu you see when right-clicking with the mouse. If you use TechSmith’s Snagit 12+ on Windows, be aware that it’s default shortcut keys may conflict with this Excel shortcut. About This Shortcut If there is no note in the active cell, a new note will be created. If a note already exists in the active cell, this shortcut will open the note....
Explanation This example shows how to filter dates using Excel’s FILTER function. Several common date-based filtering patterns are shown below, including filtering by month, filtering by a specific date, and filtering by month and year. Filter by month In the worksheet below, the goal is to filter the data to include only rows where the date falls in November. The formula in cell E5 is: =FILTER(B5:C16,MONTH(B5:B16)=11,"No data") Note that the month is hardcoded as the number 11....
Explanation The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there is no row number information in the data. Working from the inside out, the first step is to generate a set of row numbers. This is done with the SEQUENCE function like this: SEQUENCE(ROWS(data)) The ROW function returns the count of rows in the named range data....
An Excel function is a pre-built formula with a specific purpose. Excel provides hundreds of functions in various categories like dates and times, text, statistics, lookup, financial, engineering, etc. Most Excel functions require specific inputs, called function arguments . For example, the DATE function creates a date value based on three arguments: year, month, and day: =DATE(year,month,day) Some functions don’t require any arguments. For example, the TODAY function returns the current date without any input:...
Explanation The trick with this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format “d”. With a static date in D4, you can use =D4+1 to populate the calendar. This makes it easy to set up a conditional formatting rule that compares the date associated with each column with the dates in columns B and C. The formula is based on the AND function, configured with two conditions....
Explanation The gist of this problem is that we want to get the first non-blank cell, but we don’t have a direct way to do that in Excel. The easiest way to solve this problem is with the XLOOKUP function. XLOOKUP function The XLOOKUP function is a modern upgrade to the VLOOKUP function. XLOOKUP is very flexible and can handle many different lookup scenarios. The generic syntax for required inputs looks like this:...
Explanation In this example, the goal is to extract the top-level domain (TLD) from a list of domains. A top-level domain is the last segment of text in a domain name, for example, “.com”, “.net”, or “.net”. In the current version of Excel, the TEXTAFTER function is a simple way to solve this problem. In an older version of Excel, you can use a more complicated formula based on several text functions including RIGHT, FIND, LEN, and SUBSTITUTE....
Explanation In the formula, COUNTIFS counts the number of times each value in a cell appears in its “parent” column. By definition, each value must appear at least once, so when the count > 1, the value must be a duplicate. The references are carefully locked so the formula will return true only when all 3 cells in a row appear more than once in their respective columns. The helper column option “cheats” by combining all values in a row together in single cell using concatenation....
Transcript In this video, we’ll look at how to get a count in a pivot table. Pivot tables are excellent tools for counting and summing data, but you might struggle to get a unique or distinct count, because this feature is hidden in a normal pivot table. Let me illustrate with this set of data. Notice we have date, state, color, quantity, and a total. I’ll insert a pivot table to summarize the data....
Transcript In this video, we’ll look at how to create a basic chart in Excel. Here we have annual sales figures for a small company. I’ll use this data to build a basic column chart. The first step in creating a chart in Excel is to prepare the data. Charting works best if the data is structured in a simple grid without blank rows or columns, so, I’ll remove this blank row....
Transcript In this video, we’ll look at how to create an Excel table. Here we have some data that is a good candidate for a table. Each row represents an entry or record with information that belongs together. Each column has a unique name. The first step in creating a table is to remove any blank rows or columns. Tables are designed to manage data in one contiguous block of cells....
Transcript One of Excel’s really useful features is the ability to find and replace values based on formatting. You can even ignore values and just work with the format itself. Let’s take a look. To understand how find and replace works with formatting, let’s use the name table, and the example of replacing Ann with Anna . Before we replace any values, let’s add an orange fill to all cells that contain Ann ....
Transcript Creating a pivot table is easy. You just need to select a data source, a location for the pivot table, and then start building the table. Let’s take a look. Before you create a pivot table, make sure your source data is in good shape. Rows and columns need to be consecutive. If you have any blank rows or columns in the data, remove them first. Next, make sure that each column has a unique label in the first row....
Transcript In this video, we’ll look at how to make a custom table style the default table style in new workbooks. Setting a custom table style as the default table style is a little tricky. You need to create an Excel template that contains the custom style, and then make sure Excel uses the template when it starts up. Let’s walk through the process. First, create a new blank workbook....
Transcript Like pivot table styles, pivot table slicers have their own framework for creating and applying visual styles. Let’s take a look. Slicer styles are available on the Slicer Tools ribbon. They control the color and style used to display slicers. As with pivot table styles, there are a wide variety of styles available, each with a distinctly different look. To apply a slicer style, select the slicer and then click one of the thumbnail images....
Transcript If you use Excel frequently, you’ll find that you do a lot of formatting. In this video I’ll show you how to use a tool called the Format Painter to speed up manual formatting. As you work in Excel, you’ll often spend a considerable amount of time formatting data to get it to look good. And although formatting tools in Excel are easy to use, applying formatting manually can quickly get tedious....
Transcript In this video, we’ll look at how to use VLOOKUP to lookup values in an Excel Table. On this worksheet, I have a table that contains employee data, named Table1. To illustrate how to work with VLOOKUP when the source data is in a table, I’ll set up formulas to the right to extract data from the table, matching on an employee ID. First, I’ll select the table header and use Paste Special with Transpose to get the field values....
