Consolidate

1. Consolidate Consolidate Multiple Worksheets to a Single Sheet in Excel

2. What are Consolidated Worksheets ? • If you have a group of tables (or lists) it is possible to COMBINE (or consolidate) all this data into one table (or list). • This can be done using the Data > Consolidate dialog box. • You can use Data > Consolidate to combine corresponding values from different sheets onto a summary sheet. • You can consolidate up to 255 source areas. • Your data can be consolidated from several different source, either on the same worksheet, on different worksheets within the same workbook or even in different workbooks.

3. Data > Consolidate Menu • Function - One of the following 11 functions: Average, Count, Count Nums, Max, Min, Product, Stddev, Stddevp, Sum, Var, Varp. • Reference - Specifies the range of cells you select as a source area to consolidate with other source areas listed in the All references box. • All references - Lists the source area references selected for the consolidation. • Top row - This only used when you are consolidating by category. • Left column - This is only used when you are consolidating by category. • Create links to source data - This allows you to import all the detailed data of the source zones, but a generated outline will hide them. Without a link Excel will consolidate only the final values.

4. Main Scenarios • How to Consolidate Data by Position • How to Consolidate Data by Category • How to Select Source Areas for a Consolidation Table

5. Consolidation by position • When the data in the source areas is arranged in the same order and uses the same labels. Use this method to consolidate data from a series of worksheets, such as departmental budget worksheets that have been created from the same template

6. How to Consolidate Data by Position: Steps 1&2 • To consolidate data by position, follow these steps: • Step 1: Type the following data on Sheet1: A1:Letter B1:Code Number C1:More Number A2:A B2:50 C2:62 A3:H B3:99 C3:11 A4:G B4:86 C4:68 A5:K B5:18 C5:31 A6:K B6:67 C6: 9 • Step 2: Type the following data on Sheet2: A1:Letter B1:Code Number C1:More Number A2:M B2:38 C2:17 A3:H B3:53 C3:25 A4:G B4:48 C4:18 A5:C B5:59 C5:53 A6:K B6:78 C6:97

7. How to Consolidate Data by Position – Steps 3-9 • Step 3: Click the upper-left cell of the destination area for the consolidated data. In this example, click cell A1 on Sheet3. • Step 4: On the Data menu, click Consolidate. • Step 5: In the Function list, select the summary function that you want Microsoft Excel to use to consolidate the data. In this example, use Sum. • Step 6: In the Reference box, type each source area you want to consolidate, and then click Add. In this example, type the first area, Sheet1!$A$1:$C$6, and then click Add. Type the second area, Sheet2!$A$1:$C$6 and then click Add. • Step 7: Repeat Step 6 for all of the source areas that you want to consolidate. • Step 8: Under Use labels in, select the Top row check box and the Left column check box (in this example, there are labels both on the first row and also in the left column). • Step 9: Click OK.

8. Data > Consolidate Menu • Function - One of the following 11 functions: Average, Count, Count Nums, Max, Min, Product, Stddev, Stddevp, Sum, Var, Varp. • Reference - Specifies the range of cells you select as a source area to consolidate with other source areas listed in the All references box. • All references - Lists the source area references selected for the consolidation. • Top row - This only used when you are consolidating by category. • Left column - This is only used when you are consolidating by category. • Create links to source data - This allows you to import all the detailed data of the source zones, but a generated outline will hide them. Without a link Excel will consolidate only the final values.

9. Consolidation by category • When the data in the source areas is not arranged in the same order but uses the same labels. Use this method to consolidate data from a series of worksheets that have different layouts but have the same data labels. NOTE: Consolidating data by category is similar to creating a PivotTable. With a PivotTable, however, you can easily reorganize the categories. If you want a more flexible consolidation by category, consider creating a PivotTable.

10. To consolidate data by category, follow these steps: Step1: Type the following data on Sheet1: A2:A B2:50 C2:62 A3:H B3:99 C3:11 A4:G B4:86 C4:68 A5:K B5:18 C5:31 A6:K B6:67 C6: 9 A12:M B12:38 C12:17 A13:H B13:53 C13:25 A14:G B14:48 C14:18 A15:C B15:59 C15:53 A16:K B16:78 C16:97 How to Consolidate Data by Category

11. How to Consolidate Data by Category-Steps 2-8 • Step 2: Click the upper-left cell of the destination area for the consolidated data, which would be cell A1 on Sheet2. • Step 3: On the Data menu, click Consolidate • Step 4: In the Function list, select the summary function that you want Microsoft Excel to use to consolidate the data. In this example, use Sum. • Step 5: In the Reference box, type each source area you want to consolidate and then click Add. Type the first area, Sheet1!$A$2:$C$6 and then click Add. Type the second area, Sheet1!$A$12:$C$16 and then click Add. • Step 6: Repeat Step 5 for all source areas you want to consolidate. • Step 7: Under Use labels in, click to select the Left column check box (in this example, there are labels in the left column). • Step 8: Click OK NOTE: If you want Microsoft Excel to update your consolidation table automatically when the source data changes, select the Create links to source data check box. You cannot create links when source and destination areas are on the same sheet.

12. Consolidating RangesMore Examples • Lets assume that all our data is on the same worksheet and the data is arranged in simple tables, one below the other. • First of all create a table below the other tables that will contain the consolidated data, in this case table "B14:F17". • Select the first cell in this range, i.e. "C15" and then select (Data > Consolidate) to display the Consolidate dialog box. • Altenatively you could highlight the whole range of cells "C15:F17", although Excel will populate the whole range automatically.

13. Regions Example • For this example we are going to use the Sum consolidation function which is the default. • In the Reference box select the first cell range you want to use in the consolidation, in this case "C3:F5". • Press the Add button to add this range to the "All references" list. • In the Reference box select the second cell range, in this case "C9:F11" and press the Add button. Press OK to consolidate the selected ranges and to create the consolidated table of data. When you are linking ranges on the same worksheet you cannot link the data, although the consolidated table can be very easily updated by selecting (Data > Consolidate) and then pressing OK.

14. Consolidating Worksheets in the same Workbook • Lets assume that the have the same data as above but this time the data is displayed on separate worksheets within the same workbook. • There are 2 worksheets in this workbook, one called 2004 and one called 2005. • First lets create another worksheet in this workbook which will contain the consolidated data. • Lets also assume that every worksheet lots identical. The cell ranges you want to consolidate need to be in exactly the same cells on every worksheet. • First of all create a table on the new "totals" worksheet that looks identical the other tables on all the other worksheets. • You can select the whole range of cells to ensure you get the whole range consolidated it is safer to just select the first cell.

15. “Totals" worksheet • In this example we are going to use the Average consolidation function which can be changed in the Function drop-down list.

16. Average consolidation function • In the References box select the "2005" tab and select the range you want to use in the consolidation, in this case "C3:F5". • Press the Add button to add this range to the "All references list". • In the References box select the "2004" tab. You will notice that the corresponding range is automatically highlighted so just press Add.

17. Things to Remember • The method can be used to consolidate up to 255 worksheets into a single worksheet. The number of worksheets that you can have in a workbook is only restricted by the memory on the PC (i.e. how much RAM the PC has). • You can also use the Pivot Table Reports to consolidate data, for more information please refer to the Pivit Tables HELP • To scroll without changing the current selection press Scroll Lock and use the arrow keys. • You can scroll a worksheet without changing the active cell. Press Scroll Lock. • You can also have your changes on one worksheet reflected on other sheets by making your changes to one worksheet, holding down Ctrl and selecting the other worksheets. Press (Edit > Fill > Across Worksheets).

18. Joseph Rubin’s Excel Assistance at http://www.exceltip.com/se/27.html MicroSoft at http://support.microsoft.com/?kbid=214270#appliesto BetterSolutions at http://www.bettersolutions.com/excel/ Excel User at http://www.exceluser.com/help/chart/long001.htm Useful Links

19. Q&A • Consolidate and Business Graphics • …

20. Excel -- Pivot Tables – Grouping Data • In a Pivot Table, you can group the items in a Row or Column field. • For example, items in a date field can be grouped by month, and items in a number field can be grouped by tens.

21. Pivot - Grouping Dates • To group the items in a Date field: • Right-click the Date field button. • Choose Group and Show Detail | Group

22. Pivot - Grouping Dates 2 • In the Grouping dialog box, select one or more options from the 'By' list. • To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes • Click OK If you try to group a date or field, you may see an error message that says, "Cannot group that selection." This problem usually occurs when the field contains records with a blank date/number field, or text in a date/number field

23. Example • PivotSales.xls