Redact Native Excel Files with the Filter Tool

Sometimes you need to produce only certain rows of a large Excel spreadsheet.  Some lawyers will painstakingly apply redaction stamps to hundreds of pages of tiff images, leaving only the responsive rows visible. This is a terrible waste of time and money, and typically generates an unusable mess like this, which sometimes extends to hundreds or even thousands of pages:

FilterRedact-badredactX18

This type of production fairly cries out for a motion to compel.  The next time you need to heavily redact a spreadsheet, use Excel’s built-in Filter tool to quickly create an elegant display of only the responsive rows of data.

This tutorial uses a fictitious data set (which you may download here) that has a tab for each month of 2015.  Each tab contains a list of thousands of incoming wire payments from various customers, in straight chronological order. The goal of this exercise  is to produce only the records that relate to Fujitsu subsidiaries. The January tab looks like this:

FilterRedact-alldata

In this split screen view, you can see that there are 2,246 rows of data on the January tab.  This will print or tiff to about 53 pages for this tab alone, but there are only 8 rows of data that need to be produced. Ugh!

FilterRedact-alldatasplitscreen

Rather than trying to redact every row that doesn’t relate to Fujitsu, we’ll use the following simple routine to selectively display only the responsive rows and create a highly readable (not to mention defensible) production of the responsive data.

Step 1: Turn on the Filter tool.

FilterRedact-filterbutton

NB: The filter tool works properly when data is arranged as a table, i.e., there is a header row, there is one record in each row, and there are no blank rows or columns.

With the filter tool activated, you can filter the data by any of the fields by clicking the drop-down on the desired column header and choosing the filter criteria:

Here, you can filter for the Fujitsu records by scrolling through the choices on the Customer field and clicking all the Fujitsu entities (there are three in this example), or by typing “Fujitsu” in the filter tool’s built-in search bar:

FilterRedact-clickfujitsuX3  OR  FilterRedact-typefujitsu

Either way, hit OK and you should get this result:

FilterRedact-fujitsu

Pretty good trick, huh? Notice how the row numbers now show up in blue to remind you that only certain values are displayed.  Notice, too, that a funnel icon appears where the drop-down icon used to be on Column C to let you know that Column C is where the Filter is applied.

At this point the printout or tiff image would look like this:

FilterRedact-printpreview1

This is a vast improvement over 53 heavily redacted pages, but we’re going to do a few more things to make our production bullet-proof.

Step 2: Launch the Page Setup dialog by clicking Print Titles on the Page Layout ribbon:

FilterRedact-printtitlesbutton

Perform the following formatting steps:

On the Sheet tab

  1. Click into the “Rows to Repeat at top” field and click on the Row Header for Row 1 to generate the syntax below.  Now if your printout is more than one page long you will see the Header repeated on every page of the printout.
  2. Select the option to Print Gridlines.  This will improve the readability of the printout.
  3. Select the option to Print Row and column headings.  This will make it possible to identify exactly which cells of the spreadsheet are being printed, and will make it easier to refer to specific information later, such as in a deposition or in a motion.

FilterRedact-pagesetup-sheet

On the Page Tab:

  1. Select either Portrait or Landscape orientation depending on the data in the current worksheet.  In this example we’ll use the default Portrait mode because there are only a few columns.
  2. Select ‘Fit to 1 page wide and [blank] pages tall.’ Excel will figure out how many pages tall the printout needs to be.  NB: If you absolutely must make the printout 2 pages wide because there are too many columns (and you’re already in Landscape mode), then use the “Columns to repeat” option on the Sheet tab.

FilterRedact-pagesetup-page

On the Header/Footer Tab:

  1. Click on Custom Footer.

FilterRedact-pagesetup-Header

This will launch the Custom Footer dialog box, where you will create the following:

FilterRedact-customfooterIn the Left section area, type “Filename: ” and click the filename button to insert the Excel code for the filename.  Do the same for the Tab name on the next line.  Then type a line explaining what Filter setting was used–the goal here is transparency.

Before you hit OK, select all the text and hit the Format Text button (the A on the left):

FilterRedact-formatfooter

In the formatting dialog box, set the font to bold and increase the font size . . .

FilterRedact-footerboldandbig    to get this:   FilterRedact-footerbignow

These formatting tricks make the data print like so:

FilterRedact-finaltop

And generate the following footer:

FilterRedact-newfooter

And what would have been 53 ugly pages reduces to a single, highly responsive page that looks like this:

FilterRedact-final

While this may seem like a lot of steps, it will only take a couple minutes per tab once you get the hang of it–and certainly much less time than redacting the tiff images individually.

A few more points:

  • Perform these formatting changes for each tab of the Excel file that needs to be filtered
  • Do not produce the native file for this Excel document–if you do your adversary can turn off the filter and see all of the original information.  Instead, print this to pdf and have your vendor tiff the pdf file.
  • Make sure your vendor understands what you are doing, and that they apply the correct confidentiality designation and Bates numbers to the production images.
  • Explain what you have done in your production cover letter.

Producing information this way has many virtues.  The first is clarity: Your adversary, and more importantly, the judge, can immediately understand exactly what has been produced here–namely, the information pertaining to Fujitsu entities on the January tab of the file called ‘Incoming Wire Payment Register 2015.’   Because the row numbers are also displayed (in blue above) it is also evident that about 2,000 non-responsive rows were filtered out–saving piles of your client’s money and countless trees.  Finally, you send a powerful message when you produce the responsive information this way, rather than buried among hundreds of pages of redactions:  You are not afraid to produce responsive information in a legible format, because you have a strong case and nothing to hide.

In a future post I’ll explain an efficient method for redacting the native Excel file when you need to leave the customer names exposed, but redact all of the payment amounts and other data.

Enjoy this post?  Please comment, subscribe, and share!  To learn about hosting a CLE-approved Excel workshop at your law firm, company, public interest organization, or bar association, click here or email Ben Kusmin from Excel Esquire. Thank you, and be well.

 

 

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in CLE, eDiscovery, Lit Support, Native File Review, Practice Management and tagged , , , , , , , , , , . Bookmark the permalink.

One Response to Redact Native Excel Files with the Filter Tool

  1. craigball says:

    Excellent post, most especially the disclosure advice at the conclusion. Would that every lawyer shared your perspective.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s