How to Filter Excel Files

Owned by Tom Cavey
Last updated: Jul 13, 2020
3 min read

VoIP Innovations allows customers to export different information from the Back Office into excel spreadsheets, so customers can save them for filing purposes. There are many sets of information that can be exported but to know what pages can export information there has to be an Export Results button.

The dropdown beside the button will have two different options: 'Excel Spreadsheet' or 'CSV' file. This article will show how to filter down these different files.

As an example, look at the steps to filter down transactions to show charges that add up for the monthly minimum on an account.
Go to the Billing tab and click View Transactions.

On the Transactions page there will be an area to choose a date range for the transactions. For this example, there will be a months' worth of transactions to work with. When choosing a full month of transactions, select the day before the 1st of the month
and the day after the end of the month. This ensures that data isn't missed that could have been charged for the month. After selecting the date range and clicking the 'Update Date Range' button all transactions for the specified date range will appear.

Click the drop-down button by the Export Results tab and choose the Excel Spreadsheet option.

Once the Excel file is up, highlight all the headers to get them ready to filter.

After selecting all the headers, click dropdown for the 'Sort & Filter' button. This button can be found in the 'Editing' section under the 'Home' tab.

Once the drop-down for the 'Sort & Filter', choose the Filter option in the list shown.

Drop-down options for each header will appear


Click the drop-down menu button next to the option that needs to be filtered. For this example, Charge Date will be chosen.


Once that has been completed some charges can be removed in this list, as these charges are not counted towards usage for the monthly minimum. To do this, select the dropdown for the Description.

Under this drop-down un-select the following; CSI Tax, MINBILL, and Blanks. If those specific options are not there then it means you have an updated version of Excel. The newer options will be CSI Tax, Minimum Billing, and Payment. 'Minimum Billing' would be what will be charged to the account if you do not meet the minimum billing requirement and payments or billing will be any one-time payment.

Highlight the Charge Amount column after filtering and Excel will add this column up.



The other option for this would be selecting the last value in the Charge Amount column and do an AutoSum. To perform an AutoSum, go to the Formulas Tab -> AutoSum. After finding this select the AutoSum button twice.
 
For this example, it shows the usage towards the monthly minimum was $129.06. Let's say for this example that the monthly minimum is $199.00. So, the one-time payment would be $69.94 for the monthly
minimum requirements to have been met. Once the 'Minimum Billing' option is added to the filtered column it will show a one-time payment of $69.04.