Importing Data and Combining multiple Excel spreadsheets in Numbers?

Question

Craig Seeman Author

Level 4

2,137 points

Importing Data and Combining multiple Excel spreadsheets in Numbers?

Importing Data and Combining multiple Excel spreadsheets in Numbers 13.2?

I have several Excel spreadsheets.

I have create a new Numbers Table spreadsheet.

I want to import the Excel spreadsheet while reasigning the original Excel Columns to the Numbers Columns.

Example Date to Date, Dogs to Dogs, Locations to Towns etc so not all names match nor are they in the same order.

I've done this with other spreadsheets in which there's an import and assign dialog but I can't find anything like that in Numbers.

This is a common features with many spreadsheets when importing CSV, Tab Delimited or even complete .xlsx files.

I don't even see an Import feature in Numbers to import additional data formated elsewhere.

Mac Studio (2022)

Posted on Feb 23, 2024 9:38 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,568 points

Feb 23, 2024 8:18 PM in response to Craig Seeman

Craig Seeman wrote:

I'd be willing to start with a blank Table in Numbers but after importing the first Excel document I'd need to make sure the data from subsequent Excel documents are going into the correct columns.

That's what I would do. Set up a Numbers document with good Numbers design (which differs in important respects from Excel, among them the ease of putting multiple tables on one sheet). Then copy-paste data from Excel to the appropriate places. That entails work, of course, but you likely will be happier with the results. Copy-paste seems low-tech but it can be surprisingly efficient. To avoid strange-looking styles in Numbers use Edit > Paste and Match Style (or its keyboard shortcut equivalent option-shift-command-v) rather than command-v.

If you haven't done so already, be sure to have a look at the templates at File > New in your menu for examples of effective Numbers document design.

SG

Reply

Answer 2

Yellowbox

Level 7

21,951 points

Feb 23, 2024 9:26 PM in response to Craig Seeman

Hi Craig,

Numbers does not have a way to automatically re-order columns. We must do that manually. Click and hold on a column letter. That column will appear to "lift" off the sheet. Drag that column to the new position.

Sorry, I can't find anything in Numbers Help to explain the "Drag and Drop" for columns. Maybe another user can step in here.

I have rewritten your explanation to remind me which column goes where:

I need to import Sheet 3 which is a single table with A Dogs, B Cats C Bears, D Date

Column A (Dogs) must go to D

Column B (Cats) must go to C

Column C (Bears) must go to B

Column D (Date) must go to A

Select and copy an array of cells in Excel.

In Numbers, click on a blank part of the "canvas" (Sheet) and paste. That will create a new table in Numbers.

Repeat with other copy-and-paste from Excel. Then combine the new, reorganised, tables in Numbers.

Regards,

Ian.

Reply

Answer 3

Craig Seeman Author

Level 4

2,137 points

Feb 24, 2024 9:28 AM in response to SGIII

copy-paste data from Excel to the appropriate places

Tedius to do column by column for many tables as have nearly a dozen but it may be the only solution.

Reply

Answer 4

Craig Seeman Author

Level 4

2,137 points

Feb 23, 2024 5:03 PM in response to SGIII

SGIII wrote:

Have you tried launching Numbers and using File > Open to open the Excel document, then reformatting, etc. to taste?

SG

That's easy to do.

The problem is when

You already have a Numbers Table with data in it.
You need to import or extract data from multiple Excel documents.

The problem is all the data must conform and I see no way to import an Excel document to an already formatted Numbers document and no way to import and assign columns from multiple Excel documents.

Numbers seems to need an Import (to current document) and a Column assignment feature so the source columns can have destination columns.

I'm pretty sure other spreadsheet programs (and certainly databases) have that feature.

Heck, I'd be willing to start with a blank Table in Numbers but after importing the first Excel document I'd need to make sure the data from subsequent Excel documents are going into the correct columns.

Reply

Answer 5

SGIII

Level 8

37,568 points

Feb 23, 2024 4:46 PM in response to Craig Seeman

Have you tried launching Numbers and using File > Open to open the Excel document, then reformatting, etc. to taste?

SG

Reply

Answer 6

Craig Seeman Author

Level 4

2,137 points

Feb 24, 2024 9:26 AM in response to Yellowbox

Click and hold on a column letter. That column will appear to "lift" off the sheet. Drag that column to the new position.

The problem is this only works when importing the first table into a new document. Once the columns are created importing a subsequent table puts things in the wrong columns

Reply

Answer 7

Craig Seeman Author

Level 4

2,137 points

Feb 23, 2024 2:33 PM in response to Craig Seeman

If you need further explanation.

I created a Numbers spreadsheet which is one sheet with a table.

For simplicity of explanation, there are:

Four columns

A Date, B Bears, C Cats, D Dogs

I have 10 Excel spreadsheets with

5 Sheets

I need to import Sheet 3 which is a single table with A Dogs, B Cats C Bears, D Date

Column A must go to D

Column B must go to C

Column C must go to B

Column D must go to A

Numbers has no Import feature I can see.

Numbers cannot assign the imported data column to another column in the current Numbers Table that I can see.

Please explain how I can do this.

Thank you in advance for any help.

Reply

Importing Data and Combining multiple Excel spreadsheets in Numbers?

Similar questions