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

Try the consolidate tool under Data. I use that to consolidate payrolls from January to December.

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

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.

I've tried that but I keep getting the message "Consolidation reference is not valid"

Did you select the range? do not include the row labels.

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.

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

Capture.PNG (40.3 KB, 17 views)

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?

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.

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.

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.