Posts

Sometimes you may have a need to delete every other row (i.e., alternate rows), or every third/fourth/fifth row in Excel. One use-case of this could be when you have weekly data and you only want the data for even weeks or odd weeks. Another could be when you get a data dump from a database and all the useful data is only in every second or third row. While you always have the option to manually select and delete rows, this is inefficient in case you have a large dataset....

To enter data in Excel, just select a cell and begin typing. You’ll see the text appear both in the cell and in the formula bar above. To tell Excel to accept the data you’ve typed, press enter. The information will be entered immediately, and the cursor will move down one cell. You can also press the tab key instead of the enter key. If you press tab, the cursor will move one cell to the right once the information has been entered....

Let’s take a look. Sometimes you need to group values into discreet categories that don’t exist in your data. For example, in this case, we have a list of employees and each employee is in one department. What if you need to group these employees into groups A and B, by department, as shown in this table? Well, you could use nested IF statements as we’ve covered in another video, and this works fine....

A good example is when you have first, last, and middle names in separate columns and you want to join these together into one name. This is referred to as “concatenation.” In this example, we have first, middle, and last names shown separately in a table. I’ll use simple concatenation to join these separate names together into a single name. The easiest way to concatenate is to use the operator for concatenation: the ampersand character....

Now I came across a problem where I had to lookup entire row and return the values in all the columns from that row (instead of returning a single value). So here is what I had to do. In the below dataset, I had Sales Rep names and the Sales they made in 4 quarters in 2012. I had a drop down with their names, and I wanted to extract the maximum sales for that Sales Rep in those four quarters....

In this Excel tutorial, I will show you how to start a new line in an Excel cell. You can start a new line in the same cell in Excel by using: A keyboard shortcut to manually force a line break. A formula to automatically enter a line break and force part of the text to start a new line in the same cell. Start a New Line in Excel Cell – Keyboard Shortcut To start a new line in an Excel cell, you can use the following keyboard shortcut:...

You can download the example workbook using the above link. Example This formula tests the date contained in cell B3 to determine if it’s a weekend or weekday. Generic Formula Date is the date which you want to test whether it’s a weekend or not. The formula will return true if the date is a weekend and will return false if it’s a weekday. What It Does This formula will test a given date to see if it’s a weekend (Saturday or Sunday) and return TRUE if it is a weekend and FALSE if it is not a weekend....

The Ctrl + Z (for undo) and Ctrl + Y (for redo) keyboard shortcuts.The undo and redo buttons in the Quick Access Toolbar. But did you know about Excel’s feature to undo and redo multiple actions at one time? The same thing can be done with redoing actions.

Let’s take a look. Here we have the employee list we’ve looked at previously. This time, however, notice that the ID column has been moved into the data. It’s no longer the first column in the data, so we can’t use it to find values with VLOOKUP. This is actually a good example of a situation where you may need to use INDEX and MATCH instead of VLOOKUP if you need to match on the ID....

In the example shown, the formula in C5 is: This formula generates a working hyperlink to the first match found of the lookup value in the named range “data”. The MATCH function gets the position of the value in B5 inside the named range data, which for the lookup value “blue” is 3. This result goes into the INDEX function as row_num, with “data” as the array: This appears to return the value “blue” but in fact the INDEX function returns the address E6....

where “data” is the named range H5:J8 and “ids” is the named range H5:H8. Retrieving customer name Working from the inside out, the MATCH function is used to get a row number like this: The lookup value comes the customer id in C5, which is a mixed reference, with the column locked, so the formula can be easily copied. The lookup array is the named range ids (H5:H8), the first column in the customer table....

Where “impact” is the named range J6, and “certainty” is the named range J5 Context A risk matrix is used for risk assessment. One axis is used to assign the probability of a particular risk and the other axis is used to assign consequence or severity. A risk matrix can a useful to rank the potential impact of a particular event, decision, or risk. In the example shown, the values inside the matrix are the result of multiplying certainty by impact, on a 5-point scale....

The result is data sorted by group, then by score, with highest scores appearing first. array = B5:D14 by_array1 = D5:D14 sort_order1 = 1 The formula below will sort data by group A-Z: To extend the formula to sort next by score, in descending order, we need to add: by_array2 = C5:C14 sort_order2 = -1 With these arguments added, the complete formula is: Ascending vs. Descending Data is sorted in ascending order (A-Z) by default....

These are the top people, websites, YouTube channels, and Forums you’re going to want to start following. When trying to learn new skills it’s essential to immerse yourself in the topic, and these resources will be a great start! Obviously, you won’t be able to follow all of these, but you should be able to find a few great learning resources you really enjoy. Are you the proud owner of one of these amazing Microsoft Excel resources?...

For such cases, Excel introduced the Watch Window feature in Excel 2010. In this tutorial, you’ll learn how to : Keep track of important cells/formulas using the Watch Window. Use Watch Window to keep track of cells in multiple sheets. Delete cells/formulas from the watch window. Let’s get started. How to Keep Track of Important Cells using Watch Window Suppose you have student’s scores dataset as shown below and you want to keep a track of the average score in the three subjects....

In this worksheet, we have an example we looked at previously. On the left, we have quantity-based discounts, and on the right, we have some random quantities. Let’s set up XLOOKUP to return all results in a single dynamic array. As a first step, I’m going to convert the table on the left into a proper Excel Table. This step isn’t necessary, but it will make it easy to add more discounts to the table without breaking the formula....

But they have a lot of drawbacks that make them a not so great option. In this post, I’ll show you everything you need to know about merged cells including 8 ways to merge cells. I’ll also tell you why you shouldn’t use them and a better alternative that will produce the same visual result. What is a Merged Cell A merged cell in Excel combines two or more cells into one large cell....

where data is an Excel Table in the range B5:B104. The result is a case-sensitive match for “JANET”, which appears as the second “Janet” in the list of names. The final result returned by INDEX is “Fulfillment”. Cell G5 contains a standard formula that is not case-sensitive. Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. Since MATCH is not case sensitive, it returns the first match for “Janet” in row 2 of the table, even though the lookup value is “JANET” in uppercase....

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 …Read more 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....

Inside INT, the value in B5 is divided by 12 and INT simply returns the integer portion of the result, discarding any decimal remainder. The result is then concatenated to a string with a single quote and space character. To get a value for inches, the MOD function is used like this: where number comes from B5 and the divisor is 12. Configured in this way, MOD returns the remainder after division....