Using Multiple Workbooks & Consolidation in Microsoft Excel

ContentsIntroduction to Multiple Workbook ApplicationsWorking with Multiple Workbook Applications using normal keyboard actionsUsing Help of Windows Arrangement for easy consolidationAutomating Consolidation by Category through Excel’s Consolidate option

IntroductionWith Microsoft Excel you can do work with single Workbook which may contains data for your own department or company but when you are working in a medium to large environment every now & then there are times when you are required to work with multiple workbooks & Excel provides multiple ways to perform this task both with speed and ease

Manually Linking WorkbooksTo manually linking workbooks you just need to open all the workbooks in a single instance of Excel 2007By single instance we mean you have to open Excel once and open all the workbooks inside this Excel instanceIf you, otherwise, open an Excel instance from start menu & open again another Excel instance from start menu now there are two Excel instances running side by side and in this case you cannot share data in the workbooks which is opened in the first instance with the workbooks which are opened in the second one

What we want?We need the 3 workbooks to be consolidated in a forth workbookAs discussed open all the three workbooksCreate a new workbook by pressing Ctrl+NNow for getting the consolidated value you need to go to the new workbook than have to press = now to navigate to the first workbook “Income Statement - Branch A - 16-10-2009.xlsx” you can do this by mouse or you can press Ctrl+Tab it will navigate you between the open workbooks within this Excel Instance

What we want? …So in my case the expression will be:='[Income Statement - Branch A - 16-10-2009.xlsx]Sheet1'!$B$3 +'[Income Statement - Branch B - 16-10-2009.xlsx]Sheet1'!$B$3 +'[Income Statement - Branch C - 16-10-2009.xlsx]Sheet1'!$B$3

Using Multiple Workbooks & Consolidation in Microsoft Excel

A quicker wayYou can also do the same a quicker and more easier wayYou can do the same without using the Ctrl+Tab key and do it easily using your mouseFor doing same we can use on of Excel’s functionality which we not use commonly i.e. Arrange WindowsWe can arrange the windows so all the windows will be displayed on our single Excel Application and then we can easily press = and click on the cells we want to consolidate

Arranging windows to make consolidation easierFor arranging windows you have to select the View Tab -> click on Arrange All

Arranging windows to make consolidation easier – cont …Once you click on Arrange All, Excel will display the following Dialog BoxYou can select any of the formatin which you want to arrange thewindows in this case I will use theTiled arrangement which suits my need

Arranging windows to make consolidation easier – cont …As soon as you select tiled and press Ok it will show all the open Workbooks in this instance at once in your screen. Note: that the Workbooks which are minimized will not be arranged you have to maximize them first and then do the arrange windows process

Arranging windows to make consolidation easier – cont …Now you can see that it is easier to consolidate the workbooks by just pressing = and click on the cells on the respective Worksheets from which you want the data to be consolidated

Automatic ConsolidationExcel 2007 have a very wonderful feature with the name of Consolidate it will do the chores of consolidation in minutes which otherwise might take hours / days depending upon the length of the sheetsFor doing the same you have to click on Data Tab -> Consolidate option

Automatic Consolidation – cont …As soon as you click on Data Tab -> Consolidate it will show you the following DialogThis is the place where you will select the ranges of the workbooks which you want to consolidateYou can select the function you want to perform while consolidating Functions may have any of the followingOnce you have selected the desired range you can add the same or if you want any range which is added can be removedThere are two kinds of consolidation one is by position and other one is by category in my point of view by position is used rare so we wont discuss it, for performing consolidation by category you have to check top row & left column

Automatic Consolidation – cont …Suppose the data we have contains titles at left only and there is no top titlesIn this case you just have to click use labels in left column

Automatic Consolidation – cont …Or else your data only have top row containing titlesWe only have to checkuse labels in top row

Automatic Consolidation – cont …In a case when our sheet contains both top labels & left labels You have to check both uselabels in top row & left column

Step by step process for Automatic ConsolidationSelect the new workbook & place the cell where you want the consolidated table to appearClick on Data TabClick on Consolidatein the Data Tools command group of the Data Tab

Step by step process for Automatic Consolidation – cont …On the Consolidate dialog box click on Reference and select the entire range which should include the column labels and row titles, to include range from the same file you have to select the sheet from which you want to include data and from other workbook you need to press either Ctrl+Tab or navigate through mouse

Step by step process for Automatic Consolidation – cont …By click on the Reference box, selecting other workbook by mouse or Ctrl+Tab and selecting the range you want to add including the titles at top and left similar kind of screen appears

Step by step process for Automatic Consolidation – cont …Once the desired range for the first workbook selected press the Add buttonRepeat the process for all other workbooks which you want to include in the consolidationYou can see thisdialog contains 3ranges which I wantto consolidate

Step by step process for Automatic Consolidation – cont …You can now check the Checkboxes which displays use labels in top row and left column

Step by step process for Automatic Consolidation – cont …As soon as you click the OK button Microsoft Excel will consolidate the data as fast as blink of eyes

Step by step process for Automatic Consolidation – cont …If you want to update the data you just have to click on Data Tab & Consolidate and just have to click Ok to update the data without any effortOne final comment, if you want that the data will be automatically updated whenever the source data is updated you just have to click the link to source data checkbox in the consolidate dialog box

Step by step process for Automatic Consolidation – cont …You can see that Excel have done two things it have created the links to source data and also created groups, you can note that at left of the row numbers + signs appeared and at the top of these 1 & 2 appeared which shows that it created gorups

Step by step process for Automatic Consolidation – cont …If you want to open a single group just press the + sign besides it, you will notice that it will open up the source file’s data on your screens. If you want to open all the groups just press button which is captioned by 2 at the top

ConclusionYou can create workbooks which are linked to each otherThere are one of three ways to consolidate workbooksFirstly, you can use = and Ctrl+Tab or mouse to click on other workbooks to select and use appropriate operators i.e. +, -, * or /You can do it with speed by using Excel’s arrange window optionThe wonderful option of automatic consolidation may also be used to consolidate data automaticallyPlease visit:www.exceladvise.comFor morePlease comment if you like it, or have anysuggestions.28Photo Courtesy – www.freedigitalphotos.net

Using Multiple Workbooks & Consolidation in Microsoft Excel

Recommended

More Related Content

What's hot (20)

Viewers also liked (20)

Similar to Using Multiple Workbooks & Consolidation in Microsoft Excel (20)

More from Excel (10)

Recently uploaded (20)

Using Multiple Workbooks & Consolidation in Microsoft Excel