Excel Hacks for Accountants from Millennial Pros: Volume 2

by | Oct 18, 2017 | Audit, Technology

With an office full of accounting professionals that spend more time in Excel than we’d like to admit to ourselves, we’ve racked up quite the toolbox of best practices in the program. We’re excited to share this wealth of knowledge with you. Because Excel isn’t simply a tool for accountants, we hope you’ll find these hacks helpful within your own industries, roles and workplaces.

Last month, our team members, Travis Aguirre and Lee Gyomlai, shared hacks surrounding “Center Across Selection” and “Go To Special.” Take a look here.

This month, based on common questions we’ve received from our clients lately, we bring you “Subtracting Dates” from Lee and “IndexMatch” from Travis.

SUBTRACTING DATES

Purpose:

Have you ever been frustrated by Excel’s solution to subtracting dates, by only providing you with the number of days? This tool allows you to know the exact number of days, months or years between two dates, instead of simply days.

Why use it?

Subtracting dates is an excellent Excel tool to calculate depreciation. More so, this tool allows you to do so efficiently and accurately.

Limitations:

While subtracting dates doesn’t have any major limitations, you may occasionally run into slight rounding differences with special circumstances like leap years.

Steps to subtract dates:

  • For days: Excel does this automatically. Just take the day that appears in the future and subtract it from the date that appears in the past, like so: 

Here, the value, 2921, is the result, which is the number of days between values A and B.

  • For months: This requires more work. Use the formula below.

=ROUND(((YEAR(A12)-YEAR(B12))*12)+(MONTH(A12)-MONTH(B12)),0)

This formula figures out the number of years, multiplies them by 12 and then takes the remainder of the months and adds the two results.

  • For years: Just use the following formula.

=ROUND((YEAR(A12)-YEAR(B12),0)

And voila!

INDEXMATCH

Purpose:

While “vlookup” (or vertical lookup) is the most commonly used lookup tool, once you’ve mastered it, the “index()” and “match()” functions can be used, in conjunction, to create an even more powerful lookup tool. For instance, if you’re interested in looking up certain data, such as produce prices, within a larger spreadsheet that includes prices for all food your business sells, IndexMatch allows you to dictate what you want to look up and where (which column) you will find the price.

Why use it?

Especially when compared to “vlookup,” IndexMatch provides the following benefits:

  1. Increased flexibility allows you to match against rows and/or columns.
  2. Increased accuracy when adding and removing columns from a referenced table or range due to the dynamic lookup range, especially when modifications are made.
  3. Increased ease of use because lookup value can be to the left or right (above or below if looking up horizontally) of the result column.

Overall, IndexMatch is more dynamic than the commonly-used “vlookup” as it will update itself as you remove or add columns and/or rows within your Excel worksheet.

Limitations:

The two-step process involved with “index()” and “match()” can be more difficult to master when compared to vlookup or hlookup.

Understanding the functions:

Index()

The “index” function is used to return a value from with a table or range.

Syntax:  =index(array,row_num,column_num)

Match()

The “match” function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

Syntax:  =match(lookup_value,lookup_array,match_type)

The following shows similarities between the familiar vlookup and the not so familiar IndexMatch. The values highlighted in the same color are essentially asking for the same information.

=vlookup(lookup_value, table_array, col_index_num, range_lookup)

=index(array, match(lookup_value, lookup_array, match_type) 

Examples:

See how easy it is? Time to see how IndexMatch will help you!

Be sure to follow Weinstein Spira on Facebook for new Excel hacks each month and share the knowledge with your accounting industry friends and peers.

 

You may also like:

Tips and Tricks for New Auditors

Starting a career in audit can feel like plunging into a complex and detail-oriented world, but with the right approach, you can navigate the journey with ease. Whether you're working in internal or external audit, these tips and tricks will help you build a solid...

401(k) Basics: What You Need To Know

For many Americans, saving money for retirement can be challenging. While some employees prefer to take home a few extra dollars rather than participate in their company’s 401(k) plan, many simply do not understand how these plans work or the many benefits that come...

Does Your 401(K) Plan Still Need To Be Audited?

Around this time of year, CFOs and HR professionals often ask me, “Does my 401(k) plan need an audit?” Due to recent changes made by the Department of Labor for the 2023 plan year, many will be very happy to hear that they can avoid an audit for a few more years....

New Year, New Auditing Standards

In October 2021, the American Institute of Certified Public Accountants (AICPA) Auditing Standards Board (ASB) issued Statement on Auditing Standards (SAS) No. 145, Understanding the Entity and Its Environment and Assessing the Risks of Material Misstatement in...

Cybersecurity Trends: What You Need To Know

Cybersecurity is the practice of protecting data, networks, devices and systems from unauthorized access, theft or damage. It is a constantly evolving field that requires businesses and individuals to stay alert and informed of the latest threats and solutions. In...

Latest Posts

Did You Know About QBO?

Did You Know About QBO?

QuickBooks holds a significant market share in the U.S, with QuickBooks Online (QBO) rapidly growing as more users...

read more