Even though Microsoft Excel can be used for data management and fields can be calculated in Microsoft Access, the two softwares are very different, and to maximize usage in each program, it is important to understand the unique aspects of each one. Likely, they are both part of a subscription your company is paying for, so no additional expenses will be lost to undertake the learning curve required for one or both of the programs.
Behind browser and email software, Microsoft Excel is one of the most popular tools in the Office suite. Whether one uses it every day or not, most people have some knowledge of what it does, and they understand that information can be arranged systematically in columns and rows. Therefore, when it comes to a time at work when a report is needed, people tend to turn to Excel to do the job. Familiarity reigns! Recently, I met someone who did just this, even though there were no numbers or calculations in the spreadsheet.
In this blog, I’d like to identify the differences between each of the programs and help to determine if you are using Microsoft to its fullest potential in your organization.
First of all, if your data consists of primarily numerical data that requires some calculations, and it’s of moderate size, Excel is most likely the correct choice. It was designed to create calculations and answer other statistical problems that were once very complicated especially for non-mathematical users. After inputting the data and writing formulas that summarize information, Excel offers a visual tool to display the results called charts and graphs. All of this information in Excel is easily shared with others in your organization or outside.
On the other hand, Access is a database management program designed to store and manage larger amounts of data than Excel, with varying data types and by creating complex relationships with multiple tables. While it holds larger files, Access also performs more quickly than Excel. For those working with data that is not primarily numeric, Access is usually a better choice. The manner in which the data input validation is enforced also tends to render more efficiently in Access than in Excel. Last of all, Access offers better choices for generating reports and pulling queries.
The four main features in Access include tables, forms, queries and reports. Once all of the raw data has been gathered into a table, a user can pull queries to filter out the data in the table and then save the query for use in reporting. Forms are created for data input that is more user friendly than inserting data into the table. And reports are generated using either the step-by-step wizard for new users or custom reporting for advanced users. The fact that information can be pulled from multiple data sources, i.e. Excel spreadsheet, SQL database, etc. is one of the key features in Microsoft Access.
Arguments that support using Excel rather than Access include the fact that Excel does offer some advanced features such as Pivot Tables and Power Pivot. Power Pivot is a custom add-in that Microsoft has built and installed into the 2016 version of Excel. It just needs to be enabled under File, Options.
Power Pivot can process millions of rows of data in about the same time it takes to process thousands of rows in regular Excel. It also opens up a whole new user interface, something to get used to, that will allow users to create advanced relationships similar to those used in Access. And like Access, Power Pivot will pull information in from multiple sources to create reports and without slowing down the processing time. Its advanced reporting techniques using Key Performance Indicators is usually a favorite among many users, as well as utilizing pivot tables, pivot charts and the new DAX (Data Analysis Expressions) formula language.
Even though these Accessy-like features and tools are available in Excel, there is a learning curve if the decision is to absolutely stay with Excel. Learning the advanced techniques in Excel requires the LRS courses, Data Analysis With Pivot Tables and Data Analysis With Power Pivot. If you would like to consider learning and applying Access to your business needs, consider the two-day intro course for Access as well as the Level 3 one-day course in Access. In Level three, users learn how to segregate the front-end data input area from the general system set up and maintenance behind the scenes.
Both of these programs are powerful in their own right. Learning how to use them in tandem with each other will ignite productivity in the workplace that one never dreamed possible. Join us in an upcoming Excel or Access class by reaching out to us at getsmart@lrs.com or viewing our public schedule at LRS Education Services!
Looking forward to seeing you soon!
Kelly Cummings
Kelly.Cummings@lrs.com