Query returned more data than will fit on a worksheet
 

I am trying to run a query using other Data Sources, when I run it says query
returned more data than will fit on a worksheet. Is there any way to get
around this so I can get all the data?

Answer: Query returned more data than will fit on a worksheet
 

Yes, there are a few ways to work around this issue. Here are some possible solutions:

1. Split the query into smaller parts: If the query is returning a large amount of data, you can try splitting it into smaller parts. For example, you can limit the query to a specific date range or a certain number of rows. This can be done by modifying the SQL query or by using the query wizard to filter the data.
   
2. Increase the number of rows that can be displayed: By default, Excel limits the number of rows that can be displayed on a worksheet to 1,048,576. You can increase this limit by going to File Options Advanced and scrolling down to the "Display options for this worksheet" section. Here, you can increase the "Show this number of recent documents" and "Maximum number of rows to display" options.
   
3. Use Power Query: Power Query is a powerful data transformation and analysis tool that is built into Excel. It can handle large amounts of data and allows you to filter, sort, and transform data before importing it into Excel. To use Power Query, go to the Data tab and click on "Get Data" "From Other Sources" "From SQL Server" (or whichever data source you are using). Follow the prompts to connect to your data source and import the data into Excel.
   
4. Use a database management system: If you are working with very large datasets, you may want to consider using a database management system (DBMS) such as Microsoft Access or SQL Server. These tools are designed to handle large amounts of data and can be used to run queries and generate reports. You can then connect Excel to the DBMS to import the data and create charts and graphs.

Query returned more data than will fit on a worksheet
 

Carrie wrote:
I am trying to run a query using other Data Sources, when I run it says query
returned more data than will fit on a worksheet. Is there any way to get
around this so I can get all the data?


What version of Excel are you using? Excel 2007 has 1,000,000 rows and 16,000
columns.

Query returned more data than will fit on a worksheet
 

I am using 2007 but it is ending w/ 65,536.

"Glenn" wrote:

Carrie wrote:
I am trying to run a query using other Data Sources, when I run it says query
returned more data than will fit on a worksheet. Is there any way to get
around this so I can get all the data?


What version of Excel are you using? Excel 2007 has 1,000,000 rows and 16,000
columns.

Query returned more data than will fit on a worksheet
 

I haven't used XL2007, but if the file you are using is compatible with
earlier versions it will be restricted to 65536 rows (and 256 columns). The
file needs to be XL2007 format to enable data beyond this range.

--
Ian
--
"Carrie" wrote in message
...
I am using 2007 but it is ending w/ 65,536.

"Glenn" wrote:

Carrie wrote:
I am trying to run a query using other Data Sources, when I run it says
query
returned more data than will fit on a worksheet. Is there any way to
get
around this so I can get all the data?


What version of Excel are you using? Excel 2007 has 1,000,000 rows and
16,000
columns.

Query returned more data than will fit on a worksheet
 

I was running in the wrong format, needs to be xlsx. Thank you to everyone
for your help.

"IanC" wrote:

I haven't used XL2007, but if the file you are using is compatible with
earlier versions it will be restricted to 65536 rows (and 256 columns). The
file needs to be XL2007 format to enable data beyond this range.

--
Ian
--
"Carrie" wrote in message
...
I am using 2007 but it is ending w/ 65,536.

"Glenn" wrote:

Carrie wrote:
I am trying to run a query using other Data Sources, when I run it says
query
returned more data than will fit on a worksheet. Is there any way to
get
around this so I can get all the data?


What version of Excel are you using? Excel 2007 has 1,000,000 rows and
16,000
columns.

You could try dumping the data to a pivot table