In this tutorial, we will walk through the steps of creating a sum across multiple workbooks in Microsoft Excel. Whether you have a large amount of data spread across multiple Excel files or you simply need to consolidate information from different sources, this tutorial will show you how to quickly and easily sum the data in multiple workbooks into one main workbook.
In this example, we have workbooks: Jan, Feb, Mar, and Report. In these workbooks, we have a report of sales from each month, respectively. We want to consolidate all the monthly reports into the ‘Report’ workbook. To do this, here’s a step-by-step guide:
- Prepare the workbooks: Open all the workbooks that contain all the data that you want to retrieve from and the main workbook you want to put on.
- Prepare the formula: In the main workbook, type the equal sign “=” in a cell. Then click ‘View’ and then click ‘Switch Windows’. This opens a prompt to open which workbook to switch to.
- Reference the workbooks: After switching to a different workbook, click the cell you want to get the data from. This will reference the data from the selected workbook to the main workbook.
- Add mathematical operators: After referencing the data, add a plus operator “+” and then reference the data from the other workbooks by repeating step 2 and 3
- Finish the formula. Press Enter to finish the formula.
This feature can update whenever the other worksheets’ values are changed. To do this, enable the prompt when the main worksheet is re-opened.
To manually update the data, click the ‘Data’ tab and then click ‘Edit Links’ in the Queries and Connections Ribbon. This will open a window, and then click the ‘Update Values’ button.
