Excel Tips & Tricks

Tip #11 - November 25, 2024 - Data - Leading Zeroes

My homies in New England will probably already know this one with their weird zip codes that start with a zero. But let's say you're not from there and you want to capture data with a leading zero in Excel. Something like a 02139 zip code, a list of your favorite movies to include 007, or a list of bank accounts (an actual relevant one for my accountant followers).

Go ahead and type 007 into Excel right now. It's going to revert back to just 7. Normally fine, but annoying in this case right? Now type an apostrophe before typing 007. Just what you wanted! You may see a green triangle in your cell now. If you want to get ride of that, a little exclamation point should show up if you click in the cell. Click on the dropdown option from there and select "Ignore Error". You now have your formatted number with a quick hack!

Tip #10 - November 18, 2024 - Data Analysis - Evaluate Formulas

Happy International Fraud Week! In honor of this totally made-up holiday brought to you by the ACFE, I'm sharing a tip that allows you to check data integrity and find problems. As always, some other website already has a better tutorial than I could put together, so I'm linking that here.

These tools (trace precendents/dependents and evaluate formula) are great when you're trying to figure out the mess of a spreadsheet your colleague made. If there are complex formulas that reference a bunch of cells, the trace tools will put arrows onscreen to point you to the cells used. This came in really handy last week as my wife and I were trying to figure out how some of her team's financial projections were made. The other related tool, evaluate formula, will break down a formula step-by-step, allowing you to see how each calculation occurs in slow motion.

Hopefully these tools help you understand shared spreadsheets a little better. Hopefully those spreadsheets are complex because of over-engineering, and not as a way to conceal fraud!

Tip #9 - November 11, 2024 - Formatting - System Dates/Times

Thanks to all veterans on Veteran's Day today. Many of you use 24-hour time, unlike us civvies in the US. For anyone looking to have their default Excel data for date and time be more useful to your preferences, you can do this using system settings!

For example, my former team used to prefer seeing dates as "MM/DD/YY". While you can format this in Excel, it's a number of clicks in the Number Format Dialog for every time you want to format it appropriately. Instead, if you set your system settings for a date to be MM/DD/YY, anytime you type a date in Excel, it will be automatically displayed as that!

I've included a link here on how to change the system settings in Windows 10, but you can find instructions on any operating system with a quick Google search.

Tip #8 - November 4, 2024 - Data Analysis - Benford's Law

I've been doing fraud investigations for a long time, and one of the most common tests we would run is something called Benford's Law. In short, truly random set of large numbers should follow a pattern where the number 1 will be the most common leading digit (e.g. 100, 17594, 1867), followed by the number 2, then the number 3, etc. If you have a large dataset of random numbers, such as all of the journal entries in a general ledger, your data should roughly follow this pattern. A fraud might jump out if, for example, your AP clerk has been writing a bunch of fraudulent checks that they think are random amounts (such as $2,508.10 or $2,900.00), but actually don't conform to Benford's Law.

I used to have access to fancier tools such as Caseware IDEA to perform this analysis. However, I don't have that anymore and you likely don't either. Here's an awesome article from the Journal of Accountancy that breaks down Benford's Law and teaches you how to do it in Excel!

Now you may notice that this post's timing coincides with a big election in the US tomorrow. In 2020, there were a lot of false claims that Benford's Law proved election fraud. You're going to see it again this year. Don't be fooled! Benford's Law is not a good indicator, and a big reason for that is the analysis is done on local populations. If you remember from my first paragraph, you need large datasets. Local populations are not large enough. And don't just take my word for it - here's a paper from 2012, before election integrity was mainstream, debunking it.

All that to say: Benford's Law for financial fraud = pretty good (with limitations). Benford's Law for election fraud = not good. And no matter who you vote for, we all know Excel is better than Google Sheets, and that's a campaign I can get behind.

Tip #7 - October 28, 2024 - Formulas - DATEDIF

You might have noticed a gap in my weekly Monday tips. That's because we had fall break, and I take breaks/vacations pretty seriously. It is important to rest up and take care of yourself!

But in case you're having a hard time calculating how long it's been since my last tip, I have a formula for you! The =DATEDIF formula is an old formula for compatibility with Lotus 1-2-3 (which if you're still using anything from that, the actual tip here is to update your computer.) It doesn't even show up as a formula in Excel, but you can still use it anyways! Here's Microsoft's support page on how to use it, and even how to manipulate it to show different timeframes than the ones built into the formula. The standard formula allows for days ("D"), months ("M"), and years ("Y"), but you can use it to show that I haven't posted a tip for 2 weeks.

As always, there are different ways you can calculate the time elapsed between two dates, but this is one of my go-to options because it's intuitive and requires minimal math!

Tip #6 - October 14, 2024 - Data - Data Types

Since my last tip was international-related, I'll keep this one in the Geography world. Plus, I had an Excel battle this weekend (and failed miserably), which often times use "maps" in the battles.

Did you know there are different data types to which you can convert your cells? Under the Data tab, look under Data Types. There are 4 primary ones - Organization, Stocks, Currencies, and Geography. These can be useful if you're trying to pull in information about multiple cells, but don't want to look them up manually, and can be identified when there is a little icon in the same cell.

For example, if you needed to know the population of multiple cities, you can use the Geography data type. I've included a screenshot below, showing the area, latitude, longitude, population, and timezone for several cities. Note that not all fields populate, so you may have to look these up manually still.

This was helpful back in my audit days and I needed to verify stock price inputs for investment testing. Where might you find this useful? As always, try to apply these tips to your everyday work!

Tip #10 - November 18, 2024 - Data Analysis - Evaluate Formulas

Happy International Fraud Week! In honor of this totally made-up holiday brought to you by the ACFE, I'm sharing a tip that allows you to check data integrity and find problems. As always, some other website already has a better tutorial than I could put together, so I'm linking that here.

These tools (trace precendents/dependents and evaluate formula) are great when you're trying to figure out the mess of a spreadsheet your colleague made. If there are complex formulas that reference a bunch of cells, the trace tools will put arrows onscreen to point you to the cells used. This came in really handy last week as my wife and I were trying to figure out how some of her team's financial projections were made. The other related tool, evaluate formula, will break down a formula step-by-step, allowing you to see how each calculation occurs in slow motion.

Hopefully these tools help you understand shared spreadsheets a little better. Hopefully those spreadsheets are complex because of over-engineering, and not as a way to conceal fraud!

Tip #9 - November 11, 2024 - Formatting - System Dates/Times

Thanks to all veterans on Veteran's Day today. Many of you use 24-hour time, unlike us civvies in the US. For anyone looking to have their default Excel data for date and time be more useful to your preferences, you can do this using system settings!

For example, my former team used to prefer seeing dates as "MM/DD/YY". While you can format this in Excel, it's a number of clicks in the Number Format Dialog for every time you want to format it appropriately. Instead, if you set your system settings for a date to be MM/DD/YY, anytime you type a date in Excel, it will be automatically displayed as that!

I've included a link here on how to change the system settings in Windows 10, but you can find instructions on any operating system with a quick Google search.

Tip #8 - November 4, 2024 - Data Analysis - Benford's Law

I've been doing fraud investigations for a long time, and one of the most common tests we would run is something called Benford's Law. In short, truly random set of large numbers should follow a pattern where the number 1 will be the most common leading digit (e.g. 100, 17594, 1867), followed by the number 2, then the number 3, etc. If you have a large dataset of random numbers, such as all of the journal entries in a general ledger, your data should roughly follow this pattern. A fraud might jump out if, for example, your AP clerk has been writing a bunch of fraudulent checks that they think are random amounts (such as $2,508.10 or $2,900.00), but actually don't conform to Benford's Law.

I used to have access to fancier tools such as Caseware IDEA to perform this analysis. However, I don't have that anymore and you likely don't either. Here's an awesome article from the Journal of Accountancy that breaks down Benford's Law and teaches you how to do it in Excel!

Now you may notice that this post's timing coincides with a big election in the US tomorrow. In 2020, there were a lot of false claims that Benford's Law proved election fraud. You're going to see it again this year. Don't be fooled! Benford's Law is not a good indicator, and a big reason for that is the analysis is done on local populations. If you remember from my first paragraph, you need large datasets. Local populations are not large enough. And don't just take my word for it - here's a paper from 2012, before election integrity was mainstream, debunking it.

All that to say: Benford's Law for financial fraud = pretty good (with limitations). Benford's Law for election fraud = not good. And no matter who you vote for, we all know Excel is better than Google Sheets, and that's a campaign I can get behind.

Tip #7 - October 28, 2024 - Formulas - DATEDIF

You might have noticed a gap in my weekly Monday tips. That's because we had fall break, and I take breaks/vacations pretty seriously. It is important to rest up and take care of yourself!

But in case you're having a hard time calculating how long it's been since my last tip, I have a formula for you! The =DATEDIF formula is an old formula for compatibility with Lotus 1-2-3 (which if you're still using anything from that, the actual tip here is to update your computer.) It doesn't even show up as a formula in Excel, but you can still use it anyways! Here's Microsoft's support page on how to use it, and even how to manipulate it to show different timeframes than the ones built into the formula. The standard formula allows for days ("D"), months ("M"), and years ("Y"), but you can use it to show that I haven't posted a tip for 2 weeks.

As always, there are different ways you can calculate the time elapsed between two dates, but this is one of my go-to options because it's intuitive and requires minimal math!

Tip #6 - October 14, 2024 - Data - Data Types

Since my last tip was international-related, I'll keep this one in the Geography world. Plus, I had an Excel battle this weekend (and failed miserably), which often times use "maps" in the battles.

Did you know there are different data types to which you can convert your cells? Under the Data tab, look under Data Types. There are 4 primary ones - Organization, Stocks, Currencies, and Geography. These can be useful if you're trying to pull in information about multiple cells, but don't want to look them up manually, and can be identified when there is a little icon in the same cell.

For example, if you needed to know the population of multiple cities, you can use the Geography data type. I've included a screenshot below, showing the area, latitude, longitude, population, and timezone for several cities. Note that not all fields populate, so you may have to look these up manually still.

This was helpful back in my audit days and I needed to verify stock price inputs for investment testing. Where might you find this useful? As always, try to apply these tips to your everyday work!