FREE Inventory Tracker
Last Update to the Inventory Tracker File: 3/23/2022
We are no longer updating and maintaining the Inventory Tracker file for Usborne / PaperPie. Please enjoy our other direct sales training!
There are many reasons you will want to keep track of your inventory. For tax purposes you need to report your inventory on your Schedule C or if you are preparing for a large booth event or book fair you might want to know that you came home with all the books your should have. Use the form below to download our FREE Inventory Tracker which will help you quickly and easily manage your inventory. It allows for keeping track of inventory in a spreadsheet or uploading your inventory to Sqaure for real-time inventory tracking. After you have downloaded the Inventory Tracker, watch the video below to learn how to use the Inventory Tracker with your barcode scanner. If you don’t already have a barcode scanner, you can purchase our recommended scanner for about $40 on Amazon.
Get more technical help like this plus access to our courses, masterminds, real-time coaching + support and access to our exclusive rockstar community.
Checkout the Modern Direct Seller Academy today!
The Inventory tracker is provided in a Microsoft Excel (.xlsx) format. If you don’t have Excel you can upload the file to Google Sheets and it works almost exactly the same.
Related Posts:
FAQs
I clicked the link to download the file in the email I received and got an error. What do I do?
If you clicked a download link that was in an old email you previously received and there has been an update to the Inventory Tracker since that email, you will get an error when you try to download the file. Fill out the form again on this blog page to get a link to the latest Inventory Tracker file.
I have a bunch of single books from sets that were broken up. The singles don't have barcodes. Is there any way to scan them so I don't have to manually type them in?
Can I sort my inventory list Alphabetically by title?
1) Right click on one of the tabs at the bottom and click “Unhide” and select “Pre-Event Stock Horz”.
This list is essentially the Pre-Event tab just reformatted to put each entry in a single row. Since the entries are in a single row on this tab (instead of 2 rows on the Pre-Event tab) they can be sorted
2) Click Review –> Unprotect Sheet
3) Create a new blank worksheet
4) Copy the table in the “Pre-Event Stock Horz” tab to the new worksheet. Make sure to use Paste Values so the formulas don’t get copied, just the values.
5) Select a cell in the top row of the new worksheet the click Data –> Filter
6) Now you should see little drop down arrows in the column headers. Click the one in the Title column and then select Sort A to Z.
Excel may convert the SKU to scientific notation after you paste the data to the new worksheet. To fix that select the whole SKU column then on the Home ribbon in the Number section click the little box/arrow button in the bottom-right to open the Format Cells box. On the Number tab select Number and set the Decimal places to 0 then click OK.
Do you have a list of changes that have been made to the Inventory Tracker?
3/23/2023 –
- Added new releases
1/29/2023 –
- Added new releases
9/19/2022 –
- Added Advent Calendar Book Collection, 9780794553234, 34.99
7/15/2022 –
- Add Summer 2022 new releases
4/10/2022 –
- Add Spring 2022 new releases
2/3/2022 –
- Add January 2022 new releases
11/1/2021 –
- Add Fall 2021 new releases
7/5/2021 –
- Add June 2021 new releases
5/18/2021 –
- Add May 2021 new releases
1/21/2021 –
- Add January 2021 new releases
10/17/2020 –
- Add October 2020 new releases
- Correct Finger Match books prices from $7.99 to $9.99
6/21/2020 –
- Add June 2020 new releases
- Double the amount of books that can be tracked (up to 2000 now!)
3/23/2020 –
- Add March 2020 new releases
1/9/2020 –
- Add December 2019 new releases
10/8/2019 –
- Add October 2019 new releases
7/12/2019 –
- Deleted Spanish characters ¿ and ¡ that were causing an error when uploading to Square
7/8/2019 –
- Add July 2019 new releases
3/12/2019 –
- Add March 2019 new releases
- Change Learning Wrap prices from 8.99 to 9.99
1/23/2019 –
- Add January 2019 new releases
10/13/2018 –
- Add October 2018 new releases
- Deleted a few duplicates
- Add the following for singles from packs:
- Christmas Picture Book, Single 100000000045 4.99
- Weather Picture Book, Single 100000000052 4.99
- Picture Book Gift Set, Single 100000000069 4.99
- Puzzle Book Pack, Single 100000000076 3.99
7/17/2018 – Correct the prices on the following items from $10.99 to $8.99:
- Billie’s Animal Hospital Adventure 9781610676076
- Billie’s Outer Space Adventure 9781610676083
- Billie’s Underwater Adventure (H) 9781610674560
- Billie’s Great Desert Adventure (H) 9781610674577
- Billies Wild Jungle Adventure (H) 9781610675536
- Billies Yummy Bakery Adventure (H) 9781610675543
6/16/2018 –
- Added new releases from June 2018.
- Corrected price from $8.99 to $9.99 on Sticker Dolly Dressing Around the World 9780794539801.
- Add The Writing Box 9780794540456 9780794540456 34.99
- Add Magic Painting 2pk 9780794540944 9780794540944 9.99
- Add Coloring Book Collection 9780794540432 9780794540432 24.99
- Add Big Book of Fingerprint Activities 9780794540715 9780794540715 29.99
- Add 10 Days to Addition Mastery Learning Wrap-Ups 18343077016 19.99
3/31/2018 – Fix broken formulas preventing some books from showing up. 3/9/2018 – Add March releases. Added new SKU on Anna Hibiscus Book 3 and 4. 12/17/2017 – Add December releases. 131 titles total added.
10/6/2017 – Add mid-season releases. 31 titles total added. Correct price of #1 Spotter from 4.99 to 5.99.
9/8/2017 – Fixed block of barcodes with last 5 digits of the ISBN 605xxx. The preceding digits in the barcodes should be 9781933 but were 9781610. Fixed issue where Square Upload tab did not have the correct quantities for entries on the Pre-Event tab after row 501. Calculations are correct now for as many entries are allowed on the Pre-Event tab.
8/26/2017 – Correct there’s a Mouse About the House 9781601304193 (is now $11.99 but was incorrectly $10.99 in previous versions). Add 49 other missing titles including Mini books, Fizz and a bunch of Library quality books. Add a “LocationEnable” column which is needed for users that have multiple locations enabled in their Square account.
8/19/2017 – Add Shine-A-Light: In the City, 9781610675994, 12.99, H
7/26/2017 – Add mid-year releases for July 2017. 91 titles total were added to the file.
6/17/2017 – Add new releases for June 2017 .
5/5/2017 – Add Rain Forest Masks P, 9781610675475, 11.99. Update Pre- and Post-Event tabs so you can enter up to 1000 books now (was previously 250).
3/31/2017 – Add Town and Country, H, 9781610675499, 12.99 and Listen and Learn Get Ready for School, BD, 9780794539856 19.99.
3/30/2017 – Add Rubber Stamp Activities, SH, 9780794540074, $19.99. Correct SKUs for Jack Russell #1, #2, #3, #4, #5, #6, #7 paper backs. Add SKUs for a single book broken up from the following collections: Phonics (100000000021), Peek Inside (100000000014), That’s Not My Zoo (100000000038), Beginning Reference (100000000007).
3/17/2017 – Add Jungle Magic Painting Book and Under the Sea Magic Painting Book. Add the 21 new releases for March 2017.
3/12/2017 – Add Coding for Beginners Using Python.
Hi Jeremy! Is there a way for me to update the PaperPie Square upload so I can still use the tracker? I know there are formulas in the cells, and I don’t want to mess that up before asking you for advice. Thank you!
The inventory tracker pulls the titles and info from the “Square Upload” tab. To add more items to the inventory tracker you’d need to add more titles under the last used row on the Square Upload tab. You enter the info in columns B, D, E, G for the new titles and then you need to extend/copy the formulas and “Y” in columns J, K, L down to whatever the last used row is. The tab is locked to prevent inadvertent modifications, but there is no password. You just need to click Review > Unprotect Sheet and then you can make changes.
I tried to upload to Square and it says “Please check your sheet tab names. Names can only include ‘items,’ ‘Unit and Precision’ and ‘Component Inventory.” What do I do?
Hi Eileen – You can’t upload the whole file directly to Square. You need to save the Square Upload tab as it’s own file and then upload it. The step-by-step instructions on how to do it are on the Instructions tab in the Inventory Tracker file.
Will you be adding the new SmartLab items into the inventory tracker? It is currently coming up as N/A. Thanks
Hi Rachel – I added everything that was showing active a couple days ago. I see there are some smart lab items in there. The SKUs all are listed as SLxxxxxx. Here are a couple as an example:
All-Natural Lip Balm Boutique SL301439
Squishy Human Body SL301472
Tiny Baking! SL322431
If you enter the SL301439 type number they will show up in the inventory tracker. Do these products have different barcodes on them? The ISBN is listed that way on the website also, so I don’t have any other information.
Hi Jeremy! I’m so excited to use this at my book fair tomorrow! Even got the scanner you and Becky recommended – thanks!
When entering my inventory in the pre-event, one of the titles I scanned shows up as N/A. When I looked in the square tab, the title is there but one of the numbers in the bar code is transposed. Is there a way for me to fix it on my own?
Thanks!
If you unprotect the Square Upload tab you can change the SKU and then it should work. Can you let me know what the title and correct SKU is so I can fix the file? Thanks!
I tried to enter the advent calendar with my scanner to the inventory tracker and it didn’t bring it up. What can I do?
Hi Tara – If that’s a new item that was recently released it might not be in the inventory tracker. The last update I made was in July. I’ll take a look and see if there are new items to add and if so update the file in the next couple days.
Hi Tara – I wanted to let you know that I’ve released a new version of the file that adds the advent calendar. That was the only new item I saw that wasn’t already in the file. You can fill out the form in the article above to be sent the latest version.
Thank you so much! Of course there are always new titles which is exciting and I’m sure you are working on an update soon. I did notice at my last event (school book fair yay) That the learning wrap ups aren’t in the file either. I’m excited for this fall selling season and all the exciting events coming <3 Thanks for making this so easy to be organized and faster checking out process!
Hi Tara – A new version of the file has been released that adds new titles as of 10/10. I do see some learning wrap ups in the file so it may just be a difference in SKU if you are trying to add them. They all start with LWU as the first 3 characters in the SKU. You can search for that on the Square Upload tab if you want to see all the Learning Wrap-Ups in the file.
Hey Jeremy – I had someone enter all my inventory using your Inventory Tracker file, but did not read your instructions properly. I need to upload it all into Square, but it was entered on the PreEvent Stock tab. Is there any way to salvage all this work? Help!!?? Thank you so much for all you do … you guys really are very much appreciated.
Hi Kate – Scanning all the books in to the Pre-Event Stock tab is the correct first step. There is a Square Upload tab that pulls the information in from the Pre-Event Stock tab. After you’ve entered all your books on the Pre-Event Stock tab you’ll need to upload the “Square Upload” tab to Square. Toward the bottom of the Instructions tab you’ll find the steps to get it uploaded to Square.
Omigosh Jeremy – I can’t tell you how much I appreciate you and Becky. Thank you so much!!
Hello! Just wondering if there was an eta on when the newest release from March will be added to the tracker or if I can add new titles myself. Thanks so much!
Hi Shannon – I just released a new version a few minutes ago. You should have received an email from us if you’ve downloaded the file before. If you didn’t get the email you can fill out the form above and the latest version will get sent to you.
Awesome!! Thank you so much!!!
Hi there! What is the eta on the new titles being added to the tracker? Or can I add them myself somehow?
Hi Liz – I just released a new version a few minutes ago. You should have received an email from us if you’ve downloaded the file before. If you didn’t get the email you can fill out the form above and the latest version will get sent to you.
Some how once a upload my inventory and I now have duplicated books. So example 1, 2, 3 Do the Dinosaur 0 then under 1, 2, Do the Dinosaur 3.
I have uploaded again and it is fixed!
That’s great! I’m glad you got it figured out.
So once you scan and upload to Square, if you receive more inventory do you have to start by scanning everything again, or is there a way to keep your current numbers but add to it your new inventory?
Hi Carrie – We don’t have a way to update your inventory with the Inventory Tracker. If you’ve already uploaded your inventory to Square I would probably manually update your inventory in Square with any new inventory rather than trying to use the Inventory Tracker again to get updates into Square.
Hi – Is there a way I can update the tracker to include the new releases?
Hey Mandy – I just released an updated version. You can get the latest by filling out the form above, but I’ll send you an email also.
Feel free to delete my last comment… I found out how to change stock amounts in the Square Point of Sale app, just not on the computer… Maybe another dumb question, but like I said, I feel like I’m drowning in not very straight forward information on Square from any where else… Can I sort my inventory on Square by those that I currently have stock for?
I don’t believe there is a way to sort it by what is in stock. If you add a low stock alert level to all your items you can filter by the items that are currently on low stock alert instead of all inventory. Or if you export your item library to a spreadsheet you could sort it in the exported spreadsheet.
I’m just learning Square and you have given me more information than anywhere else that I’ve been able to find!! THANK YOU!! Is there a way to adjust inventory within Square itself or if you don’t have a new amount on import you can’t add any later? Does that make sense?
You can edit stock both online and in the app. There is more information here: https://squareup.com/help/us/en/article/5228-basic-inventory-management
i need the google sheets version. how do i get that?
Hi Stefanie – It’s the same file. You’ll just open it with Google Sheets. If you upload it to your Google Drive then open it from there it should open with Google Sheets.
Hello, How can I open the inventory tracker into google vs microsoft excel?
Hi Ali – If you download the file, then upload it to your Google Drive folder and open it from Google Drive it will open up in Google Sheets.
For some reason I cannot get it to work in Square. I’ve followed the instructions for exporting my list, and then importing it to Square, but when I scan a book in the checkout, it puts the SKU# as the price… talk about the most expensive book ever, LOL!… any idea what I’m doing wrong? Thanks!
Hi Jordan – What device are you using Square on? Are you on the items area and tapping in the search box prior to scanning?
Just FYI The Finger Match show up in the inventory tracker as $7.99 but on the website they are $9.99.
Thanks so much for creating this and making it available!!
Hi Lisa – Thank you for letting me know! I’ll make sure to update that in the next release.
Hi Jeremy,
Thank you for the inventory tracker file. I’m not done scanning in all my inventory but I’m on the last purple cell on the sheet…when I scan the next book, nothing happens. Any suggestions?
Hey Annelisa! You have a lot of books! When I created the inventory tracker I thought 1000 books would be more than enough :). Once you get past the Purple cells and row 2001 it won’t work any more. If you are just using the Inventory Tracker to get a total retail value of the books you have your best bet would be to make a copy of the Inventory Tracker file and clear out all the data in your second copy then keep scanning your inventory. Then you can add the total from two Inventory Tracker files to get your overall total. If you are planning to upload your inventory to Square and manage inventory real time then two separate files won’t work for you. If you are planning to manage your inventory in Square let me know and I’ll see about making a new version that allows more books to be scanned.
Thanks Jeremy, Yes, I do have a lot of books. I have 4 more boxes to scan and 4 more boxes arriving. Due to COVID my events have been canceled, so I have more books on hand than usual. I have customers buying from me directly and I want to keep track of what is going out. If you could make a new version, that would be great. Would I have to scan everything all over again? Or is there a way to merge the existing file into another file?
That is a bummer on the events, but I bet books are in high demand right now with everyone teaching their kids from home now. Hopefully sales are good for you!
I’ll take a look at adding that for the next update. Just use multiple files for now then you can copy and paste the data from the old files to the new file when it gets released. You won’t need to scan everything again. I should be able to have it done in a couple weeks. I’ll send you a message when it’s ready.
Thank you Jeremy!!
Hi Jeremy! Thank you so much for your amazing files! I was just uploading my inventory for an upcoming event and several of the Christmas titles are #N/A. I wasn’t sure if there was something that I could do from my end to get the titles updated on the file, but I’m not as well versed with excel. Thanks 🙂
Hey Sarah – If they are showing up with #N/A it means it isn’t being found in the inventory tracker. Can you send me the SKU and title for the ones that aren’t showing up and I can look into it? Or if you send me your file I can get it from your file. [email protected]. Thanks!
I feel a little silly now! I got it to work now. I was on a roll inputting and didn’t realize until I was figuring out which SKUs weren’t working when I realized I had started scanning them in the QTY instead. So sorry!
No worries! I’m glad you figured it out. Let me know if you anything else comes up or if you have any other questions.
Hi Jeremy! Thank you so much! Quick question: When I jump over to the Square tab, it says #REF! under the New Quantity column instead of the quantity in my stock…any ideas? It’s keeping me from being able to save and download to square.
Hi CJ – Would you mind sending me your file so I can take a look? [email protected]. Thanks!
I am having the same issue here. Would you mind sharing how you resolved this?
Hi Brittany – It usually has to do with a copy/paste issue or a problem with the data on the Pre-Event tab. If you want to send me your file at [email protected] I can take a look. Thanks!
Just sent! Thank you so much!
My scanner is no longer working. I have only used it once to scan my inventory. Well I accidently did something and now it won’t scan any more. (A red light shows up but nothing is registering). Can you help me? Thanks!
Hi Colleen – A couple thoughts…Is the battery running low? Double-check that the scanner is still paired with your device (computer, phone, etc.). You can also try going to your bluetooth settings on your device and forget the scanner then re-pair the scanner with your device and try again. If you still have trouble get the user manual (link) and try scanning the “Recovery to Factory Default” barcode in the Function Barcode section. That will reset the scanner back to defaults so you may need to pair it with your device again after that. Hopefully that helps. Let me know how it goes!
Hi Jeremy, I am having trouble when I download the Excel Inventory sheet. I am only getting one tab at the bottom labelled SquareItemUpload_….. this may be a simple fix but I am new to Excel and have no idea what I am doing. Any help would be appreciated. Thank you!!
Hey Emmie – I haven’t seen that before. Is there a yellow bar and button at the top with a warning saying editing is disabled? If so, click the button to enable editing. If that doesn’t resolve it, right-click on the tab available at the bottom and select unhide and see if the other tabs are hidden. Hope that helps. Let me know if you are still having trouble with it!
So I have my items scanned into the tracker (pre new mid-season releases). I got the new tracker with the new titles, but how do I get what I already have scanned into the new list? Do I have to scan it all again into the updated tracker?
I tried copying and pasting the list of “pre event stock” But I cannot seem to get that to work.
Thank you for this amazing tool!!!! And thank you for helping us work through the learning curve. 🙂
Hi Chandra – You should be able to copy and paste your data from the old file to the new file. If you are having trouble with it, send me your existing file and I’d be happy to move it to the latest version for you and send it back. You can send it to [email protected].
i appreciate this so much. I have an event coming up and purchased some mid-season releases (Oct 2019). Will these get added to the inventory tracker? Thank you
Hi Diana – I just posted new Square and Inventory Tracker files that have the latest releases in them. You can use the form on the web page to download the latest one. Let me know if you notice any missing titles. Thanks!
OMG, the Square Item Uploader and Inventory Tracker have both been so helpful! Thank you so much!
FYI, I found these 2 missing from the 7/12/2019 Tracker (these were new June releases; they have different covers than the same titles with a different ISBN):
* Earthquake Challenge, The – Bear Grylls Adventures 9781684640409
* The Volcano Challenge, The – Bear Grylls Adventures 9781684640416
Thanks for the heads up, Rachel! I’ll add this to my list to add in the next release.
You’re the best! Thank you so much for doing this!! Always very appreciated!
You’re welcome! Thanks for the comment 🙂
Sent you some beer money! Thanks Jeremy! I hope everyone sends you a little thank you, whether it’s a note or donation! Thanks so much!
You rock! Thanks so much! I had one today at lunch :). Honestly, the thank you notes and positive feedback are just as gratifying. We’ve built a great community here and I really enjoy being able to help you all out!
Would love to try the inventory tracker but all your files are in an Excel version that I can’t open on my antiquated Office 2003 version. Anyway you can offer versions for older Office products?
Hi Terri – Unfortunately some of the formulas used in the Inventory Tracker are only compatible with Excel 2007 and later. It does work with Google Sheets though. To use it with Google Sheets go to your Google Drive and click New –> File Upload then select the “InventoryTracker_[DATE].xlsx” file. Then within Google Drive do a right-click on the file then Open With –> Google Sheets. Let me know if you have any trouble using it that way!
Hi, Jeremy! So I’m working in Google Sheets for the first time (working from an iPad Pro). When I scan using the barcode scanner, it only scans partial numbers, and the numbers it picks up each time varies (sometimes it’s 8 numbers of the entire SKU & sometimes it’s only 4…it’s never consistent); so I always get #N/A. However, when I manually input the SKU, it works. But who wants to do that!?!
As a solution, I also downloaded Excel for iPad & signed up for the free trial to edit. BUT…the same thing happens. Am I doing something wrong or does this just not work on an iPad? I do scan all my book sales/checkout customers at my events from my iPad so I know the scanner reads the SKU in that regard. Hope this all makes sense. Any help will be greatly appreciated. Thanks so much!
Hey Christine! That’s a new one. I haven’t seen that before. It sounds like a problem between the scanner and the iPad, but that is interesting it works for checkout. The scanner is just a generic input device (like a bluetooth keyboard). It can input text in to any file and bluetooth device. Are you far away from the iPad when you are trying to scan things? Is there anything between the scanner and iPad when you are trying to scan items?
If the answer is no to both of those can you try a few things to attempt to narrow down where the issue is?
1) Reboot the iPad then try again. If it’s still not working go to #2.
2) With your scanner paired to your iPad open a generic text file, put the cursor active in the text file then scan a book and see if the whole barcode gets entered in the text file or just part of the barcode? This should tell us if the issue is specific to using the inventory tracker file or not. If the same issue happens with the text file then go to #3.
3) Pair the barcode scanner with a different device (PC or phone, just something other than the iPad) then open the inventory tracker and see if the full or partial barcode gets entered after scanning with the barcode scanner connected to the other device. This should tell us if the issue is tied to the iPad or the barcode scanner.
4) After #3 pair the scanner with the iPad again and re-try scanning a book into the inventory tracker. Do you still have the same issue?
Let me know what happens with those tests then we can figure out what device or file to troubleshoot.
Hello again Jeremy. Ok, so #1 did not work but #2 did! I opened Numbers on my iPad & the entire barcode was scanned…I even did it 5 times & it was consistent each time. I also tried #3 on my iPhone; only partial/inconsistent barcode scanned. I then tried step #2 on my iPhone; I opened up Numbers & that scanned a full/complete barcode. When doing step #4, it was still a partial/inconsistent barcode.🤷🏻♀️
I have a really old iPad (iPad2 I think) and tried using the Inventory Tracker in Google Sheets and had the same issue. The Google Sheets app on the iPad seems to respond really slow when the data is coming from the barcode scanner so some numbers in the barcode get dropped. If I open a note on the iPad and scan a book the whole barcode gets transferred like you were seeing.
Did you try importing the Excel file into Numbers? I am not able to download Numbers on my iPad since it is so old so I couldn’t try that.
I downloaded Google Sheets on my phone (Samsung S9+) and paired the barcode scanner with my phone and the Inventory Tracker works fine that way. Just a side note, I find using my phone with the barcode scanner is more convenient than the iPad since I can still use the on-screen keyboard while the barcode scanner is connected to it.
I’m not sure why there’s an issue with the iPad. If Numbers doesn’t work for you I guess you can try your phone or you may have to use a full computer. If you try Numbers on your iPad let me know how it goes because I’m sure other people will have the same question.
Do you have the October releases added yet?
Not yet! It’s on the todo list for this weekend 🙂
Do you have the newest list loaded? Thank you 🙂
It’s on the todo list for this week. Make sure you sign up for our email list (in the footer) if you haven’t already so you get notified it is updated!
Do you have barcodes for the single books out of the Puzzle Book Packs, Christmas Picture Book Pack, the Picture Book Gift set, or the Weather Picture Book Pack? Thank you so very much!
By the way, I love that you have made this available for us!!!!!!
I don’t have any single barcodes for the those packs. If you send me the prices that they are when sold individually I can add them to the inventory tracker in the next release and also update the PDF in the FAQs.
I sent an email with this question before reading your response. Both Christmas picture book pack and the weather pack are $14.99 for 4 books. The picture book gift set is $69.99 for 20 books. I am charging $4.99 each. The puzzle book packs are $9.99 for 3 books and I am charging 3.99. But, someone else might have better advice on their preferred price. I also have the Billie B and Jack back packs that are free with a set of books. I would like to sell those and am open to a price. Thank you!
The Billie B (9781610675826) and Hey Jack (9781610675833) backpacks are already in the file and are $22.99. The others I’ve added to my list to include in the next update to the file.
Is there a way to sort books by title? I read through the comments above and saw where it said to send an email. If you can share something here, great. If not, I’ll go the e-mail route.
Also, I have several out of print titles. Is it best just to add these to the square or is there another option?
Hi Diana! I’ve been asked the question about sorting the inventory list alphabetically so many times I finally added an FAQ tonight explaining how to do it. Try that out and let me know how it works for you.
For the out of print titles, if they aren’t showing up in the inventory tracker you can just add them manually. Unprotect the sheet then you can manually add the titles and prices to the end of your list.
Jeremy, I have tried that and at first the data appears correct on a new excel spreadsheet after I have highlighted it and copied it and then pasted it into the new excel sheet. However, a few seconds after it appears to have pasted correctly, it changes each piece of data into “#REF!”. Am I missing a step? Thank you so much!!!
It sounds like you might have pasted it with the formulas. Did you use Paste Special, Values?
Yes I did, and I figured out how to paste differently so it now works perfectly. Thank you! However, I added some things to the tab Pre-inventory stock and tried to update my inventory by creating a new copy in Excel and the Pre-event Stock Horz option is no longer there when I hit “unhide” in the Pre-inventory stock tab. The only option is “All Titles”. Your help is sooo appreciated.
Hmmm…I’m not sure what happened. If you email me your file I can take a look at it.
I am doing a few events with a team member. We are bringing our own inventory and cross selling. Is there any easy way to reconcile who sold which books? In the past I manually printed out the sales summary from square and would update my spreadsheet. Could I just add a column in your inventory sheet for who a book belongs to? We can use one square account, but I want to be able to give credit to the book owner.
Hi Kristin – You could scan your inventory into the Inventory Tracker then import the Square Upload tab into your Square account. This will put your current inventory into your Square account. As you sell things at your event the inventory will be updated in real time. If an item shows 0 in inventory and you sell some the inventory will change to a negative number. After your event, if you export your Item list from Square, you can tell that any items showing a negative number in stock were sold from your teammate’s inventory. This might not work well if you and your teammate both bring some of the same titles, though. For example if you have 1 of Book A and your teammate also has 1 of Book A and you sell 1 of Book A at your event how will you know if it was your Book A or your teammate’s Book A that sold?
Another thought was maybe using the Category column. The Category is included in the item details in Square. Normally it is L for Library, P for Paperback, etc., but in the Inventory Tracker you could replace the text in the category column and put your name instead to denote that it was your book. Then when you looked at the Item Sales report after your event you could filter by the Category to see how many books were sold with your name as the Category and how many did not have your name as the category to determine how many of each person’s book sold. Again I think you have a problem if both you and your teammate have the same books.
Sorry I don’t have any concrete recommendations. I’m just throwing out some ideas here. Let me know if you have any more questions!
Hi – when I enter my inventory, and then go to the Square page and export it to a new book like you mentioned in the video, I get #VALUE in the New Quantity field of every item. Any idea why this happens and how to prevent it? Thanks!
Hi Kelly – It looks like when the copy of the Square Upload tab is made it takes the formulas for the New Quantity column with it. If it can’t find the original worksheet then the New Quantity formula wont’t work. Another way to do it would be to just create a new blank workbook, select all the contents on the Square Upload tab of the inventory tracker, then go to the new workbook and do a Paste Special –> Values in the new workbook. That will just paste the values without any formulas. The SKU column may re-format to scientific notation when you paste in the new workbook and if it does select the SKU column and change it to number format and remove any decimals. Hope that helps. Let me know if you have any additional questions!
Hi! I started using square before I saw your post and manually entered all of my inventory plus pictures. If I use the inventory tracker spreadsheet when I get a scanner will it update the inventory pieces that I have entered? I just don’t want to loose what I have entered since I had to get all of the pictures added separately.
Hi Laura – At this time the Inventory Tracker can’t pull in your current inventory from Square and combine new inventory with what you already have in Square. You would need to do it manually for now.
Jeremy – Firstly thanks for this! I’m about to do my first ever event/book fair and was super nervous until I saw how easy your system is to use! 🙂 Just wanted to let you know that in my inventory scanning the 10 Days to Addition Mastery Learning Wrap-Ups came up as N/A – the SKU comes up with the scanner as 18343077016 (there is a leading zero if you look on the book itself). Thanks so much for all your hard work and easy explanations!
Hi Audra – Thanks so much for the feedback and I’m glad you found the Inventory Tracker helpful! Thanks for letting me know about the 10 Days to Addition Mastery Learning Wrap-Ups. I will add it to the list to include in the next update.
im followed your directions when I go to pre party and go to spot b2 I scan the book but nothing happens?
Hi Debra – Are you using Excel or a different program? I’ve heard of similar issues with formulas not working correctly for some people using Numbers with Macs. I’d recommend using Google Sheets if you aren’t using Excel.
Hi Jeremy! I’m entering in a large inventory order I received this week and noticed that the SKU for the title ‘Sticker Dressing Heroes’ is 532673 in the inventory file, but this title was recently revised. The newly revised edition is now SKU # 539818 FYI 🙂 This is my first time using your fabulous inventory tracker with Square (& THANK YOU both very much by the way! 💜)… So I’m not super familiar with the in’s & out’s. If I just manually edit the item ‘Sticker Dressing Heroes’ to the SKU # of the newer revised edition is it going to mess with anything or would you recommend I just create a new item for it altogether? Thanks in advance!
Hi Gina – Thanks for the heads up on the SKU change. I’ll add that to the list for the next revision. If you want to modify the Inventory Tracker yourself you can go to the Square Upload tab, unprotect the sheet, find Sticker Dressing Heroes in the list then change the SKU for it. I think it should work OK and not mess up anything else. I would save a copy of what you currently have just in case you need to revert back to it. Let me know if that works or you find it causes an issue.
Thank you so much for all your hard work in creating this! I have a few items that are not coming up. Is there a way for me to add them into Square myself, after I have uploaded everything else? I don’t want you to have to do the work?
Hi Kelly – Yes, you can manually add items to Square after you have uploaded everything else. Log in to your Square account, click Items on the left, then click the Create Item button in the top right. Enter all the item details then click Save. If you don’t mind, let me know what items you find are missing from the Inventory Tracker and I’ll add them to my list to include in the next update. Thanks!
Sure, Here are the ones that were coming up NA for me: Hooray for Anna Hibiscus, Good Luck, Anna Hibiscus!, Have Fun, Anna Hibiscus!, Tigers 9780794532864. Thanks so much!
Can you send me the SKUs off the Anna Hibiscus titles that aren’t coming up for you? I have those titles in the file, but it might be under a different SKU. I don’t see Tigers in there so I’ll add that to the list for the next update. Thanks!
Hi! Thank you so much for this! I’m using this for the first time and ‘BOY’ came up as N/A when I scanned it. Is there something I can do to fix this?
Thanks!
Hi Tasha – “Boy” is in the current version of the Inventory Tracker. I have it under SKU 9781610677394. If you go to the “Pre-Event Stock” tab and type 9781610677394 in the SKU cell you will see Boy show up in the Title column. Does your copy of Boy have a different SKU? The online catalog shows it as the SKU I have in the Inventory Tacker. If it is different, let me know and I can update the file. Thanks!
Hi Jeremy! Thank you so much for this!! It’s such a lifesaver!! Do you know if the file will be updated with the mid-season new releases that just came out? Or is there a way we can add them in?
Thanks so much!
Hi Jennifer – I’m glad you are finding it helpful! Adding all the new releases is on my todo list for this weekend. We’ll send an email out to everyone when it is ready. If you’ve downloaded the inventory tracker before you will be on the email list and get notified.
Thanks so much Jeremy!! I downloaded the new one and for all the new titles it’s still saying #N/A… I’ve tried My Daddy is a Hero, My Mommy is a Hero, and Bug Hotel and they are all showing up like that. Am I doing something wrong?
You are right! The books were in the list but there were some problems with the formulas working in the background. I have released a new file that should correct the issue. You can get the new version using the form in the post, but I’ll email it to you also. Let me know if you find more books that aren’t showing up.
Hey Jeremy. Thanks for the great video. If I have my inventory uploaded to Square POS and I use this barcode scanner at an event for sales with Square POS on my iPad, will it deduct the stock as items are scanned and the transaction completed?
Hi Cynthia – That is correct! If you scan your inventory into the Inventory Tracker then upload it to Square any sales you make through Square will update your inventory in Square accordingly.
Hi Jeremy,
I’ve been banging my head trying to figure this out. I have my cursor in the first cell (B2), and scan a bar code, but nothing pops up in the cells. I hear the beep of the scanner, and the scanner is paired to my PC, Bluetooth is also on. Any idea of what is going on? I was able to take inventory earlier this summer, but can’t do so with a new (10/6/17) file now. Much thanks!
Turns out, I needed to re-pair my Smart & Cool scanner to my PC. I deleted it, re-paired, and found there was an update for it. Did the trick.
Yay! I’m glad you figured it out.
Hello! Is there a way to organize the inventory alphabetically? It is difficult to find the books when I am curious what I have. When i highlight them and try to “sort” It says they have to unmerged and all the same size. Thanks so much for all your hard work!
Hi Callie, please email [email protected] and he will help you out!
Hi Jeremy, sorry to post another question, but I need help. When I went to create an upload for my square, it never occured to me that there may already be data in the book. I created a “new book”, uploaded to my square successfully, but the data is not mine, it a ton of books I do not have. Help!
nevermind…figured it out
Hi! I’m not very skilled with Excel. Is there a way to enter data in columns C, D and E? I had several books that didn’t scan or scanned, but didn’t total the amount in column E…it scanned in the correct title, sku and amount per book, but not the total of the quantity entered.
Thanks,
Gina
Hi Gina – If you send me an email ([email protected]) with your file I can take a look and see what’s going on.
This is wonderful, thank you so much!! Question – how do you add new titles and still have all the info populate correctly?
Hi Kelly – If you enter a barcode and the Title and Price show “#N/A” it means I don’t yet have that title in the Inventory Tracker. If you send me which titles you noticed are missing I can add them.
I posted a message, but this clears that up. I’ve noticed these titles missing:
First Sticker Book Museums (9780794539993) 6.99
Mini Performance Cars (9781610676519) 2.50
Mini Sticker Dolly Dressing Princesses (9780794538873) 2.99
Mini Sticker Dolly Dressing Ballerinas (9780794538880) 2.99
Once these are added, do we have to download the file again?
Thank you so much for all of your work on this. I know it can’t be an easy undertaking! You and Becky are awesome!
These titles were all added to the Inventory Tracker we released on 8/26 so it sounds like you may have an old version of the file. If you fill out the form on this blog post again with your name and email you will automatically get emailed the latest version of the file. If you already have a bunch of entries in your version of the Inventory Tracker you can copy and paste them to the new file. Let me know if you have trouble getting the new file or moving your data over.
Fantastic! Thank you!! I’m so glad I don’t have to figure out how to do it myself!
I have done the pre-event list and used the scanner, why doesn’t the square upload list update the quantity? Do I have to do that part manually?
Hi Jocelyn – Are you asking about the Excel Inventory Tracker not updating the quantity or your Square inventory not showing the correct quantity after uploading the file? In the Inventory Tracker Excel file, when you enter items and quantities on the “Pre-Event Stock” tab it will populate those quantities on the “Square Upload” tab under the “New Quantity” column. When you save off the “Square Upload” tab and import it into your Square account at the step where you are aligning Square’s core product attributes with the imported columns you need to make sure you match up the “New Quantity” attribute in Square with the “New Quantity” column of the Square Upload file in order for the quantities you entered in the Inventory Tracker to be reflected in your Square inventory. Hopefully that helps. If it is a different step that isn’t working for you let me know!
Hi – so I just noticed after I scanned all of my books into the Pre-Event sheet that I have 510 books, but on the Square Upload on the “New Quantity” section, I only have a total of 385 books?! I see where some are scanning and updating, and some are not. Any clue what’s going on?
There was a bug in the Inventory Tracker that was recently fixed in the version released 9/8. If the entries on the Pre-Event tab went below row 501 they weren’t being picked up on the Square Upload tab. If you fill out the form on this post with your name and email you will be sent the latest version of the file (“_20170908” in the filename). Try copying and pasting your info in the Pre-Event tab to the new version of the file and see if it works. If you still notice any issues let me know and I’ll take a look.
Hi 😉 Thank you so much for offering this! I downloaded and opened (I use a chrome book with google sheets) but it was blank. Am I doing something wrong? Any advice appreciated. Thank you again!
Hi Sara – I just tried uploading the latest Inventory Tracker file to my Google Drive and opening it with Sheets and it looks like it is working OK. I went to my Google Drive, clicked New –> File Upload then selected the “InventoryTracker_20170726.xlsx” file. Then within Google Drive I did a right-click on the file then Open With –> Google Sheets. I see all the tabs and entered a couple SKUs and everything seemed to be working OK. Maybe try this approach and see if it works OK? Let me know if there is anything else I can help with!
Jeremy,
Thank you SO MUCH! I’ve only been using a chrome book for a few months so it’s all pretty new to me, and I’m NOT tech-savy AT ALL haha. Your advice worked perfectly! Thank you so much!
Yay!!! Jeremy’s the coolest!! Huzzie of the Year! hehe 😉
I’m not seeing the option to download the excel file….? Thank you!
Hi Amy. When you fill out the form above with your first name and email the Excel file will be automatically emailed to you. Let me know if you have filled out the form but not received the email.
Hmmm…I’m not seeing the form as an option…I did see it the other day when I was on a PC but now I’m on a Mac using Chrome. Maybe thats why?
Don’t see the form on Chrome or Safari on a Mac but it does show in Firefox, just FYI 🙂
Interesting…thanks for the heads up. I’ll look into it.
One more question…is there anyway to add titles to the catalog sheet? I’m trying to scan in my inventory but Coding with Python isn’t on there…
I’ve added the title and emailed you a new Inventory Tracker file.