add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Consolidating data from a number of different tables is a task that is not easy for Excel users to perform. Excel offers data consolidation techniques that use formulas, and consolidation techniques that do not use formulas (Consolidate).
Below are several examples of the types of data organized in lists and tables that you consolidate in your work in Excel workbook sheets.
© Consolidation of monthly tables containing sales receipt data.
© Consolidation of tables containing salary data for various departments in the organization.
© Consolidation of trial balance tables to create tables that will become the basis for preparing financial statements and profit and loss statements.
© Consolidation of tables with budget data broken down by department/profit center.
Consolidating Data Tables
There are several methods for consolidating data tables.
Consolidate with formulas if the row & column headers of all worksheets are identical
If you have several worksheets and every worksheet has an identical structure - that is, the headings in row 1 and column A are identical from sheet to sheet, you can create a total worksheet using simple formulas.
Example: In the workbook, the sheets January, February and March contain tables with salary data. On every sheet, the items in cells A2:A8 are identical. On every sheet, the employees listed in B1:H1 are identical and never change.
Add a new worksheet called Total. Use a formula like the one shown in cell B2 to add the other sheets.
The formula in Cell B2 is =SUM (January:March!B2).
The technique for entering a formula to sum a single cell on a number of sheets
1. Select Cell B2 in the sheet called Total.
2. In the cell, type =SUM, and press Ctrl+A (the shortcut for displaying the formula argument box).
3. In the first argument box, select the name of the first sheet in the range January.
4. Press Shift, and select the last sheet in the range March.
5. Click OK.
Consolidate with Copy & Paste if the columns of all worksheets are identical but the rows contain different records
By using the technique described below, you can easily consolidate tables to a single data table, as well as sort, filter and create subtotals.
You may have several worksheets with identical columns, for example, months. These worksheets all have similar text in column A, but the text labels in each row differ from worksheet to worksheet.
Example: a budget workbook with expenses for each department. Every worksheet has identical columns, but each department has a different list of expense items in column A.
Adding a department name column to each table
Add a column to each table that contains the name of the department in all the cells.
1. Add a column (see the figure on next page), and type the department name in the first cell.
2. Copy the department name to all the cells in the range, to the end of the list of items.
Consolidating the tables
Select the sheet Department 1; select and copy the data table (without the totals); and paste (press Enter) the table for Department 1 into Cell A1 in the Consolidate sheet in the workbook. From the Department 2 table, copy the table without the headers or the totals, select the Consolidate sheet, select the first empty cell at the end of the table in Column A, and paste the Department 2 table (press Enter). Repeat this procedure, and copy the Department 3 table without the headers into the Consolidate sheet, below the new table.
The result – A single data table (see the figure below).
At this stage, you can sort, filter and create a PivotTable, as necessary.
Consolidation
From the Data menu, select Consolidate.
Rules for consolidating data with the Consolidate technique
© The structure of the tables must be identical. The headings of all rows and the left-most columns in the tables must contain the same topic. The number of columns and the number of rows do not have to be identical; neither does the internal order of the text.
© Tables must have a single label row and a single column for labels.
© The cells in the table’s data range must contain only numeric data.
Excel consolidates data by identifying corresponding text crossed between the header row and the leftmost column.
Example:
In the figure below, the top header row holds the employee name, and the leftmost column contains Gross Salary & Deductions. The number of employees and the order of their names in the header row, and the number and order of Gross Salary & Deductions in the leftmost column, do not correspond across all tables.
The data range contains all the cells from Cell B2 (see figure below).
Crossing the employee name Eric with Gross Salary in the table January (see figure) returns the result in Cell B2. The amount in the cell is 2,540. In the table February, the crossed cell of the text Eric with Gross Salary is E2, and the amount in the cell is 2,758. The Consolidate technique identifies the crossed text in the tables and returns a total.
Consolidation options
© Consolidating data without links to the source data.
© Consolidating data with links to the source data.
Consolidating without links
Stage 1 – definition of names in the data tables
In the tables, select only the data range, including headers (without selecting subtotals or totals).
1. Select the sheet January, select Cell G5, press Ctrl+Shift+Home (to quickly select a range from any cell in the sheet, up to A1).
2. Press Ctrl+F3 (the Define Name dialog box).
3. In the Name box in the workbook, enter the name AJanuary. Click OK.
4. Select the sheet February, select Cell F6, and perform steps 1-3. In step 3, enter the name BFebruary.
5. Select the sheet March, select Cell E5, and perform steps 1-3. In step 3, enter the name CMarch.
Explanation
© Defining Names in the tables makes it easier to perform the consolidation.
© Referring to Names in the data table should not include the total rows or columns.
© In defining Names, assign the names alphabetically. This is particularly important when using multiple consolidation ranges in a pivot table as discussed at the end of this chapter.
Press F3 to open the Paste Name dialog box. The names are sorted alphabetically.
Stage 2 – the Consolidate dialog box
1. Open a new sheet in the workbook.
2. Select Cell A1. From the Data menu, select Consolidate.
The Function Box
Select the function for consolidating the data. The default function is SUM.
The Reference Box
In this box, enter the reference (Name) temporarily, until the reference is transferred to the All References dialog box.
You can consolidate data tables from three sources: data tables in the active workbook, data tables in an open workbook, and data tables in a closed workbook.
© Data table in the active workbook
Select the Reference box, and press F3. Select the Name of the reference, and click OK.
© Data table in an open workbook
Select the Reference box. From the Excel menu, select Window, and select the open workbook. Choose the sheet containing the data table, select it, and substitute the table reference in Name, after the file name and the exclamation point.
© Data table in a closed workbook
Select the Reference box, and click Browse. Select the name of the file containing the table to be consolidated, enter the Name you defined for the data table, and then click Add. The Name of the reference is transferred from the Reference box to the All References dialog box.
Add other data tables. Select the Reference box, press F3 (if the data table is in the active workbook), select the Name of the reference, and click Add.
The All References Dialog box
The All References dialog box contains the names of the tables that will be consolidated.
Use Labels In
Select both the checkboxes in the Use Labels In option. The names of employees and Gross Salary & Deductions are the labels in the example presented. Selecting these boxes causes the text to be transferred to the consolidation table in the sheet.
The Delete Button
Use this button to delete a table from the All References dialog box. Select the Name of the table in the All References dialog box, and click Delete.
Stage 3 – Consolidate the data
1. In the Consolidate dialog box, select the Reference box, press F3, and select the name AJanuary.
2. Click OK, and click Add.
3. Repeat steps 1-2, and add the tables BFebruary and CMarch to the All References dialog box.
4. Select both checkboxes in the Use Labels In option.
5. Click OK.
The result: the consolidation of data tables into values without formulas.
Refreshing data
The result of consolidating data tables is only in the values. Changing the data in the source tables does not update the data in the consolidated tables.
Solution
Delete the data in the Consolidate sheet. Select Cell A1, and from the Data menu, select Consolidate. In the All References dialog box, you will see the names of the data tables. The names are saved in the Consolidate dialog box, in All References. Click OK.
Adding or changing a consolidation formula
1. Delete the data in the Consolidate sheet, and select Cell A1.
2. From the Data menu, select Consolidate.
3. Open the Function option, and select the Count function.
4. Click OK.
The result of consolidating with the Count function returns the number of appearances of every Employee Name and/or Gross Salary & Deductions in the table.
In the figure below, the consolidation was performed three times in the same sheet. In each consolidation, a different function was chosen. Each time the technique is performed, select a different cell in the sheet and perform the Consolidation.
Consolidating with links
1. Delete the data in the consolidated table, and select Cell A1.
2. From the Data menu, select Consolidate.
3. Select the Create links to source data option.
4. Click OK.
Note the results of the consolidation. The formula cells contain links to the source data in the tables. On the left side of the sheet, in the continuation of the sheet’s header row, notice the subtotal buttons. Pressing Button 1 will show only the subtotals. Pressing Button 2 will open and expand the data.
Click the + sign to the left of one of the items. Notice the Gross Salary details of the employee Eric, as well as the total (SUM formula) of his gross salary.
As opposed to consolidating without links, refreshing data is automatic provided you do not add other data tables or add rows or columns to the tables.
Adding data tables
Delete the consolidated data from the sheet by selecting the sheet. Select the button in the corner of the sheet’s header row and column, right-click, and select Delete (do not press the Delete key, since only the data is deleted without the subtotal buttons). Select Cell A1, and from the Data menu, select Consolidate.
Select the Reference box, and press F3. Select the name of the new table you defined, and click OK. Click Add (to transfer the reference to All References), and click OK.
Copying and coloring rows in the subtotals
Try to copy the results of the consolidation. Select a single cell in the current region, press Ctrl+*, and press Ctrl+C. Select a new sheet, and press Enter.
Note the results – all the data is copied, including data in the hidden rows.
Solution
Select the visible cells and isolate them from the hidden cells.
1. Press Group & Outline Button 1 (to the left of the header row) to show sub-totals.
2. Select one of the cells in the current region, and press Ctrl+*.
3. Press F5, or from the Edit menu, select Go To Special.
4. In the Go To dialog box, click Special. Select Visible cells only.
5. Click OK.
The result – only visible cells are selected.
6. Copy the visible cells. Press Ctrl+C.
7. Move to another sheet in the workbook, select Cell A1, and press Enter.
Note the results – only the subtotals are copied, and the results are only values.
Color the subtotal rows
1. Return to the sheet with the subtotals. Use the technique described above to make sure only the visible cells are selected, and repeat steps 1-7.
2. Click the Fill Color icon on the Formatting toolbar, and select any color.
3. Click Button 2. Only rows with subtotals are colored.
Comparing Lists
The data consolidation technique allows you to compare lists quickly and easily.
In Chapter 8, Summing and Counting, refer to the technique for comparing lists by using the COUNTIF formula.
With the consolidation technique, you can compare two or more lists without using a formula.
Look at the figure: List 1 is in column A, and List 2 is in column B.
1. Add Column B. In Cell B1, type "List number".
2. In Cells B2:B7, enter the number 1.
3. In Cells D2:D7, enter the number 2.
4. Cut Cells C2:D7 and paste them into Cell A8.
The result is shown in figure to the right.
1. Select cell A1. Press Ctrl+*, press Ctrl+F3, and define a name for the list.
2. From the Data menu, select Consolidate.
3. In the Reference box, press F3 and paste the Name you defined for the list.
4. Click Add, select both Use labels in checkboxes, and click OK.
The result is shown in the figure.
The number that appears in Column B is the total number of lists in Column B before Consolidation.
If the result = 1, the name appears in List 1 and does not appear in List 2.
If the result = 2, the name appears in List 2 and does not appear in List 1.
If the result = 3, the name appears in both lists (1+2=3).
Comparing Three or More Lists
Using the technique described above, paste these lists one list below the other.
Note – the list number should be factors of 2 (or any other mathematical combination that you choose).
For List 1, use 2^0 or the number 1. For List 2, use 2^1 or the number 2. For List 3, use 2^2 or the number 4.
The consolidation results in a series of numbers from 1 to 7, in which:
1,2,4 = the name appears in only one list.
3,5,6 = the name appears in two lists, 3=2+1, 5=4+1,6=2+4.
7 = the name appears in all three lists.
Consolidating Data Tables by Using a PivotTable, Multiple Consolidation Ranges
Another technique for consolidating data is using PivotTable, Multiple Consolidation Ranges. For further information and an explanation of the use of PivotTables for data analysis, see Chapter 22, PivotTable.
1. Use the examples that are presented here – salary tables using the Consolidate technique.
2. Select an empty sheet in the workbook. Select Cell A1.
3. From the Data menu, select PivotTable Report.
4. In the PivotTable Wizard, in Step 1, select the Multiple Consolidation Ranges option and click Next >.
5. In Wizard Step 2A, leave the default setting, Create a single page field for me, and click Next >.
6. In Wizard Step 2B (the consolidation tables are located in the active workbook), select the Range option.
7. Press F3, select the first name AJanuary, and click OK.
8. Click Add. The name of the table is transferred to the All Ranges box.
9. Repeat this step for the names BFebruary and CMarch(both tables), and transfer them to the All Ranges box.
In order to consolidate data from an open or closed workbook, follow the same steps under the Reference box section of this chapter.
10. Click Next >.
11. In Excel 97 for Step 3 of 4, click Next >.
12. In the final step, click Finish. Notice the PivotTable in the sheet.
If the PivotTable toolbar is not displayed? Select one of the toolbars, right-click, and select PivotTable.
13. Format the data in the PivotTable – select one of the cells in the active region, and from the PivotTable toolbar, click PivotTable Field.
14. In the PivotTable Field dialog box, make sure the function for summing data is the SUM function. Click Number, and select the number formatting you prefer.
15. Click OK, and click OK again.
Change the names of the fields in the PivotTable by double-clicking the gray buttons with the text – row, column and page 1 (Cells A4, B3, A1).
16. Instead of Row, type Gross Salary & Deductions.
17. Instead of Column, type Employee Name.
18. Instead of Page 1, type Month.
Organize the rows and columns in the PivotTable
The default order the rows is alphabetical. To reorder into a logical format:
Example 1:
Select Cell A6, Gross Salary. Move the mouse to the upper border of Cell A6. When the mouse cursor changes, click the mouse, drag the row, and place it as the first row, before Row 5.
Example 2:
The item Gross Salary is located in the center of Gross Salary & Deductions. To move Gross Salary to the beginning of the list, type *Gross Salary in Cell A6 (the asterisk goes before the text), select Cell A5, and click the Sort Ascending icon. This will force Gross Salary to the top of the list.
Example 3:
Put the cellpointer in A5 and type Gross Salary. Hit Enter, and the 401k entry in A5 will move to Row 6.
Move the location of the fields from column and row to page
Click and drag the Gross Salary & Deductionsfield from Row to Page. Drag to the upper left area of the PivotTable, below Month.
Click and drag the Employee Namefield from Rowto Page. Drag to the upper left area of the PivotTable, below Month.
List the names of the months (tables) in the Month field
1. Click and drag the Month field to a row in the PivotTable (see figure on page 314).
2. Change the text Item 1 by typing directly into the cell January.
3. In the same fashion, change the text Item 2 to February, and Item 3to March.
Item 1 represents Table number 1. In the example, the name of the table is AJanuary. Make sure the PivotTable does not relate to the names of the tables, but that it relates to the entire reference range as an item.
Be careful to define names in alphabetical order, to prevent errors in identification when changing the name of an item.
Refreshing, adding or deleting a data table
Select one of the cells in the table, and click the PivotTable Wizard button in the PivotTable toolbar. In step 3 in the dialog box that opens, select <Back.
To delete a data table, select the name of the table, and click Delete.
To add a data table, select the Range box, press F3, click Add, and click Finish.
To refresh data, click Refresh Data (red exclamation point on the PivotTable toolbar).
In the figure below, the example report contains a list of gross salaries according to employee name.