If you’re reading this, you’re probably well aware of the importance of good inventory management in growing your eCommerce business.

But we understand that not everyone is ready to invest in a full-blown inventory management platform. We have to use the tools available to us, and any inventory management is better than none at all.

For many, this means using Microsoft Excel as a makeshift inventory management system. 

Excel is one of the most popular tools for tracking products. Efficient utilization of Excel requires proper planning and organizing data to prevent unwarranted issues.

Excel could be a reasonable option for those searching for a low-cost way of managing their inventory. Despite its limitations, there are some tips and tricks that you can incorporate into your spreadsheets to make them more dynamic and interactive.

Basics to Consider When Using Excel for Inventory Management

Alphabetize in Excel

Alphabetizing in Excel is data manipulation 101. The feature comes in handy in countless scenarios, such as organizing lists of items or numbers in ascending or descending order.

The most straightforward way to alphabetize in Excel is to use the “Sort” feature located in the “Data” tab of the ribbon. The feature makes it possible to alphabetize both rows and columns.

Alternatively, you can also use the “Z to A” and “A to Z” shortcuts to quickly reorder cells when you have a small batch of data.

Make the Most of Excel Shortcuts

While keyboard shortcuts may not seem “basic,” we highly recommend getting these under your fingers early on. Any Excel pro will tell you that the less you touch the mouse, the more productive you’ll be. 

Build these habits now, and you’ll thank yourself later. Some of the handiest shortcuts include:

• Ctrl + Spacebar – to select a column
• Ctrl + ‘– to copy from the top cell
• Ctrl + 9 – to hide row
• Ctrl + 0 – to hide column
• Alt + Shift + F1 – to insert a new worksheet
• F2 – navigate to the proceeding cell
• Alt + H + D + R – to delete a row
• Alt + = — to auto-sum numbers

Excel has more shortcuts than these that can prove to be a great time saver. Master them to have a simpler experience.

Create Several Sheets

Sometimes you may have complex data or have a lot of data spanning many cells. To make it feel less overwhelming, you can utilize multiple sheets and interlink them as necessary.

As you store your data on multiple spreadsheets, make sure to properly label them to avoid confusion. In so doing, you can quickly find the information you are looking for. 

Please note that any new workbook you create comes with one worksheet labeled Sheet1. You can rename this sheet by right-clicking on Sheet1 and choosing Rename from the context menu. 

It may not seem necessary when you’ve got two or three sheets. But trust us, when you’re wrangling a dozen interlinked sheets (a very common inventory management scenario), you’ll want a logical naming convention.

You can duplicate the content of one sheet to the next from the same context menu. Choose the Move or Copy option from the context menu. Click the checkbox Create a copy, then OK.

To make things more visually distinguished, you can change the color of your worksheets. From the worksheet menu, choose Tab Color and select the desired color. 

Save the Workbook as a Template

If you frequently use the same layout for your workbook, save it as a template so that you don’t have to start from scratch every time. You can use a personal template to create the workbook or download pre-existing templates from Microsoft Office Online.

To save your workbook as a template, set the default personal templates location.

1. Click on File and then Options
2. Choose Save and then Default personal templates location
3. Set the location that you prefer. You can use the above Default local file location to guide how to define the location for templates.
4. Click OK to save the changes
5. With the Template’s location set, export your workbook but change its file type to Template. Give it a resonating name and save.
6. You can now create new workbooks from the Template that you just saved.
7. Click on File and then New
8. Choose PERSONAL
9. Double-click on your Template

Basic Excel Formulas and Functions to Work on Your Data

Whether you have advanced knowledge of Excel or getting started, understanding the basic formulas and functions can go a long way in saving you time working on data and keeping things organized.

Formulas refer to any dynamic operation that you execute in a cell. For instance, =B3+B4+B5. On the other hand, functions are predefined Excel formulas, such as =SUM(B3:B5).

If you’re managing your inventory in Excel, you’ll want to have these formulas in your back pocket:

• SUM “=SUM(C8:C30)” aggregates the cell contents in the range
• AVERAGE “=AVERAGE(C8:C30)” gets an average of data in the range
• COUNT “=COUNT(C8:C30)” counts numeric cells
• COUNTA “=COUNTA(C8:C30)” counts all cell types
• MAX & MIN when you want to get the maximum and minimum number in a range
• TRIM to deal with unruly spaces
• IF when you want to sort data as per a predetermined logic (for example, IF a certain condition is true, THEN perform another action)

Work With Hyperlinked Cells to Save Time

Hyperlinks are common on web pages, but they also work out great in Excel. You can easily identify hyperlinks by placing your cursor over them or looking for the blue and underlined styled text. 

To insert a hyperlink, choose the cell with text or select a picture or shape. Click on Ctrl + K to launch the hyperlink Dialog box. Navigate to the location where you would like to establish a hyperlink. 

You’ll have various options, including:

• Existing file or web page
• Email address
• Link to the current document
• Create a new document to link to

Linking to an existing file or webpage is the most commonly used hyperlink. For example, linking out to your eCommerce store backend or Google Analytics dashboard. 

Editing or deleting hyperlinks

To edit or delete a hyperlink, right-click on the cell with hyperlink and select desired action from the menu.

Also, you can edit the default blue color of your hyperlink in the same way you edit text color. More use cases for including hyperlinks in an inventory management context include: 

• Attaching shapes that resemble buttons to give your system a more professional touch
• Interlinking large Excel workbooks, perhaps siloed by warehouse location or supplier 
• Attaching purchase orders, tracking information, or other relevant documents to the corresponding data

Hiding Unnecessary Data

As your Excel skills grow, so will your sheets. Having data you don’t need handy cluttering up your workspace can be distracting.

An underrated and highly useful way to overcome this is simply hiding data. Hiding unused sections in your Excel involves two functions:

1. Hide rows
2. Hide columns

Hide unused rows by selecting the row at the bottom of the last used cell. Press Ctrl + Shift + Down Arrow to highlight all unused rows. 

From the Home tab, click the drop-down arrow on the Format menu. Choose the Hide & Unhide option and click on Hide Rows.

Hide unused columns by selecting the first empty column header. Press Ctrl + Shift + Right Arrow to highlight the other unused columns.

From the Home tab, click the drop-down arrow on the Format menu. Choose the Hide & Unhide option and click on Hide Columns.

 

[html_blocks id=”20105″]

 

Pro-tips for Sorting in Excel

Sorting alphabetically is a good start, but when you start dealing with 1,000+ rows of data, you’ll want some more strategic ways to sort.

Sort in Numerical Order

Most inventory managers prefer sorting in ascending numerical order because it offers an organized, objective data view. Plus, it’s easy. 

The sort operation is performed from the Data tab of the ribbon:

1. Choose the column that you would like to sort
2. Navigate to the Data tab and choose Sort
3. Choose the Sort by column and the order (smallest to largest)
4. Click OK

You can also perform the sort operation without opening the Dialog box. While on the Data tab, click on the ascending command to sort as desired.

Below are some examples of other sorting methods at your disposal.

Sort by Color

Ordering your rows by color allows you to easily sort data using color and get the inventory information you want. For instance, you can decide that any cell colored in green appears on top (or on bottom).

You can see how stacking these sorting methods opens up a whole new world of data visualization. 

Sort Multiple Columns

Most of the time you work in Excel will require you to sort multiple columns. 

In most cases, the setup is as follows: you start with a primary sort operation followed by a secondary sort based on the primary Sort. 

You can continue with the sort function to more than just two columns.

Sort a Range of Items

Sorting data in a range is a crucial part of the analysis in Excel. It could involve arranging items in alphabetical order, sorting your inventory from the highest to the lowest, or arranging items per a certain color scheme.

When you sort your range of items, you can easily visualize the data, get what you are looking for, keep everything organized, and make informed decisions without affecting the rest of your workbook.

To sort a range of items, choose the cell range you would like to sort and then move to the Data tab and choose Sort. Make your sort operation definitions and click on OK to sort as desired.

Please note that even though you can sort a range within a range, we don’t recommend it. That’s because the results obtained do not associate the sorted range with the original data, and the data is no longer accurate.

Add Multiple Levels to Any Sort

The simplest part of working with Excel is when you have to sort only one column. However, most inventory management scenarios will have you working with many more. 

For instance, you may have three columns – Region, Sales Rep, and Sales. If a sort operation is performed by the Region column, then by Sales Column, you can determine the performance of a sales rep in particular regions.

Whereas sorting data by one column may be straightforward for some people, sorting multiple levels requires you to take additional steps. You can use the Dialog box to achieve multiple-level sorting.

Navigate to the Data tab and choose Sort. Define the first sort operation from the Dialog box and click on Add Level to achieve multiple level sorting.

Sorting Faster With Custom Filters 

You can effectively locate and analyze data in spreadsheets using the filter operation. With this tool, you can isolate the essential components that you need in a column of data contained in a table.

On the other hand, sorting makes it possible to sort based on alphabetical order, number, date, and many other criteria.

When you choose to filter data, a drop-down menu applies to each of your column’s headings. Clicking on the drop-down icon reveals several options for further drilling down the data.

Conclusion

While Excel is a powerful tool that can take years to master, we’d be lying if we told you it’s the best choice for inventory management.

If you’ve got your heart set on growing your business quickly, you’ll soon bump into Excel’s limitations.  We’ve written more tips on using Microsoft’s iconic program for inventory management, but plain and simple: Excel doesn’t scale well.

Forward-thinking eCommerce businesses need an agile software solution that keeps up with ever-changing demands. SkuVault exists to serve that need so business owners can get back to working on their business instead of in it. 

To learn more about SkuVault, click the link to schedule a demo today. Or, if you’re new, subscribe to our blog for more in-depth articles on inventory management, eCommerce, and logistics.