We usually maintain the data in multiple worksheets to check the data for each instance and to view them individually but it would be great to merge all the data from different worksheets in Excel spreadsheet to analyze it.Consolidate feature in Excel 2010, lets you to pull-each record from the worksheet in to one master worksheet, which adds-up all data from spreadsheets.You can also click another function to perform a calculation on the data as the sheets are merged. Click the “Browse” button and navigate to the workbook containing the first spreadsheet to merge -- this is necessary only if the spreadsheet is in a different workbook.Click the file, and then click “OK.” The file path is added to cell references for the merge.In her spare time, she advises Sistema England, a music charity and is an ISPA committee member.She is a regular theatre and concert-goer: jukebox musicals are her guilty pleasure.If all of the spreadsheets use the same category headings, you can merge data by category name regardless of where the categories and data are located on the spreadsheets.You can also drag to select the desired cells to merge in the wizard to reference the cells by position only, regardless of category name or data type. Open Excel, and then open the first of the spreadsheets to be merged.
If all spreadsheets are located in the same workbook, the file path is not necessary in the References field. Click the “Top Row” check box to merge by categories listed along the top row.
You then create a worksheet called ‘Year’ that adds the four quarters together to give you the sales for the year. Note that, if you add an extra worksheet before Quarter 1 or after Quarter 4, this won’t be picked up in the formula, but if you slot one in between Quarter 1 and Quarter 4 it will be.
On your ‘Year’ worksheet, you could enter – manually or by selecting the cells – the formula: ’Quarter 1′! Author Sarah Verge Sarah is an accountant with a background in IT.
As you can see in the screenshots below that we have included three worksheets containing students records in each exam.
Exam 1: Repeat the same procedure in other sheets to assign them name range exam2 and exam3 respectively.