Excel in logistics: consolidation, linking, and version control

If you’re in logistics or supply chain, you have definitely already faced difficulties brought on by the overall complexity of the business. One of the tools that makes our life easier is spreadsheets, which are used nowhere else but in the famous MS Excel document. Nevertheless, Excel doesn’t make your logistics less complex, but it can help your teams merge and combine various data sources and deliver consolidated versions of documents to your desk.

If you’re an operational manager, you most likely want to rely on consolidated data sources from operations and the operations support group, which means the office or Control Tower. Remember that all logistics people use Excel for various parts of data registering, inventory control, and shipping management, so you need that data combined to get the right outlook for overall operations.

This article will tell you how to consolidate and merge spreadsheets and build internal links in Excel files. We’ll also share AI in Excel features, such as version history, and restore, to not lose your data, that Microsoft has enabled for its users. So, this article is a rich source of ideas on how to improve your data point management. You can hand over the material to your logistics co-workers and let them utilize these tips as much as possible, both on Mac and Windows operating systems.

What is data consolidation in Excel, why do you need it, and how to manage all the changes?

Data consolidation in Excel, which logisticians use, combines data from multiple worksheets or spreadsheets into a single location. If you’re using various spreadsheets for data collection, such as inbound reports, shipping reports, and others, your team needs to compare data at the end of the day. They need to collect both reports, whether stored on the shared drive, server, or in the Cloud, and they need to be handed over to a single person who will combine the data. This can be done through various methods, including formulas, functions, and tools provided by Excel. Simply put, you have two seamlessly temporary files that need to be merged to allow you to make comparisons on a single page.

As we said in the introduction, you need to consolidate data in Excel to get straightforward access to all operational results. By seeing daily receiving numbers, you can observe shipping volumes to understand how to prevent delays and inbound performance. This gives you an outlook on how the receiving department is doing. 

Despite this, in short, data consolidation offers several benefits, including:

• Efficiency. It simplifies data analysis and reporting by reducing the need to manually copy and paste data. So, if you input the correct command in an Excel file, like the right macro or formula, you can automatically pull data from files with the same name. That’s just one example, and we'll discuss more below.

• Accuracy. Consolidating Excel files in logistics minimizes the risk of errors when manually entering data. This means consolidation can be used to compare data sources, as logistics often involves multiple reports that may contain records of similar operations, such as outbound and inbound.

• Organization. Logistics must be supervised, and Excel improves data organization and accessibility. By having a single source of data for your key logistics metrics, you’re always up to date with what's happening in your logistics.

It's important to note that today's important Excel features include the Excel AI tools. These are enabled through a simple-looking interface on the right side of the screen, where you can get prompts from AI on how to consolidate data in Excel. This fantastic solution makes working in Excel much more accessible. However, you may still find this helpful article, as we share a more pragmatic approach that can provide hints on navigating AI for spreadsheets.

5 examples of data consolidation in Excel

To consolidate data in Excel, you must first put a good process in place for collecting separate data sources. Sometimes, it is enough if all warehouse and admin clerks store all the reports in a single folder dedicated to weekly reports in your logistics. You also need to assign responsibilities for who is supposed to do this from each department and who will back them up during their absence from work.

You may also want to get insights from a single Excel spreadsheet, so here are the five ways of consolidating data in your logistics Excel:

1. Benefit from the CONCATENATE function

This is how to combine text from multiple cells into a single cell.

=CONCATENATE(A2, " ", B2, " ", C2)

2. Start using the common but important SUMIF function

This is the solution for calculating the sum of values based on a condition.

=SUMIF(A:A, "Category 1", B:B)

3. One of the most important is using the VLOOKUP function

This helps you look up values in a table based on a specific criterion.

=VLOOKUP(A2, D:E, 2, FALSE)

4. Start using the Power Query tool

You may find this crucial for importing data from various sources and transforming it into a desired format. How to use it?

In step 1, let’s start with the main Excel spreadsheet view and Go to Data > Get Data > From File > spreadsheet.

Following step 2, select open your spreadsheet and navigate to the desired sheet.

By moving further in step 3, use the Power Query editor to transform and combine data as needed.

Upgrade your Excel workbook for logistics.

5. Nothing works better than using PivotTables

It's all about summarizing and analyzing large datasets.

To use PivotTables, you should select your data. Then, go to Insert > PivotTable, and at the final step, drag and drop fields into the rows, columns, and values areas to create a summary table. Automate data importing & consolidation in Excel

Excel's Power Query tool can automate data import and consolidation processes. This involves creating a query that connects to the data source, transforms the data as needed, and loads it into an Excel table.

TABLE

Get ready-to-use Excel formulas and functions and copy-paste into your spreadsheets

Full name*

Business email*

By sending this form I confirm that I have read and accept the Privacy Policy

Download

Why and how to link spreadsheets in Excel?

Linking spreadsheets in Excel for logistics allows you to create more dynamic relationships between different spreadsheets. You should be more familiar with these terms, but in general, linking can be helpful for:

• Real-time updates, where changes are made in one spreadsheet, are automatically reflected in linked workbooks.

• Data consistency ensures that data is synchronized across multiple spreadsheets.

• Higher efficiency, where you can reduce the need to manually update data in multiple locations.

Here is a guide on how to manually link spreadsheets in your workbook Excel:

First, create a reference, which should be in the formula bar, in an equal sign (=), followed by the path to the linked Workbook and cell.

Next, adjust the reference. Therefore, if you move or rename the linked spreadsheet, you may need to change the reference.

You can also use absolute references. Absolute references, such as $A$1, for example, prevent the reference from changing when you copy the formula.

Here is what it looks like in Excel: ='[Workbook2.xlsx]Sheet1'!A2

Let's also look at how to merge an open version of a shared Excel workbook. Merging is a great way to combine changes efficiently. You need to leverage the merge changes feature to seamlessly merge changes from different versions of a shared Excel file for daily logistics work. This powerful tool allows you to consolidate updates from multiple contributors into a single, unified version.

Here are the several steps to merge changes to match your general results:

• Navigate to the review tab in the Excel ribbon.

• Click on the merge changes button.

• Select the version you want to merge with the current one.

• Choose your desired merge options. You can accept all changes, reject them, or review them individually.

• Click merge to finalize the process.

40 Excel tips and tricks: Excel mastery free course

Article by:

Pawel Bes

How do you link between cloud-based files in Excel and sync them in real-time?

You can link between cloud-based files in Excel. Using this method is very useful in logistics as you can benefit from the more centralized data source. So, again, you can use this in the same way as methods for local files. Simply replace the local path with the cloud-based file's URL, and you can get your Excel file files downloaded into a single spreadsheet.

You can use cloud-based collaboration tools like Microsoft OneDrive or Google Drive to link Excel files in logistics and sync in real-time. These tools allow you to share files and collaborate with others in real-time. But you must remember version control before downloading or saving a file to avoid overwriting someone else's results. See more about it below.

What is Excel version control and how does restoring a previous version work?

Excel select version history control is a file extension that tracks and manages changes to an Excel file over time. This can help prevent data loss and get back unsaved workbooks where you have access to previous versions of your work. Below, we help you to find solutions to get your data back.

So, why do you benefit from Excel version control:

• Data recovery to get the previous version and simply restore a previous version. You can recover the previous version of the spreadsheet if you accidentally delete or overwrite data. You can use the restore button. By double click restore you'll get a popup where you can select version history.

• Collaboration and version history. Version control can help you collaborate on a single spreadsheet without overwriting each other's changes. It can be done through version history.

• Auditability. You can track who made changes to a workbook and when.

What are the Excel version control limitations?

While various types of Excel files offer various version control options, it's essential to be aware of their limitations. This becomes even more important as knowing them can prevent data losses and get your spreadsheet's previous versions:

• Manual backups. Relying solely on manual backups can be time-consuming and prone to errors.

• Cloud-based collaboration tools. While convenient, these tools may only meet some version control needs, especially for complex workflows or large-scale projects.

• Version control software. Using specialized software can introduce additional complexity and require a learning curve.

But let's consider options to address the limitations of Excel's built-in version control. We highly recommend to explore alternative version control options:

• Git. A popular version control system that can effectively track changes to Excel files, offering features like branching, merging, and history tracking.

• xltrail. An Excel file version add-in that provides comprehensive version control and audit capabilities, making it a valuable tool for tracking changes and identifying potential issues.

• Dolt. A SQL database designed for version control, allowing you to track changes to Excel files and other data in a structured and efficient manner.

Conclusion: getting your Excel file to free up logistics operations

With its powerful features and versatility, Excel files have become an indispensable tool in the logistics and supply chain industry. By effectively consolidating data, linking spreadsheets, and implementing version control, you can streamline operations, enhance decision-making, and improve overall efficiency. It is an excellent time to explore and implement new ways in your business.

Experimenting with the techniques and tools discussed in this article to optimize your logistics processes can go far beyond. At Adexin, we help companies increase their operations through various ways that improve their data-driven decision-making processes with custom logistics software development. We can help you switch from basic Excel spreadsheets to fully automated and centralized data sources. By embracing these recommendations, you can leverage Excel's full potential to drive success in your logistics endeavors.