Open navigation

How to place and move Supermetrics queries in Excel

Follow these steps to place and move Supermetrics queries in Excel.


Before you begin

When a query runs for the first time, the starting point for the data will be in the cell that is selected when the query is run (this happens when you click Get Data).


When this happens, the query's configuration is added to the SupermetricsQueries worksheet. This sheet is automatically created when the first query in the workbook has completed. It's hidden by default — click Add-ons → Supermetrics → Manage queries to open it.


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


Instructions

Move a query within a worksheet or workbook

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


Drag and drop

This method works best for smaller queries that you just need to reposition 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.


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.


Edit the query 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. (This is the saved configuration of all the queries in the file, and it’s hidden by default. Click Add-ons → Supermetrics → Manage queries to open it.)
  2. Find the row that has the query you want to move.
  3. In that row, delete the Query ID and Sheet name values (columns A and D).
  4. 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.

    Note that it's more important to have the right start cell in the range than the right end cell. If the wrong cell is entered here, it’ll be overwritten to match the whole size of the query.

  5. Move the cursor away from the row being edited so it doesn't lock the cells. For example, select another cell above the header (above row 16).
  6. In the Supermetrics sidebar, click Refresh all to refresh the queries.


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.


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.

Move queries to another workbook

Follow these steps if 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. (This is the saved configuration of all the queries in the file, and it’s hidden by default. Click Add-ons → Supermetrics → Manage queries to open it.)
  2. Find the row that has the query you want to move. 
  3. Copy the whole row of data.
  4. Open the new file you want to copy the query into.

    If the new sheet file has never used Supermetrics before, click Data → Show Supermetrics to initialize the add-in. Create any query to make it generate the SupermetricsQueries sheet needed for the next steps. You can then 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.
  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. In the Supermetrics sidebar, click Refresh all to refresh the queries.


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.


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.