The Shopify sales tax report can be daunting at first glance. But in this post, we’re going break it down so you can understand it. This sales tax report will help you understand how to identify nexus, as well as how much sales tax you owe.
We’re going to cover:
- Where to download your report
- Walk-through your report so you know what you’re looking at
- Show you how to understand your report
- Show you a trick for easily seeing where you have nexus and how much you owe
Where Do You Have Nexus?
Shopify is a fantastic platform for ecommerce, but it is important to remember that it is NOT a marketplace facilitator. Shopify DOES NOT automatically collect sales tax for you, and it DOES NOT file and remit for you.
You also need to remember that you need a sales tax permit BEFORE you can collect sales tax. Each state has different economic thresholds, and you need to check regularly to see if you’ve crossed them. What are the thresholds? We’ve done the research for you and updated our sales tax download. The appendix has a chart that will show you the economic thresholds state-by-state. The download also goes into a LOT of detail about economic nexus and how to register for sales tax permits. We tried to answer as many commonly asked sales tax questions as we could in it.
So, how do you know if you’ve crossed the thresholds? At the end of this blog post, we’re going to show you a handy trick to easily tell.
How to Set-Up Shopify to Collect Sales Tax
After you know what states you have nexus in and you’ve registered for the permits, you need to set up Shopify to collect sales tax for you. To show you how to do that, you can watch our video “Shopify Sales Tax Set-Up | A Walk-Through Guide” or read “Shopify Sales Tax Set-Up | Where and How to Collect.”
After you’ve got it all set up, orders are coming in and sales tax is being collected. Now it’s time to file and remit.
Finding Your Shopify Sales Tax Report
When you login to your Shopify account, you’ll be on your home screen. Click on Orders in the upper left-hand corner. That’ll open a list of all your orders. Then click on Export in the upper right-hand corner. This will open a pop-up screen that gives you the choice of how you want to view your orders. You may be doing quarterly filings or need to look at a whole year for a nexus analysis. To choose your timeframe, click on Orders by Date and choose the begin and end dates you want. Click on Export Orders and the file will be sent to the email address you have on file. It may take a while to get into your inbox, depending on how many orders you have. FYI – it could take up to a few hours.
After you’ve gotten the report in your email, download it and open it in Excel. This is going to look like a confusing pile of numbers, but we’ll show you how to sort it out.
Cool Tip! Resize the table so you can see all the headers clearly.
How do you do that? I’m so glad you asked. Select all the columns in the spreadsheet. Then click Ctrl–double left-click on the top right of the last column.
How to Read Your Shopify Sales Tax Report
Okay, now we’ll walk through the columns, so you know what you’re looking at. Many of them are self-explanatory, so we’ll focus on the most important ones.
Name is the transaction ID number. It’s important to note here that you’re going to see duplicates. That is because every product a customer purchases creates a new line item within that order. So, if a customer purchases 5 items in the same order, they’ll all have the same transaction ID number but will be listed on 5 separate lines.
Financial Status shows where it was paid or not. This may seem like an unnecessary column, but if it’s unpaid, there was probably a problem with the payment method. You don’t have to remit sales tax for unpaid orders. We’ll talk more about this later.
The part we want to focus on most is columns I-N (the financial part).
The Subtotal is what the customer ended up paying for the product. This is “net of discount,” meaning after the discount is already taken off the original price. You can see the Discount Amount in column N. The Subtotal – Discount Amount + Shipping + Taxes = Total.
Going to the right will show columns describing the items the customer bought. Other columns show info related to shipping and taxability. Then, there a bunch of columns related to billing and shipping information.
For sales tax purposes, we really want to focus on the shipping information. Column AP – Shipping Province is really important to pay attention to for nexus. (FYI – Shopify calls it Shipping Province instead of Shipping State since other countries usually use the province terminology.)
How to Use Your Shopify Sales Tax Report to Remit Sales Tax Correctly
The easiest way to analyze this report with a pivot table. This is a way to summarize & organize the data. I’m going to explain how to create a pivot table here, but if you’d rather watch Kexin, our sales tax guru, do it, watch Shopify Sales Tax Report | How to Find and Understand It. She starts explaining it at 7:16.
Preparing to Create a Pivot Table
The first step is to add 4 columns at the beginning of the table so you can strip out the important information. Label the columns:
- A – Year
- B – Month
- C – Month Year
- D – Calculated Order Number (This will help you determine how many transactions you have in each state.)
After you’ve labeled your columns, you may want to highlight them a different color to differentiate them from the rest of the spreadsheet.
Then add these functions to the first row after the headings:
- A2 -> Type in =year(H2)
Don’t type H2 – click the box H2 to add to the function properly.
If you’re confused, H2 is the second row down in the column H, which is labeled Paid at. This is going to help you see exactly when this order was purchased, so you can get an accurate count for your sales tax filing.
- B2 -> Type in =month (H2)
- C2 -> Type in =B2 & “ “ & A2
Make sure to add the space between the quotation marks so there is a space between the month and year in the data.
Again, click the actual boxes; don’t just type in B2 and A2.
- D2 -> Type in =if(E2=E1,0, if(E2=””,0,1))
Don’t panic! This means that if the order number in a row is the same as the order number in the previous row, we don’t want it to count again. If not, we count the order. This takes into account all the duplicate transaction ID numbers.
When you’re finished the functions, select them all and drag them down to apply them to every order. All the rows should populate.
How to Create a Pivot Table for Your Shopify Sales Tax Report
Okay, you’re all ready to create your pivot table. Click on the triangle in the top left-hand corner of the table. This will select the entire table.
Then click Insert and then Pivot Table. It will pop up a box asking if you want to use the entire range you selected. Click OK.
A new screen is going to pop up. On the left side is where the pivot table will appear. On the right side, you’ll see all the columns from the spreadsheet listed. You now get to decide how you want to set up the rows and columns and how you want to filter the information. We suggest the following:
Filters (so you can manipulate the data to see only what you want to see):
- Month Year
- Financial Status
- Payment Method
- Shipping Country (so you can filter out transactions not US-based)
- Shipping Province (so you can see the information by state)
- Calculated Order Number
You can change the format to make them look cleaner, from this (messy):
To this (neat and tidy to look at):
You do this by Selecting all except Sum of Calculated Order Number, clicking on Home, and then click on the big Comma under Numbers. This will make the numbers look neat and tidy. (By the way, the column titled Count of Discount Code in the image was a mistake. You don’t need that one. That’s where Sum of Calculated Order Number will go.)
Filtering the Pivot Table
Now that you’ve made a pivot table, you can filter it to get the information you need.
Click on Month Year so you can select the date range you want to analyze. (Ex. for Q4 of 2020, select 10 2020, 11 2020, and 12 2020).
You’ll notice that an option is 1 1900. That’s not because any orders came during that time frame. It’s because it’s the default when a month and year are not listed. The order date is only listed once for each order. The rest of the boxes in that column, for that order, are blank. Unselect 1900.
Click on Financial Status. This is where you filter out Pending because those orders didn’t go through.
Click on Payment Method. Some of our clients want us to exclude manual orders. If you want that, too, Select All and then Unselect Manual.
Click on Shipping Country. Select US to eliminate international orders.
Summarizing Data with Your Pivot Table
You are finally ready to summarize your Shopify sales tax report data for determining nexus and filing sales tax. Choose a state to start with. You’ll see the Subtotal of all the orders and Sum of Calculated Order Numbers in that state. Compare it to the chart of economic thresholds in the appendix of the sales tax download. Remember that those are annual thresholds. So, if you’re analyzing for one quarter of the year, multiply by 4 to see if you’re getting close.
These thresholds are something you really need to pay attention to. Some states require you to register as soon as you cross it. Others give you some leeway. It’s important to know the difference. Why? Some states are getting pretty aggressive about sales tax. That detail for each state is also in the appendix of our download to help you protect yourself.
Note – Economic threshold includes ALL the sales from ALL your channels. That means that you must add the info from all your channels for each state to determine nexus. Also, most states have requirements that look something like “$100,000 OR 200 transactions.” If you have less than $100,000, but over 200 transactions, you may still have nexus in that state.
Next, look at Sum of Taxes. If you see a number for that state, it is because you have determined you have nexus there and have turned-on sales tax collection for it. This is the amount you need to remit to the Department of Revenue.
Understanding and sorting through the data in your Shopify sales tax report may seem like a serious headache. To be honest, it is. If you have questions and would like to talk to a sales tax guru, consider getting a sales tax consultation.
Also, if you’re approaching $1 million in annual revenue, we can take over your sales tax headache. We have a whole team of sales tax gurus who are happy to do it for you!
Let us know how else we can help you!