skip to content »

Consolidating multiple spreadsheets into one

consolidating multiple spreadsheets into one-25

Click the upper left cell on the spreadsheet and drag to select the area to merge.Make sure to select extra rows after the data to make room for the data you're adding.

For an explanation of the first three bootstrap methods, see my post “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve” at The second column is the present value of each coupon payment using the sum of all the previous discount rates multiplied by the semiannual coupon payment.When you're working in Excel, it can be frustrating to switch between multiple spreadsheets.Either manually type this header in the master spreadsheet or manipulate the original spreadsheets so the A1 cell is blank.Production forecasting with Excel usually entails using straight-line regression. asked how to adapt this method when there is seasonality in the sales data. This shows five years of historical data and a straight-line forecast. It is important that the historical data contain a multiple of 12 months. The data could run from January 2006 through December 2010, or it could run from July 2007 through June 2011. I always say that you should never choose Months without also choosing Years; however, calculating seasonal indices is the one time that you can choose only Months (Figure 3). You want to change the pivot table to show averages, as well as the numbers as a percentage of the column total. Open the Show Values As dropdown and choose % of Column Total (Figure 4). If the average monthly sales is 100%, then the value in each month shows how that month compares to the average.Until 11g, Oracle SQL did not have a direct mechanism for allowing multiple values from the same column to be displayed in the same row of output.

SQL is designed not to disallow repeating values on a single line of output, but Oracle has several tricks for allowing repeating groups of same-column values on a single line of output.

If you have five Decembers, then you should have five Junes. You will have a pivot table identical to the original data set. On the Design tab, open the Grand Total dropdown and choose Off For Rows and Columns. Next, you should divide each sales figure by the seasonal index for that month.

Having five of some month and six of other months will skew the seasonal indices. Select one cell in the data and choose Insert, Pivot Table, OK. The formula in Figure 6 (below) uses a few clever tricks to do this quickly.

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.

Then click "Add" to add this spreadsheet to the master sheet.

These instructions are for Excel 2013, but the process is similar in earlier versions of the program.