Wikipedia:Reference desk/Archives/Computing/2019 March 27

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

March 27

MS Excel copying large worksheet into a workbook with small worksheets.

When I try to do this I get the following message:

"Excel cannot insert the sheet into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook."

I infer that workbooks know the maximum row and column count of all their worksheets, and don't like to import a sheet that would expand that size. But pasted data can (obviously) expand it.

Why does Excel have this constraint? Why can't it cope with imported larger sheets?Hayttom (talk) 10:36, 27 March 2019 (UTC)

I do not know, this is a guess, but. Most data in spreadsheets uses a small amount of the available sheet space. If program A allows 10000 * 10000 cell sheets and program B allows 1000000 * 1000000 cell sheets, you can't put a big sheet into the small program. However if your data is only 1000 * 1000 cells, it will comfortably fit into 10000 * 10000. -- SGBailey (talk) 17:27, 27 March 2019 (UTC)

As a piece of precautionary advice: when using large Excel spreadsheets extensively in a professional context, I sometimes found that one had somehow acquired a spurious cell entry or just unnecessary formatting hundreds or thousands of rows and/or columns beyond the last meaningful one. This can obviously cause various problems including ones similar to those experienced by Hayttom. {The poster formerly known as 87.81.230.195} 90.200.138.194 (talk) 01:34, 28 March 2019 (UTC)

The problem is with the iteration of Excel that created the "smaller" file. With Office 2007, Microsoft changed their file types to XML format and the default file extension switched from .XLS to .XLSX. The newer file types were able to hold much more data as well: an .XLS file could only hold 65,536 rows and 256 columns, while .XLSX files can hold 1,048,576 rows and 16,384 columns. So, if you're trying to paste 65,537 rows worth of data (or more) into an .XLS file, it will not work. The workaround is simple: open the destination file in Excel and then "save as" the .XLSX file type; it should now be large enough to accommodate whatever you're trying to paste into it. Matt Deres (talk) 12:59, 28 March 2019 (UTC)