The cells are now converted to numbers only without the formula.Delete the old columns and add a new header to completer the revised spreadsheet.First, open Excel and open both of the files you're looking to merge. Now open a new spreadsheet, which will be the destination for your merged data.From this new spreadsheet, click on the "Data" tab and click on "Consolidate." The Consolidate menu will pop up with Function and Reference fields.She maintains several blogs on travel, music, food and more.She is also a contributing writer for Suite101 and has articles published on e How and Answerbag.By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or that use the same labels (when you consolidate by category).
You will see the Reference field populate with the spreadsheet information (it will look something like this: '[DS Excel Book 1.xlsx]Sheet1'! In the Consolidate menu, under Use Labels, click to select "Top Row" or "Left Column," or both depending on where your labels are.To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data.(If need be, insert a new sheet in the workbook by clicking the Insert Worksheet button.) To begin consolidating sheets in different workbooks, open a new workbook.For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years.If you used a template to create each worksheet you’re consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets.Close the backups when you finish and return to the original two spreadsheets.Adjust the column and row headings so that they follow the same format in both spreadsheets.This is known as merging spreadsheets, and it's a fast way to combine data without the hassles of copying, pasting and formatting.These instructions are for Excel 2013, but the process is similar in earlier versions of the program.By Greg Harvey Excel 2016 allows you to consolidate data from different worksheets into a single worksheet.Using the program’s Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets.