What's PLANERGY?

Modern Spend Management and Accounts Payable software.

Helping organizations spend smarter and more efficiently by automating purchasing and invoice processing.

Learn more

We saved more than $1 million on our spend in the first year and just recently identified an opportunity to save about $10,000 every month on recurring expenses with PLANERGY.

King Ocean Logo

Cristian Maradiaga

King Ocean

Book a Live Demo

Download a free copy of "Preparing Your AP Department For The Future", to learn:

  • How to transition from paper and excel to eInvoicing.
  • How AP can improve relationships with your key suppliers.
  • How to capture early payment discounts and avoid late payment penalties.
  • How better management in AP can give you better flexibility for cash flow management.
Download Free Copy

Common Excel Problems For Finance and Accounting

Category.
Tags
Last edited
October 18, 2022
Common Excel Problems
Download PDF

IN THIS ARTICLE

When businesses need to create spreadsheets for data analysis, present data graphically, or simply need to calculate a set of numbers, the majority of CFOs, accountants, and clerks turn to Excel, part of the Microsoft Office suite of applications.

Popular globally, Microsoft Excel currently boasts over 1 billion users worldwide.

A large part of its popularity is due to its flexibility and how easy it is to get started. But while the basics are easy enough to master, creating complicated formulas or pivot tables is not.

Yes, Excel has its problems, a lot of them have to do with misuse. 

But before we discuss Excel’s shortcomings, let’s take a look at what this popular application does best.

Benefits of Using Excel

Yes, there are a ton of benefits derived from using Excel, as long as it’s used for the purposes intended. 

For example, using Excel to perform calculations and create analytic spreadsheets is great; using it as an accounting application is not.

Here are a few of the benefits of using Excel in your business.

  1. Easy To Share Data

    Nearly everyone uses Excel, making it easy to share data with colleagues and others outside your business. And because many software applications allow you to download data to an Excel workbook you can easily share that information with others.

    And to protect data, you can utilize other Excel tools such as data validation to specify what type of entry is acceptable for that cell.

  2. It’s Easy To Get Started

    Yes, Excel can be complicated, but most can learn the basics quickly. To use advanced features like pivot tables, you’ll need to learn more, but just about anyone can create a simple spreadsheet with formulas.

    Excel also includes numerous shortcuts that can be used to do everything from opening a workbook to hiding selected columns or rows.

  3. You Can Create Charts in Minutes

    Excel makes it easy to create charts for your data, even providing you with an option to access recommended charts based on your data structure. Just browse available charts and click on an option to view how the chart would look.

    And if you don’t like the results, it’s easy to create a different chart that may work better.

  4. It Helps You Become More Organized

    While there’s an app for just about anything these days, Excel remains one of the best resources around for entering and organizing data in a way that you choose.

  5. Can Store a Lot of Data

    Excel lets you store up to 1,048,576 rows of data, along with up to 16,384 columns. Chances are that you’ll never need to use it all, but it’s nice to know that it’s an option.

What Is the Best Way To Learn Excel?

For new users, one of the best things to do is spend some time just exploring the application. Click on the menu options on the toolbar and take a look at what’s in each section.

This makes it easier to find the feature or function that you’re searching for.

Once you’re comfortable with the interface, consider taking some of the free Excel classes offered by Microsoft, which will teach you fundamentals as well as advanced skills.

You can also search the web and find some free classes and tutorials on YouTube, or sign up for an online class.

While you’re learning, make sure that you spend a lot of time with the application, learning basics such as how to calculate numbers, how to properly enter Excel formulas, and how to use the AutoSum feature for quick calculations.

The following are a few of the basic features in Excel that everyone should be able to master:

  • How To Sort Data Properly

    Even if you’re a beginner, you will need to know how to sort your data properly. For example, if you’re using Excel to track customers, you’ll need to know how to sort data, whether by city, state, or even zip code.

  • Import and Format Spreadsheets

    Excel can import data from numerous applications. Once you get the data in an Excel file, you can format it the way you like.

  • Eliminate Duplicates

    When you’re entering data from multiple resources, you’ll probably end up with duplicate entries. Excel allows you to search for and remove any duplicates that are in your spreadsheet.

  • How To Calculate Data

    Excel can save you a lot of time by calculating large amounts of data. But first, you need to know how to do that. Excel can perform addition, subtraction, multiplication, and division, but to use them, you’ll need to know and understand the basic formulas.

  • How To Freeze Panes

    When you’re using a lot of columns, you’ll want to know how to freeze panes. This allows you to see your headers even as you scroll through to additional columns.

    For example, if you have twelve months of data in columns, you can freeze panes to view the column headings as you scroll down, even if you have 100 rows of data.

  • How To Expand Column Width

    Excel’s default column width is 8.43. But if you commonly use Excel to record longer numbers, you’ll need to expand the default column width so that all your data is displayed properly.

What Are the Downsides of Using Excel?

Even the best applications have their downsides, and Excel is no exception. These are just a few of the downsides you can encounter when using Excel.

  1. Becomes Slower As More Data Is Added

    One of the advantages of using Excel is how much data it can handle. Unfortunately, the more data you add, the slower the application becomes. The bottom line is that while Excel may be able to handle a million lines of data, it may not be a good idea to do so.

  2. Steep Learning Curve

    Learning to do the basics in Excel is fairly easy. But if you need to create complex worksheets, data comparisons, and pivot tables, the learning curve can be pretty steep.

  3. Collaborative Efforts Can be Difficult

    Short of combining multiple spreadsheets into a single document, which itself presents some difficulty, sharing a spreadsheet among collaborators can be tricky, especially since there are no real safeguards in place if multiple collaborators can add data to the spreadsheet. Issues may also arise if you and your collaborators are using different versions of Excel.

  4. Not a Good Fit With Agile Business Practices

    Agile business practices are all about efficiency and automation, while a lot of manual data entry is required to create Excel spreadsheets.

  5. Propensity for Errors

    Information in a spreadsheet is only as reliable and accurate as the information that is entered. Human error is a real concern with Excel, with both data entry errors and formula errors a significant issue.

Close to 90% of spreadsheets contain errors in 1% or more of all formula cells with an average cell error rate of nearly 4%.

What Are the Most Common Problems With Excel?

Research by Ray Panko indicates that nearly 90% of spreadsheets on average contain errors in 1% or more of all formula cells. 

With an average cell error rate of nearly 4% this makes the probability of calculation errors very high.

All errors you encounter in Excel will begin with #, with the following the most common.

ErrorHow to Fix
####This error message appears when a column is not wide enough. Simply increasing the column width will fix this error.
#REF!This error appears when Excel is trying to reference a cell that has been deleted. This will happen when you change data and forget to update the formula. Fix the error by updating the formula, or if the cell was erroneously erased, use the ‘undo’ command to place the cell information back into the spreadsheet.
#NAME?This error will appear when parts of your formula are incorrect. The fix for this is to check your formula for inconsistencies. If the formula is correct, check the cells to see if any have incorrect information in them.
#VALUE!This is another common error that occurs when there is an error in a cell that the formula is trying to reference. For example, if you’re attempting to calculate a column, but if you have a word or letters in that cell instead, this error will appear. The fix is simply getting rid of the letters in the cell.
#DIV/0!This error can appear when you are attempting to divide a set of cells and the formula is trying to divide by zero. This can happen if the formula references the wrong cell to divide by or is written wrong. The quick fix is to correct the formula or the cell that the formula is referencing.

These aren’t the only errors that you’re likely to encounter in Excel, but they are probably the ones that you’ll see most often.

If you regularly use Excel, you’ll also have to keep an eye out for errors that don’t have a message. These are formula errors. Research

For example, you’ve created a spreadsheet with departmental expenses, with a formula at the bottom of the spreadsheet that calculates the totals automatically.

Yesterday, your departmental expenses totaled $1450. Today, your assistant entered $1,000 in expenses but forgot to update the formula. That means that your total is still displayed at $1,450, even though the actual total is $2,450.

Another common error in Excel is simple human error. Let’s go back to your assistant today. 

She was tasked to enter $1,000 worth of expenses, but because of a data entry error, she entered $10,000 worth of expenses for today, bringing your expense total to $11,450.

While that error will likely stand out, a transposed number or one even off by a few dollars can easily escape the notice of reviewers.

How Do I Locate Errors in Excel?

Excel includes automated error troubleshooting, displaying the appropriate error message for you to correct. 

The problem with finding human errors is more complex and time-consuming. 

For example, a common problem resulting in errors is not highlighting the correct number of cells for a calculation. But don’t feel bad, even respected Harvard economists can make this type of error.

But there are a few things you can do to help you spot a typo or missing formula.

  1. Know what the result of your calculations should look like. If you know your expenses should be around $5,000, a $750 total should be a red flag.

  2. Add the totals manually. While this process defeats the purpose of using Excel’s formulas for calculating, if you’re unsure whether the results in your spreadsheet are correct, adding them using a calculator can provide some reassurance.

  3. Check formulas for the correct cells. This is particularly important if the spreadsheet is regularly updated. The formula may not be updated properly or may be accidentally erased. Clicking on the cell to make sure the formula is still there and that it includes all the appropriate cells only takes a few moments, but can help to reduce these common errors.

What Is the Most Difficult Thing in Excel?

If you’re unfamiliar with Excel, just about anything can be difficult, but even those familiar with the application can find these challenging.

  1. Macros and VBAs

    Macros are computer programs written in Visual Basic that complete a specific action once they’re activated. However, before you can write a macro, you need to be skilled in Visual Basic. By far the most difficult task in Excel to master, writing macros can help automate properties in pivot tables and can help format data quickly.

  2. Pivot Tables

    Pivot tables are used to create reports that feature large sets of data. Pivot charts can be used in a variety of ways and they can be a useful function to master, particularly if you need to manage large data sets.

  3. VLOOKUP

    VLOOKUP stands for vertical lookup and is used when you need to retrieve data in a table vertically. VLOOKUP helps merge multiple sets of information into a single report.

  4. SUMIF

    SUMIF is used to calculate values that meet certain criteria. For example, if you have data from four employees, but only want to calculate data from one employee, you can create a SUMIF formula to do that.

Being prepared to deal with the downsides of Excel is essential. 

While using Excel can be a valuable resource for your business, understanding common errors will help reduce frustration levels and better prepare you to deal with issues that may arise.

Download PDF

What’s your goal today?

1. Use PLANERGY to manage purchasing and accounts payable

We’ve helped save billions of dollars for our clients through better spend management, process automation in purchasing and finance, and reducing financial risks. To discover how we can help grow your business:

Book a Live Demo

2. Download our guide “Preparing Your AP Department For The Future”

Download a free copy of our guide to future proofing your accounts payable department. You’ll also be subscribed to our email newsletter and notified about new articles or if have something interesting to share.
download a free copy of our guide

3. Learn best practices for purchasing, finance, and more

Browse hundreds of articles, containing an amazing number of useful tools, techniques, and best practices. Many readers tell us they would have paid consultants for the advice in these articles.

Related Posts

Business is Our Business

Stay up-to-date with news sent straight to your inbox

PLANERGY LOGO

Sign up with your email to receive updates from our blog