Exporting a Thread report to Excel 

Click on the downloaded report to open it in Excel. 

Searching by word 

You can easily find anything in the exported report by just typing in what you are looking for in the Find box. 

Sort and Filter 

When you open your report in Excel, select the first row and on the Home tab choose Sort & Filter dropdown. 

Sort 

If you wish to sort the report A - Z ascending or descending, such as by client name, or date created, select either of the Sort options. Choosing Custom Sort allows you to choose which column you wish to be sorted alphabetically. 

Filter 

If you wish to filter the report by any of the values, such as matter owner, or time type, select the Filter option. 

You will notice that all columns will now have an arrow on the right where you can choose the value by which you wish to filter. You can untick '(Select All)' and only tick the boxes of the values you wish to show, such as your own name if you want to only see the results for matters you are the owner of. 

You can apply multiple filters at once, for example to filter the report to show only your matters, and only the Unbilled time other than 0.  

Filtering by Date Range 

You might want to further filter your report to show you only a specific date range. For example, when pulling a time report on a matter, but you only want to see the time recorded last month. 

Some reports might need an extra step to format your data as ‘Date’ in order to use the 'Date Filters'. If yes, please follow the steps below first to format the Date column to Date format. Then, you can go ahead and use the Date Filters as needed. 

Formatting data as Date 

1. Select the Date column, and in the Data tab, click on Text to Columns. In the pop-up that opens, click on Delimited, and choose Next. 

2. Tick the Tab and Space box, and then Next. 

3. In the last box, choose the Date option and select the DMY format, then click Finish. 

4. Your selected column will now show in the date format, and you can select it again to choose either a Short Date or Long Date, from the Home tab. 

Using Date Filters 

When your data is showing as a Date Format, you can now choose to filter the report using Date Filters.  

1. Select the column with the dates, and choose Filter option.  

2. Click on the arrow right of the column name, and choose Date Filters. 

3. You can now use any of the options provided, for example Between.. and specify the time period you wish to see. 

You can also sort the dates in sequential order using the Sort options. 

Filtering 0 Time Values 

If you want to view only the time logged on your report without any entries with no time, you can easily do this by filtering the Time column to show only values greater than 0. 

This way you would only see in the report entries that have time logged (Billed or Unbilled). 

Calculate Totals 

If you need to see the totals of values in a column, such as total time spent or value, you can easily do this by using the function =AGGREGATE(9,3,range of cells) 

Example: =AGGREGATE(9, 3, A1:A10) 

Here’s what each part of the function does: 

9 is the function number for SUM within AGGREGATE. 

3 is the option to ignore hidden rows, error values, and nested subtotals. 

A1:A10 is the range of cells you want to sum 

This formula will return the sum of the visible cells in the range A1:A10, excluding any cells with errors and ignoring any hidden rows. 

Example in a Thread report that has been filtered and sorted: 

=     

Beware that some reports may already include totals per matter, which will be included in your calculation. Please see the steps below to remove those totals, so your calculation would show correctly only the total of individual entries. 

Removing Existing Totals 

Some Thread reports would already have multiple totals included. If your report already has totals included, but you don't want those totals to be counted in your calculations, you will need to filter or remove these so it doesn't include it in the sum. Thread reports would usually show totals in grey background. Follow the steps below to find all cells with this formatting. 

This method allows you to find and filter cells with specific  format: 

1. Select the column where you want to find the cells with formatting (e.g. ‘Total Time’ column). Apply the Sort&Filter option to this column. 

2. In ‘Filter by Color’ option, select grey colour filter.  

3. Once you found all the totals in the spreadsheet, you can choose to delete them entirely or clear values. 

If you wish to delete these rows, simply select the row and choose Delete.  

If you wish to Replace All with blank values, select all the cells with totals, right click and choose Clear contents from the menu. 

Formatting data to Currency 

If you wish to show some data in your report as a currency, such as the rate, and value, you can switch the cells formatting to currency selecting the relevant currency you need. 

1. Select the columns where you have date that needs to show as a currency.  

2. In the Home tab, click on the Number Format box dropdown, and select Currency.  

3. The data in selected columns will automatically show as a currency. If the currency showing isn't the one you need, click on the Number Format expand button in the bottom right corner to see more options. 

4. Select the relevant currency symbol, and choose OK. 

Your selected columns will now show the correct currency. 

Finding Duplicates 

You might want to use Excel to quickly find any duplicates in your data. For example, this will help you find any matters or contacts that are duplicated in the system. 

Finding Duplicate Contacts 

From your Contacts page, click on Export to open the report in Excel. 

Once you open the report in Excel, you will need to insert a column where you can merge the First name, Middle name and Last name columns into one. Click on the column after the Last name, and Insert a new column. 

In the first cell of the new column, enter the formula to merge the columns. For example, if ‘First Name’ is in column E, 'Middle name' in column F, and ‘Last Name’ in column G, you can use the formula: =CONCATENATE(E2, " ", F2, " ", G2) 

To apply the same formula to the entire column, select the first cell in the column where you have the formula applied, point to the small plus icon at the bottom-right corner of the cell, and double click it. Excel will automatically fill the formula down the column to the last adjacent cell with data. 

You can give your new column a name such as Full name, and it will now look like this. 

You may already notice some duplicates showing there, but to quickly mark all duplicates in the report, select the column again, then click on Home tab -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values 

Select how you want the duplicate values to show on the report. 

All your duplicate contacts will now show in red in the spreadsheet, so you can easily find them, go back to Thread and remove them as needed. 

Finding Duplicate Matters 

On your matters page, choose the relevant filters, and then click on Export.  

When you open the report in Excel, select the column with matter names, and click on Conditional Formatting -> Highlight Cells Rules -> Duplicate Values 

Select how you want the duplicate values to show on the report. 

Select the column again, and apply a filter. 

From the filter dropdown, choose Filter by Font Color if this was your chosen duplicates value format, and select the relevant color. 

You will now see a list of all your duplicates in the report, and you can decide if you need to delete, or rename these matters in Thread.