Inventory Management
Step-by-Step: How to Create and Add a Picklist in Excel
- Written by Jana Gentry Smith
Lately, you’ve spent your spare moments watching TikTok’s Miss.Excel trying to learn how to create a pick list in Excel. Now, you’re here—hoping we can show you how to make a pick list that will appease your warehouse team so they don’t mutiny.
We can, and we’ll get to that. But first, you need to know—there are better ways.
But sometimes, you have to start with what’s available.
And Microsoft Excel is a popular tool for creating spreadsheets because it’s already available on most business computers, so you can start using it for free.
It's easy to customize and has many helpful tools to make managing your stock easier.
So, for now, let's focus on mastering picklists in Excel to help you ensure your team picks orders correctly and efficiently (and later in this article, we'll explain how using software and digital picking can improve inventory accuracy).
What is a picklist?
Think of a picklist as the packing checklist for your warehouse team.
Just as you list all the essentials for a beach vacation (sunblock, swimsuit, flip-flops), a picklist outlines the exact items and quantities they need to "pack" from the inventory to fulfill a customer's order.
It's their fail-proof guide to ensuring no item gets left behind and the warehouse runs efficiently.
Further reading: Building a Smarter Future with IoT in Warehousing
How to create a picklist in Excel (Step-by-Step)
Alright, now that we have a handle on what a picklist is, let's roll up our sleeves and craft one in Excel.
Stick with these steps, and you'll have a functional picklist faster than you can say "inventory management" (not really because you’re creating one manually in Excel).
1. Open your Excel Sheet
When you're ready, start a new Excel sheet and give it a name in the folder where you want to save it. Now, we're ready to start building your picklist.
2. Add your picklist options to your spreadsheet columns
When setting up your picklist, arrange your columns to aid 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 to be 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 complete 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
Structuring your picklist with these labels equips your pickers with all the necessary information for a seamless picking operation. Remember, you can tweak these columns to align with your business needs.
3. Create input options for your columns for streamlined picking
After you've marked your columns, the next step is determining what information goes into each one.
We'll walk you through the data we used for our sample picklist and explain our choices. But first, let's cover 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. Stay with me here.
There are a few steps to this, but it will make filling out picklists easier later. Here's a quick guide:
Click the '+' symbol at the bottom of your Excel workbook to create a new Sheet tab. Name it after the column header for your dropdown options.
Add each dropdown option into a separate cell.
Once done, click one of the cells in the list and hit CTRL+T to form a table.
Go back to your picklist by clicking the Sheet 1 tab at the bottom.
Choose the top field in the column where you want your dropdown list.
Click on Data in the top menu, then select Data Validation in the Data Tools section.
In the Data Tools section, select the Data Validation icon.
In the settings pop-up, go to Allow and choose List. Click in the Source field.
Select the tab with the list you turned into a table, then highlight the cells to include in your list. They'll appear in the Source field.
Click OK. Now, cells in the selected column will have an arrow to access your list.
Click in the Source field.
Then, click the tab with the label belonging to the list you turned into a table.
Highlight the cells you want included in your list (they should appear in the Source field).
Click OK.
Make your selection for each cell.
And there you have it: dropdown lists made easy-ish.
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 Sheet at the bottom or Excel Book that serves as your product catalog. It should include SKUs and corresponding item descriptions.
Then, look into how Microsoft suggests you use Power Query to import data from other sources.
Field 3: SKU
For the 'SKU' column, a drop-down list will simplify your task. Just apply the data validation steps we went over previously.
This method isn't just for efficiency—it's also for accuracy. By removing the need to enter SKUs manually, we avoid potential typos that could occur.
Field 4: Quantity
For the 'Quantity' column, go to the Number section of the Home menu and select the Number format.
Field 5: Warehouse location
Also, use a drop-down list for the 'Warehouse Location' column. Follow the same data validation steps, but this time, input your warehouse locations.
This drop-down list will save time and reduce errors, especially in a multi-warehouse operation with multichannel orders. It helps your team pick the right items from the correct locations.
Further reading: Best Alternatives to Managing Warehouse Stock in Excel
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 add the specific aisle, shelf, and bin details this time.
Sharing bin locations serves as your warehouse's GPS. It guides pickers to exactly where they can find each product and helps you manage your inventory.
Field 7: Customer name
The 'Customer Name' column has options. If you have regular customers, you can keep it as plain text input or create a drop-down list.
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.
Field 8: Ship-to address
You can stick to basic text input for the 'Address' column.
On the other hand, if it fits with your ecommerce model, you could create an Excel Book with a directory of customers and their respective shipping addresses.
Then, use the Power Query process to maintain uniformity.
Accuracy is king, no matter the method you choose—lost orders can transform a six-year-old’s birthday bash into a birthday bust.
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 who is supposed to pick the order without guesswork and confusion.
Field 10: Picked date
For the 'Picked Date' column, make sure all dates look the same to avoid confusion later. Pick a date format you like and use it every time. This way, you won't mix up days and months.
To do this, click the cell in the column header. Then, from the Home menu, go to the Number section and choose your preferred date format from the drop-down list, and you’re good to go.
Field 11: Notes
For the 'Notes' section, keep it as a text input. This way, you can jot down any extra details to help your picker do their job correctly. Think of it as a notepad for any information that doesn't belong in the other columns.
Look at you! You just made a straightforward picklist with Excel.
Veeqo is the hassle-free alternative to Excel
While Microsoft Excel is a good mid-point solution, it’s not the best tool if you have a medium to large inventory.
When you're dealing with lots of products, selling on different platforms, and trying to manage your time, Excel is a hair-pulling way to lose most of your day.
You’ll spend the rest of your day brainstorming ways to ditch Excel for inventory management for a dedicated inventory management tool. Veeqo is your easy, headache-free answer to Excel.
Veeqo’s app is designed for folks running ecommerce businesses. It prevents errors and turns your picking process into a super-efficient digital machine.
With Veeqo, 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
Near-instant updates: This means 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: For shipping, Veeqo's batch printing feature can print up to 100 labels at once. This feature saves one of our sellers 6 hours of time per day!
Veeqo’s easy-to-use free app has an intuitive interface. There’s no need to memorize formulas or learn shortcuts.
Plus, Veeqo helps you beat carrier deadlines, streamline operations, and thrill your repeat customers—all from your phone.
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.
Since Amazon owns Veeqo, 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.
Want to handle your picklists like a pro, win back your time, and cut down on costs? Sign up for your free Veeqo account today.
