Query placement and how to move queries in Excel

This guide covers how Supermetrics for Excel inserts queries into worksheets and its cells, and how to move queries after they have been created.



Quick links to sections below:


Behavior on initial query creation

When a query configuration is run for the first time, the start point of the data will be in the cell that is selected when the query is run from the Get data button.


In this example, this new query will be added in worksheet Sheet3 at cell D3 as the starting point.



When the query runs, data will occupy cell D3 and out to the right and down below from there, based on the size and depth of the data.



This then translates to the query configuration added into the SupermetricsQueries worksheet. This sheet is automatically created when the first query in the workbook has completed.


In the SupermetricsQueries sheet you can now find the query configuration with the sheet reference and the cell range that the data resides in. The range address will change on later refreshes if the size of the data changes.



How to move existing queries within the same worksheet

Once a query has already been created, there are two ways to move it around in the same file.


Method #1 - The drag-and-drop

This method works best for smaller queries that you just need to re-position within the same worksheet.

  1. Find the query data you want to relocate in your sheet.
  2. Highlight all cells that are part of that query.
  3. Hover the cursor over the edge of the selection until you see the hand-icon.
  4. Click and drag the selection into the new position, and drop it.
  5. The range address for this query will update automatically in the configuration as seen in the sidebar and in the SupermetricsQueries sheet. Future refreshes will use this new location.



Method #2 - Manually editing the configuration

This method works best for very large queries that need to be moved either within the same worksheet or moved to another worksheet in the same workbook file.

  1. Open the SupermetricsQueries sheet.
  2. Find the row that has the query you wish to move. In this example, we want to move this query in row 17 to another location:
  3. In that row, delete the Query ID and Sheet name values (columns A and D).
    In Range address (column E), delete the existing formula/value, and fill in the new sheet name (column D) and range address (column E) for the query.
    In this example, the new location for the query is a sheet with the name "moveHere" and the range address starts from cell B2.
    Note that it's more important to have the right start cell in the range than the right end cell - if you mess it up, it will be overwritten correctly to match the whole size of the query.
  4. Move the cursor away from the row being edited so it does not lock the cells. For example, select another cell above the header (above row 16).
  5. Refresh all queries from top left menu icon in the Supermetrics sidebar  Refresh all:
  6. This will refresh the query into the new location, and update the deleted columns to have a new Query ID and Sheet name, as set in the Range address cell.
    Note that in this example the range's end value has been automatically corrected from C3 to D4, to match the actual data size.
  7. This scenario will usually delete the data in the original location, but double-check this has been done after the query has refreshed. If the data was not removed in the original location, delete it manually to clean it up.


How to move existing queries to another Excel workbook

These instructions can be used you need to relocate a query to another file or copy the query to reuse it.


  1. Open the SupermetricsQueries sheet in the file you will be copying the query from.
  2. Find the row that has the query you wish to move. In this example, we want to move this query in row 18 to another file:
  3. Copy the whole row of data.
  4. Open the new file you wish to copy the query into.
    • If the new sheet file has never used Supermetrics before, please click on Data → Show Supermetrics to initialize the add-on, and have it generate the SupermetricsQueries sheets (create any query) needed for the next steps. You can delete this query and have a fresh SupermetricsQueries sheet.
  5. Open the SupermetricsQueries tab in the destination file.
  6. Paste the copied query row with Paste Special → Values (on Mac) or Paste Options → Values (on Windows) into the next available row under the header (if a new file, always copy into row 17 to start).
  7. In the row where you pasted the query row, delete the Query ID and Sheet name values (columns A and D).
  8. In Sheet name (column D) and Range address (column E), fill in the value for the new sheet and range address for the query. Make sure the sheet name exists in this new destination file, and that the range address matches where you want the data to populate (In this example, Sheet1, starting from cell A2).
  9. Move the cursor away from the row being edited so it does not lock the cells. For example, select another cell above the header (above row 16).
  10. Refresh all queries from top left menu icon in the Supermetrics sidebar  Refresh all.
  11. This will refresh the query into the new location, and update the deleted columns to have a new Query ID and Sheet name, as set in the Range address cell.
  12. If this was to relocate the query, now delete the row for the query in the original file and delete the data in the original location.


Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.