Posts

But one IF often leads to another, and once you combine more than a couple IFs, your formulas can start to look like little Frankensteins :) Are nested IFs evil? Are they sometimes necessary? What are the alternatives? Read on to learn the answers to these questions and more… 1. Basic IF Before we talk about nested IF, let’s quickly review the basic IF structure: The IF function runs a test and performs different actions depending on whether the result is true or false....

When you copy or cut data in Excel, that data is temporarily stored on the clipboard. The Excel clipboard will also show data from other programs that you copy or cut. This allows you to paste the stored data later on and will even give you access to more than the last item copied. This way, you can copy multiple items before pasting them. The clipboard will save all the items you’ve cut or copied even when it’s closed....

It’s a popular way to visualize your data! While colored cells are a great way to highlight data to quickly grab someone’s attention, they are not a great way to store data. Unfortunately, a lot of users will color a cell to indicate some value instead of creating another data point with the value. For example, they might color a cell green to indicate an item is approved instead of creating another data point with the text Approved....

When working with Excel, there are some small tasks that need to be done quite often. Knowing the ‘right way’ can save you a great deal of time. One such simple (yet often needed) task is to number the rows of a dataset in Excel (also called the serial numbers in a dataset). Now if you’re thinking that one of the ways is to simply enter these serial number manually, well – you’re right!...

And since you’re reading this article, I can assume that it’s not happening with your Excel. One common issue with arrow keys in Excel is that it moves the worksheet, but the cursor doesn’t move. It’s as if the cursor is stuck in the cell and doesn’t move with the arrow key, but the worksheet page scrolls. And it has a really easy fix – the scroll lock. In this short tutorial, I will show you how to fix when arrow keys are not working as expected in your Excel file....

If C5/D5 returns a value, that value is returned. If C5/D5 returns an error, IFERROR returns an empty string (""). The IFERROR function takes two arguments: a value (usually entered as a formula), and a result to display if the formula returns an error. The second argument is only used if the first argument throws an error. In this case, the first argument is the simple formula for calculating the average order size, which divides total sales by the order count: The second argument is entered as an empty string ("")....

The result is 3, since there are three rows where Previous sales are greater than Current sales. SUMPRODUCT function The SUMPRODUCT function is designed to work with arrays. It multiplies corresponding elements in two or more arrays and sums the resulting products. One of SUMPRODUCT’s special features is that it can handle “array operations” natively, without requiring Control + Shift + Enter. This allows us to perform a comparison between current and previous sales directly in array1....

To allow a user to enter only dates between two dates, you can use data validation with a custom formula based on the AND function. In the example shown, the data validation applied to C5:C9 is: The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because we want to allow only dates in the month of June 2016, we give AND with two logicals....

Where data is an Excel Table based on the data in B5:D17. The result from SUMIF spills into the range G5:I9, and the results show the sum of Qty for each City and Size combination. Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021. Create the Excel Table One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed....

The CONCAT function accepts multiple arguments called text1, text2, text3, etc. up to 255 total. Arguments may be supplied as cell references, ranges, and hard-coded text strings. Only the first argument is required, and values are concatenated in the order they appear. For example, to concatenate the value of A1 and B1, separated by a space, you can configure CONCAT like this: This is equivalent to using the concatenation operator (&) manually like this: When concatenating numbers, number formatting is lost....

You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the DATE function. For example, to change the year of a date in cell A1 to 2020, but leave the month and day as-is, you can use a formula like this: See below for more examples of formulas that use the DAY function....

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.

Step-by-step example To illustrate how ISOMITTED works, imagine a simple LAMBDA formula that adds 10 any given value. With the value 100 in cell A1, this formula will return 110: Next, we alter the formula to make both a and b variables: With 100 in A1, if we supply 10 for b, the formula returns 110. If we supply 20 for b, the formula returns 120. So far so good. Now let’s say we want to make b optional, and we want b to default to 10 if not provided....

For example, a test score greater than 80% of all test scores is said to be at the 80th percentile. In this case, PERCENTRANK will assign a rank of .80 to the score. In the example shown, the formula in C5 is: where “data” is the named range C5:C12. Note: Microsoft classifies PERCENTRANK as a “compatibility function”, now replaced by the PERCENTRANK.INC function. Inclusive vs. Exclusive Starting with Excel 2010, the PERCENTRANK function has been replaced by two functions: PERCENTRANK....

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.

On the Mac, Ctrl + C also works 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.

On Windows, once you have the Paste Special Dialog open, you can type a letter to select the particular command you want, for example: F = Formula V = Values T = Formats C = Comments N = Validation H = All using source theme X = All except borders W = Column widths R = Formulas and number formats U = Values and number formats D = Add S = Subtract M = Multiply I = Divide B = Skip blanks E = Transpose...

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.

With a laptop keyboard, use Control Shift +. With a full keyboard, use Control + Note: In Mac 2016, this shortcut was changed to Command Shift +, and now Control + I changes text to italic. In previous Mac Excel versions, Control + I inserts a row (when a row is selected) or brings up the Insert Cells dialog when the selection does not include an entire row. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

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.