Question about Microsoft Excel for PC

1 Answer

Consolidate few sheets under 1 excel


I have 8 to 10 worksheet contain same format with diff data but at the end I have to make master file by consolidating all files will guide how to do it
u can mail me on my personal id
i e

Posted by on

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points


    An expert that got 5 achievements.


    An expert who has written 7 tips or uploaded 3 video tips


    An expert who has written 3 tips or uploaded 2 video tips.

  • Expert
  • 58 Answers
Re: consolidate few sheets under 1 excel

It is actually very simple on the new worksheet in the first row just type:
in cell - A1: =[Book1]Sheet1!A1
in cell - B1: =[Book1]Sheet1!B1
in cell - C1: =[Book1]Sheet1!C1

Where Book1 is the worksheet(filename) and Sheet1 is the sheet with the data, just drag down the results with the magic square and continue to do so on all of your data worksheets.

Let me know if you need more help

and Please use our community and not emails

Good luck


Posted on Dec 18, 2007

Add Your Answer

0 characters

Uploading: 0%


Complete. Click "Add" to insert your video. Add


3 Points

Related Questions:

4 Answers

Excel sheet has been lost how to recover

Lost Excel file can be recovered from:
  1. Recycle Bin - If you have deleted the file with normal delete.
  2. Backup - If have maintained the backup of file.
  3. Email - It may possible that you have mailed the file to someone.
  4. 3rd Party Recovery Software - Recuva, Stellar Data Recovery ...

Oct 29, 2012 | Microsoft Office Excel (License Only)...

1 Answer

Dear Sir, In case there are atleast 80 files or more having same format containing datas in columns in each file with different figures, I want to merge all file in a single sheet in one shot. Kindly...


If the column names and orders are same across files, then you can directly use the MS Excel's import data function, this will do your job.

Alternatively, if you want to do it manually, import each file in separate excel worksheet using data import wizard or simple copy paste of data (in latter case you have to use Text-to-Col feature of excel), and then manually append all figures (copy-paste in one go) to any external excel sheet.

Then finally, export/save as that external sheet to any filename of your choice.

Hope this helps.


Mar 24, 2009 | Microsoft Excel for PC

1 Answer

Merging problem

Use the Help function in excel and search for "Consolidate". This will show you how to consolidate data from multiple worksheets into one worksheet.

Feb 19, 2009 | Microsoft Excel for PC

1 Answer

Input data

If you want to transfer your data into SAS, SPSS, or some other program, follow these guidelines:
The cells in Row 1 should contain the column's eventual data set name. Each name should be a relatively short and unique acronym that clearly identifies the data. It should begin with a letter and contain only letters, numbers, or an underscore ( _ ) where spaces would naturally fall. Avoid using special characters such as $, &, @, in variable names. Since each row represents the values from one subject, the first column(s) should contain one or more variables that give each subject a unique identifier. They become especially important if you need to merge two or more data files.
In Excel, data formats are defined for a range of cells rather than for a complete column. For this reason it is important that each entire column, including cells with missing or uncollected data, have one, and only one, format. Actually, you do not need to format the entire column, only the portion you will eventually use. Highlight that portion and select the appropriate format from the Format/Cells option. Do not select formats that will enter commas, dollar signs, or other visual enhancements. Numeric, text, and date formats (e.g. mm/dd/yy is often a good choice) are probably the only formats you'll ever need.
The "Split" option (under the "Window" pull-down menu) keeps the row of variable names and the columns of identifiers in view, whatever range of cells in the worksheet you may need to review. First place the cursor at the most extreme upper left-hand corner where data entry begins (e.g., the intersection of Row 2 and the column in the upper left-hand corner where data appear) and then select "Split" from this menu. For any row or column of the worksheet you move to, you'll know exactly which variables you are observing (column names) and their associated ID values (rows).
For versions of Excel later than 4.0, one file can contain multiple worksheets. By default, the tabs at the bottom of these sheets are supplied names ("sheet1," "sheet2," etc.). You can change these names by clicking this space with your mouse and entering a new name. Use the same conventions for first-row variable names: use a short acronym of the page contents that begins with a letter, use only letters or numbers, and enter the underscore ( _ ) where a space naturally falls.

Jan 05, 2009 | Sage Instant Accounts 8.0 (013604ug)

1 Answer

Excel 2003 / 2007 vba code

A similiar thing happened to me once and I used the format
"Sheets("Lab Work Order Master").Copy(,ActiveSheet)" instead of
"Sheets("Lab Work Order Master").Copy After:=ActiveSheet"

You may also want to try using a sheet index instead of "ActiveSheet" to refer to your sheet.
P.S. Don't forget to rate me. Wish you the best.

Oct 17, 2008 | Microsoft Office Standard for PC

5 Answers

Forgot password to open Excel sheet

If you mean that you can open the Workbook and read the Worksheet - then all you have to do is select all and paste it to another Worksheet. This will allow for changes of formulas etc.

If you mean the whole Workbook - then I dunno.

In future, either use the same password for everything (easy to remember) or Protect the work with nothing entered into the password field (leave this blank). This last will protect the work from people who try to guess your password, but will be easy for you to remember - eg every password is left blank.

If you must use unique passwords, then keep a record of them somewhere other than on your computer.

Sorry I can't help with the "Workbook" solution - but somebody else out ther may be able to do so.

Apr 25, 2008 | Microsoft Office Professional 2007 Full...

2 Answers

Unsure of correct formula

You can add a reference from the worksheet 1 to all other worksheets

Is it OK?

Mar 08, 2008 | Microsoft Excel for PC

1 Answer

Export data in excel shld yoeet through VB

When i first figured out how to pull data from SQL and put the results in an excel file i referenced these two articles....
Reading and writing excel file using VB.NET (
Get the Values From DataBase and Stored into excell Sheet (

This is the code i ended up using.... (check out those links to see how you need to import the ms office excel reference file with visual basic)

Const stcon As String = "Provider=SQLNCLI;server=xxxxx;database=xxxxx;uid=xxxxx;pwd=xxxxx;DataTypeCompatibility=80"
Dim stSQL As String = "select * from scs_rate_class_money where irate_book = 124 and snew_used = 'U' and sclass = '2' and splan = 'T4' and sopt_code = 'F1'"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
'Open the connection.

'Open the recordset.
With rst
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly , _
.ActiveConnection = Nothing 'Disconnect the Recordset.
End With
'Close the connection
cnt.Close ()
Dim exp As Export = New Export()
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet )
Dim xlWSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim xlRange As Microsoft.Office.Interop.Excel.Range = CType(xlWSheet, Microsoft.Office.Interop.Excel.Worksheet).Range("A2")
Dim xlCalc As Microsoft.Office.Interop.Excel.XlCalculation
Dim i As Short

'Turn off Excel's calculation.
With xlApp
xlCalc = .Calculation
.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
End With
'Write the fieldnames.
For Each fld In rst.Fields
xlRange.Offset(0, i).Value = fld.Name
i = i + 1
'Populate the range.
xlRange.Offset(1, 0).CopyFromRecordset(rst)
'Close the recordset.
'Make Excel available to the user.
With xlApp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlCalc
End With
'Release variables from memory.
fld = Nothing
rst = Nothing
cnt = Nothing
xlRange = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing

Jan 03, 2008 | Business & Productivity Software

1 Answer

Consolidate few sheets under 1 excel

What version of Excel are you working with. Newer versions allow you to have multiple spreadsheets grouped together into what is called a, "Workbook." To view the sheets separately, you simply click on the tabs at the bottom of the workbook. The easiest way I know of to join spreadsheets together into a single workbook is to open all of them at the same time, then choose, "View / Arrange All," and they'll be small, but you can grab each one by its tab and drag it to the workbook you want them in. Then you save the workbook (give it a new name like, "Accounting Roll-UP," or, "Consolidated Books," or whatever best describes it) making sure to close all the (now empty) spreadsheets without saving your changes to them (or else you'll lose your 'back-up' data). Voila! You open one file, and all your spreadsheets are available with the click of a mouse.

Dec 18, 2007 | Microsoft Office Standard for PC

1 Answer


There is an easier way. in your workbook with the worksheets to consolidate, insert a new blank sheet. from the DATA menu, select consolidate. click ADD and go to the first sheet, select the required information (best to select all headings aswell). Continue this procedure for each sheet. at the bottom of the Consolidate dialog box, check all three options for links. and click OK. This should now give you a consolidation of all your sheets.

Hope this solves your problem

Oct 09, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Microsoft Excel for PC Logo

Related Topics:

130 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers


Level 3 Expert

18259 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides