Excel and Access: Tools of our Trade

What would you bring if you were stranded on a desert island?

I would bring Microsoft Excel because it’s the only application I would need! It stores more than 17 billion cells in a single sheet, summarizes data with Pivot Tables, generates charts and has a built-in Web browser. Excel, in my opinion, is the greatest software application of all time. However, it has its limitations. In addition, it’s nice to know that when we run into an Excel limitation, we can turn to Microsoft Access.

Excel Tips

When I was the accounting manager at a public company, my life was sometimes stressful because I had more work than time. I took a long time to complete my work because I didn’t use the right Excel functions or features. Once I figured out how to use Excel the right way, I was able to delegate many of my mechanical recurring tasks to Excel. The following are my top three favorite functions, features and shortcuts:
Functions

• SUBTOTAL: The SUBTOTAL function is similar to the SUM function, except the SUBTOTAL function excludes other SUBTOTAL functions in the range.
• SUMIFS: The SUMIFS function is similar to the SUM function, except it only includes the rows that meet a condition. For example, sum up the “amount” column, but only include those rows where the “account” column equals “cash.”
• VLOOKUP: The VLOOKUP function looks for matching rows in a related table, and returns a corresponding value. For example, look up account number “100” in the chart of accounts and return the matching account name.

Features

These functions help improve my productivity, increase my accuracy, and make my workbooks more “bulletproof.”

• Tables: Tables solve one of the biggest pitfalls in Excel. Tables have special properties, including auto-expansion. Any new data typed or pasted directly under a table will expand the table, and will be included in any formulas that reference the table. (Insert > Table)
• PivotTables: PivotTables are reports. They aggregate and summarize transactional data, are interactive and allow you to analyze the underlying data effortlessly. This is a huge feature and the most powerful feature of Excel. (Insert > PivotTable)
• Hyperlinks: Hyperlinks allow us to create an organizedcollection of related electronic documents. A hyperlink creates a clickable link that opens another worksheet, workbook or file. You can use hyperlinks to organize your work papers or your reports. (Insert > Hyperlink)

Shortcuts

Three time-saving shortcuts are:

• Fill-down: You can quickly fill a formula down a column when there is data in the adjacent column by double-clicking the lower right corner of the formula cell.
• Next worksheet: You can navigate to the next worksheet in the workbook by selecting Ctrl + PageDown. (The previous sheet is Ctrl + PageUp.)
• Arrow keys: You can use the arrow keys to navigate within a worksheet or to define function arguments. Holding down the Shift key with the arrow keys extends the current selection. Holding down the Ctrl key with the arrow keys jumps to the edge of the data region. Holding down both the Shift and Ctrl keys with the arrow keys extends the current selection to the edge of the data region.

Introduction to Access

Excel can be a powerful tool, but there are times when certain job tasks are better suited for Access. Access is Microsoft’s smallest database application. A database is a collection of related data. The data is stored in “tables” within an Access file. A database table is similar to an Excel worksheet, as it has rows and columns. These are called records and fields in database lingo.

Although an Excel worksheet can technically store more than a million rows of data, it’s better to store large data sets inside a database, such as Access. Databases are designed to store and process many records, and have special settings designed to handle large data sets, such as indexing.

Excel doesn’t handle several people working on the same workbook at the same time. Databases like Access are designed for multiple concurrent users. Use Access when you have a project that requires several people to work with data simultaneously.

Excel has limited security settings, but Access has detailed user-level security settings. For example, we can allow the user to view certain tables, another user to edit data, and another user to run reports. In Access, user level security is well-defined.

We can centralize data in an Access database and pull it into Excel using the external data feature. Clicking the Refresh button pulls the changes into Excel. If there’s no software to help you automate a specific or unique business process, use Access to create your own application. Examples include a budget system or a commission reporting system. Access can help us overcome Excel’s limitations, improve Excel’s utility and help automate workflow and processes. Excel and Access are both tools of our trade.