5 Things You Should Stop Doing with Microsoft Excel
By Mike Raia Posted June 13, 2018
Excel is a great tool but it's often used in ways it was never intended. Here are some of the ways it should never be used.
Microsoft Excel is a powerful spreadsheet that has been greatly refined over the past 30 years, especially with the development of macros. However, these capabilities have prompted many people to use Excel in ways for which it was never designed. For example, Excel isn’t a database, although many people use it as one. Excel may be able to serve this function for smaller data sets with simple rules, but a data set can quickly exceed Excel’s limitations as it grows.
There are far better tools out there to handle the specific use cases for which Excel is ill-equipped. Here are some of those use cases.
There are several reasons to avoid using Excel for forms. For instance:
- Data entered into forms is typically disconnected from any database. Most Excel forms are just spreadsheets with blank cells to type into. This is only marginally better than a paper form.
- Format and layout are cumbersome and time-consuming. To design a user-friendly form in Excel is a brutal exercise.
- There are limited methods to control and validate input, resulting in bad data capture. Any attempts at validation are easily avoided.
Excel was not designed for creating forms and to get a usable form that collects and stores data is prone to issues and simply not worth the effort. You're better off using a tool built for designing forms and handling data in a sensible way.
Excel is often used for project planning, usually for small to midsize projects. The primary reasons many people use Excel for this purpose are that many team members are unfamiliar with project planning software options and Excel is readily available. Also, there are Excel project planning templates available for download. For simple solo projects that are basically a list of tasks, Excel can be fairly effective.
However, multiple users can’t work on the same Excel template at the same time unless your team is using the online version. It also doesn't handle complex projects well and can become a densely-packed, color-coded nightmare for everyone except the person who created it.
Furthermore, updating statuses manually and generating the required reports in Excel can more time than the work itself. Dedicated project planning software allows users to visualize and update the entire process of planning, reporting and monitoring a project in real time. Manual data entry and duplicate reports are no longer a concern since all team members receive updates with the same report.
Big Data Analysis
The possibility of using Excel to analyze big data arose when Microsoft introduced Power Pivot in Excel 2010. This online analytical process (OLAP) application allows users to analyze data sets far larger than Excel’s historical maximum of 1,048,576 rows. Excel still has this row limit for worksheets, but Power Pivot is only limited by the operating system and available memory.
A steep learning curve and poor understanding of the technology involved can cause Power Pivot’s OLAP cubes to be badly misused for data analytics. Organizations that understand the challenges of analyzing big data are far more likely to rely on a dedicated analytics solution that allows users to obtain meaningful insights into large data sets.
Shared Task Management
The availability of list templates makes Excel a popular choice for maintaining lists, especially task lists. These templates include columns for task attributes such as priority, status and target date that can be easily used by anyone with basic spreadsheet knowledge.
The primary drawback of this approach is that the list can’t be easily shared by multiple people concurrently, making it an inappropriate solution for a team working on a common project. Even if you're using the online version of Excel it's easy to write over each others' work (track changes confuses most users), managing items is cumbersome, it's hard to manage multiple lists, and the list goes on (no pun intended).
Dedicated task management software is designed for simultaneous collaboration by multiple users, making a better choice than any Excel-based solution. Task management software can also be customized more easily for a particular project or organization. There are so many great task management apps out there, some of which are free, that it doesn't make sense to waste your team's time using Excel to manage tasks.
Excel is often used as an approval process tool, in which someone sends an Excel document such as a travel expense form as an attachment to an email message. Each person in the approval chain manually makes annotations and approves or disapproves the form before sending it to the next person in the chain. The final document will then be stored on a network drive, shared drive or locally.
This use of Excel can be fairly straightforward, albeit clunky, when you’re using it for something simple like obtaining everyone’s preferences for a luncheon, although it’s still terribly inefficient. Someone still has to manually compile the results, but that’s a one-time burden in this p[articular example. However, this process becomes a serious bottleneck with any process that requires approvals from multiple parties, since everyone has to open the attachment, review it, make changes and save it under a new name. In addition, there is no easily verifiable audit trail of who did what and when. If the process requires auditing later, be prepared to dig through a lot of email and spreadsheets.
A full approval management solution includes a central portal, form design, approval routing, audit trails and more. It also includes easily-configured business rules that can implement multi-tiered, parallel approvals and can be configured for any approval process, including finance, HR, IT and operations.
Excel is a great tool when it's used as a spreadsheet but it falls down when pushed into use cases it wasn't designed for. Before you spend a lot of time trying to bend Excel to your will, look around for a more suitable solution and take advantage of purpose-built tools that can save you a lot of aggravation.