Explanation In this example, the goal is to calculate the number of days a ticket/case/issue has been open. We start counting on the date a ticket was opened and stop counting on the date a ticket was closed. If there is no closed date, the ticket is still open. Because dates in Excel are just serial numbers , the math is quite simple. We can simply subtract the “Opened” date from the date today, which we calculate with the TODAY function....
Explanation Imagine a company that uses a tiered commission structure for its sales team. Each salesperson is assigned a commission rate based on the total sales they have made. The commission tiers are structured like this: For sales less than $10,000, the commission rate is 10%. For sales from $10,000 to $20,000, the commission rate is 15%. For sales from $20,000 to $30,000, the commission rate is 20%. For sales over $30,000, the commission rate is 25%....
Purpose Return value Syntax =COT(number) number - The angle provided in radians. Using the COT function The Excel COT function returns the cotangent of an angle provided in radians. In geometric terms, the cotangent of an angle returns the ratio of the length of the adjacent side over the length of the opposite side of the corresponding right triangle. For example, the cotangent of PI()/6 (30°) returns the ratio 1.732....
Explanation The table in B3:D11 is a log that shows courses completed by various people. If a course has been completed by a person, there will be an entry in the table with name, course, and date. For the purpose of this example, if we find and entry for a given name/course, we can assume that course is complete. In the summary table in F3 to I7, we have the 4 names that appear in the data log in rows, and 3 courses we want to track as column headers....
About This Shortcut Displays the Format Cells dialog box with the Font tab selected, no matter which tab was used last. About This Shortcut This shortcut toggles bolding on and off for the current selection. On Windows, Ctrl 2 also toggles bolding.
Purpose Return value Syntax =ERROR.TYPE(error_val) error_val - The error for which to get an error code. Using the ERROR.TYPE function The Excel ERROR.TYPE function returns a number that corresponds to a specific error value. You can use ERROR.TYPE to test for specific kinds of errors. If no error exists, ERROR.TYPE returns #N/A. See the table below for a key to the error codes returned by ERROR.TYPE. The ERROR.TYPE function takes just one argument, error_val , which is expected to be an Excel error like #VALUE!...
Below are some of the topics covered on our site. Formulas Formula Basics Formula Examples Formula Criteria Formula Errors Dynamic Arrays Formula Challenges Formula Training Functions Function Guide 101 Excel Functions You Should Know INDEX and MATCH Nested IF examples Function Training Dynamic Arrays Dynamic Array Formulas in Excel Alternatives to Dynamic Array Functions Dynamic Array Formula Training Pivot Tables Pivot Table Introduction Pivot Table Tips Pivot Table Examples Pivot Table Problems Pivot Table Training Excel Shortcuts 200+ Excel Shortcuts 50 Excel shortcuts you should know 30 Excel Shortcuts in 12 minutes Shortcut Training Excel Tables Excel Table Overview Anatomy of an Excel Table Introduction to Structured References Table Training Excel Charts Chart Types Chart Examples Chart Training Conditional Formatting Quick Start Formula Examples Troubleshooting Conditional Formatting Training Data Validation Data Validation Guide Data Validation Examples Dependent Dropdown Lists Key functions IF function VLOOKUP function SUMIFS function COUNTIFS function INDEX function MATCH function SUMPRODUCT function More… Other Custom Number Formatting Named ranges in Excel How to concatenate in Excel Excel Glossary Video samples Video Training
An expanding reference (or expanding range) in Excel defines a range that expands as a formula is copied down or across cells. This is done by “mixing” absolute and relative references – making the first cell an absolute reference and the last cell a relative reference . Example In the example shown the formula in D4 is: =SUM($C$4:C4) As this formula is copied down column D, it changes as follows:...
About This Shortcut This shortcut extends the selection from the active cell to the cell immediately to the right. If multiple cells in the same column are selected to start with, Excel will extend the entire right edge of the current selection. If cells in multiple columns are selected to start with, Excel will extend the selection by one column to the right, away from the active cell. About This Shortcut This shortcut extends the selection from the active cell to the cell immediately to the left ....
Explanation The choose function uses the first argument to “select” remaining elements. For example, in a scheme where 1 = small, 2 = medium, and 3 = large, this formula will “map” the number 2 to “medium”. =CHOOSE(2,"small","medium","large") In the case of fiscal quarters, we can use this same idea to map any incoming month (1-12) to one of 4 quarter values. We just need to use the MONTH function to get the month number as the first argument, then provide 12 numbers (one for each month of the year) that are carefully ordered to reflect the fiscal year desired:...
Purpose Return value Syntax =HOUR(serial_number) serial_number - A valid Excel time. Using the HOUR function The HOUR function returns the hour portion of a time as a number between 0-23. For example, with a time of 9:00 AM, HOUR will return 9. HOUR takes just one argument , serial_number , which must be a valid Excel date or a valid Excel time . Times can be supplied to the HOUR function as text (e....
Transcript Each field that you add to a Pivot Table has attributes that you can set in a dialog box called Field Settings. These attributes include things like the name of the field, the type of calculation used, layout options, and more. Let’s take a look. Once you add a field to a Pivot Table, you can view and change attributes of the field using the Field Settings dialog box. One way to get to this dialog box is to use the drop-down menu for that field in the Field List pane, and select Value Field Settings, or Field Settings from the menu....
Transcript In this video, we’re going to build on the technique we covered in an earlier video, where we added in missing data using formulas. Here again we have a music collection with some missing data. The twist, in this case, is that the Artist isn’t in the right column. Instead of column B, it appears in column C. You’ll sometimes see this kind of pattern when the data has been exported from another system....
Explanation The #NAME? error occurs when Excel can’t recognize something. Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a matter of correcting spelling or a syntax problem. The examples below show misconfigured formulas that return the #NAME error and the steps needed to fix the error and get a working formula again. Function name misspelled In the example below, the VLOOKUP function is used to retrieve an item price in F3....
Explanation The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the “range intersect” operator and the #NULL! error is reporting that the two ranges do not intersect. This is baffling to the average user but in most cases replacing the space with a comma or colon as needed will fix the problem....
Transcript In this lesson we’ll look at how to add symbols and other special characters to text in Excel. This includes things like the copyright symbol, math signs, arrows, and the graphics found in fonts like Wingdings. Let’s take a look. There are several different ways to insert symbols and special characters. One way, that’s not obvious, is to use AutoCorrect. AutoCorrect is a feature built into Excel that automatically substitutes one bit of text for another....
Transcript After you create a calculated field, you can easily update the formula at any time. Let’s take a look. To modify a calculated field, you need to navigate to the Insert Calculated Field dialog box. First, select any cell in the pivot table. Then, on the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, then choose Calculated Field. Next, select the calculated field you want to work with from the name drop-down list....
Transcript One of the options grouped under Alignment is Shrink to fit. Shrink to fit will automatically reduce the font size until text fits in a cell. Let’s take a look. To illustrate how Shrink to fit works, let’s look at a typical layout problem. Here we have a basic feature table that needs some cleanup. Let’s start by applying a horizontal and vertical alignment of center for all cells. We don’t have a lot of long text in this table, so center alignment will work well....
Transcript In this video we’ll look at how to use the REPT function in Excel to repeat text. Excel contains a special function for repeating text named REPT which stands for “repeat.” The REPT function takes two arguments: the text to repeat, and the number of times to repeat the text. So, if I enter an upper case “A” and the number “3,” we’ll get a triple A. If I expose these arguments on the worksheet, I can quickly add a few more examples....
Explanation The example shown contains almost 10,000 rows of data. The data represents temperature readings taken every 2 minutes over a period of days. For any given date (provided in cell H7), we want to get the maximum temperature on that date. Inside the IF function , logical test is entered as B5:B9391=H7. Because we’re comparing the value in H7 against a range of cells (an array), the result will be an array of results, where each item in the array is either TRUE or FALSE....
