How to Override Data from One Sheet to Another in Excel

Sometimes, you may want to copy or transfer data from one sheet to another in Excel, and override the existing data in the destination sheet. For example, you may have a master sheet that contains the latest data, and you want to update another sheet with the same data. Or, you may have a summary sheet that pulls data from multiple sheets, and you want to override the data in the summary sheet whenever the source sheets change.

There are different ways to achieve this goal, depending on your needs and preferences. You can use simple cell references, VLOOKUP formulas, or INDEX and MATCH formulas to link data from one sheet to another. You can also use copy and paste options, such as Paste Link or Paste Special, to create dynamic links between sheets. Alternatively, you can use Power Query to import and transform data from multiple sheets into one sheet.

In this article, we will focus on how to use formulas to override data from one sheet to another in Excel. Formulas are useful when you want to automate the process of overriding data, and when you want to perform calculations or lookups on the data.

Procedures

To override data from one sheet to another using formulas, you need to follow these steps:

1. Identify the source sheet and the destination sheet. The source sheet is the sheet that contains the original data, and the destination sheet is the sheet that you want to override with the data from the source sheet.
2. Decide which method you want to use to link data from the source sheet to the destination sheet. You can use simple cell references, VLOOKUP formulas, or INDEX and MATCH formulas. Each method has its own advantages and disadvantages, which we will discuss later.
3. Enter the formula in the destination sheet, and specify the cell range or criteria that you want to override. You can enter the formula in one cell, and then drag it down or across to fill the rest of the cells. Alternatively, you can enter the formula as an array formula, and press Ctrl + Shift + Enter to apply it to multiple cells at once.
4. Check the results and make sure the data in the destination sheet is updated with the data from the source sheet. If the data in the source sheet changes, the data in the destination sheet should also change automatically.

Explanation

Simple Cell References

The simplest way to override data from one sheet to another is to use simple cell references. This means that you enter the address of the cell in the source sheet, preceded by the sheet name and an exclamation mark, in the destination sheet. For example, if you want to override cell A1 in Sheet2 with the value of cell A1 in Sheet1, you can enter the following formula in cell A1 in Sheet2:

=Sheet1!A1

This formula will copy the value of cell A1 in Sheet1 to cell A1 in Sheet2. If the value of cell A1 in Sheet1 changes, the value of cell A1 in Sheet2 will also change accordingly.

You can use this method to override a range of cells from one sheet to another, by entering the address of the range in the source sheet, followed by the sheet name and an exclamation mark, in the destination sheet. For example, if you want to override the range A1:B10 in Sheet2 with the values of the range A1:B10 in Sheet1, you can enter the following formula in cell A1 in Sheet2:

=Sheet1!A1:B10

This formula will copy the values of the range A1:B10 in Sheet1 to the range A1:B10 in Sheet2. If the values of the range A1:B10 in Sheet1 change, the values of the range A1:B10 in Sheet2 will also change accordingly.

The advantage of using simple cell references is that they are easy to use and understand. You can quickly override data from one sheet to another by typing the cell or range address in the source sheet, followed by the sheet name and an exclamation mark, in the destination sheet.

The disadvantage of using simple cell references is that they are not flexible or dynamic. If you insert or delete rows or columns in the source sheet, the cell or range address in the destination sheet will not adjust automatically. This may cause errors or incorrect results in the destination sheet. Also, if you rename or move the source sheet, the cell or range address in the destination sheet will become invalid, and you will see a #REF! error.

VLOOKUP Formulas

Another way to override data from one sheet to another is to use VLOOKUP formulas. VLOOKUP stands for Vertical Lookup, and it is a function that allows you to look up a value in the first column of a table, and return a value from another column in the same row. For example, if you have a table of employee names and salaries in Sheet1, and you want to override the salary of a specific employee in Sheet2, you can use the following formula in Sheet2:

=VLOOKUP(A2,Sheet1!A1:B10,2,FALSE)

This formula will look up the value of cell A2 in Sheet2, which is the employee name, in the first column of the table A1:B10 in Sheet1, which is the source sheet. If it finds a match, it will return the value from the second column of the same row, which is the salary. If it does not find a match, it will return a #N/A error. The last argument, FALSE, means that the function will look for an exact match, not an approximate match.

You can use this method to override a range of cells from one sheet to another, by entering the VLOOKUP formula in the destination sheet, and specifying the lookup value, the table array, the column index number, and the match type. For example, if you want to override the range B2:B10 in Sheet2 with the salaries of the employees in the range A2:A10 in Sheet2, you can enter the following formula in cell B2 in Sheet2:

=VLOOKUP(A2:A10,Sheet1!A1:B10,2,FALSE)

This formula will look up the values of the range A2:A10 in Sheet2, which are the employee names, in the first column of the table A1:B10 in Sheet1, which is the source sheet. If it finds a match, it will return the value from the second column of the same row, which is the salary. If it does not find a match, it will return a #N/A error. The last argument, FALSE, means that the function will look for an exact match, not an approximate match.

The advantage of using VLOOKUP formulas is that they are more flexible and dynamic than simple cell references. You can override data from one sheet to another based on a criteria, such as a name, a code, or a date, rather than a fixed cell or range address. You can also insert or delete rows or columns in the source sheet, and the VLOOKUP formula in the destination sheet will still work, as long as the lookup value and the table array are still valid.

The disadvantage of using VLOOKUP formulas is that they are more complex and error-prone than simple cell references. You need to make sure that the lookup value, the table array, the column index number, and the match type are correct and consistent. You also need to make sure that the first column of the table array contains the lookup value, and that the table array is sorted in ascending order if you use an approximate match. Otherwise, you may get wrong or unexpected results in the destination sheet.

INDEX and MATCH Formulas

A third way to override data from one sheet to another is to use INDEX and MATCH formulas. INDEX and MATCH are two functions that can work together to perform a lookup based on a row and a column criteria. INDEX returns a value from a specific position in a range or an array, and MATCH returns the relative position of a value in a range or an array. For example, if you have a table of employee names, departments, and salaries in Sheet1, and you want to override the salary of a specific employee in a specific department in Sheet2, you can use the following formula in Sheet2:

=INDEX(Sheet1!C1:C10,MATCH(A2,Sheet1!A1:A10,0),MATCH(B2,Sheet1!B1:B10,0))

This formula will use the INDEX function to return the value from the range C1:C10 in Sheet1, which is the salary column, based on the row and column position returned by the MATCH functions. The first MATCH function will look up the value of cell A2 in Sheet2, which is the employee name, in the range A1:A10 in Sheet1, which is the name column, and return the relative position of the match. The second MATCH function will look up the value of cell B2 in Sheet2, which is the department name, in the range B1:B10 in Sheet1, which is the department column, and return the relative position of the match. The last argument, 0, means that the function will look for an exact match, not an approximate match.

You can use this method to override a range of cells from one sheet to another, by entering the INDEX and MATCH formula in the destination sheet, and specifying the array, the row number, and the column number. For example, if you want to override the range C2:C10 in Sheet2 with the salaries of the employees in the range A2:A10 in Sheet2, based on the departments in the range B2:B10 in Sheet2, you can enter the following formula in cell C2 in Sheet2:

=INDEX(Sheet1!C1:C10,MATCH(A2:A10,Sheet1!A1:A10,0),MATCH(B2:B10,Sheet1!B1:B10,0))

This formula will use the INDEX function to return the values from the range C1:C10 in Sheet1, which is the salary column, based on the row and column positions returned by the MATCH functions. The first MATCH function will look up the values of the range A2:A10 in Sheet2, which are the employee names, in the range A1:A10 in Sheet1, which is the name column, and return the relative positions of the matches. The second MATCH function will look up the values of the range B2:B10 in Sheet2, which are the department names, in the range B1:B10 in Sheet1, which is the department column, and return the relative positions of the matches. The last argument, 0, means that the function will look for an exact match, not an approximate match.

The advantage of using INDEX and MATCH formulas is that they are more powerful and versatile than VLOOKUP formulas. You can override data from one sheet to another based on multiple criteria, such as a row and a column criteria, rather than a single criteria. You can also look up values in any column of the table array, not just the first column, and you can use any order of the table array, not just ascending order.

The disadvantage of using INDEX and MATCH formulas is that they are more complicated and cumbersome than VLOOKUP formulas. You need to use two functions instead of one, and you need to make sure that the array, the row number, and the column number are correct and consistent. You also need to use array formulas if you want to override a range of cells at once, and press Ctrl + Shift + Enter to enter them.

Example

To illustrate how to use formulas to override data from one sheet to another in Excel, let us consider the following scenario and example.

Suppose you have two sheets in your workbook, named Sheet1 and Sheet2. Sheet1 contains the following table of product information, such as product ID, name, category, price, and quantity.

Sheet2 contains the following table of sales information, such as product ID, name, quantity sold, and revenue.

You want to override the data in Sheet2 with the data from Sheet1, based on the following criteria:

• You want to override the product name in Sheet2 with the product name in Sheet1, based on the product ID.
• You want to override the revenue in Sheet2 with the product of the price and the quantity sold in Sheet1, based on the product ID.

You can use the following formulas in Sheet2 to achieve this goal:

• To override the product name in Sheet2 with the product name in Sheet1, based on the product ID, you can use the VLOOKUP formula in cell B2 in Sheet2, and drag it down to fill the rest of the cells:

=VLOOKUP(A2,Sheet1!A1:E5,2,FALSE)

This formula will look up the value of cell A2 in Sheet2, which is the product ID, in the first column of the table A1:E5 in Sheet1, which is the source sheet. If it finds a match, it will return the value from the second column of the same row, which is the product name. If it does not find a match, it will return a #N/A error. The last argument, FALSE, means that the function will look for an exact match, not an approximate match.

• To override the revenue in Sheet2 with the product of the price and the quantity sold in Sheet1, based on the product ID, you can use the INDEX and MATCH formula in cell D2 in Sheet2, and drag it down to fill the rest of the cells:

=INDEX(Sheet1!D1:D5,MATCH(A2,Sheet1!A1:A5,0))*C2

This formula will use the INDEX function to return the value from the range D1:D5 in Sheet1, which is the price column, based on the row position returned by the MATCH function. The MATCH function will look up the value of cell A2 in Sheet2, which is the product ID, in the range A1:A5 in Sheet1, which is the product ID column, and return the relative position of the match. The last argument, 0, means that the function will look for an exact match, not an approximate match. The formula will then multiply the value returned by the INDEX function with the value of cell C2 in Sheet2, which is the quantity sold, to get the revenue.

After entering the formulas in Sheet2, the table will look like this:

You can see that the data in Sheet2 is updated with the data from Sheet1, based on the criteria. If the data in Sheet1 changes, the data in Sheet2 will also change automatically.

Other Approaches

Besides using formulas, there are other approaches to override data from one sheet to another in Excel, such as:

• Copy and paste options: You can use the copy and paste options, such as Paste Link or Paste Special, to create dynamic links between sheets. For example, you can copy the data from the source sheet, and then right-click on the destination sheet, and choose Paste Link or Paste Special > Values > Paste Link. This will create a link between the sheets, and the data in the destination sheet will update whenever the data in the source sheet changes. However, this method may not work well if you want to override data based on a criteria, or if you want to perform calculations or lookups on the data.
• Power Query: You can use Power Query, a tool that allows you to import and transform data from multiple sources, to override data from one sheet to another in Excel. For example, you can use Power Query to combine data from multiple sheets into one sheet, and then refresh the query whenever the data in the source sheets changes. You can also use Power Query to filter, sort, merge, append, group, pivot, unpivot, or aggregate data from multiple sheets. However, this method may require some advanced skills and knowledge of Power Query, and it may not be compatible with older versions of Excel.