To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then adds 1 to the year value. Next, a new date is reassembled by the DATE function, using the same day and month, and year + 1 for year. The first formula therefore returns a new date of 1/15/2011, one year later than the starting date. Once the first formula is entered, it is copied down as far as needed....
The most basic way to extend a selection is to use the shift key + any arrow key. From a single cell, this lets you add additional cells in any direction. If you begin with larger selection, shift will let you extend the edges of that selection. To change which edges of a selection you can move, you can reposition the active to a different cell to a different corner using control + period....
Note: references to B5 and C4 are mixed references to allow the formula to be copied across and down. In this snippet, the delimiter (delim) is replaced with a number of spaces equal to the total length of the string: Then the formula uses the MID function to extract the nth substring. The starting point is calculated with the code below, where N represents “nth”: The total characters extracted is equal to the length of the full text string....
The Status Bar is the area at the very bottom in Excel where various information about the current mode or any special keys that are engaged can be seen. You can also select different worksheet views and zoom in and out on the worksheet from the status bar. The Status Bar.
Let’s take a look. To illustrate how IF works, let’s look first at a case where we need to assign a “pass” or “fail” to a group of students. We have five test scores in columns D through H, and an average in column I. To pass, students need to achieve an average of 70 or greater. Anything lower is a fail. This is a perfect application for the IF function....
where data is the name of the Excel Table in the range B5:E14. Note: see below for an equivalent formula based on INDEX and MATCH. XLOOKUP function In the worksheet shown, the formula in cell G5 is: The lookup_value is provided as 1, for reasons that become clear below. For the lookup_array, we use an expression based on Boolean logic: In the world of Boolean algebra, AND logic corresponds to multiplication (*), and OR logic corresponds to addition (+)....
Sorting data is usually a key component of any data analysis. It can help you organize your data, spot trends, or find the minimum and maximum values. However, there might be instances where you need to unsort your data and remove any previous ordering! Randomizing the order of a list can be useful if you need to randomly select a sample in your data. Get your copy of the example workbook used in this post and follow along!...
GUID stands for Globally Unique Identifier, but it might also be referred to as a UUID which stands for Universally Unique Identifier. These are the same thing. A GUID is a 32-character code consisting of both numbers and letters that are commonly used to identify items in a database. GUIDs are generated randomly, but the probability of duplicating a randomly generated GUID is so infinitesimally small that you can be sure that it will be unique....
You either need to know the keyboard shortcut, or use the methods that are not very straightforward. And there are a lot of symbols that many people need to insert regularly, such as the degree symbol, cent symbol, delta symbol, etc. Note: In this tutorial, I have shown all the methods for the Greek Capital Letter Delta symbol (Δ). You can use the same methods for other delta symbols as well....
Formatting is key to making your Excel workbooks look good! However, too much formatting can become a distraction. When this occurs, you may need to reset all of your format options on the sheet and begin again. Any format that has been applied such as bold font, increased font size, borders, strikethrough, number formats, date formats, and cell fill color will need to be removed before you start over! How can you reset all the format options to their original defaults?...
where data is the named range B5:D14. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365, where dynamic array formulas are native. To get the last row used, we use the ROW function together with the MAX function like this: Because data contains more than one row, ROW returns an array of row numbers: This array goes directly to the MAX function, which returns the largest number: To get the last column, we use the COLUMN function in the same way: Since data contains three rows, COLUMN returns an array with three column numbers: and the MAX function again returns the largest number: Both results are returned directly to the ADDRESS function, which constructs a reference to the cell at row 14, column 4: If you want a relative address instead of an absolute reference, you can supply 4 for the third argument like this:...
Which returns “B”, the correct grade for a score of 88. Note that the last argument is 1 (equivalent to TRUE), which allows MATCH to perform an approximate match on values listed in ascending order. In this configuration, MATCH returns the position of the first value that is less than or equal to the lookup value. In this case, the score is 88, row 4 is returned. Once MATCH returns 4 we have: Which causes INDEX to retrieve the value at the 4th row of the range C5:C9, which is “B”....
In this worksheet, the table in B5:C9 contains quantity-based discounts. As the quantity increases, the discount also increases. Let’s set up the XLOOKUP function to calculate the correct discount for each quantity shown in E5:E11. Now, to make entering the formula a bit easier, I’m going to start by creating two named ranges. I’ll name B5:B9 quantity and I’ll name C5:C9 discount. This is an optional step, but it will eliminate the need to use absolute references inside XLOOKUP....
Like clustered column charts, clustered bar charts become visually complex as the number of categories or data series increase. They work best in situations where data points are limited. Pros Allow direct comparison of multiple data series per category Can show change over time Cons More difficult to compare a single series across categories Become visually complex as categories or series are added Tips Limit data series and categories Avoid all 3d variants...
And if you’re thinking that you can do that by easily combining the two cells by using the concatenate formula or the & sign, you’ll find out that it doesn’t work. I have tried to use the & sign to combine the cells that contain date and time, and the resulting value is not in the expected format. In this tutorial, I will show you a couple of ways to quickly combine date and time values in Excel....
Note: formulas are entered relative to the upper left cell in the range, which is B5 in this example. The “stop if true” option is enabled for the first two rules to prevent further processing, since the rules are mutually exclusive. 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....
If you work with Excel, you undoubtedly have been asked to create summaries of your work. Sales reports, invoices, forecasts, schedules, you name it. You know what all these documents have in common? They look great in PDF form. You know what else? Management loves PDFs! Convert Excel to PDF In this tutorial, I’ll show you how to use Excel VBA to convert all kinds of Excel objects to PDFs: I’m going to present each of the macros with some commentary....
The basics Why pivot tables are so powerful, and how to set up a pivot table in less than 2 minutes. What it means to refresh a pivot table, and how to refresh a pivot table when your data changes (pivot tables work like a formula, but they don’t automatically pick up new data). The goofy names you need to know to talk about (and understand) the parts of a pivot table....
where date is the named range D5:D16. The result is the number of dates in D5:D16 that are between June 1, 2022 and June 15, 2022, inclusive. Note: Excel dates are large serial numbers so, at the core, this problem is about counting numbers that fall into a specific range. In other words, SUMIFS and SUMPRODUCT don’t care about the dates, they only care about the numbers underneath the dates....
where data is the named range B5:B15 and exclude is the named range D5:D7. But this doesn’t scale very well if you have a list of many things, because you’ll have to add an additional range/criteria pair to each thing you don’t want to count. It would be a lot easier to build a list and pass in a reference to this list as part of the criteria. That’s exactly what the formula on this page does....
