If all of your financial records are maintained in Excel spreadsheets then you may be missing out on many of the advantages of using Access databases. Microsoft Access may already be available in your version of Microsoft Office. It is a component of Office Professional (and some other versions) and a non-development version of the Access runtime is downloadable from Microsoft. It is also available separately. The biggest investment would be the time to learn or have someone trained to develop databases with it.
You will not want to convert all of your information to an Access database, of course. Access and Excel have different strengths and the two can be integrated. You can use Access for some needs and Excel for others. You can develop Excel spreadsheets and use them later to develop (upgrade to) an Access database. You can even use Excel to analyze data stored in Access or export Access data to Excel. The reason so many people use Excel for everything is that so few people know how to use Access, and vice-versa. Companies are actually moving away from Excel for some tasks, and using standalone products for those functions. In many cases, developing an Access database could be a more effective, economical, and versatile alternative.
There have been other articles[1] written about the choice between Excel and Access, and the benefits of each. Some good points have been made, but others are a bit more superficial. This may not remedy that, but the additional insight may help you develop a plan that fits your company or organization. One guideline[2] I’ve read has been that Excel is better for numbers than text. Excel does have more advanced statistical functions and it can be a better tool for data analysis. However, Access is an excellent resource for creating complex financial reports, and makes it easier for multiple users to access the data. Both have their strengths and weaknesses, and you are not limited to using just one.
Microsoft Excel and other forms of spreadsheets are everywhere, and access to them is often built into other software, such as the Document Management System in Lacerte. Other programs, like Microsoft Word have the ability to embed spreadsheets in their documents. Other companies provide on-line access to their spreadsheet applications at no cost. Workers are so accustomed to working with Excel it would be unthinkable to be without the self-calculating grids in a spreadsheet.
As previously mentioned Excel has an extensive collection of built-in formulas and is particularly effective in statistical analysis. It is also useful in what-if scenarios, cost-benefit analysis, and financial calculations. Excel can accommodate any type of information that can be organized in rows and columns. Some reasons to consider Excel over another option may include
Excel is considered to be the one software package that office professionals and particularly prospective accounting staff will need to know. A candidate's qualifications for a job will obviously include several areas of accounting, but the prime candidates should have an expert knowledge of Excel.
What constitutes an expert knowledge can be debated, though. From an accountant's point of view, that would include knowing many of the financial and formatting formulas, a few special features of Excel (Pivot Tables, etc.), and proficiency with basic functions and shortcuts so that he will be able to create and modify spreadsheets quickly. For an IT professional those may be just the basic requirements, and real expertise would involve writing macros, creating forms, and writing VBA code to enhance the spreadsheet, simplify use, and integrate with other applications or data sources.
Even so, the need for Excel is unquestionable. But from a practice management perspective, Excel’s value may be overstated. Excel has many limitations and risks.
Everyone reading this probably knows how Excel works, but for comparison later, this is a short version of how spreadsheets allow us to do the work we do.
Excel has limitations on what it can do easily. While more advanced users can create complex spreadsheets, many users will limit their use to the more basic features of Excel. This is particularly true when it comes to structured or three dimensional data. When users ignore the limitations or stretch the capabilities of Excel or their own abilities, the resulting spreadsheet may be prone to errors. In some cases, it might be better to consider a database, especially if the requirements are complex and the same type of data is frequently used by other clients. Limitations of Excel may include the following.
In addition to the limitations, and often because of the limitations, Excel contains potential risks.
Security is one risk associated with indiscriminate use of Excel. Since spreadsheets are so easily shared, confidentiality of data may be a concern, as with any standalone document. Likewise, as with other documents, loss could be a concern if there isn’t a reliable IT policy in place for backups and data security.
While security is the concern of many professionals, the biggest risk may be simply the errors that spreadsheets may contain. The actual data may not be reliable, or information produced from the data may be inaccurate. Much has been written about the prevalence of errors in business spreadsheets. Some articles have estimated that almost 90% of spreadsheets have errors,[3] and the risks involve the possibility of material errors in a report. Further, one paper[4] analyzing errors in various companies found that errors were common at every level of the organization and that people are only moderately good at correcting them.
Errors can also be made when creating a spreadsheet, when using it, or when making modifications to update or customize it. The errors can be associated with using the wrong formulas, the wrong fields, entering data outside of a formula’s field range, or improper formatting of numbers. Some errors could occur as spreadsheets age. If the formula uses a common value (hard-coded) in a formula and that value changes, the formula has to be changed. One example may be a tax return analysis that includes 7.5% as the floor for medical expenses. The risk is greater with more people in the department, and the wider the distribution of the spreadsheet.
Errors can range from simple mistyping to errors in more complicated formulas. One article[5] analyzed spreadsheet error using this list of common error types.
In general, errors can be segregated into those created during development of a spreadsheet, those that are the result of usage, or a combination of the two.
Development Errors include the actual errors made during spreadsheet creation, although many of those can be eliminated with company-wide development and usage standards.
Logic and formula errors may be the most common types of errors made during development. While formula errors are inherently logical errors, other errors could be the result of business logic errors, or incorrect interpretation or legal requirements.
Formula errors are particularly onerous because they can cascade across a spreadsheet and incrementally distort the reporting for long periods of time. This is particularly true when formulas reference formulas in other cells. Dependencies must continually be evaluated when spreadsheets are updated or re-purposed. A lack of understanding of differences between similar formulas can produce accurate records initially and later change as more data or more types of data are entered. A formula that gets an average by taking a total and dividing by a count of cells would usually be an inappropriate use of Excel formulas.
Operational errors refer primarily to the use of spreadsheets. Some errors may be made by using the wrong spreadsheet for a particular task, or for the wrong client. Spreadsheets should always clearly document their purpose and any assumptions that are not defined in formulas.
Reproduction is another risk of Excel spreadsheets. In one of my jobs we used an initially standardized worksheet for each client. However, sometimes it was duplicated from a previous year, and sometimes developed from the template. Often the inherited spreadsheet had issues, either with enhancements or overridden formulas.
In many cases these spreadsheets are re-used from year to year or by different employees and for different clients. Unfortunately, when an error is introduced into a formula and not discovering it until later, there may not be a trail for finding errors in previously shared worksheets. The more complex and the more community oriented the worksheet the less likely it will be discovered early. Other types of errors are also discussed in a paper presented by Panko.[6]
Not only are errors possible in the development of a spreadsheet and improper operational standards, spreadsheet usage could be a source of inaccurate spreadsheet calculations. There are many possible usage errors, but the most destructive ones are those involving unprotected cells.
If a spreadsheet is used by others, there is a risk that a user deletes or overrides a formula. When formula cells are unprotected, at every use of the spreadsheet there is a potential of corruption somewhere on the spreadsheet. Formula cells can be protected in Excel but the process is seldom done for in-house spreadsheets because it is so time-consuming.
Combining development errors with usage errors may be another source of corruption. The coding may be correct if the user uses it as designed, but there may not be any error-checking. For example, a macro that relies on a hard-coded value to insert rows could be rendered worthless or destructive if the user manually inserts or deletes rows.
There have been estimates of the cost of errors but the actual costs associated with spreadsheets are often not known. The financial impact of most errors may be minimal, but the risk is still there if there aren’t policies in place to monitor spreadsheet development and maintenance. Even when the actual errors are small, when they are part of a formula, the effects can be cumulative.
The biggest risk may be that errors can go undetected for years. Excel can help you find errors in a formula’s syntax and it does provide warning tips about cells, but it doesn’t know if the formula accurately summarizes the information the user needs. Errors are possible in the simplest spreadsheets. The more a user strays from the primary features of Excel, and tries to use a spreadsheet like a database (Risks of Using Excel as a Database[7]), the greater the risk.
“If the spreadsheet is ridiculously complex and impossible to read then nobody has a chance of actually reading it – and determining if there are any errors of it in the first place. That just becomes impossible."[8]
Detecting errors can be as simple as regularly checking formulas being used in a spreadsheet, but there are other methods for detecting errors.
Show formulas. When developing a spreadsheet, Ctrl-` will toggle between formula and values. This will enable you to see which values have formulas. A cursory view of each formula may help verify its accuracy. This toggle can also enable you to spot when a column of formulas has been interrupted with a value typed into one of the formula cells. When editing a formula, selecting a cell reference will highlight the range being used, further verifying the proper reference. Newer versions of Excel feature additional operations that will evaluate formulas checking for obvious errors, trace errors, or trace references from or to a given cell.
One of the most effective means of error detection is peer review, or having someone else that is knowledgeable about the project at hand to closely look at the spreadsheet. If the project is one that can be useful to others, collaboration may need to be considered. A team project subjects each member to the scrutiny of other members.
While most CPA firms spend some time performing audits for their clients during the tax off-season I wonder how many of them audit the IT processes of those clients. Even more critical how many of those firms who are involved in peer reviews of their operations have audits done on their own IT operations. An IT audit will not only look at potential spreadsheet issues, but uncover security and other IT risks. The CPA profession has high standards to maintain in providing their services.
Identifying errors and the ways errors are creating will be the first step to preventing errors. That goal can be achieved at two levels. Create a system to follow in developing and controlling spreadsheet development. A company-wide standard will be the most effective in developing accurate and functional spreadsheets. The following guidelines are given in “How do You Know Your Spreadsheet is Right?”[9]:
Secondly, each individual should develop good skills and practices when creating spreadsheets. Consider these few, more specific, guidelines for developing spreadsheets.
Naming fields and variables will improve the clarity of many functions that refer to other cells or ranges. Even better, Excel will keep a list of all names used and you can go to them from the Name Box just left of the formula bar. You can also use the Name Manager to review names and their references.
The field naming advantages are also effective when using tables in Excel 2007 and later. Tables (and their named reference) can adjust for insertions, deletions, and still maintain boundaries.
Avoid hard-coded values in formulas. Never assume a value will never change. If a value represents a set amount or percentage, create a separate cell for that value, and improve on that by naming the variable. A lookup section can be useful for collections of values that are easy to change, and lookup when needed. State’s sales tax rates would be one example.
After completing development of a spreadsheet, protect it and any formulas. Even if you use a universal company password, protection will help prevent accidental overwriting of formulas used in the spreadsheet. More advanced restrictions could also define what information is relevant in a spreadsheet.
There are many ways to evaluate spreadsheet development. This is a simplified series of questions you might ask about new spreadsheets.
Create spreadsheets to get new information instead of just transferring your existing information to Excel.
Know what information you have to work with, and if it contains what you need to get useful information.
A formula that uses Sum/Count may not get an average if values are missing from the range being used in the calculation. Using average may (or may not) be the function you want. Statistical formulas in particular require values that meet a certain range of values.
While Excel will accept the entry of a formula, it can’t tell if the arguments in the formula have been swapped. Incorrect cell references will also make even the best thought out formula generate meaningless information.
If the information you are getting doesn’t make sense, you may need to verify formulas or re-examine the logic behind the calculations.
Development of spreadsheets is typically task-oriented. When a new task presents itself a new spreadsheet is developed and put to use for that person or project. For that reason, the skill set required to create spreadsheets is not much different from simply using spreadsheets and the line between use and development of spreadsheets is blurred. This is particularly true if the spreadsheet simply totals multiple columns of data.
Often complex tasks require more complex worksheets. While someone with basic spreadsheet skills may devote considerable time with such projects, there is a significant difference between the skill set required to develop complex spreadsheets and a user with basic knowledge. Not only will such projects be more time-consuming (cost-benefit ratio), they are prone to errors. The user/developer mentality is so common that the concept of developing and maintaining spreadsheets may seem foreign to many managers. The error level of average spreadsheets, however, accents the need to rethink the development process of Excel and spreadsheets and consider demarcation.
Improving quality control of Excel spreadsheets is mentioned here only briefly. For more information review the articles included in the footnotes. “Are Your Excel Spreadsheets Accurate?” mentions some basic ways to guard against errors and detect errors in a spreadsheet.[10] “Is your Love for Excel Misplaced?” looks at a few things that may help to identify reasons for inaccuracies including the need to define the purpose of a spreadsheet before creating it.[11]
Standards have also been developed for spreadsheet development and are available on the Spreadsheet Standards Review Board website.[12] Related to this organization is a structured site devoted to Excel maintenance called “Best Practice Modeling.”[13] Site pages discuss Excel development, including a description of auditing tools available, Modular Development, and a Knowledge Base of industry specific applications. While a system can help to organize the development process, it is the individual developer, whether secretary or IT professional that creates spreadsheets and a guide on “How to Make Spreadsheets Error-Proof”[14] could be a good initial step. “How do you know your spreadsheet is right?” presents some best practices to use in developing spreadsheets. [15]
Much can be done to improve quality of Excel-generated information but there are cases where a database might be more appropriate.
Databases are similar to spreadsheets in that data is often represented in rows and columns. The biggest difference is that database information is relational. Each table in a database is typically related to one or more other tables in the database. Relationships are usually one-to-many. Thus, for each client, there may be one or more invoices. For each invoice there may be one or more items. For each employee there may be one or more clients he is responsible for. Unlike a spreadsheet, the data entered is separate from the way it is gathered or presented, so the cells do not have display formatting associated with them. Forms and reports, however, can display the data in many different ways.
Databases are the primary sources for Enterprise Resource Planning (ERP) packages, in part because they are highly efficient at data management. On a local level, databases can also be created on an individual PC and shared on the local network using Microsoft Access. Microsoft Access provides users with all the tools for developing functional databases. Whether or not you have a network database or other ERP, learning how to develop using Access may fill the need for many tasks.
In the earlier times, data processing was separate from business functions of a company. Computer programmers developed programs that users accessed to generate reports. Most of those programs were database-based. Business users didn’t concern themselves with formatting and output. Their job was to enter data, and request suitable reports from that data. When the first business PC showed up, the first major spreadsheet (Visicalc) helped users to get many of their financials done without an IT department. Lotus 1-2-3 soon followed and then Excel.
As focus moved to spreadsheets, companies found a way to eliminate, downsize, or refocus their IT department. As a result IT-developed databases were replaced by desktop produced spreadsheets. Today, many of the larger companies have ERP systems which are replacing some of the spreadsheet applications. The software industry has also developed specialized database software to handle specific tasks. In accounting today, database software handles tax services, audit functions, and many bookkeeping and financial accounting functions. Progressive companies may do well to bring database-driven information back into other areas.
Excel and spreadsheets have a useful purpose in an organization, but there are times when a database is more appropriate use of resources. When appropriate, the advantages of using an Access database are multi-faceted. The following categories illustrate the benefits of an Access database.
One characteristic of a database is in its layered process. In a spreadsheet, data and presentation information are integrated into each cell. In a database project there are at least two layers; the data layer and the presentation layer. By segregating the tasks of entering information and creating reports users can focus on just one area at a time. Furthermore, once the data is in, the information can be presented in any number of ways, with selected levels of detail.
Extensive use of Access databases is not for everyone. Databases may be a new process for many cases, and staff will have to adjust to the operations and limits. Databases separate the two processes, development and use, so access to the database will depend on whether the person has development skills. Other limitations include the following.
There are risks associated with database development, and executives will not have as much control. While a senior partner can review spreadsheet formulas, he will likely not have the knowledge to check a database table, formula, or report for errors, other than possibly verifying the information with spreadsheet calculations.
Errors in a database are not easy to detect by users other than double-checking the data entry. A comprehensive development process is needed to insure errors are detected and prevented prior to deployment of the database system. When centralized, loss of data can be catastrophic. Because a database contains large amounts of data, the potential loss is large. Incremental backups are crucial.
Following the introduction of the PC in the mid-1980’s, with the limitation that not much software was available, one of the main reasons a company would hire a computer guy was to set up a database. Today, given the universal use of SQL and normalization, and the availability of affordable database software, the value and versatility of a database is greater than ever. Most any size firm can begin the process if they have a resident database guru, either hired for that or trained in-house. While a company may not need a full-time IT department to begin using more Access databases, there must be someone available who has a good understanding of how to develop a database. A very simple database can be developed with minimal training but the real benefits of a database are based on a well-developed database program.
Database Development training is not unlike training requirements of an Excel. Although formal training is not required extensive training is a requirement. It will require more than a single introduction to Access. A possible misconception is that a person can take a single class in database development and be able to handle all of a company’s database needs. Just as Excel experts gain their expertise through progressive experience and often several classes in the subject, Access training requires more than a rudimentary understanding of Access basics. Given the time and opportunity, however, a staff member can grow into being a database guru.
You don’t have to invest in a dedicated IT department, but you do have to make an investment. Many managers may decide it’s not worth it, or that other projects are more in line with their business. You can begin a database team. Start with a few projects, evaluate, improve and refine them. Then integrate one or more of them. As you grow you can consider more complex applications, network databases, or web based (cloud) databases.
It’s not always apparent what project to consider for database development. One clue may be the level of frustration you have with a complex spreadsheet. The prevalence of task-oriented software is one indication of the power and flexibility of Access databases. A lot of such software is database driven and provides safeguards to many common risks. A good guide is to look at what things are done with separate software.
I wouldn’t recommend creating your own tax software in an Access database because it is so complex, but a look at what tax software includes may help you picture what the inside of an Access might look like. Tax database doesn’t just contain all the data for a single client. At end of tax-season, your tax software may contain 1000’s of completed forms with hundreds of fields for 100’s of clients in a single database. The fields of one form may be dependent upon the values other forms. Underlying the forms is the collection of related tables. Tax software selects only the information required from those tables for a particular client for a particular year and groups it into a single tax return.
Similarly, any requirement that has multiple layers of the same type of data may be better represented in a database. Spreadsheets that consists of multiple levels of spreadsheet data is a prime candidate for an Access database.
Excel files may contain several (many) sections in the same spreadsheet, with each section using the same formulas. In one case I looked at, a survey project, modifying the spreadsheet was time-consuming. Formatting was fixed and analytical tools did not fit the fragmented format of the spreadsheet. It was essential many spreadsheets on one spreadsheet.
Another Excel strategy was to actually create multiple spreadsheets in the same workbook, with one spreadsheet acting as a summary of the others. I’ve seen over 50 spreadsheets tabs in a workbook and a full sheet of cross-spreadsheet formulas that summarized the other worksheets. As such the data is fragmented and producing the spreadsheets for distribution was very time-consuming. Just the pure volume of information and number of formula cells made it challenging, if not impossible, to insure the calculations were accurate. Not all workbooks are like this, but when repeating spreadsheets are common then a database may be a better choice.
These types of worksheets are actually useable, although they require a very organized developer to use or manage it without introducing the potential for error and associated risks.
Access is not only an alternative to spreadsheets and other digital resources. In many cases, a database can be a new thing that improves management of a crucial element of important business processes. That has been the process that led me to develop databases for inventory control, order processing, and resource management, rather than use Excel as an intermediate step. As companies become more comfortable with databases and improve their internal development potential they will find new uses for this technology to save time and provide improved reporting for clients and within the company.
The first lesson to be learned from this is that companies need to establish a system of spreadsheet development, particularly when spreadsheets are distributed throughout the company. Occasional audits of spreadsheets will help to identify problems and risks associated with spreadsheets. Depending on the structure of the organization, consideration may need to be given to protecting some spreadsheets. In conjunction with implementing a quality control system for spreadsheet development, continuous training will help prevent users from getting stale on features of Excel that they do not use often.
Secondly, sometimes a spreadsheet is not the right tool. If all you have is a hammer, everything looks like a nail. Sometimes you really need a screwdriver. Sometimes you need to have a database. If your firm or department is large enough or has the staffing, consider investing the time to train a couple of people in database development.
This is a simplified coverage of Access databases. Without more exposure to databases you may not fully see the nature of development and daily use of databases. As time permits, more information may be posted to illustrate the effectiveness of internally developed Access database, the process of developing a database, and a program to train staff to develop and use databases.
[2] http://blogs.technet.com/b/hub/archive/2010/01/15/where-should-i-put-my-data-excel-or-access.aspx
[6] Panko, R. R., & Halverson, R. P., Jr. (1996, January). Spreadsheets on Trial: A Framework for Research on Spreadsheet Risks. Proceedings of the Twenty-Ninth Hawaii International Conference on System Sciences, Maui, Hawaii.
I used Excel when I worked at Squyres, Johnson & Squyres last tax season. In my daily work I had multiple programs open at once in two displays, and multiple spreadsheets open in Excel. Having an organized "desktop" enabled me to navigate between programs almost effortlessly. Listening to one of the CPAs there, I learned that the ability to quickly navigate in Excel in the new Office was an issue so I put the following explanation together. While time and motion studies are typically only used in manufacturing, it appears to me that some motion study of office workers, including CPAs, could be very useful in speeding "production" and eliminating frustration.
Unless you change the default, Excel 2007/2010 opens all new workbooks in maximized view. In maximized view you do not see both workbooks at the same time. The confusion about this is so common that I put together the following explanation.
If you want to see more than one workbook at the same time, you have a couple of options.
1. You can restore down (de-maximize) the current workbook(s) within the Excel instance. The restore button for each window is inside the main Excel window at the top right.
After de-maximizing, you can move and resize the workbooks to suit your needs. If you want to be able to click and see either workbook, you may want to cascade, by having each overlap the other. You can do this by dragging the windows to where you want them. That’s my personal preference unless I’m comparing two spreadsheets. You can also get Excel to do cascades from the View menu. From the View tab. Select View, and then Arrange All, Cascade.
If you want to show the workbooks side by side (with no overlap), use the same View tab and then Arrange All, Vertical. You can also use a dedicated button to View Side By Side.
2. A second option for showing two worksheets on the same screen is to open two instances of Excel. After opening the first workbook, right click on the Excel quick task bar icon and select Microsoft Office Excel.
From this instance you can open the second workbook you want to use. When using two instances, you can move and resize each instance the same as you would two workbooks. In Windows 7 you can drag each instance to the edge to take up that half the screen, which would give you side-by-side windows like before. With two instances, however, each has its own ribbon and you may not be able to see as many ribbon options.
Another way to view multiple workbooks is to switch between them. If both workbooks are open in the same Excel instance, then you can Ctrl-Tab between them. If workbooks are open in separate instances of Excel, you will have to Alt-Tab between them. Alt-Tab switches between two programs, and considers each instance of Excel as a separate program.
Another nice feature in Windows is Windows-Tab where programs are shown in carousel view and you can select the window you want to go to from there. These shortcuts are a part of Windows, not Office.
]]>One of the things that always baffles me at work is the amount of time that is spent entering and re-entering data into the computer to create a report. The way that reports are generally formatted the data in them aren't generally re-useable without being reformatted. So when the report is done and a related report is needed some of those numbers are copied to the new report.
Not too long ago the spreadsheet was the exception. Visicalc and 1-2-3 was a little more complicated and a little more intimidating. In the present anything that resembles a report or that has columnar data is put into Excel.
Before spreadsheets became so popular the database was the standard practice in many operations. At least with the database any number of reports could be generated without having to re-enter all the numbers again.
Business (in a general sense) has in many ways abandoned IT expertise and has attempted to delegate a lot of the number crunching to the clerical staff. They think they don't need database specialists and instead rely on the secretary with a minimum of training. They think after all any staff member can put the information in the neatly arranged square boxes. In their world IT handles all the computers printers networking and the like.
What they apparently don't realize is that one database report can be printed out every week without having to type all the numbers in again. Not only does that one report live on but the database can be used to create reports with all kinds of useful information.
This is one example of the difference between a struggling and a successful business. The way a business handles information will largely determine how efficient they are and how effective they can be in operating their business. While many businesses use SQL databases to maintain some major number-crunching operations the spreadsheet report lives on in many offices.