Consolidating workbooks excel 2016
TIP You can also save your Excel 2016 workbook either as an Excel 97–2003 template (.xlt) or as a macro-enabled Excel 2016 workbook template (.xltm).For information about using macros in Excel 2016 workbooks, see Chapter 12, “Automate repetitive tasks by using macros.” When you create a new workbook by using the tools found in the Backstage view, the New page displays the blank workbook template, built-in templates, a search box you can use to locate helpful templates on Office.com, and a set of sample search terms.This chapter guides you through procedures related to using a workbook as a template for other workbooks, linking to data in other workbooks, and consolidating multiple sets of data into a single workbook.After you decide on the type of data you want to store in a workbook and what that workbook should look like, you probably want to be able to create similar workbooks without adding all of the formatting and formulas again.
Before you start: if your worksheets are identical, it's probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data. However, the beauty of the Consolidate feature is that it can easily sum, count, average, etc this data by looking at the labels.
You can leave the workbook’s labels to aid in data entry, but you should remove any existing data from a workbook that you save as a template, both to avoid data entry errors and to remove any confusion as to whether the workbook is a template.
You can also remove any worksheets you and your colleagues won’t need by right-clicking the tab of an unneeded worksheet and, on the shortcut menu that appears, clicking Delete.
You can always copy data from one worksheet to another, but if the original value were to change, that change would not be reflected in the cell range to which you copied the data.
Rather than remembering which cells you need to update when a value changes, you can create a link to the original cell.