Posts

Excel XLOOKUP function has finally arrived. If you have been using VLOOKUP or INDEX/MATCH, I am sure you’ll love the flexibility that the XLOOKUP function provides. In this tutorial, I will cover everything there is to know about the XLOOKUP function and some examples that will help you know how to best use it. So let’s get started! What is XLOOKUP? XLOOKUP is a new function is Office 365 and is a new and improved version of the VLOOKUP/HLOOKUP function....

One common task many Excel users have to do is to extract the last name from the full name. While it may seem like an easy task, it can get complicated (especially when you’re dealing with data that is not consistent). In this tutorial, I will show you five different ways to extract the last name from the full name in Excel. I will cover different scenarios, where you only have the first and the last name in the dataset, or the first, middle, and last name....

where data is an Excel Table in the range B5:D16. The result is a list of the values in Group A that are within +/- 0.05 of 1.2. Background reading This article assumes you are familiar with Excel Tables and the FILTER function. If not, see: Excel Tables - introduction and overview Value is within tolerance - formula example The FILTER function - introduction and overview Basic FILTER function example (video) Dynamic Array Formulas (paid training)...

Here’s a little puzzle for you…how can you use Excel to generate a list of dates that are weekends only? For example, a list of Saturday Sunday pairs like this: A couple years ago, I found and described a formula that will do it using the WEEKDAY function and some tricky date logic handled with IF: With a date in A1, you can enter the formula in A2 and drag down to get your list of weekend dates....

In this example, a $5000 payment is made each year for 25 years, with an interest rate of 7%. To calculate future value, the FV function is configured as follows like this in cell C7: with the following inputs: rate - the value from cell C5, 7%. nper - the value from cell C6, 25. pmt - negative value from cell C4, -100000 pv - 0. type - 0, payment at end of period (regular annuity)....

where data is the named range C5:I13. The result is an array of with 7 sums, one for each column in the range, as seen in row 15. Note: in older versions of Excel you can use the MMULT function, as explained below. With the BYCOL function In Excel 365, the most straightforward way to generate subtotals for each column is with the BYCOL function. The purpose of BYCOL is to process data in a “by column” fashion....

To highlight duplicate columns, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula used to highlight duplicate columns is: Note that row references are fully absolute, while cell references are mixed, with only the row locked. With a helper row If you don’t mind adding a helper row to your data, you can simplify the conditional formatting formula quite a bit. In a helper row, concatenate all values in the column....

In this example we’ll create a custom list for a few European countries. In the Options window. Now we can add our list. Now to use this list all you have to do is type in one of the items in the list then drag the cell cursor using the lower right corner. It’s a pretty neat feature that can save you some time.

Once you have a chart, you may want to add or remove data series. If all data series are in contiguous cells, it’s easy to just select the chart, and drag the data range selectors to include or exclude cells as needed. However, if the data series are not in contiguous cells, you’ll need to use another approach. For example, here if we want to plot all 3 test results, but ignore the two quiz results, we can’t use the data range selector....

Let’s take a look. To apply a fill, start by selecting the cells you want to fill. Then, open the Format Cells dialog box and navigate to the Fill tab. There are two basic types of fills. The first is a solid background color. Just select the color you’d like to use from the color grid. You’ll see the color appear below in the sample area. When you’re happy with the color, just click OK to apply....

Let’s take a look. Here we have a table that shows hours worked and gross pay for a small team. Everyone is paid the same hourly wage, so the formula used to calculate pay uses an absolute reference to cell C3. Now lets say you want to convert these formulas to use a named range. First, I’ll name cell C3 “hourly_rate” using the name box. Just type the name and press Return....

You don’t need to know any formula or coding to quickly crunch thousands of rows of data and create quick summaries out of it. All you need to know is how to drag and drop and a little bit of knowledge of how to create a Pivot Table. In this tutorial, I will show you various ways to delete a Pivot Table from Excel. How to Delete a Pivot Table in Excel Since you’re reading a tutorial about removing or deleting a Pivot Table, I am assuming you already have a Pivot Table(s) in place that you want to remove....

Sometimes, when you have the text data, you may want to extract the first word from the text string in a cell. There are multiple ways you can do this in Excel (using a combination of formulas, using Find and Replace, and using Flash Fill) In this tutorial, I will show you some really simple ways to extract the first word from a text string in Excel. Extract the First Word Using Text Formulas Suppose you have the following dataset, where you want to get the first word from each cell....

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. The function name “VLOOKUP” is spelled incorrectly, and the formula returns #NAME? When the formula is fixed, the formula works properly: Range entered incorrectly In the example below, the MAX and MIN functions are used to find minimum and maximum temperatures....

First create a new sheet called “Comments” for the purpose of writing out all the comments. Then add this bit of code into the visual basic editor. Now all you have to do is run the code and all your comments will appear in the sheet you created called “Comments” and you will be able to click the hyperlink to go to the cell containing the comment.

Sometimes, it’s so frustrating that a new page is printed just to accommodate a few extra lines of data (which also leads to wastage of paper). There are some simple techniques you can use to make sure you Excel data in printed on one page (or in less number of pages in case you’re printing a big report). In this Excel tutorial, I will share some methods you can use to print the Excel sheet on one page....

Let’s take a look. The easiest way to update a pivot table manually is to right-click anywhere in the table and choose “Refresh” from the menu. This causes Excel to reload the source data and display the latest information in the pivot table. If the source data has not changed since the last refresh, the pivot table will not change. When you Refresh a pivot table, it’s important to understand that the only thing changing is data running through the pivot table....

Unfortunately the acquisition fell through but the development team had already finished coding the Easter egg into the core code. The Easter egg addition was long forgotten and was never removed from the core Excel code but can still be access during the Excel loading screen. While Excel is loading press Up, Up, Down, Down, Left, Right, Left, Right, B, A on your keyboard. If you entered the code correctly during start up, you will be able to play Contra from within Excel....

When to use Excel MAX Function MAX function can be used when you want to get the largest value from a set of values. What it Returns It returns the largest value from a set of values. Syntax =MAX(number1, [number2], …) Input Arguments number1 – the first number from a set of values from which you want to get the maximum value. [number2], .. – (optional) second number from a set of values from which you want to get the maximum value....

When to use Excel ROUND Function ROUND function can be used when you want to return a number rounded to a specified number of digits. What it Returns A number rounded to a specified number of digits. Syntax =ROUND(number, num_diguts). Input Arguments number – the number that you want to round. num_digits – the number of digits to which you want to round the number argument. Additional Notes If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places....