Explanation In this example, the goal is to convert an ordinary number into a column reference expressed in letters. For example, the number 1 should return “A”, the number 2 should return “B”, the number 26 should return “Z”, etc. The challenge is that Excel can handle over 16,000 columns, so the number of letter combinations is large. One way to solve this problem is to construct a valid address with the number and extract just the column from the address....
Purpose Return value Syntax =CONVERT(number,from_unit,to_unit) number - The numeric value to convert. from_unit - The starting units for number. to_unit - The ending units for the result. Using the CONVERT function The CONVERT function converts a number in one measurement system to another. For example, you can use CONVERT to convert feet into meters, pounds into kilograms, gallons into liters, and for many other unit conversions. The CONVERT function is case-sensitive....
A picture is worth a thousand words. Nothing beats an effective chart for presenting data in a way people can instantly understand. Excel has a powerful and deep charting engine built right in, so you already have the tools you need to create a wide range of charts and visualizations. This course will untangle and demystify Excel’s powerful charting tools, so you can create effective charts quickly and painlessly.
Explanation At the core, this formula uses the COUNTIFS function to count dates that are greater than or equal to the first day of the current month, and less than the first day of the next month. The EOMONTH function is used to create both dates based on the current date, which is supplied by the TODAY function . =COUNTIFS(dates,">="&EOMONTH(TODAY(),-1)+1,dates,"<"&EOMONTH(TODAY(),0)+1) To get the first day of the month, we use the EOMONTH function like this:...
Purpose Return value Syntax =DOLLAR(number,decimals) number - The number to convert. decimals - The number of digits to the right of the decimal point. Default is 2. Using the DOLLAR function The DOLLAR function converts a number to text, formatted as currency. The name of the function and the currency symbol used is based on language settings of the computer. It’s important to understand that DOLLAR returns text and not a number, so the result cannot be used in a numeric calculation....
About This Shortcut This isn’t really a shortcut, but it’s important to understand that pressing enter or return will complete a cell entry and move the cursor down by one cell by default. Using other keys (like the tab and arrow keys) you have the option to move the cursor in other directions after completing the entry in a cell. About This Shortcut This shortcut is an alternative to pressing enter/return when entering data....
About This Shortcut Extends the current selection one screen to the right. Note: this shortcut is unusual on a Mac because the Command key is required (Option doesn’t work), even on Macs with Page Up / Page down keys. About This Shortcut Extends the current selection one screen to the left. Note: this shortcut is unusual on a Mac because t he Command key is required (Option doesn’t work), even on Macs with Page Up / Page down keys....
Details A reader sent me an interesting formula problem this week, so thought I’d share it as a formula challenge. The problem is this: You have a fixed monthly payment, a start date, and a given number of months. What formula can you use to sum total payments by year, based on the following worksheet: In other words, what formula works in E5, copied across to I5, to get a sum for each year shown?...
Explanation In this example, the goal is to extract the last name from names that appear in format, where the middle name is not always present. The easiest way to do this is with the newer TEXTAFTER function. In older versions of Excel, you can use a significantly more complex formula based on the RIGHT, FIND, and SUBSTITUTE functions. Both options are explained below. Note: This is a great example of how new functions in Excel like TEXTBEFORE and TEXTAFTER are game-changers that can radically simplify formulas....
Explanation In this example, the goal is to highlight empty cells in the range C5:J16 with conditional formatting. This is a quick and easy way to locate missing values in a data set. To apply a conditional formatting rule to highlight empty cells, follow these steps: Select the range that contains empty cells you want to highlight (C5:J16 in this case). On the Home tab of the ribbon, click Conditional Formatting, then New Rule....
Transcript Adding a calculated field to a pivot table is an alternative to adding a new column to the source data. It’s especially useful when you don’t have access to the source data, for example when you’re connecting to a database. Let’s take a look. This pivot table shows sales data by Product. If we check the source data, we see that we have columns for Quantity and Total sales. However, the data does not contain a unit price....
Transcript In this video, we’ll look at how to compare two lists using conditional formatting. This is a great way to visually highlight missing items in a list. Here we have two lists. Both lists contain the same number of items, but each list is slightly different. We can use conditional formatting with a formula to quickly find and highlight the differences. First, I’m going to name each list. The first list I’ll call list1, and the second list I’ll call list2....
Transcript In this video, we’re going to look through the error-checking options for formulas in Excel. When you use Excel frequently, you’re likely to see a number of potential errors flagged on the worksheet as you work. Exactly which errors are flagged depends on which error checking rules are enabled. To confirm, navigate to: options > formulas > error checking rules Note that these are global settings. If you disable a rule here, it will stay disabled for all worksheets until reenabled....
Transcript In this lesson, we’ll take a look at one of the most powerful tools in Excel, the fill handle. The fill handle allows you to fill cells with data that originates from one or more source cells. This data can be a straight copy of the source cells, or, it can be based on a repeatable pattern. Let’s take a look. The fill handle is a special box that appears in the lower right-hand corner of a selection....
Explanation At the core, this formula extracts the number, adds the increment, and joins the number to the original text in the right format. Working from the inside out, this formula first extracts the numeric portion of the string in column B using the RIGHT function: RIGHT(B5,3) // returns "001" The result returned is actually text like “001”, “003”, etc. but when we add the numeric value from C, Excel automatically changes the next to a number and performs the addition:...
Purpose Return value Syntax =ISNUMBER(value) value - The value to check. Using the ISNUMBER function The ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number. The ISNUMBER function takes one argument , value , which can be a cell reference, a formula, or a hardcoded value....
Explanation LAMBDA function can be used to create custom, reusable functions in Excel. This example illustrates a feature called recursion, in which a function calls itself. Recursion can be used to create elegant, compact, non-redundant code. This example is primarily proof of concept, to show a very simple recursive LAMBDA function. When creating a recursive LAMBDA formula a key consideration is how the formula will “exit” the loop it performs by calling itself....
Purpose Return value Syntax =LOWER(text) text - The text that should be converted to lower case. Using the LOWER function The LOWER function converts a text string to all lowercase letters. The LOWER function takes just one argument, text , which can be a text value or cell reference. LOWER changes any uppercase characters in the text value to lowercase. Numbers, punctuation, and spaces are not affected. LOWER will convert numbers to text with number formatting removed....
Explanation The goal in this example is to return the minimum value that is unique, i.e. the minimum value that occurs only once in the data. The UNIQUE function, new in Excel 365 , will return a unique list of values from a set of data. By default, this is a list of any value that occurs one or more times in the data. UNIQUE has an optional third argument called exactly_once that will limit results to values that occur once only in the source data....
About This Shortcut It doesn’t matter whether cells contain data or not. About This Shortcut The last cell is at the intersection of the last row that contains data and the last column that contains data. The last cell may or may not contain data.
