4 Replies Latest reply: May 15, 2014 9:54 AM by frugalman _ RSS

    Export too Large for Excel

    Will L.

      My export file has many more lines then Excel can fit. What is the most practicle way to split the data into multiple Excel sheets?

        • Export too Large for Excel
          RalphB _

          Will,

           

          The only way I know of is to insert a calculated field, say "Rows", and use the Rowno() function.  This will number the rows you have and then set the first filter for your calculated field < 65536 or whatever the max rows you want or max number of rows the version excel of on a sheet.  Your next filter will be > the number in the first filter and < 131072 or the next max number and so on for however number of rows you have.

           

          I generally use Access if I am working with that many rows and do my work with Access and then export out to Excel.  Usually by then, I have fewer than 65000 rows.

           

          FYI, Excel 2007 can handle up to 1 million rows.

          • Export too Large for Excel
            Data Kruncher

            You may have a couple of options Will.

             

            Based on your data set, you may be able to create a few filters, each of which generate Excel friendly results with less than 65k rows (I take it that you're not using Excel 2007). It would probably be best if you can pick the same field to filter. Then you can export each set into a different sheet.

             

            Alternatively, if you don't need all of the detail your data provides, you could build quite large summaries and export them to Excel. This is often a good solution as with that many rows, you're likely to be summarizing the inital data in Excel anyhow. I've often used this approach.

             

            HTH,

            Kruncher

             

            Edit: Ralph posted a great idea as I was typing.

            • Export too Large for Excel
              Will L.

              Thank you gentlemen very much. I am not sure if I need all the detail yet or not, but either way it looks like i'll be fine.

              -Cheers