We often discuss Microsoft Excel's advantages in our articles and how logistics can benefit from them. Although we are strong fans of Excel's capability to solve most major problems in daily logistics operations, we still want to remain objective. We should discuss the positive aspects, but each solution has pros and cons, and Excel isn't an exception.
The bigger your company is becoming, the more requirements it will involve, and not all of the operations can be handled by Excel. There are many Excel data limitations, and you cannot overcome this even with skilled experts who know Excel inside out. However, you can still work to some extent with big data in Excel, and we give you a better picture of data analysis in this article.
Look further into this article to learn more about our cases and how we have exploited Excel so far and in the past. That's true because, in the article, we share both direct experiences with Excel in logistics and from within the IT industry, where we delivered solutions for managing various operations. Come with me, and let's see.
Understand Excel data limitations: a brief explanation
Excel limitations are known on many levels. You cannot do all that you want unless you will not close your strategy in certain frames. These frames are about the edges of Excel capabilities. You just cannot do all of this because the engine of this brilliant app has its limits. Of course, you can figure out how to force some breaks and demand higher performance from Excel, but this effort will equal building complete custom logistics software.
Alright, so going straight to the point, out of our experience, we can summarize the main limitation of big data management. We were able to break this limitation into three groups. Here they are:
Technical limitations
Cell size and entire data set. Cells have a maximum character limit of 32,767 characters.
Row and column limits. While there are millions of rows and columns, working with extremely large datasets can become slow and cumbersome. Sometimes, we can find comments on various forums that people search for solutions to bypass Excel with more than 1 million rows. This can happen with any important logistics project where it is needed to export, let's say, 9 million rows of data. Excel cannot exceed the limit of 1,048,576 rows and 16,384 columns. Splitting data across several sheets is not an option. So, alternative programs could include Microsoft Access which allows for a bigger data set.
File size. Large files can slow performance and cause issues with certain features. Because of this, you won't be 100% able to do anything more.
Formula сomplexity. Complex formulas can be difficult to understand and maintain, especially for those without a strong understanding of Excel's functions. We explained in our other articles on Excel, how important it is to have at least one skilled person in your logistics who can solve the most common problems with Excel. Most people in logistics or office work don't have Excel skills. They can perform repetitive tasks on Excel but struggle with more complex calculations, etc.
Data type limits. Excel has limitations on the types of data it can handle. Some of them might be the size of numbers or the length of text strings. You won't be able to fill cells with fewer digits, etc. But personally, I don't consider this a challenge. You won't complain about that limitation by doing daily reports in logistics.
Functional limitations
Database capabilities. While Excel can handle databases, its capabilities are limited compared to dedicated database management systems. So, here, we actually narrowed the subject of this article much more. Data capabilities are crucial if we begin to work with data analysis to oversee past data from the last 2-3 years and prepare forecasting. By doing this with Excel, you'll need to use multiple files and collect and narrow data volumes and variables to get a more holistic picture. Yes, this might be quite troublesome and time-consuming.
Statistical analysis. While Excel has many statistical functions, it may not be as powerful as specialized statistical software for complex analyses. At this point, one of the major problems is outlined -- how to effectively automate data processing. Analyze Data has limitations in dataset size, date formatting, and compatibility mode.
Programming and data model. While VBA can be used for programming in Excel, it has limitations compared to full-fledged programming languages. For example, I built a tool for registering RMA with a nicely looking interface in Excel, where all data calculations run in the background in cells on behalf of the macro.
This was a nice tool, as anytime you clicked the Excel icon from the file, it automatically opened the VBA and minimized the Excel workbook on the desktop. In that way, the user hasn't seen a complex spreadsheet/worksheet with hundreds of data cells but only nice windows with 4-5 windows to fill data in and buttons to click confirm.
It was a good solution used in various warehouses of my previous company, but I need to tell you something. Frankly, it was far from perfection, and there was always the risk that the tool would crash while someone had a different setting on his PC. I couldn't guarantee that it would work after the Microsoft Windows system updates. So, it wasn't better than custom logistics software, which was far better isolated.
Collaboration. While Excel has collaboration features, they may not be as robust as those in dedicated collaboration platforms. This means that by using Excel, you won't be able to control access to files and track changes as well as you can with a custom solution. There might be many cases in which you want to gain an overview of who has access to the files, and with Excel, you cannot control that so easily.
In a bigger company, bigger problems arise as you always need to ask the IT department to enable access, etc. With custom software, it is far easier as you can have a dedicated panel at your fingertips. Of course, there might be more similar things related to Excel collaboration, but we won't exemplify all of them.
Performance limitations
Large datasets as a powerful tool. Working with large datasets can often lead to slow performance, especially when using complex formulas or functions. I mentioned this above as some action in Excel overleap, but in this specific case, it is even harder to get the right outcome when we need to sort out, filter out, or search for certain results.
If you don't have established really good data sets, you won't be able to use the full capabilities of Excel. When I say really good, it means that you must mostly split certain values among various cells in Excel and then set references with macros or formulas. It is very time-consuming.
Complex calculations with large datasets. Complex calculations can also slow down performance, especially involving many cells or formulas. This should be clear – Excel becomes slower as you have more data. It isn't always about your hardware. It's just because Excel won't be able to calculate data setups with large formulas. What I faced most was that Excel crashed in two different ways when we added huge data sets and fairly complex formulas.
First, less problematic, it showed us malfunction with macros, even if debugged macro several times. The Second was that Excel completely froze and stopped working. We were required to force the system to stop. So, in fact, we knew that we could not write huge macros, as if we did many repetitive calculations, it would shoot down Excel completely.
Add-ins and multiple sources. Some add-ins can slow down Excel's performance, especially if poorly designed or resource-intensive. It is very common to add some plugins to make jobs for us. Some guys will say that the macro modules can be seen as Add-ins, but I disagree. Add-ins in Excel are like SmartArt, which creates professional-looking diagrams and charts. In fact, you need to do a lot of manual work to make your data look nice. I cannot even recall that anyone has time in logistics to use it. Except me, to some extent.
So, to be clear, macros are recorded actions or VBA code sequences that automate repetitive tasks within Excel. Add-ins are external programs that can be integrated into Excel to provide additional features or functionality.
How to manage big data in Excel?
This is an obvious question, after all we have said about all those limitations. So, don't worry, you can somehow manage big data in Excel. Let's not forget that Excel is primarily designed for smaller datasets, it can still manage larger data sets with careful planning and techniques. Here are some hacks to help you effectively handle big data in Excel:
1. Process to optimize data structure
Remove unnecessary data. Delete columns or rows that are not relevant to your data analysis.
Use data validation. Ensure data integrity by setting up rules to restrict input types.
Normalize data. Break down large tables into smaller
2. Ability to leverage Excel's features
Filtering and sorting. Quickly narrow down your dataset to specific criteria for data cleaning.
Use pivot tables. Summarize and analyze data with large amounts of volumes and variables in a structured format.
Conditional formatting. Highlight important data or trends visually to get a holistic picture of the entire data.
Named ranges. Assign meaningful names to cell ranges for easier reference in formulas.
3. Consider external data analysis tools
Power Query. You can import data from various sources (CSV, text files, databases) and transform it before loading it into Excel.
Power Pivot. Create data models and perform complex calculations on large datasets.
MS Access. This complex tool from Microsoft should help you build huge databases that you can export and integrate with Excel.
How to work with big data in Excel?
There are many ways to work with big data, such as using Excel. For example, to overcome issues with data analysis, you can filter large datasets or convert text strings to dates using functions like DATE or DATEVALUE. Doing this allows you to save files in modern formats like .xlsx, which is based on XML (Extensible Markup Language).
It makes Excel more flexible and easier for developers and programmers to work with. By addressing these limitations and applying the suggested workarounds, you can effectively use Analyze Data for your data analysis needs.
We can give you countless examples of how to work with Excel more proficiently. You just need someone who knows how to do it right. But this is not the case. This article aims to better understand what kind of tool you are using, based on this, you should better cope with challenges. All that I said is like a guideline to tell you what you need to be prepared for or what tool you're using.
Well, to not beat around the bushes, you can make it all much easier. What if we remove Excel because it still seems to be too troublesome? I would like to bring to light one of the cases. We helped a company with a custom order management system developed to create, manage, and optimize the procurement process. This model was suitable to exchange all Excel-based processes and actually shorten training time for staff. Do you think that might be an option for you? Well, we have a full hand of experience in that area and we’re willing to help you.
Are you in search of a reliable tech partner?
Adexin can help with advanced logistics solutions
Contact usFinale takeaway
At Adexin, we help companies work with Excel and integrate it with software that you have on-premises or custom build one. However, our core work is building custom software for logistics. So, we won't convince you to switch from Excel to Custom software, but we want to highlight that if you want to use it with big data, just be ready. It can be as difficult as you can only imagine if you don't have the right support. We have often seen that it is much better to develop custom software. Custom software can help you manage big data much easier without constant support from your IT team, which is the opposite of Excel.
Well, that's it for today. We hope you like it and that you'd rather contact us shortly. We can support you with Excel and our knowledge of logistics programming.