Posts

When the formula is copied down, it returns an index for each column listed in column H. Getting an index like this is useful when you want to refer to table columns by index in other formulas, like VLOOKUP, INDEX and MATCH, etc. The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH function: The nice thing about this reference is that it will automatically adjust to any changes in the table....

When the formula is copied down, it returns an name for each column, based on index values in column H. This range goes into INDEX for the array argument, with the index value supplied from column H: The result is the name of the first item in the header, which is “ID”. Although the headers are in a horizontal array, with values in columns, INDEX will use the row number as a generic INDEX for one-dimensional arrays like this and correctly return the value at that position....

As the formula is copied down, it returns the first name from each name in column B. RIGHT function The RIGHT function takes two arguments, the text itself and num_chars, which specifies how many characters to extract: For example, if we use “apple” for text and 3 for num_chars, we get “ple”: So, at a high level, this formula uses RIGHT to extract characters from the right side of the name....

First, let’s look at the problem we’re trying to solve. Here we have monthly sales data. At the moment, we only have 5 months, but we’ll be adding more data over time. Now, if I insert a column chart, everything works fine. But notice if I add new data, the chart doesn’t change. It still plots the original data. To update the chart, I’ll need to expand the range manually How can I make this chart automatically include new data?...

Using a combination of Excel functions and the date of birth, you can easily calculate age in Excel. You can either calculate the age till the current date or between the specified period of time. The technique shown here can also be used in other situations such as calculating the duration of a project or the tenure of the service. How to Calculate Age in Excel In this tutorial, you’ll learn how to calculate age in Excel in:...

Excel has some powerful functions to calculate the number of days between two dates in Excel. These are especially useful when you’re creating Gantt charts or timelines for a proposal/project. In this tutorial, you’ll learn how to calculate the number of days between two dates (in various scenarios): Calculating the Total Number of Days Between Two Dates in Excel Excel has multiple ways to calculate the days between two dates....

Excel allows a variety of date formats and depending where you’re from the default formats will be different. A date like 31st December 2016 might appear formatted in Excel in any of these ways: Dec, 31 20162016-12-3131-12-201612/31/2016 (mm dd yyyy format is only used in the US)31/12/201616-Dec-31 There are many more possible formats and you can even create your own with custom formats. In this example of Dec, 31 2016, the date is really stored in Excel as the value 42735 regardless of the format....

Step 1: Replace Any Formula With Text Replace any formula with text in the range which you want to transpose. Step 2: Copy And Paste Transpose Now we can transpose our selection with copy and paste special. Step 3: Change The Transpose Range Back To Formula Now we just need to change the text in the transposed version back to formulas. Now we have a transposed version of the original range and all the formula’s have maintained their original references....

The formula then uses the MATCH function to find the position in the array of the first TRUE value. This position is used by the INDEX function to return the value in ResultsRange from the same position. In our example, EXACT({“Aaa”;”aaa”;”Bbb”;”bbb”;”Ccc”;”ccc”},”aaa”) results in the following array of Boolean values. This results in one TRUE value in the second position where we have an exact match to “aaa“. MATCH(TRUE,{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0) will then return 2 as a result because the first TRUE value is in the second position....

If you don’t want them to change anything, you have the option to either protect the entire worksheet/workbook or protect certain cells that have important data (that you don’t want the user to mess up). But even when you protect the worksheet, the end-user can still click on a cell and see the formula that’s used for calculations. If you want to hide the formula so that the users cannot see those, you can do that as well....

Here we have a simple lookup table that shows material costs for various heights and widths. The formula in K8 uses the INDEX and MATCH functions to retrieve the correct cost based on width and height values entered in K6 and K7. Note the that the lookup is based on an approximate match. Since values are in ascending order, MATCH checks the values until a larger value is reached, and then steps back and returns the previous position....

Let’s take a look. To insert a cell horizontally, select the cell to the right of where you want the new cell. Then, click the Insert menu on the ribbon and choose “Insert Cells”. When the dialog box appears, select “Shift cells right” and click OK. An empty cell appears and cells are shifted to the right. You can also use the right-click menu to insert cells. To delete a cell horizontally, first select the cell you want to delete....

Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update. You need to force a refresh every time there are changes. Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table. This tutorial covers a couple of ways to do this....

And when working with multiple sheets, you may sometimes need to change their names. In this Excel tutorial, I will show you some simple ways to rename a sheet in Excel (including a keyboard shortcut). And in case you want to rename all the sheets as one go (such as adding a prefix or suffix to all the sheets), check out the section on using VBA to do this (it’s not complicated)....

Selecting columns and rows is handy when you want to move information around, delete information, or when you want to copy a row or column. Let’s take a look. To select a column in Excel, just click the letter in the column heading. You’ll see Excel immediately select the entire column. If you want to select more than one column, and the columns are together, just click a column letter and drag to expand your selection....

If you’re new to Excel and wondering how to subtract in Excel, you’re at the right place. In this tutorial, I will show you how to subtract in Excel (subtract cells, ranges, columns, and more). I will start with the basics and then would cover some advanced subtraction techniques in Excel. I also cover how to subtract dates, times, and percentages in Excel. So let’s get to it! Subtracting Cells/Values in Excel Let’s start with a really simple example, where I have two values (say 200 and 100) and I want to subtract these and get the result....

Let’s take a look. To move some information in Excel using drag and drop, first select the cells you’d like to move. Next, hover over the edge of the selection until you see the cursor change to a symbol with four arrows. Then, just drag the selection to a new location. When you release the mouse, the contents of the cells are moved to the new location. To copy information with drag and drop, the approach is very similar....

When to use Excel SUMIF Function SUMIF function can be used when you want to add the values in a range if the specified criteria is met. What it Returns It returns a number that represents the sum of all the numbers for which the specified criteria is met. Syntax =SUMIF(range, criteria, [sum_range]) Input Arguments range – the range of cells against which the criteria is evaluated. It could be numbers, text, arrays, or references that contain numbers....

Let’s take a look. Here we have a table that shows the number of widgets sold over a 12-month period. There are six different kinds of widgets, so the table has six rows of data. What we want to do is show the total number of widgets sold each month in row 13, and the total number of each widget type sold for all 12 months in column O. First, let’s add a total for each month on row 13....

In this worksheet we have two tables. In the first table, we have order data. You can see that we’ve got a date, customer id, product, and total. In a second sheet we have customer data. We’ve got first and last name, street, city, state and so on. And because the id is in the first column, we can use VLOOKUP to extract all of the data in this table to the right of the id....