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:

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...

Are Client Accounting and Advisory Services (CAAS) For You?

Accounting also personally referred to me once as “the necessary evil”, has a rough reputation.  Pocket protectors, ten keys, panty hose, and even maybe an abacus is what people imagine when they think of my profession. Although, not entirely inaccurate, I believe the...

Is ASC 842 Lease Software Worth Utilizing?

Does your company have a significant number of leases? Are you required to apply accounting under Accounting Standards Codification 842, Leases (ASC 842)?  If so, you may want to explore purchasing software to assist with accounting for those leases. Lease accounting...

Latest Posts