Excel linking sheets and combining data from multiple sheets.

Link Excel work sheets to combine data and save time

Excel linking sheets and working with data from multiple sheets.

In this Excel video tutorial I will discuss Excel linking sheets and combining data from multiple sheets.

1. To enter  data on multiple spreadsheets in the same workbook.
2. 0pen all spreadsheets concerned by CTRL-click for non-adjacent and Shift-click for adjacent spreadsheets on the tabs with the spreadsheet label. All tabs will now be underlined meaning that they are all linked.
3. Select cell to receive data on the first spreadsheet (or group) and type data or = to copy data or a formula. It will now simultaneously appear on all the selected spreadsheets.
4. Don’t forget to de-select by Ctrl or shift clicking when you need to enter data on only one spreadsheet. Or right click and select ‘ungroup sheets’ from dropdown.
5. When copying data, the formatting of the source sheet is not copied. The destination sheet can be formatted without affecting the source sheet.
6. To consolidate data or do calculations using data from several spreadsheets in the same workbook.
7. All spreadsheets must be setup exactly the same so that equivalent data is in the exact same cell address in all the spreadsheets. Do this by starting the spreadsheet in the same cell on all the spreadsheets or deleting any empty rows or columns which may alter the cell addresses.
8. Open a new spreadsheet to receive the consolidated data. It can have a different layout if necessary.
9. Select the cell to receive the consolidated data. Enter ‘=’ and type of calculation eg ‘Sum’ or if you only need to copy data from a one other spreadsheet then only type the = sign ( Remember in this case to press enter before navigating away from the source document)
10.  Navigate to the first spreadsheet but clicking the spreadsheet I/D tag. Select the cell which contains the data to be consolidated ( be sure it has the same cell address across all the spreadsheets).
11. Now if you want to sum data from several sheets,  click on the next spreadsheet I/D tag or select multiple spreadsheets. Excel will auto-select the equivalent cell across all selected spreadsheets. The formula bar will reflect this eg =SUM(‘Rainfall 2016:Rainfall 2020’!B3
12. Press enter, excel will close brackets and put the answer in the selected cell in the consolidated spreadsheet.
13. If you need to apply this formula to more cells. They can be auto populated in the consolidated spreadsheet in the usual way by dragging from the bottom left corner of the cell with the formula across to the end of the row to populate all the columns. Let go then drag down to to populated all the rows
14. Changes to any spreadsheet will be updated on the consolidated spreadsheet.
15.       Additional calculations like ave can be made on the consolidated spreadsheet, these will not affect the other spreadsheets, but updates to the other spreadsheets will be reflected in these additional calculations on the consolidated spreadsheet.
16. Now to demonstrate what happens if all the spreadsheets are not laid out exactly the same. We will add a blank column to the left of one of the spreadsheet. This changes the totals in the consolidated spreadsheet.

Here is a video tutorial on linking worksheets and combining data:

Here are more Excel tutorials:

Comments are most welcome, try this and send us some feedback☺️ Why not visit our YouTube channel and browse through our other videos? http://www.youtube.com/c/AnnErasmus

Short Disclaimer/disclosure: My videos/articles are for informational and educational purposes only, you should consult with a professional before attempting anything seen or done in my videos/articles. Please read full disclaimer here: https://basiclowdown.com/disclaimer-disclosure-page/