Did you know that you can use the Paste Link command to quickly consolidate data from multiple Microsoft Excel worksheets into one? The consolidated data can be more easily analyzed and combined on a regular or ad-hoc basis. Furthermore, the consolidated data will be updated whenever changes are made in the source worksheets. Let’s check this out in this tip.
In our example, we consolidate sales figures from the East and West regions into one worksheet for easy analysis.
Example exercise – Excel Tips & Tricks – Quick way of summarizing data
You’re welcome to download the workbook to practice!
Applies To: Microsoft Excel 2010 and 2013
1. Select the data on the East Coast worksheet.
a. Select cell B10.
b. Press CTRL + A.
2. Press CTRL and C to copy the data.
3. Select the summary worksheet.
4. Right click in cell A2 .
5. Select the Paste Link
6. To Paste the formats: Right click on the data and select Paste Special.
7. Select Format and click OK.
8. To Paste the column widths: Right click on the data and select Paste Special.
9. Select Column Widths and click OK.
10. Select the data on the West Coast worksheet.
a. Select the range A4 to C4.
b. Press CTRl + SHIFT + Down Arrow.
11. Press CTRL and C to copy the data.
12. Select the summary worksheet.
13. Right click in cell A27 .
14. Select the Paste Link.
15. To Paste the formats: Right click on the data and select Paste Special.
16. Select Format and click OK.
As you can see the data has been consolidated onto the summary worksheet. If you make changes on the East or West worksheets, it will automatically update the summary worksheet. However, you can’t update the East or West worksheets by editing data on the summary worksheet.
