Video: What is a number format What can you do with custom number formats? Custom number formats can control the display of numbers, dates, times, fractions, percentages, and other numeric values. Using custom formats, you can do things like format dates to show month names only, format large numbers in millions or thousands, and display negative numbers in red. Where can you use custom number formats? Many areas in Excel support number formats....
The DATEVALUE function takes just one argument, called date_text. If date_text is a cell address, the value of the cell must be text. If date_text is entered directly into the formula it must be enclosed in quotes. Examples To illustrate how the DATEVALUE function works, the formula below shows how the text “3/10/1975” is converted to the date serial number 27463 by DATEVALUE: Note that DATEVALUE returns a serial number, 27463, which represents March 10, 1975 in Excel’s date system....
Sometimes, when the result is an array, Excel won’t return the top-left value in array (step #3) unless the formula is entered an array formula with control + shift + enter. In other words, entering a formula with control + shift + enter disables implicit intersection. Example Implicit intersection can occur when a formula is entered next to vertical data, or above or below horizontal data. For example, the formula in D6 in the example shown is: In this case, Excel resolves the range B4:B8 to the value in B6 (3) and returns a result of 4....
Excel uses iteration to arrive at a result, starting with the guess (if provided) or with .1 (10%) if not. If an accurate IRR can’t be calculated after a fixed number of iterations, the #NUM error is returned. A better guess will prevent this error. Notes The values array must contain at least one positive value and one negative value. Values should be in chronological order. If IRR returns the #NUM!...
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Note: the Scroll Lock status message may or may not be visible on the left side of the status bar, since it can be disabled. See How to disable Scroll Lock for for a full run-down on how to turn off scroll lock if you don’t have an extended keyboard. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel....
The T function takes one argument, value, which can be a cell reference, a formula result, or a hardcoded value. Examples The T function returns text when given a text value and an empty string ("") for numbers, dates, and the logical values TRUE and FALSE. For example: In most cases, the T function is unnecessary, because Excel automatically converts values when needed. The T function is provided for compatibility with other spreadsheet programs....
Volatile function list The following functions are considered volatile: Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
In the example shown, generate a value of 60 every 3 months. The formula in B8 is: Which returns 60 every 3rd month and zero for other months. In this case, the number is created with the COLUMN function, which return the column number of cell B8, the number 2, minus 1, which is supplied as an “offset”. We use an offset, because we want to make sure we start counting at 1, regardless of the actual column number....
In the example, the date is March 9, 2016, which is the serial number 42,438. So: This means there are 13,033 days before January 1, 2050, when counting from March 9, 2016. Without TODAY Note: you don’t need to use the TODAY function. In the second example, the formula in D6 is: Concatenating with text In the third example, the same basic formula is used along with concatenation operator (&) to embed the calculated days in a simple text message: Since there are 15 days between December 10, 2014 and December 25, 2014, the result is this message: Just 15 days left!...
In this formula, the SEARCH function is used to search cells in column B like this: When SEARCH finds a match, it returns the position of the match in the cell being searched. When search can’t find a match, it returns the #VALUE error. Because we are giving SEARH more than one thing to look for, it will return more than one result. In the example shown, SEARCH returns an array of results like this: This array is then used as a divisor for the number 1....
If you need a new data point that can be obtained by using existing data points in the Pivot Table, you don’t need to go back and add it in the source data. Instead, you can use a Pivot Table Calculated Field to do this. Download the dataset and follow along. What is a Pivot Table Calculated Field? Let’s start with a basic example of a Pivot Table. Suppose you have a dataset of retailers and you create a Pivot Table as shown below:...
You won’t find an import style command in Excel, or any other direct method for moving a custom style from one workbook to another. However, there are a couple easy workarounds. One option is to copy an entire worksheet into another workbook. In this workbook, I have a custom style already defined, as you can see in the styles menu. In a second open workbook, there are no custom table styles....
For example, below I have an example where I have student’s scores and I have used conditional formatting to highlight all the scores that are above 80. If you’re interested in learning all the amazing things you can do with conditional formatting, check out this tutorial where I show some useful conditional formatting examples. Once you have set the conditional formatting rules for a cell or range of cells, you can easily copy it to other cells on the same sheet or other sheets....
Let’s take a look. This pivot table shows a breakdown of sales and orders by product. Let’s use manual grouping to organize these products into 2 custom groups. When you group items manually, hold down the Control-key and select each item that you want to include in the first group. With these cells selected, click “Group Selection” from the Options tab on the PivotTable Tools Ribbon. Excel will put the selected items into their own group and each remaining item into other groups....
This is made possible by the Workbooks.Open method, which takes the file location as the argument and opens that Excel file. You can do a lot more with the Workbooks.Open method, such as opening all the files in a given folder, opening files as read-only, opening files and then saving them with a different name, etc. In this tutorial, I will show you how to use VBA to open Excel files and all the amazing things you can do with it....
When you work with data in Excel, there is a handy feature that allows you to freeze the top row and header columns (or even lock multiple top rows or left columns). So when you scroll down, the headers are always visible. But that’s not the case when you print your reports. By default, a printed report would only have the header row at the top of the first printed page, and then rest all the other pages won’t have it....
A lot of times, we need to work with text data in Excel. It could be Names, Address, Email ids, or other kinds of text strings. Often, there is a need to combine cells in Excel that contain the text data. Your data could be in adjacent cells (rows/columns), or it could be far off in the same worksheet or even a different worksheet. How to Combine Cells in Excel In this tutorial, you’ll learn how to Combine Cells in Excel in different scenarios:...
When to use Excel TRIM Function Excel TRIM Function is best suited for situations when you need to clean the dataset by removing leading, trailing, and double spaces. What it Returns It returns the text string where all the leading, trailing, and double spaces have been removed. Syntax =TRIM(text) Input Arguments Here, ‘text’ is the text string from which you want to remove the extra spaces. This could either be entered manually (within double quotes) or can be a cell reference that contains the text string....
