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:
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.
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.
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.
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 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.