Excel Tips & Tricks
I'll post my Excel tips and tricks here. You can hopefully navigate through the table of contents once enough tips are posted to quickly find tips that may be relevant to you. I'll also try to include keywords in the title, such as a formula name or the general category (such as VBA/macros, pivot table, etc.) so that you can quickly search.
Many times, I'll provide context on how a tip can be used or my own commentary on the trick, but I'll link to another site that actually walks you through the mechanics step-by-step. I don't get paid for those links; I just don't want to recreate the wheel and do a bunch of screenshots/extra steps when someone has already done that. To paraphrase Kevin from The Office, why waste time make stuff when other stuff do trick.
For all of these tips, I encourage you to read the tip and then think about how you can apply it to your own work. Then, intentionally practice that week! I was going to post these on Tuesdays and call it "Tricky Tuesday" or some fun alliteration like that. But nobody starts their work week on a Tuesday and you'd miss out practicing on Monday.
Tip #12 - December 2, 2024 - Not a Real Tip
Shoot me an email or responded to my poll on LinkedIn. Is the format of these tips working for you? Or would you be interested in seeing a "consulting" project from start to finish, with hands-on tips showing different things like conditional formatting, data best practices, and other things along the way?
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 #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 #5 - October 7, 2024 - Formulas - The Most Useless Tip I'll Ever Have
Just for fun, I like to share the least useful formula I've ever found with people who ask me about my Excel skills. Very few people have ever heard of BAHTTEXT. Even fewer have used it. It converts a number to Thai text and then adds the word "Baht" after it... The real takeaway here is that if you're not sure if there's a formula to do the thing you want, there probably is!
Tip #4 - September 30, 2024 - Miscellaneous - Fill-In Blanks Using Go-To Special
Often times as an accountant, you will get a report that has data in a column (usually on the left) that has information that you need, such as an account number, but is only in the first row, then has blank data until the next value. However, for data analytic purposes, it's really helpful to have that data in each row, so you want to fill in the blank cells with the data above. There is an easy solution! Rather than recreate the wheel in teaching you though, the Journal of Accountancy has a pretty good tutorial here:
https://www.journalofaccountancy.com/issues/2023/may/fill-blank-cells-excel.html
The only thing I would add is that if you do this while the cells are formatted as text, you will need to change that format BEFORE completing the steps, or else your results will just be formula garbage. This is especially common with reports downloaded from QuickBooks, so keep it in mind!
Tip #3 - September 23, 2024 - Formulas - TOCOL
I competed in another Excel Esports battle last week, and it did not go well. Like over 100th place in a pool of just 265. But I promised an update on LinkedIn so there it is. However, with every challenge comes a lesson! Keep that in mind whenever you're struggling through a formula in Excel; once you get it, remember where you went wrong so you can be the expert who shows your coworker the right way to do it the next time!
An incredibly useful formula in Excel Esports is TOCOL, which I only learned about through the competitions. However, I can see its practicality in finance as well. It takes an array of data and converts it to a single column. Let's say, for example, you have monthly financial data across multiple years, like below:
This is considered a "wide" format. Many times, it's easier to work with data in a "narrow" format. While the data I show below could get even more "narrow", by using TOCOL, you are able to convert the table above into a three-column table where each line is a single month's financial data. Specifically, my formula in cell C14 is is =TOCOL(B2:M11), which then "spills" all of the corresponding data down into the rest of column C (which you can see by the blue outline of column C when the formula is selected):
Like anything in Excel, there are other ways to get to this point, including Get & Transform, but this can be a quick and dirty method to transpose data, which comes in handy when you're crunched for time!
Tip #2 - September 16, 2024 - Miscellaneous - Find and Replace an Asterisk (or Tilde or Question Mark)
Ever had a bunch of asterisks in your spreadsheet that you wanted to replace? When you go into the Find and Replace tool in Excel and do that for an asterisk though, it replaces everything! Same with a question mark - maybe you're trying to change the Jeopardy answers in your spreadsheet to no longer be in the form of a question. When you try to replace a question mark, it just replaces every character as well. In order to actually replace your * or ?, you need to "escape" the wildcard. Which means placing a tilde (~) in front of the character you're trying to replace. I've included a screenshot below - give it a shot yourself! And let me know, how would you find this useful in your daily work?
Tip #1 - September 9, 2024 - Shortcuts
If you want to be efficient in Excel, keyboard shortcuts are essential. Microsoft has a great list here. Just looking at these shortcuts isn't going to help you though. Some of them (like ctrl-c and ctrl-v) are probably already second-nature to most of you. My suggestion for becoming proficient in shortcuts is to find just one shortcut on that list that you aren't already using but think would come in handy. Maybe it's using ctrl+shift+arrow keys to select and navigate data. Maybe it's ctrl+0 to hide the selected columns. Whatever it is, make that the shortcut you focus on. Keep using that in your day-to-day work until it becomes automatic for you. Then, choose your next shortcut to focus on, until that becomes automatic. Rinse and repeat with shortcuts as they become more and more natural, and the next thing you know, you'll be a keyboard master!
In a future tip, we'll cover how you can even create your own keyboard shortcuts with macros, once you're a master!