Explanation Excel times are stored as fractional parts of one day . For example, 12 hours is equal to 0.5, and 18 hours is equal to 0.75. This means if you try to multiply an Excel time by an hourly rate, you’ll get a total far less than expected. The trick is to first convert the Excel time to a decimal time by multiplying by 24. =(B5*24) // returns 1 Then you can multiply by the hourly rate:...
Explanation In this example, the goal is to count errors in a workbook by sheet, where the sheet names have been previously entered in a column as shown. This is a tricky problem in Excel for a couple of reasons. First, there is no direct way to generate a list of sheets in a workbook with a formula. Second, once we have a list of sheet names, we need to run the text through the INDIRECT function to get a valid reference....
Purpose Return value Syntax =DEVSQ(number1,[number2],...) number1 - First value or reference. number2 - [optional] Second value or reference. Using the DEVSQ function The Excel DEVSQ function calculates the sum of the squared deviations from the mean for a given set of data. Variance and standard deviation functions deal with negative deviations by squaring deviations before they are averaged. DEVSQ calculates the sum of the squared deviations from the mean, without dividing by N or by N-1....
Excel 365 refers to the subscription version of Excel as opposed to a fixed version of like Excel 2019, Excel 2016, Excel 2013, etc. Typically, the subscription version of Excel is available as part of a suite of applications called “Office 365”, which was renamed “Microsoft 365” in 2020. You can see subscription status by navigating to File > Account, which shows current product information. The subscription version of Excel is still downloaded and launched like a normal application on Windows and Mac OS....
Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. This article introduces the basic concepts you need to know to be proficient with formulas in Excel. More examples here . What is a formula? A formula in Excel is an expression that returns a specific result. For example: =1+2 // returns 3 =6/3 // returns 2 Note: all formulas in Excel must begin with an equals sign (=)....
Explanation In this example, the goal is to extract common values from two text strings that contain comma-delimited values. In the worksheet shown the values for “List1” appear in column B and the values for “List2” appear in column C. The results in column D show the intersection of the two lists, that is, the values shared by List1 and List2. Notice that the order of the values in each list is random....
Explanation The core of this formula is a basic lookup with INDEX: =INDEX(list,row) In other words, give INDEX the list and a row number, and INDEX will retrieve a value to add to the unique list. The hard work is figuring out the ROW number to give INDEX, so that we get unique values only. This is done with MATCH and COUNTIF, and the main trick is here: COUNTIF($D$4:D4,list) Here, COUNTIF counts how many times items already in the unique list appear in the master list, using an expanding reference for the range, $D$4:D4....
Explanation There is no built-in function to perform a series of find and replace operations in Excel, but you can create a formula that does the same thing. The goal in this example is to replace multiple (“find”) values with corresponding (“replace”) values in a single “replace all” operation. The text strings to process are in column B. The “find” and “replace” values appear in the table shown in columns F and G....
Details One problem that comes up a lot in Excel is counting or summing based on multiple OR conditions. For example, perhaps you need to analyze data and count orders in Seattle or Denver, for items that are Red, Blue, or Green? This can be surprisingly tricky, so naturally it makes a good challenge! The challenge The data below represents orders, one order per row. There are three separate challenges....
Explanation In this example, the goal is to get the first day of the month based on any valid date. This problem can be solved by combining the EOMONTH function with simple addition. The EOMONTH Function The EOMONTH function returns the last day of the month, a given number of months in the past or future. For example, with a start date of January 15, 2025, EOMONTH will return the following results with months set to -1, 0, and 1:...
Explanation Imagine you have a random date and want to find the Monday of the week in which the date appears. You can see you will need to “roll back” a specific number of days, depending on what day of the week the given date is. If the date is a Wednesday, you need to roll back 2 days, if the date is a Friday, roll back 4 days, and so on, as seen in the table below:...
Explanation In this example, the goal is to extract the name from a list of email addresses. In the current version of Excel, the easiest way to do this is with the TEXTBEFORE function or the TEXTSPLIT function. In older versions of Excel, you can use a formula based on the LEFT and FIND functions. All three options are explained below. TEXTBEFORE function The TEXTBEFORE function returns the text that occurs before a given delimiter....
Explanation This formula is uses the WORKDAY function, which returns a date in the future or past, based on start date and required work days. WORKDAY automatically excludes weekends, and can also exclude holidays if provided as a range of dates. In the example shown, the project end date is in column C, and days is in column D. The end date represents a due date or target date, and days represents duration in work days....
Explanation In this example, the goal is to retrieve the last available close price for each symbol shown in column B. This can be done with the STOCKHISTORY function . The main purpose of STOCKHISTORY is to retrieve historical stock price information, and we need to make a few adjustments to prevent errors that might occur when a close price is not available on a given date. We also need to adjust arguments to return just a single value per symbol, instead of an array of values that spill onto the worksheet into multiple cells....
Explanation In this example, the goal is to return the name of the current workbook with a formula. This is a fairly simple problem in the latest version of Excel, which provides the TEXTAFTER function and the TEXTBEFORE function . In older versions of Excel, you can use a more complicated formula based on the MID and FIND functions. Both options rely on the CELL function to get a full path to the current workbook....
Transcript You might want to create a second pivot chart using the same data source, to provide a different view of the same data. Let’s take a look. Here we have a simple pivot table and pivot chart already set up. As always, any change to the pivot table is reflected in the pivot chart, and vice versa. Let’s say we want to add a second chart using the same data....
Transcript In this lesson, we’re going to take a look at the first step in doing almost anything in Excel—selecting one or more cells. Excel will let you select one cell, a range of cells, or any combination of single and multiple cells. Let’s take a look. The first thing to understand about selecting cells is that there is always at least one cell already selected in the worksheet. To see what is currently selected, check the name box....
Transcript In this video, we’ll look at how to set up a running total in an Excel table. Setting up a running total in an Excel table is a little tricky because it’s not obvious how to use structured references. This is because structured references provide a notation for current row, but not for first row in a column. Let’s explore a few options, starting with formulas based on regular references....
Transcript Pivot table slicers come with a variety of settings and options. Let’s take a look. There are several ways to get to slicer options. First, you can click the Option tab of the Slicer Tools ribbon. The Slicer Tools ribbon will appear whenever you select a slicer. The Slicer Tools ribbon gives you direct access to slicer settings, slicer styles, tools for arranging slicers, and tools for configuring column count, button size, and slicer dimensions....
Purpose Return value Syntax =INT(number) number - The number from which you want an integer. Using the INT function The INT function returns the integer part of a decimal number by rounding down to the integer. It is important to understand that the INT function returns the integer part of a decimal number, after rounding down . One consequence of this behavior is that negative numbers become more negative . For example, while INT(10....
