Each of Clio's reports and exports are available as a CSV (comma separated values format) which can be opened and manipulated in Micosoft Excel. This article provides a few advanced tips for filtering the data in your CSVs to ensure that you get quick access to the values of greatest importance for your reporting needs.
- For steps on how to export your data from Clio, click HERE.
- For basic information on filtering CSVs in Excel, click HERE.
In Excel, you can compare or combine columns from one or more master sheets into a new data range by using the "Paste Link" feature. This technique would be particularly useful for creating new tables from large Matter or Contact exports to better be able to view desired data without manipulating the original export data.
This example shows how to take name and address columns from a Contact export to create a new table. By using "Paste Link", all of the data in the new table will be linked back to the master sheets so that any updates to the master will also update the new linked table.
In the master data, highlight the column cells that you would like to add to the new table. Do not select entire columns. Click in the top-leftmost cell of the area that you want to copy. Then go to the bottom-rightmost cell of the range to be copied and click in the cell while holding the Shift key. With the range selected, copy it then open a new sheet.
In the new sheet, click in the cell where you want the table to begin then click on the "Paste" button and select the icon that looks like a clipboard with a link. Hovering over the button will show you the button name: "Paste Link". Paste options can also be found by right-clicking in the cell.
If your data range has any empty cells, those cells will now have zeros added. If you do not want to have zeros in your data, keep the range selected after pasting then hit Ctrl+f on your keyboard to open find and replace options. In the "Find and Replace" window, click the "Options" button then complete the fields as follows:
- Find what: Enter a zero (0)
- Match case and Match entire cell contents: Check these boxes
- Look in: Change this field to "Values"
Click "Find All".
A list of results will now appear in the "Find and Replace" window. Hit Ctrl+a on your keyboard to select all results. This will select all the zero cells in your data.
With the zero cells selected, click back on your worksheet and hit the "Delete" key on your keyboard.
Repeat the above steps with the next group of columns that you want to add to your new table, making sure to perform the paste in the cell to the right of the columns that you have already pasted.
In this example, we now have a new table of names and addresses.
For details on how to convert the data range into a sortable table, continue on to the next section.
Excel offers a number of methods for sorting data. One of the quickest and most versatile methods of sorting data in Excel is to convert the data range to a table. Converting a range to a table has a number of benefits including easy filtering & sorting, instant formatting, and quick & easy Totals.
To start, select the data range to be converted by clicking in the top-leftmost cell of the range then click in the bottom-rightmost cell of the range while holding the Shift key. Do not select entire columns or rows as this may cause Excel to crash.
With the range selected, click on the "Format as Table" button on the Home ribbon. You will now be presented with a variety of formatting options. Select which ever formatting suits your purposes.
In the "Format as Table" window, check the box beside "My table has headers" then click OK.
You may be alerted that formulas in the header row will be removed. This is fine. Click "Yes" on the alert pop-up to continue.
Your data will now be formatted as a sortable table.
To sort or filter your table by a particular column, click on the pull-down button in the column header.
- Select a sort option to quickly organize the data in your table; OR
- Add a Text Filter to filter your table by a specific criteria; OR
- Uncheck a value in the selection box to hide all rows with that value from your table. Uncheck "Blanks" to hide all rows without a value in that column.
You can sort and filter your table by setting filter options for as many columns as you like. In the above example, a number of Contacts do not have a mailing address. By filtering the "Business Street" column to hide all "blanks", only Contacts with a mailing address will be visible. The "Last Name" column can then be sorted by "A to Z" to provide an ascending list of Contacts with mailing addresses.
Columns that have been filtered or sorted will be identifiable by an icon in the column header.
To remove a filter, click on the pull-down button in the column header then click "Clear Filter From...".
Once you have converted a range to a table, you can easily add a Totals row to that table for any columns that contain numeric values such as dollar amounts. Adding a Totals row would be useful on a Trust Ledger Report, for example.
To add a Totals row to your table, right click anywhere in the table then click on "Totals Row" from the "Table" menu.
In the added Totals row, click in a cell to bring up a pull-down button. Click on the pull-down button for a list of common functions to add to the cell.
Selecting the "Sum" function, for example, will add the total of all values in the column.
In the above example, the table shows funds in to and out of an IOLTA trust account. To get the balance of the account, some additional steps are required.
- Click in the cell where you would like to display the balance.
- Then click in the Function bar and type the equals symbol (=). Once you have added the = to the Function bar, you can select the cells to be included in your calculation.
- Click in the Total cell in the "Funds In" column.
- Type the minus symbol (-) after the table cell info in the Function bar.
- Click in the Total cell for the "Funds Out" column. You will now see the full equation in the Function bar.
- Hit the Enter key on your keyboard to complete the function.