Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-14-2019, 04:09 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 36
Joanne is on a distinguished road
Default Consolidate data from different Excel files

I have four Excel files containing data - Jan to Mar, Apr to Jun, etc. Is it possible to consolidate this data into one annual spreadsheet without copying and pasting?



Many thanks
Joanne
Reply With Quote
  #2  
Old 02-14-2019, 08:13 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 171
Marcia is on a distinguished road
Default

Try the consolidate tool under Data. I use that to consolidate payrolls from January to December.
Reply With Quote
  #3  
Old 02-14-2019, 08:46 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 36
Joanne is on a distinguished road
Default consolidate spreadsheets

Hi Marcia

Thank you for your reply. I saw the Consolidate option and have Googled it, but still can't work out how to use it

I will have another go and if it still doesn't work for me - I'll be back!

Regards,
Jo
Reply With Quote
  #4  
Old 02-14-2019, 08:51 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 171
Marcia is on a distinguished road
Default

Open a blank sheet, go to data tab, select consolidate. A dialogue box will pop out, click the browse button, select your January file. I presume that you have headers in your files so check the "row headers", click "add". Repeat the same process until the last sheet that you want to add in the consolidated data. To make the consolidation process easier, remove all merged cells.
Reply With Quote
  #5  
Old 02-14-2019, 08:57 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 36
Joanne is on a distinguished road
Default

I've tried that but I keep getting the message "Consolidation reference is not valid"
Reply With Quote
  #6  
Old 02-14-2019, 09:11 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 171
Marcia is on a distinguished road
Default

Did you select the range? do not include the row labels.
Reply With Quote
  #7  
Old 02-14-2019, 09:16 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 36
Joanne is on a distinguished road
Default

I've opened a new sheet, clicked on Consolidate, Browse and click on the first of my files. The file path appears in the Reference box. When I click on Add, the error message appears.
Reply With Quote
  #8  
Old 02-14-2019, 04:44 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 171
Marcia is on a distinguished road
Default

Our internet signal flew off somewhere last night. After opening your file through the browse buttton, click the mouse at the end of the file path, after the exclamation mark, then go to the file and select the range, say b1:ac74. Add.
Attached Images
File Type: png Capture.PNG (40.3 KB, 8 views)
Reply With Quote
  #9  
Old 02-18-2019, 02:54 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 36
Joanne is on a distinguished road
Default

I have been having trouble entering the range. When I browse and add the file I want, the file path is longer than the typing area. If I click on the file path and try to arrow along to the exclamation mark, the range appears midway. However, I finally managed to get the four files up with the range in the correct place and the final total is correct but I am getting peculiar dates, eg 28/12/32 instead of 28/04/16. Any ideas?
Reply With Quote
  #10  
Old 02-18-2019, 04:18 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 171
Marcia is on a distinguished road
Default

Quote:
Originally Posted by Joanne View Post
I am getting peculiar dates, eg 28/12/32 instead of 28/04/16. Any ideas?

It's quite a pain learning new features of Excel but once we get the hang of how they operated, they're a tremendous tool in quickly making our tasks done. Do not include the dates in the range to be consolidated because it reads the dates as numbers resulting in total values.
Reply With Quote
  #11  
Old 02-18-2019, 04:21 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 36
Joanne is on a distinguished road
Default

Oh dear - dates are crucial in my consolidated worksheet. Never mind, I will have to consolidate them manually.

Many thanks for all your help though - now I understand how it works, hopefully I will be able to make use of this feature for another task.
Reply With Quote
  #12  
Old 02-18-2019, 04:28 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 171
Marcia is on a distinguished road
Default

If the dates are located in the left column or they are the row labels, check the appropriate boxes so they won't be included in the "sum" function.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Move & Consolidate Data meggenm Excel Programming 1 08-27-2017 12:54 PM
Add data from two Excel Files to Word Doc Template nivolaped Word 4 04-10-2016 04:23 PM
Merge the data from different excel files. Sandhya Excel Programming 2 10-10-2015 02:53 PM
Consolidate data from different excel sheets Sandhya Excel Programming 1 10-10-2015 12:28 AM
How to replicate, consolidate and count cell data in excel for data consoldation. Loran Excel 7 06-05-2014 01:07 AM


All times are GMT -7. The time now is 07:35 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft