Row capacity in Excel

Resolved · High Priority · Version 2010

Thu 23 Jul 2015, 16:42 Delegate Ali said...

Ali has attended:
Excel Intermediate course

Row capacity in Excel

I have a list of all the postcodes in the United Kingdom, and they are longer than the amount of rows in an Excel spreadsheet.

I have convereted the file to Microsoft Access so I can access it there, but to copy the postcodes from Access to differing Excel spreadsheets, will take ages as Access only allows me to copy and paste 65k each time.

Is there a way I can copy and paste from Access to differing Excel sheets in one go?

Fri 24 Jul 2015, 10:49 Trainer Jens said...

RE: Row capacity in Excel

Hi Ali,

Thank you for the forum question.

You have many options to do what you want. First of all Excel is not limited to 1048576 rows (Records). In Excel 2010 and newer versions we can handle any number of records by using PowerPivot. This is an amazing new tool which can handle any number of data sources and any number of records. If you want to have a look at this option you will need to download it from Microsoft's website. It is integrated in Excel 2013 and Excel 2016.

Another solution can be to link Excel to the Access database. You can also query the Access database from Excel to tell Access which postcodes you want in the different worksheets.

You will find the tools you need on the Data tab in the Get External Data group.

Copy and paste a huge amount of data is never a solution I would use.

I hope this can help you to do what you want.

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 Jul 2015, 17:55 Delegate Ali said...

RE: Row capacity in Excel

Dear Jens,

Thank you for your reply. However, when I attempt to upload the Access spreadsheet onto PowerPivot, it does not allow me to. It does not find the sheet that the postcodes are on.

Furthermore, on Access, the sheet that has all of the postcodes on is under a different icon than the choices i.e. Table 1 or Table 2.

Fri 24 Jul 2015, 20:19 Trainer Jens said...

RE: Row capacity in Excel

Hi Ali,

Have you connected the database to the PowerPivot window?

It is difficult for me to say what is wrong I have never had any problems connecting PowerPivot to external databases.

When you from the PowerPivot window click onget external data from Access you need to select the database and a list with all the tables will be shown. Select the tables you want to import to the PowerPivot window and click finish.

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

Mon 27 Jul 2015, 09:28 Delegate Ali said...

RE: Row capacity in Excel

Dear Jens,

The problem I am finding is that it is not finding the table which I wish to select from my access spreadsheet..

Mon 27 Jul 2015, 10:03 Trainer Jens said...

RE: Row capacity in Excel

Dear Ali,

I do not know why Excel cannot find the table in Access. If you can forward the database to me I can have a look at the data.

send it to:

info@stl-training.co.uk

Please add my name in the subject.

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