Summary
I’m a big Excel fan. I’ve done some crazy things with it (some have nothing to do with financial data!). It’s great for some things, okay for others, and probably not appropriate at all for others. We’ll touch on a few examples of these in this article, primarily from the perspective of finance and accounting professionals. Key takeaways:
- What Excel is good for
- What Excel is not as good for
- Alternative solutions to Excel
- Creative uses of Excel
What Excel is Good For
A lot of people feel like Excel is one of the most important applications ever built, and I don’t disagree. The flexibility and ability to build out complex models, calculations, charts, macros, etc. is great for companies of all sizes and people of all backgrounds. But that flexibility is a double-edged sword, as we’ll see below. Below are some good uses of Excel.
Making basic lists and tracking things
I use Excel to make all kinds of basic lists, and I bet you do too. Groceries, meal plans, potential vacation ideas, people in a group – the list of potential lists is endless. Because of its tabular structure and the ability to sort, filter, calculate, and link references together, it has several features you might need when building and managing lists.
Quick and dirty analysis
Excel is also really useful for analysis on the fly. As an accountant, I reviewed transaction details (usually out of the general ledger system) several times a day. PivotTables and PivotCharts are two of my favorite ways to look at data – easily summarizing by person, location, category, risk, month, etc.
Facilitating imports and exports
Most applications have import/export functions supporting Excel and related file formats (.xlsx, .csv, .txt, etc.) because of its tabular nature. It’s become the undisputed standard for moving around manageable chunks of data that way. I’ve often had to export data to Excel, update some of the data, and reimport it to the same or a different application. This is why I’ve built so many macros into the XLEV8 add-in, like the ability to easily add, delete, export, and bulk-update records in Outlook (tasks, appointments, contacts, etc.). If you’re building an application, make sure to include import/export functions everywhere you can as you go – your users will greatly appreciate it!
Reporting/dashboards
This is one of my favorite uses of Excel – building reports, dashboards, and scorecards that help measure performance, drive improvement, and facilitate key decisions. Charts, ranking, variances, and conditional formatting are some of features I’ve used to make reports eye-catching and impactful. I use macros wherever I can to make them efficient, and I try to build everything around consistent data sources to limit the work in rebuilding things. Even though it’s good at building reports, there may be an even better purpose-built application for building, analyzing, refreshing, and distributing key information. The more data points you have, the more people involved, and the more critical the process, the more compelling a purpose-built solution becomes.
What Excel is Not So Good For
One of the biggest complaints with Excel is that because it’s so flexible and not really built to be locked down, it’s way too easy to make mistakes. A quick web search for “biggest spreadsheet fails” leads to many results like this one:
Why do these mistakes happen so frequently?
- It’s easy to reference the wrong cell, use the wrong functions, or use hard-coded figures in a formula.
- It’s easy to copy and paste or start from a previous file and forget to update all the items that should have been updated.
- It takes a tremendous amount of time to review everything within a spreadsheet, and it’s just not feasible to review every formula for every new version of a workbook.
What are some signs that Excel isn’t the best tool for the job?
- You’re working with critical data and accuracy is crucial.
- You’re working with sensitive or confidential data.
- You’re working with a lot of data or big file sizes.
- Your process has a lot of separate files.
- You have a lot of people collaborating, especially from different places.
- Your process has a lot of repetitive manual steps that are prone to error.
Chances are you have many processes with one or more of those traits. Excel may have been great when you started, but after scaling a bit, you might have outgrown it. That’s okay – a lot of fabulous solutions started because spreadsheets weren’t designed specifically for them (hence the quote below). Below are some uses of Excel that are either not advised at all, or okay to an extent.
A database
Excel works a lot like a database, right? It’s tabular in nature, you can filter, sort, reference, summarize, etc. For basic lists, that’s great. For anything that’s needed to power business processes, it can’t cut it. Even though it’s capable of storing over a million records, practically it becomes very difficult around 100k records, especially with a lot of formulas. When you combine this with the need to control data integrity and access among many other things, it’s just not ideal.
An accounting system
I’ve seen some groups use Excel as their accounting ledger. I’ve even done it myself when serving as treasurer of small groups. But for any true business, a true accounting system is needed, for many of the same reasons as a database (data integrity, access, collaboration, etc.). I’m not just talking the general ledger though – related processes for treasury management, budgeting/planning, reporting, consolidations, taxes, and reconciliations are also often better suited in purpose-built applications.
Project/task management
For your basic personal to-do list, Excel can work well. But when collaborating and your tasks fit into processes with many inter-dependencies, Excel just can’t keep up. It also doesn’t work well for capturing formal sign-offs and certifications, or housing and organizing important supporting documents. The ability to see the status of projects and other tasks in real time, at various levels and from various perspectives, is a major benefit of using purpose-built task management solutions.
Account reconciliations
Wait, what? You’ve only seen account recs done in Excel? That’s certainly the way it’s been done most commonly since spreadsheets became commonplace. However, it’s often not the best tool for the job. Account recs are an important part of a company’s control environment, so ensuring accuracy and completeness are crucial. They are also often very manual to prepare and review, contributing to errors and delays. The more account recs you have, the more data that flows through them, and the more time it takes to complete and review them, the more it makes sense to consider solutions other than just Excel. Think about how they work – you download source data (or compare 2+ sources of data), you apply logic, you summarize the data, and ensure it supports your GL balance. Even the most complex account recs follow this approach. That’s a lot of automation potential!
Journal entries
There are a lot of similarities between journal entries and account recs, but they’re worth calling out separately. The vast majority of journal entries are based on repetitive steps: start with a data source, apply some logic/calculations, and format them in the way your GL requires for input/upload. There are a lot of manual steps, and it’s so easy to make errors. The most common error type I’ve seen (and made!) is getting the signs backwards, especially when nine times out of ten it’s a debit but this month it’s a credit! Just like with account recs, there is a lot of automation potential for journal entries. In fact, they are often related, allowing you to kill two birds with one stone.
So what can you do about it if you have to use Excel?
- Build in as many checks as you can.
- Use comparisons to previous periods and trends (year-over-year, month-over-month, compare to trailing average, etc.).
- Use comparisons to benchmarks/thresholds – budgets, standard/expected amounts, etc.
- Use completeness checks – does the sum of the parts equal the whole? New locations, cost centers, etc. can be gotchas here, so account for those.
- Automate as many manual steps as you can – creative formulas, macros, etc.
- Document all the steps (what, how, and even why) so you don’t forget any of them. Screenshots can be great here.
Alternative Solutions to Excel
Be aware as much as you can at how things are working (and even moreso not working), within and outside your circle of influence. When you break it down, often over-relying on spreadsheets is a major cause when things aren’t working well. When that’s the case, look at alternative solutions, ideally ones that are purpose-built for what you’re trying to do. Keep these inter-related goals in mind as you do – there may be others, but most of the time these are among them:
- Reduce risk – there may be many different risks you want to mitigate, but the risk of errors is likely the biggest one.
- Reduce time – this should be an obvious one; a purpose-built solution should be able to automate not only the work itself, but a lot of indirect steps that facilitate that work.
- Reduce cost – while it’s largely driven based on time in terms of labor, often there are other costs – avoiding write-offs, penalties/fines, consultant/auditor fees, training/retention/recruitment, etc.
- Improve morale – technology improves people’s lives and increases their engagement as they take on more meaningful, challenging, and rewarding work, which in turn, reduces turnover.
Below are some of the finance and accounting systems I’ve personally used or been exposed to that made a huge difference as the processes were migrated away from Excel. There are dozens of other types of processes like these with purpose-built systems in finance and accounting alone, and hundreds if not thousands of processes with purpose-built systems outside of finance and accounting that help you take the leap from Excel.
- Dashboards and internal reporting – most of these are based on pulling several data sources together into a data warehouse, which is then connected to Power BI or something similar.
- Managing budgets and planning – there are many solutions that work well with Excel as a facilitator, such as Adaptive Planning, Planful, Anaplan, and Prophix (among many others).
- SEC reporting – Workiva’s wDesk solution is tremendous at this, touching on the four factors above and then some.
- Account reconciliations and journal entries – BlackLine makes an incredible impact in digitizing and automating the accounting close process, including account recs, journal entries, task management, and much more.
Be Creative with Excel!
Here’s an example of being pretty creative with Excel. My wife is an educator and during her first year or so as a teacher, she asked if I could help with something that the teachers loathed doing – putting together the “Fun Arts Schedule.” Fun arts were PE, music, and art. They rotated 4 different groups within the grade level (A, B, C, and D) like this: PE, Art, PE, Music, or some variation like that. They needed a way to show the students what activity they would be doing each day based on the rotation. They would literally fill out a calendar with an Excel template (a different month on each sheet) one day at a time – every school day – for all four rotations. It took over a whole day to do that, and inevitably there was a mistake with a lot of rework! Wouldn’t it be more impactful for them to be working with students or otherwise setting them up for success?
Using formulas creatively with the start/end school year days/holidays, and a drop-down list for the rotation, I built the whole thing in Excel (without macros!). Then next year, they loaded the new holiday list, new start/end days, and reused it – just minutes to update all four schedules. Here’s the best part – almost 20 years later, they are still using that Excel file! If only other schools would too…
Some other crazy things I’ve done with it (some are on the example files page):
- Bowling score sheet (not all bowling alleys have automatic scoring…)
- Many different board/card game score sheets
- Softball schedule/player management
- File I can use to update songs, playlists, etc. in iTunes
- Team vacation tracker
If you have your own creative Excel use case or your favorite alternative when Excel just doesn’t cut it, please share in the comments below!
Recent Comments