Adding Location Account Numbers to a Sales Tax Return Spreadsheet

Ensure ALL of your sites are up to date on your sales tax account before beginning.

Go to Revenue Online at Colorado.gov/RevenueOnline.
Locate the "Sales and Use Tax" menu panel and select "Find Sales and Use Tax Rates."
On the "Select a Sales Tax Option" screen, select "View Business Location Rates."
On the "Business Location Rates" screen, enter your eight-digit Colorado Account Number (CAN), and select "Enter" on your keyboard.
Select the "Export" button. This will initiate a system download of the information in a .TXT format.
Open the downloaded file. The site list will appear as a delimited (flat) file.
Once the file is open, copy the exported data.
On Tax.Colorado.gov/Sales-Tax-Spreadsheet-Filing, download and open the spreadsheet template that corresponds to the timeframe for which you need to file a return.
Once the file opens in Excel, select "Enable Editing."
In the Excel spreadsheet, select "File," then select "Save As." Select a file destination for the spreadsheet and name your spreadsheet using the following format:. CompanyNameFilingPeriod.xlsx.

Example 1: BigCompanyMarch2024.xlsx.

Example 2: MomAndPopShop1stQuarter2024.xlsx.
In the Excel spreadsheet template, select the "Setup" worksheet.
Paste the previously copied data from the export file into cell A1 of the Excel "Setup" worksheet.
Select Column A which will highlight the entire column.
From the "Setup" worksheet, select the "Data" tab from the ribbon display, then select "Text to Columns."
In the "Convert Text to Columns Wizard" pop-up box, Step 1 of 3, select "Delimited". for the file type. Then, select "Next."
In the "Convert Text to Columns Wizard" pop-up box, Step 2 of 3, select "Semicolon". as the delimiter. Then select "Next."
In the "Convert Text to Columns Wizard" Pop-up, Step 3 of 3, select "Text" under "Column Data Format." Then under "Data Preview" select the column heading containing the six-digit jurisdiction codes.

Note: You may need to scroll to the right to locate the appropriate column. After selecting the column, change the "Column Data Format" from "General" to "Text." Then, select "Finish."
Select the "Return Data" worksheet in Excel, then right click on the column A heading.
Right click on the column A heading, then select "Format Cells."
In the Format Cells pop-up box, select "Number" under the "Category" options. Then, change the number in the "Decimal Places" box to "0." Then, select "OK."
Copy the following formula into cell A3 in the Return Data worksheet:. =INDEX(Setup!$A$1:$A$800,MATCH('Return Data'!B3,Setup!$G$1:Setup!$G$800,0))'.
Copy the formula down Column A. You can double click the bottom right corner of the selected cell A3 to speed up this process.
In the “Return Data” worksheet, select cell A3 to the bottom value in Column A usingShift+CTRL+. Then press CTRL+C followed by ALT+E+S+V (Paste Special).

Note: For Paste Special, you can also right click, select the arrow to the right of Past Special, and select “Paste Special…” at the bottom of the expanded menu.
In The "Paste Special" pop-up box, select "Values" in the Paste section, then select "OK."
Clear the contents in Column A for all values "#N/A."

Note: Column A can easily be filtered to show only values equal to "#N/A." Left click on the down arrow in the lower right corner of cell A2, type "N/A" into the search box, then select "OK." Select the first cell below A2, then highlight all cells with the value "#N/A" using the keyboard shortcut Shift+CTRL+ Contents." Once all cells are highlighted, right click, then select "“Clear Filter from ‘Location Acct#’.”
Once all contents are cleared of "#N/A" values, clear the filter from Column A by selecting the down arrow box in the lower right corner of cell A2, and select "Clear Filter from ‘Location Acct#’."
Select the "Header" worksheet and fill out all data fields in the file, including the "Ack Email" field.

Note: Omit dashes in all fields. Dates should be in MM/DD/YYYY. FEIN is 9 digits. CAN is 8. Digits. Zip code is 5 digits. Contact phone is 10 digits.
Save the file.
If you have not been approved for spreadsheet filing previously, please submit your completed spreadsheet to DOR_LocationFilers@state.co.us.