Excel Hell or how to overcome the challenges of data management?
Miguel Domingos
BDM | Knowledge Sharing, Partnership, Craftsmanship, Pioneering Innovation & Know-How.
In today's business world, data management has become a complex and crucial task. Controllers and corporate finance departments often face major challenges when it comes to manipulating and analyzing data in Excel. However, despite these difficulties, Excel remains a powerful and indispensable tool.
In this article, we'll explore the specific challenges of using Excel for data management, and propose strategies to improve its use. We strongly believe that, with the right practices and a thorough understanding of its features, Excel can be transformed from an obstacle into a valuable ally for data management.
Together, we can turn these challenges into opportunities, and make data management a strength for your business. Stay tuned to find out how.
I. Complexity and Errors
A. Complexity
Excel, with its endless grids and cryptic formulas, can seem like a maze for novices. Cells expand, creating an ocean of possibilities, but also confusion. The formulas, while potent, require surgical precision to function properly. A misplaced comma or a forgotten parenthesis can turn a useful formula into a source of frustration. For novices, Excel can look like a complex puzzle where each piece must be perfectly in place.
For advanced users, Excel offers an even greater level of complexity. Pivot tables, macros, and advanced formulas are all powerful tools that can turn raw data into valuable insights. However, these tools require a thorough understanding and delicate handling. A poorly configured pivot table can yield misleading results, and a poorly written macro can cause more problems than it solves.
However, despite its complexity, Excel remains a valuable tool. With patience, practice, and a willingness to learn, users can navigate Excel's maze and discover the treasure trove of efficiency and productivity it offers. Whether you're a novice or an advanced user, Excel has something for everyone. You just have to take the time to understand its complexity and tame it.
?
B. Mistakes
Excel, in all its glory and complexity, is a tool that, if used incorrectly, can lead to errors. These mistakes can manifest themselves in a variety of ways and lead to significant consequences.
First, there are the formula errors. An incorrect formula can give erroneous results, leading to decisions based on inaccurate information. A simple typing error can turn a precise formula into a source of errors.
Secondly, there are the format errors. Excel offers a multitude of formats for data, but misuse of these formats can lead to errors. For example, a misformatted date can be interpreted as ?another date or even as a number.
Third, there are sorting and filtering errors. Incorrect sorting or filtering can lead to incorrect analysis of the data. This can lead to incorrect conclusions and ill-informed decisions.
Finally, there are the errors related to the use of advanced features like pivot tables and macros. These powerful tools can turn raw data into valuable insights, but misuse can lead to errors.
However, despite these challenges, it's important to remember that these errors are usually the result of misuse, not an inherent flaw in Excel. With the right support and technology having attention to detail, most of these mistakes can be avoided, allowing Excel to realize its full potential as a data management tool.
C. Some examples of mistakes made with the use of Excel
There is no shortage of cases reported in the press where these errors have led to major problems
1. JPMorgan Chase's "London Whale"
In 2012, a formula error in an Excel template contributed to $6 billion in trading losses for JPMorgan Chase. The error was caused by the incorrect use of an Excel formula for risk calculations.
2. Barclays Capital
In 2008, during the sale of Lehman Brothers, Barclays Capital accidentally acquired 179 unwanted contracts due to a concealment error in Excel. This led to a legal dispute to cancel the contracts.
3. Monitoring Covid-19 in Great Britain
Indeed, there was a notable incident in the UK where Excel played a role in managing the Covid-19 pandemic. In 2020, nearly 16,000 coronavirus cases in England flew under the radar due to a problem with Excel.
According to reports, an Excel spreadsheet used to compile Covid-19 test data has reached saturation and stopped uploading test results. This distorted the national database where these results were normally consolidated.
As a result, 15,841 new cases were missed between September 25 and October 2. These people were not taken care of by the government's "Test and Trace" tracing system, which should have prompted them to self-isolate. In addition, those with whom they were in contact were also not tracked, meaning that more than 50,000 people potentially continued to spread the virus.
?
II. Lack of Control, Traceability (and therefore Collaboration)
Excel has some limitations that can give the impression of a lack of control. Some of these limitations include:
1.???? Error handling: Excel does not have a robust error handling system. A small error in a formula can lead to errors throughout the workbook, which can be difficult to detect and correct.
2.???? Change tracking: Excel doesn't have as robust a change tracking system as database management systems. This can make it difficult to detect the origin of an error or unexpected change.
3.???? Data Security: Excel is not designed to handle sensitive data. It doesn't have the same access and security controls as database management systems.
4.???? Scalability: Excel has limitations in terms of the size and complexity of data that it can handle effectively. When working with large volumes of data or complex calculations, Excel can become slow and unstable.
5.???? Data integrity: Excel does not have the same data integrity controls as database management systems. This means that data can be easily changed, deleted, or duplicated by mistake.
III. Capacity Limitations
Excel, despite its popularity and power, has significant capacity limitations.
A.??? Limitations of the Data Grid
An Excel sheet is made up of a number of rows and columns. Their number is limited: 1,048,576 rows and 16,384 columns. Additionally, the .xls file format has a limit of 65,536 lines in each sheet, while the .xlsx file format has a limit of 1,048,576 lines per sheet.
B.??? Memory Limitations
The 32-bit environment requires 2 gigabytes (GB) of virtual address space, shared by Excel, with the workbook and add-ins running in the same process. The 64-bit environment does not impose any strict limits on file size. The workbook size is limited only by available memory and system resources.
C.??? Character and Cell Limitations
The maximum number of characters that can be stored in a cell is 32,767 characters, which corresponds to 2^15 (or 15 bits) for text, and 8,192 characters for formulas, or 2^13 (or 13 bits of memory).
领英推荐
D.??? Limitations of Spreadsheets
Normally, new Excel files can contain up to 255 sheets, which corresponds to 2^8, or 8 bits (1 byte). However, depending on the capabilities of your computer and memory, you may add more sheets.
These limitations can cause problems when manipulating large data sets or complex tasks. So, it's crucial to understand these limitations when using Excel.
?
IV. What can Planning Analytics do for Excel?
IBM Planning Analytics, an extension for Microsoft Excel, offers several benefits that can help Excel users manage their data more efficiently and secure their use of Excel.
For novice users:
? Improved Formulas: Planning Analytics optimizes certain formulas to reduce network traffic and improve performance1. For example, DBR is equivalent to DBRW and DBS is equivalent to DBSW1.
? Report Management: Planning Analytics reports for Microsoft Excel are designed to work even in a wide area network environment, without the need for Citrix. In addition, you can copy and paste values multiple times in Planning Analytics for Microsoft Excel.
? Report customization: Planning Analytics for Microsoft Excel uses named styles, which means you can easily customize the look and feel of your reports.
For advanced users:
? Data exploration: Planning Analytics offers drill-down views that are a quick and easy way to slice, filter, and analyze your data.
? Quick Reports: Quick reports are ideal for standard reports where row and column definitions won't change.
? Dynamic and custom reports: For more sophisticated reporting, Planning Analytics offers dynamic and customized reports.
These features can help Excel users manage their data more efficiently and securely, improving performance, making it easier to customize reports, and providing new ways to explore and analyze data.
V. How can Cubewise help you?
Cubewise offers several solutions to common problems with Excel.
1. Complex Model Management: Cubewise helps overcome the limitations of complex models created in Excel spreadsheets.
2. Disparate Data Source Management: Cubewise makes it possible to effectively manage disparate data sources.
3. Improved Data Governance: Cubewise provides better data governance, ensuring accuracy and consistency in your planning and reporting.
4. Real-time collaboration: Cubewise allows workbooks and reports to be shared to facilitate real-time collaboration with team members on planning, budgeting, and forecasting activities.
5. Real-time access to data: Cubewise connects directly to the IBM Planning Analytics database to give you real-time access to data for analysis, reporting, and planning activities.
All in all, Cubewise improves the efficiency of Excel by adding real-time, multi-dimensional analysis capabilities, while maintaining the familiar Excel environment and the care of your bespoke solution.
?
Resources?:
6 fa?ons de "Dommage au Déposer était Si vaste Cela répare N'étaient pas Possible" Excel Erreur (repairmsexcel.com)
La responsabilité civile : comprendre les bases et les situations d'application - Légavox (legavox.fr)
Covid-19. Au Royaume-Uni, 16 000 malades passent sous les radars à cause d’une erreur sur Excel (ouest-france.fr)
?
PS: I'm Miguel Domingos, Business Developer at Cubewise France.
I regularly write articles about Planning Analytics because I love this technology. It changes the lives of organizations, saves their time (and money) and improves their decision-making.
Now I'd like to know about your experience with Excel. Have you encountered similar challenges? How did you overcome them? Share your thoughts in the comments below...