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 #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!