Inventory Management
How to Create and Add a Picklist in Google Sheets
- Written by Jana Gentry Smith
Imagine you're in the middle of a warehouse, surrounded by a mountain of products, armed with nothing but a pen and an unorganized spreadsheet on a floppy piece of paper to guide you.
Sound like a nightmare? It doesn't have to be.
If you're an ecommerce seller trying to create picklists in Google Sheets, it feels a bit like trying to perform a ballet in hiking boots. Especially if you manage your inventory across multiple channels and marketplaces!
Sure, it works, but it's not pretty. Google Sheets is not the most efficient way to manage your picklists in the long run.
The real showstopper? Specialized software for digital picking.
But sometimes, you have to start with what’s available, and Google Sheets is a handy tool at your fingertips. So, for now, let's focus on mastering picklists in Google Sheets to help you ensure your team picks orders correctly and efficiently, and later in this article we'll explain a better and more efficient (and free!) alternative.
What is a picklist?
In its simplest terms, a picklist is a shopping list for your warehouse team.
Just like you list cookies, soda, and chips for a party, a picklist tells your team what items to "pick" from the inventory to fulfill a customer's order.
It's their roadmap to swiftly and accurately finding the right products in the right quantities.
How to create a picklist in Google Sheets (Step-by-Step)
Now that we understand what a picklist is, let's create one in Google Sheets. Follow these steps, and you'll have your picklist up and running in no time.
1. Open your Google Sheet
Once you’re ready, create and name a new Google Sheet in your desired file location. Now, we're all set to start building your pick list.
2. Add your picklist options to your spreadsheet columns
To set up your picklist, you'll want to organize your columns to streamline the picking process. Here are some column labels to consider:
Order number: A unique identifier to easily track each order
Item description: A brief product description so your picker can recognize the item being picked
Item code/SKU: A unique code or SKU for each product to identify the product quickly
Quantity: The number of each item to pick for the order
Warehouse location: The warehouse location if you have more than one warehouse
Bin location: The bin or location where each item is stored to help pickers find items quicker
Customer name: Useful for verifying order details or contacting the customer if needed
Ship-to address: The full shipping address (street, city, state, zip code) for accurate delivery
Picked by: A column showing the person assigned to pick the order
Pick date: The date somebody picked the order so you can track the order or identify delays
Notes: A column for any extra notes or special instructions
Organizing your picklist with these details gives your pickers all the information they need for a smooth picking process. Keep in mind that you can customize these columns to suit your specific business needs.
Further reading: Here's Why You Need to Ditch Excel for Inventory Management
3. Create input options for your columns for streamlined picking
Once you've labeled your columns, your next step is deciding how to enter information into each field. We’ll share the input options we used for our test picklist and why we chose them. But first, let’s learn how to create drop-down lists.
How to create data validation for dropdown lists:
You’ll need to know how to validate data for dropdown lists for the next few steps. There are a few steps to it, but it will make filling out picklists easier down the road. Here's a quick guide on how to set them up:
Select the top cell of the column for fields you want to have drop-down lists.
Click the downward-facing arrow in the right corner.
Select Data validation from the menu.
Click on the + Add rule box.
Make sure the criteria is set to Drop-down.
Click the box that reads Add another item.
Enter the text or numeral you want as an option.
Click Add another item, and enter the next text or numeral you want as an option.
Repeat until you finish entering your list of options.
And there you have it: dropdown lists made easy.
Here is how we set up inputs for all of the fields in our picklist:
Field 1: Order number
All you have to do to create auto-populated numbers is type this formula into the first cell under your order number column: =CONCATENATE("ORD-", TEXT(ROW(A1), "0000")).
Then, click the little square at the bottom right of the cell (the 'fill handle') and drag it down to apply this formula to all cells in the column. Voila! Now, each new order will automatically get a unique order number.
Field 2: Item description
Consider keeping the 'Item Description' column as a text input so people can freely type descriptions.
If you're considering using a drop-down menu for consistency’s sake, you could craft a separate Google Sheet that serves as your product catalog. It should include SKUs and corresponding item descriptions. Then, make friends with the VLOOKUP function to pull data from your catalog spreadsheet.
Heads up, though—it might take a chunk of time to set up, but VLOOKUP helps link data from different spreadsheets.
Field 3: SKU
For the 'SKU' column, using a drop-down list makes your job a bit easier later. Just follow the data validation steps we covered earlier, and you're golden.
This approach isn't just about speed—it's also about accuracy. By eliminating the need to type in SKUs, we're sidestepping sneaky typos that creep in when fingers get fumbly.
Field 4: Quantity
For the 'Quantity' column, stick with the drop-down list like you did for SKU. Follow the same data validation steps, but this time, punch in numbers for quantity.
Field 5: Warehouse location
Use a trusty drop-down list again for the 'Warehouse Location' column. Follow the same data validation steps as before, but enter your warehouse locations this time.
The drop-down list saves a bunch of time (and errors) if you're running a multi-warehouse operation because it ensures the correct items are picked from the right places.
Field 6: Bin location
Next, we have the 'Bin Location' column. Again, let’s use the drop-down list. Use the same data validation process as before, but this time, add the specific aisle, shelf, and bin details.
Sharing bin locations serves as your warehouse's personal GPS. It guides pickers to the exact spot where they can find each product and helps you manage your inventory.
Field 7: Customer name
The 'Customer Name' column has options. You can keep it as plain text input or create a drop-down list if you tend to have regular customers.
Keep in mind that if you choose the drop-down option, you'll need to go through the data validation process to add each new customer's name to the data validation list.
It's more time and work upfront, but it could be a timesaver if you find yourself shipping to the same folks often.
Further reading: White Label vs Private Label: Which One is Best for Ecommerce?
Field 8: Ship-to address
You can keep the 'Address' column simple with text input.
Or, if it makes sense for your ecommerce business, you could create a Google Sheet with a customer list and their shipping addresses. Then, use the VLOOKUP function to ensure consistency.
Whichever route you choose, accuracy is key—no one likes a lost package.
Field 9: Picker name
Here's where your list approved pickers' names using the data validation list.
Assigning picking tasks and sharing the names with your team speeds up the creation of the picklist and ensures accuracy.
Everyone knows exactly who is supposed to pick the order: no guesswork, no confusion, just smooth order picking.
Field 10: Picked date
For the 'Picked Date' column, aim for consistency to avoid future guesswork.
You'll want to set your preferred date format to ensure dates are entered in the same format every time (and avoid any mix-ups between days and months).
To do this, click the cell in the column header. Then, navigate to Format > Number > Date. Choose your preferred format, and you're all set.
Field 11: Notes
Finally, leave the freedom of a text input for the ‘Notes’ section. Text input lets you drop in extra details to help your picker pick efficiently and accurately. Think of it as a memo pad ready to capture any tidbits that don't fit neatly into other columns.
Ta-da! Now, you’ve created a simple picklist using Google Sheets. Your result probably looks a bit like this:
Why Veeqo Trumps Google Sheets for Picking
While Google Sheets can get the job done, there's a tool that can do it better: Veeqo. Purpose-built for ecommerce sellers, its free software and app transform your picking process into a digital, efficient machine.
You can manage your entire order fulfillment process including inventory and warehouse management, and shipping, all in one place.
Seamless integration: Syncs in real-time with ecommerce apps and marketplaces to eliminate manual data entry
Optimized workflows: Streamline your process with efficient routing and wave-picking features
Mobile access: Update picklists on the go and say goodbye to paper lists
Almost Real-time updates: Near instant updates mean your picklists are always accurate
Pick path optimization: Spend less time wandering and more time picking with optimized routes
Smart order routing: Fulfill orders from the nearest location to your customer for faster delivery and reduced shipping costs
Veeqo scanner: Use the same scanner Amazon fulfillment centers use to process millions of orders globally
Batch-printing: When it comes to shipping, you can use Veeqo's batch printing feature to print up to 100 labels at once. This feature saves one of our sellers 6-hours of time per day!
Veeqo is an easy-to-use free app with an intuitive interface. No need to memorize formulas or learn shortcuts. Veeqo helps you beat carrier deadlines, streamline operations, and thrill your repeat customers—all from your phone.
Further reading: Toy retailer eChapps saves $15,000 a year with Veeqo
With seamless integrations with major 3PLs and carriers, such as Amazon MCF, Amazon Shipping, USPS, UPS, FedEx, and DHL, Veeqo also seamlessly integrates with marketplaces including Amazon, Walmart, eBay, Shopify and more. And with Veeqo being owned by Amazon, it makes it a great choice for anyone selling on Amazon.
As well as being free, you'll also be able to access the lowest commercially available rates, based on our pre-negotiated pricing from UPS, USPS, FedEx, and DHL and up to 5% back with Veeqo Credits. Mike Truffa, owner of A1 Great Deals has earned over $13,000 back in Veeqo Credits in just seven months, "We get Credits daily, we’ve gotten $13,469.36 back since September. It’s been a lot.”
Ben Chappell, co-founder of toy retailer, eChapps, told us, “As everybody knows, software very quickly adds up - the monthly cost, yearly cost - some even take a percentage of your sales nowadays, and that’s just profit gone. That is something nice about Veeqo, is there is no cost, it’s just a better product and it’s free.”
Ready to handle your picklists more efficiently and lower your costs? Sign-up to Veeqo today.
