Extensive spreadsheet use was supposed to be just a means to an end, playing a supporting role in organizingbusiness data. Yes, Excel can handle curating products, managing logistics, tracking transactions, creating reports. Its prevalence, accessibility, flexibility and power make it the go-to tool for managing and communicating data. However, the relationship between user and Excel is often a shaky one. Manipulating values in cells somehow came to consume much of the working day and we never had much of a say in it. But it doesn’t have to be this way.
Below, we identify some of the common processes we see retailers managing in Excel, and the pain points around those processes. Our clients have shared enough spreadsheet burdens that we’ve decided to create tools and strategies for streamlining their workflow. Like most tools, when used in the right way, Excel can be incredibly powerful. Despite what you might have experienced, with a bit of structure and automation, it’s entirely possible to build spreadsheets efficiently tuned to your processes. We’ve helped retailers turn chores that previously took hours or days into a matter of mouse clicks, and we see how liberating that can be. Once you know what Excel’s capable of, you’ll never look back.
Creating supplier/partner documents
Our relationship with suppliers and partners often hinges on how well we communicate with them. This communication often comes in the form of spreadsheets detailing product designs and specifications or tracking materials and logistics. It’s a relationship that requires both parties to be rigorous and consistent, and documents play a big part in this interaction. These documents often take the form of tables stripped or enriched of data specific to the recipient, or they might be more tightly formatted, with logos and legal clauses. Retailers often struggle with the time it takes to create these documents on a weekly basis. Collating data from different sources is, as a manual process, both time consuming and prone to error.
Excel is a very flexible way to create these documents, since the data typically already resides in Excel or can be easily imported. It also offers a lot of freedom in formatting, cleaning data and managing calculations. Add the ability to write scripts using macros and you can largely automate the entire process at a batch or ad-hoc scale.
We’ve helped retailers build custom templates and scripts to manage this process through a single click. Each document can be logged so that all parties know where they stand. It’s important that the template is flexible enough to manage variations across all suppliers or partners whilst meeting all their requirements. The automation should also validate all information is present and correct. It should name each file appropriately, storing them in easily referable directories.
Pulling data from multiple sources
We regularly have to pull data together from multiple sources, for example, spreadsheets, databases, websites, or documents. The data then typically needs additional cleaning and calculating to get it in the right format and at the right level for analysis. This tends to become several steps of copying and pasting, correcting typos and extending formulas, and refreshing pivot tables and charts. In short, it’s a grind that eats up a lot of focus and time.
Excel is one of the best platforms for collating data. It’s naturally capable of importing data from virtually any source, whether using formulas to look up from other workbooks, the inbuilt import functionality, or custom scripts. There’s also some basic functionality to clean up duplicate records and split data into separate columns. And once you’ve settled on a logical routine, much of this process can be captured in macros.
For more complicated setups, we’ve written scripts to pull in, validate and clean data from a variety of different sources, usually from other spreadsheets or ERP systems. Structure is the key. As long as the data resides in a consistent format with appropriate headings and any necessary IDs, we can clean, collate and organize the data into any useful format. For reporting, particularly with large datasets, Power BI and other reporting BI tools provide additional power and visual options, and they can be set up to run on a schedule.
Reconciling external data
In communicating with 3rd parties, we often have to validate their data, ensuring it aligns with our own. Validation might mean checking that quantities match, product details are up-to-date, dates are reasonable, and prices are as agreed. Any anomalies should be flagged and communicated in a timely fashion. It’s another manual process that is mentally taxing, time-consuming and vulnerable to error. This is only compounded as data sets grow and change on a regular basis.
As a universal document format, spreadsheets are the natural form of data communication between external parties. For reconciliation, we can write formulas for simple comparisons and use conditional formatting to highlight any differences. Or we can use pivot tables to get an aggregate view and analyse larger discrepancies. Teams can also share files online for real-time collaboration, using a messaging system like MS Teams or Slack for open dialogue. It’s important to agree a standard format for sending data and a consistent way to communicate and handle differences as we move towards more open, fluid data flow systems.
Yet we’ve also come across large data sets that require more complicated reconciliation, and for that we have written scripts to automate the process, produce exception reports, correct the data or trigger the relevant actions and updates. Once verified, data can be imported directly back into the original data sources. In an ideal world, much of this data will be synchronized and validated automatically, and through Excel and online tools, we can achieve that to a large degree.
Tracking dates and changes
Retailing naturally comes with a host of time-sensitive activities. There is a schedule for every product we bring to market, and when things go wrong, it’s vital we’re able to react quickly. Besides tracking dates, it’s also useful to track other details for reference, communication and auditing purposes. These changes might include price updates, quantity adjustments, amended product specifications or last minute logistical rerouting. Keeping everyone informed on changes in a timely fashion is the foundation of smooth operations.
We’ve seen retail teams build countless ‘Trackers’ in Excel for all the activities and transactions in progress. There’s a tracker for managing samples, for logistical processes, for Quality Control, for invoices, etc. It’s an administrative headache that needs constant attention. Excel allows for quick, constant updates and helps us filter to the things that need our attention. But there are areas where it could do with some upgrades.
Sometimes we also want to keep a running history of any changes applied. Excel’s “Track Changes” feature is closely tied to its Shared Workbook functionality, which has always been unreliable and something Microsoft seem to be phasing out in favor of Excel Online. And for most purposes, Excel Online does the job. However, for more complicated tracking, we’ve built tools that track changes over set intervals, validating and creating reports on specific changes, especially if they need to be communicated with others. Teams have also found it useful to be automatically alerted of any potential issues regarding dates or changes. We’ve helped teams’ setup scripts and schedules to continually stay informed on only the things that need their attention, relieving the constant anxiety of retail logistics.
Image management
Accompanying data with appropriate images makes working with that data far more memorable and meaningful. Fashion products in particular benefit greatly from visuals, even if it’s only a thumbnail. And images are ultimately necessary for manufacturing, e-commerce, catalogs and reporting. There are systems dedicated to managing all the files that follow a product through its life cycle, but most of the time, we’re in need of something much more light-weight and customizable, without the expensive overhead. We also want to escape some of the manual tedium that comes with organizing images.
If you’re working with a reasonably sized data set, images can be inserted into Excel alongside product info, though this can be fiddly when managed manually. Two automations we’ve built for clients are the ability to rename images that have been captured in batches and the ability to insert images into Excel, either alongside data tables or in more custom arrangements for catalogs and reports. Images can be resized to fit the layout and compressed to reduce file size. These automations save hours if not days of image management and are totally customizable to very specific workflows and different types of images.
Streamlining Data
The power of Excel is in its agility. And continually aligning your business processes with the needs of the modern consumer requires a high degree of operational agility. If you don’t have a willing team of forward-thinking software engineers at the ready, Excel gives you the pragmatic agility to keep adapting with the best of them. Taking advantage of the hidden power behind Excel can drastically improve morale and productivity and empower teams to take on larger goals. Teams are constantly surprised at what can be achieved, but once they’ve had a taste, a whole new world of possibilities is open.
If any of the processes we’ve mentioned are a burden in your daily work life, get in touch with us at [email protected] to discuss how we can help streamline your workflows.
