How to Pull Data from Another Sheet in Excel


How to Pull Data from Another Sheet in Excel

If your Excel spreadsheet has a variety of information, think about using totally different sheets to manage them. To pull information from one other sheet in Excel, observe this information.

Excel doesn’t simply allow you to work in one spreadsheet—you’ll be able to create a number of sheets inside the similar file. This is beneficial if you’d like to preserve your information separated. If you have been working a enterprise, you may determine to have gross sales info for every month on separate sheets, for instance.

What if you’d like to use a few of the information from one sheet in one other, nonetheless? You may copy and paste it throughout, however this may be time-consuming. If you make modifications to any of the unique information, the information you copied throughout gained’t be up to date.

The excellent news is that it’s not too difficult to use the information from one sheet in one other. Here’s how to pull information from one other sheet in Excel.

How to Pull Data From Another Sheet in Excel Using Cell References

You can pull information from one Excel sheet to one other by utilizing the related cell references. This is a straightforward approach to get information from one sheet into one other.

To pull information from one other sheet by utilizing cell references in Excel:

  1. Click in the cell the place you need the pulled information to seem.
  2. Type = (equals signal) adopted by the title of the sheet you need to pull information from. If the title of the sheet is multiple phrase, enclose the sheet title in single quotes.
    sheet reference in Excel
  3. Type adopted by the cell reference of the cell you need to pull.
    Excel cell reference
  4. Press Enter.
  5. The worth from your different sheet will now seem in the cell.
    pulled date in Excel
  6. If you need to pull throughout extra values, choose the cell and maintain the small sq. in the bottom-right nook of the cell.
    autofill in Excel
  7. Drag down to fill the remaining cells.
    filled cells in Excel

There is another technique that saves you from having to sort in the cell references manually.

To pull information from one other cell with out typing the cell reference manually:

  1. Click in the cell the place you need the pulled information to seem.
  2. Type = (equals signal) after which open the sheet from which you need to pull information.
  3. Click on the cell containing the information that you really want to pull throughout. You’ll see the components change to embrace the reference to this cell.
    select a cell in Excel
  4. Press Enter and the information will likely be pulled into your cell.
    pulled date in Excel

How to Pull Data From Another Sheet in Excel Using VLOOKUP

The technique above works nicely when you’re not planning to do a lot along with your information and simply need to put it into a brand new sheet. However, there are some points when you begin to manipulate the information.

For instance, when you type the information in the July Sales sheet, the names of the gross sales staff can even be rearranged.

Excel data sorted

However, in the Sales Summary sheet, solely the pulled information will change order. The different columns will stay the identical, that means that the gross sales are now not aligned with the proper salesperson.
misaligned cells in Excel

You can get round these points by utilizing the VLOOKUP perform in Excel. Instead of pulling a price straight from a cell, this perform pulls a price from a desk that’s in the identical row as a novel identifier, such because the names in our instance information. That implies that even when the order of the unique information modifications, the information that’s pulled will all the time stay the identical.

To use VLOOKUP to pull information from one other sheet in Excel:

  1. Click in the cell the place you need the pulled information to seem.
  2. Type =VLOOKUP( then click on on the cell to the left. This would be the reference that the VLOOKUP perform will search for.
    vlookup in Excel
  3. Type a comma, after which click on on the sheet that you really want to pull information from. Click and drag over the 2 columns that maintain your information.
    select array in Excel
  4. Type one other comma, after which sort the variety of the column that comprises the information you need to pull throughout. In this case, it’s the second column, so we’d sort 2.
  5. Type one other comma, then FALSE, then a last closed bracket to full your components. This ensures that the perform appears to be like for a precise match on your reference.
    formula in Excel
  6. Press Enter. Your information will now seem in your cell.
  7. If you need to pull throughout extra values, choose the cell and click on and maintain on the small sq. in the underside right-hand nook of the cell.
    autofill in Excel
  8. Drag down to fill the remaining cells.
    filled cells in Excel
  9. Now when you type the unique information, your pulled information won’t change, since it’s all the time searching for the information related to every particular person title.

Note that for this technique to work, the distinctive identifiers (in this case, the names) should be in the primary column of the vary that you choose.

Make Excel Work For You

There are lots of of Excel features that may take a variety of the grind out of your work and assist you to to do issues shortly and simply. Knowing how to pull information from one other sheet in Excel means you’ll be able to say goodbye to limitless copying and pasting.

Functions do have their limitations, nonetheless. As talked about, this technique will solely work in case your figuring out information is in the primary column. If your information is extra advanced, you’ll want to look into utilizing different features corresponding to INDEX and MATCH.

VLOOKUP is an effective place to begin, nonetheless. If you’re having hassle with VLOOKUP, try to be in a position to troubleshoot VLOOKUP errors in Excel.