Copying & pasting data cells into one worksheet | Excel forum

Resolved · High Priority · Version 2016

Fri 24 Feb 2017, 11:36 Delegate Paulina said...

Paulina has attended:
Excel Advanced course

Copying & pasting data cells into one worksheet

Hey All,

During our last week's Excel session with Jens we were told the best way to create Pivot table including data from several worksheets is to paste all the data into one worksheet.

I have 12 different worksheets (one per month). When I attempt to paste the February data into the worksheet with the January data, Excel gives me the message:

To copy all cells from another worksheet to this worksheet, make sure you paste them into the first cell (A1).

However, doing this would override the January data that currently exists in the worksheet.

What is the best way to get all the data cells into one worksheet so I can create Pivot table based on the all-year data?

Many thanks,
Paulina

Fri 24 Feb 2017, 11:49 Trainer Jens said...

RE: Copying & pasting data cells into one worksheet

Hi Paulina,

Thank you for the forum question.

I have never seen the message you have got. You should be able to paste your data where you want in your worksheets.

Another solution could be to write a sql query in MS Query, but you will need to have sql knowledge to do this.

If you want, I can have a look at your workbook.

You can send it to:

info@stl-training.co.uk

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 24 Feb 2017, 14:16 Delegate Paulina said...

RE: Copying & pasting data cells into one worksheet

Hi Jens,

It has actually worked now, thank you.
The other problem I have now is that all the data cannot fit in one worksheet.
Is there a limit for the number of records in one worksheet?

As I'm trying to paste the data from the final month, the msg I get is that I can't paste here because the copy area and paste area are not the same size.

Can you please assist with this one?
Thank you,
Paulina

Fri 24 Feb 2017, 14:30 Trainer Jens said...

RE: Copying & pasting data cells into one worksheet

Hi Paulina,

The limitation in a sheet is 1048576 rows. If you have more, Excel is may be not the right tool. Excel is not a database. You still have options. You can use PowerPivot which can handle any number of records or you can keep your 12 worksheets and write a union query in sql and create your PivotTable from the query.

Both options are not straight forward and both options use the sql language to union the data.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Activating the formula bar with the keyboard in Excel 2010

The formula bar in Excel 2010 contains a number of different formulas. However, it is time consuming to open the formula bar every time and insert the formula using the mouse. Instead, you can activate the formula bar with the keyboard. Simply press the F2 button on the keyboard then the information in the cell will expand allowing you to edit the information in the cell.

View all Excel hints and tips