Most accounts receivable specialists use Excel on a daily basis. You’re constantly inputting data and using it to keep track of cash flow. That being said, Excel is not always easy to work with. One wrong move and the information you have been working relentlessly to get may end up incorrect. Luckily, there are a lot of short cuts and tricks to use to make Excel easier – and faster – to use.
If you’re using Excel everyday, you might already know some of these, but you might also discover some new tricks that could help speed up your work by minutes.
SWITCH TO DIFFERENT WORKBOOKS
Often times, were copying and pasting numbers from one Excel spreadsheet to another. Although it is not ideal, sometimes it just needs to be done. In order to make it easy, instead of clicking off one sheet to another, you can use the Alt+Tab function. Anytime you want to switch back and forth between the two spreadsheets simply hold down Alt and then Tab.
If you’re dealing with a customer list, especially one that you have exported from your ERP, CRM or CCM, you may want to double check that you don’t have any duplicates, like 15 John Smiths from ABC Company. In order to get rid of these duplicates, highlight all the fields you want to check, click on the data tab and click on “Remove Duplicates”. It will automatically remove all duplicate values.
FILTER BY COLOR
This trick is extremely helpful when it comes to creating your aging report. (Check out our blog post on how to create an entire aging report in Excel) If you want to be able to see how late each customer is in color, for example red for the latest and blue for those who still aren’t due, then you want to use conditional formatting. Simply highlight all the cells you want the color coding in, select “New Rule”, then format it based on how you want it to look. To see step by step instructions, check out our other blog post.
If you have a really long string of cells that you want to highlight, but don’t have time to sit there clicking to the end of the column or row, use this trick: Shift+CTRL+Arrow. Some people may be thinking, why not just click on the column or row that you want to highlight everything? This shortcut will ONLY highlight the cells with values, whereas clicking on the entire row or column highlights EVERYTHING.
FIND INFORMATION QUICKLY
This little trick will be helpful inside and outside of Excel. By simply using CTRL+F, you can find anything you need inside of webpage, word document, PDF or Excel. For example, if you have a particularly large spreadsheet and know exactly what you’re looking for, simply use CTRL+F and a search box will pop up to help you find anything you need.
If you want to make this process even easier and forget about Excel, consider using automated accounts receivable software. It can pull all the data straight from your ERP or business software, create individualized reports, send alerts, invoices and emails and perform customer lookups from inside the software.