How Jason uses Excel to speed up his invoicing process
Everybody has monthly invoicing that takes them hours to do. Jason is no different. He's the manager of a consulting company. He wants to issue monthly invoices to customers for services provided during the month... and there are thousands of records! Jason wants an easy way to extract activities on each client so he can bill them quickly.
Use this one Excel function to help you quickly search and extract data.
Using AutoFilter to filter data in Excel
When you filter a data selection, you'll see a little arrow in the top right hand side of the selection. You can select any cell in the table.
1. Press Ctrl + Shift + L
to access the filter function;
2. Click the arrow attached to the column you want to filter. This opens a drop-down list with all the data in that column;
3. Clear the fields you don't want to display after filtering. Excel selects all by default. The drop-down list can contain up to 10 000 entries so make sure you unselect all and then select only the data you want to display.
Keep reading to see what your spreadsheet should look like…
Do you know why Gail doesn't spend R10 000 a month on bookkeeping fees?
Gail is a GP who's just opened her own private practice. Because she's trying to save money on admin costs, she is doing her books herself. She thinks: 'How hard can it be?' The answer: 'It's very hard unless you've got the right tools at your fingertips!'
Well, I found just such a tool. It has everything from checklists about how to prepare for an audit, to detailed explanations about financial statements and how to use Excel to halve the time it takes her to do her financial management and accountancy tasks!
Find out more
about my newly-discovered one-stop accountancy resource! It's on special until 17 March!
How your spreadsheet looks after you filter it
Before you filter the spreadsheet:
After you filter the spreadsheet to display only 'Client_A' in the customer name column:
You can see in the image above that you've filtered 'column D'. Excel hides the rows that don't meet the filter criteria you set for the column.
But what if you want to filter other columns in the spreadsheet? You can! In the above table, you can display the activities of the Client_A in July or August 2012, for example.
If you want to remove the filter rule applied in the customer name column, but don't want to remove the other filters, select option Clear Filter from 'Customer Name' from the drop down tool.
Want advanced tips on how to deal with Excel for finance professionals? The early-bird special to save R1 000 for Advanced Excel for Finance Professionals Workshop 2014
ends tomorrow. Seats are limited so book now
to avoid disappointment.
Note: 5 of 1 vote