Microsoft Excel Tips & Tricks for CJA Cases: Formatting Tables

This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.

Previous Post: Filename Lists
Today’s Post: Formatting Tables

CJA panel attorneys and federal defenders frequently receive some of their discovery in spreadsheet or Excel format. Call detail records and indices listing information regarding discovery productions (often called document indexes) are two examples. Having the files in Excel format instead of PDF is advantageous as spreadsheets are designed to sort and filter information, either by a single or multiple criteria. With voluminous information, this ability to sort and filter by multiple criteria can speed up review and allow you to identify the information you are interested in. For example, if you have telephone call records in Excel format, it is easy to filter by a number of phone numbers and quickly narrow the entries to review with several clicks. If the same information is in PDF format, it would have to be done manual and take much, much more time.

For those experienced with Excel, it is a welcome sight to see the data in Excel format. However, for the neophyte Excel user who only reviews PDF files, it can be frustrating to navigate Excel and review the data. This post provides quick and easy formatting options that are available within Excel that can save you review time.

Let’s look at a mock discovery document index as an example. Often the document index starts as a simple list of files with some basic information. As these lists become longer and include more document details (frequently in the form of additional columns), they can be hard to read and work with unless formats are applied. The video below demonstrates quick and simple options within Excel that can help to transform a basic list into a better looking, more functional table that include easy to use sort and filter features.


Some of the topics covered in the video above are:

Column Width
It often helps to be able to adjust the width of columns to better fit important information on your screen. To adjust the width of a column:

  • Move the cursor in between the column headers until it becomes a black line with two arrows.
  • To manually adjust the width of a column left-click then drag the black line to the right or left.
  • To automatically adjust a column, double left click and the column width will become as wide as the longest text entry in that column.
  • Multiple columns can be adjusted at the same time by selecting them before making a manual or automatic adjustment.

Wrap Text
Automatically resizing row heights helps to make the words within longer text cells visible. Select the cells, rows or columns to be adjusted then choose the “Wrap Text” button from the Home menu.

Cell Alignment
Adjusting cell alignments can sometime help make items more uniform and easier to view. By default, Microsoft Excel aligns numbers to the “Bottom-Right” of cells and text to the “Bottom-Left”. A common adjustment is to change cells that are “Bottom” aligned to become “Top” aligned, as that is generally easier to read. To do this: select the cells, rows or columns to be adjusted then choose the “Top Align” button from the Home menu. “Right” aligned cells can be adjusted to “Left” alignment through a similar process.

Freeze Panes
Selecting certain columns and rows to always be visible greatly increases the readability of longer lists. To freeze panes:

  • Left click on the cell to the right of, and below the rows and columns you wish to always be visible.
  • From the View menu, click on the “Freeze Panes” button and select the “Freeze Panes” option.

Data Filter
Data filtering is a powerful formatting option. It unlocks the ability to easily sort, filter and search within columns. To turn on filtering:

  • Select all of the data including any column names.
  • From the Data menu, select the “Filter” button.
  • Once data filtering has been enabled, items can be sorted, searched and filtered on by choosing the filter button from the columns.

Format as Table
Alternatively, data can be filtered by selecting a “Format as Table” style. “Table” styles are a quick way to make the data visually pleasing and they automatically include the data filtering feature. To turn a list into a “Table” style:

  • Select all of data including any column names.
  • From the Home menu, click on the “Format as Table” button and select your desired style (I like the “Medium” styles personally).
  • From the “Create Table” dialog box select the “My Table has headers” option then click the “OK” button.

Note: Be aware that these format changes are modifications to the original Excel file. If preserving an original copy of the file is important make sure to choose the “Save As” option when saving changes.

Microsoft Excel Tips & Tricks for CJA Cases: Filename Lists

By Alex Roberts

This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.

Today’s Post: Filename Lists

When working with discovery, investigative documents, or other case-related materials, it is often helpful to have a list of filenames in an Excel table.

There are times when the government produces to defense counsel digital files where the name of the file indicates something about the file content without a user having to open each file individually.

For example, the government may produce a list of investigative reports in PDF format which, as part of the file name, has the date of the report, the type of report (e.g. FBI 302) and the author. In those instances, it can be beneficial to create a spreadsheet of the filenames and information about the files for later review and organization. Even in instances where the filename is only the Bates number of the file, it can be useful to have a spreadsheet of those numbers.

Microsoft Excel is a useful tool for generating such a filename list. When properly setup, Excel allows users to sort, filter and search for specific files based on different criteria. Fields can be created and associated such as comments, document type, review status, dates and related issues. Additionally, hyperlinks to a specific file or folder can be created for quick and easy access to an item. We will examine these functions in greater detail in future videos.

This video will demonstrate how lists can quickly be created and recommendations to follow when setting up a file list. The video looks at three methods for creating filename lists:

  • Method 1: Creating a query table by running the “Get Folder Data” process that is currently available in the newer “Office 365” version of Excel.
  • Method 2: Using the “Copy Path” process available in Windows File Explorer.
  • Method 3: Using a “File List Program” specifically designed for creating a list of files in Excel format (ex: Directory List and Print).