Hide Your Name When Printing Outlook Emails

When you print an Outlook email–whether your own or someone else’s–your own name appears by default in large text at the top of the printout.

Header-No

This is awkward if you are printing emails as a delegate, or are using them as exhibits to a motion.  How do you get rid of it?  For some reason, Outlook won’t let you remove the header entirely, but there is an easy way to make it virtually invisible.  Here’s how:

1. Under the Print menu, click on the small box promising “Print Options”:

2-printoptions

2. Then select Page Setup

3-pagesetup

3. Under the Format tab you can see that Outlook prints your name in the header with a larger font by default.  We can change that by selecting Font:

4-font

4.  As with other Microsoft applications, the choices of font size appear limited, but you can always type in the exact size you want.  Here type in 1 to make it really small.

5-typeoneasfontsize

5.  Click OK, confirm the other print settings, and print normally.

6-gonenow

Voila!  Your name is so small it may as well not even be there.  This change should stick, so you won’t ever have to think about changing the settings again–until your colleagues ask you how you did that!

You could also have a little fun by choosing a symbolic font to obscure your name, while keeping the font size legible.  I like Wingdings.

8-wingdings

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.

Posted in CLE, Everybody, Lit Support, Practice Management | Tagged , , , , , , , | 4 Comments

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.

 

 

Posted in CLE, eDiscovery, Lit Support, Native File Review, Practice Management | Tagged , , , , , , , , , , | Leave a comment

Get Excel to Automatically Launch Documents from an Exhibit List, Privilege Log, or Hot Documents List

Today I’ll explain how to create a schedule of documents or other files in Excel that includes a hyperlink for each document.  Anyone reviewing this list will be able to open any document described on it by just clicking the link.  This makes it easy to review a list of deposition exhibits or hot documents, or the internal version of a privilege log, and immediately review any document from the list that looks interesting.

Step 1:  Generate a list of the filenames that will be linked in the schedule.  They should all be in the same folder.  You can learn how to do this here.

NB:  If the filenames follow a predictable naming convention such as JX1, JX2, etc. the names can be generated with a formula such as =“JX”&A2 where column A contains the number of each exhibit.

Step 2:  Paste or create the filenames in the column of your choice.  In my example they are in Column A, and I’ve left cell A1 free:

hyperlink-filelist

Step 3:  Navigate to the folder that contains the files in Outlook Explorer.  In this example I want to link to the photographs saved in a subfolder called “late 2014” within the My Pictures directory:

hyperlink-folder

Step 4:  Click in the address bar to make the full path come up.  Copy the path by hitting Ctrl+C

hyperlink-fullpath

Step 5:  Paste the path in a convenient cell in your schedule file.  In this example I’ve pasted it in cell A1.

hyperlink-pastepath2

Step 6:  Use the HYPERLINK formula to generate a hyperlink to each document.  The function has two arguments: 1) the “link location,” i.e., the path to the file, including the filename; and 2) the “friendly name,” i.e., the text of the clickable message that you want to appear.  Excel prompts you for these two arguments when you type out the name of the function and the open parenthesis:

hyperlink-arguments

We’ve set the schedule up in such a way that the link location will follow a predictable pattern—we can simply “splice” the path in cell A1 and the file name in the adjacent cell of Column A to get the complete path to the relevant document.  The first formula—to launch the file in cell A3— looks like this:   =HYPERLINK($A$1&A3,”Open File”)

And this is how it appears onscreen:

hyperlink1

See how the friendly name is what appears in the cell, while the underlying function you created is visible in the formula bar.  Note the use of dollar signs for the A1 reference (insertable by hitting F4), and the quotation marks around the friendly name text. 

Step 7: Copy the formula down to the rest of the filenames.  You can accomplish this by double-clicking the cross-hairs in the lower right-hand corner of cell B3.  Excel will generate the appropriate formula for each filename, concatenating the path in cell A1 and the correct filename.  Observe how the formula in cell B4 still refers to the path name in A1 (thanks to the dollar signs) but uses the filename in the same row, viz., A4:

hyperlink2

Observe how clicking on the link next to the file IMG_1317.JPG launches the picture in a new window:

hyperlink-launchporcupine

That’s all there is to it.  The key to making this work is that you have to have access to the folder containing the documents in the same environment in which the Excel file is open.  For example, if your documents reside in a local shared drive, someone in another office will not be able to open the documents from this Excel file because the links will not work.

If you want to make this file portable, make sure you use the correct path for the media where the files will reside.  For example, you could store all your deposition exhibits on a thumb drive, and put this file on the thumb drive as well.  Just make sure that the path you are using to launch the documents is the path that points to the thumb drive, not your computer back at the office.

Thanks for reading.  If you have an idea about how to use this tip in your law practice, please share it in the comments below.  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.

Posted in CLE, eDiscovery, Everybody, formulas, Lit Support, Privilege Logs | Tagged , , , , , , , | 4 Comments

Generate Properly-formatted Bates Numbers in a Snap

Let’s say you get the following panicked email from an attorney on your team:

Bates-email2

You understand that these are the Bates numbers for documents on the Big Bank case, but you need to convert them to the proper format in order to pull them out of Relativity all at once.  Viz.,

62 refers to BigBank_000062

7550 refers to BigBank_007550

and so on.  The tricky part of this is adding the “BigBank_” prefix while also getting the right number of padding zeroes into each number.  You certainly don’t want to type them out one at a time–right?  Excel’s TEXT function makes this easy.

First, cut and paste those numbers into Excel and put the following formula in cell B2:

Bates-formula

which generates the following result:

Bates-firstresult

Here’s how the formula works:  “BigBank_” is the prefix for each Bates number, and the TEXT formula with the “000000” argument tells Excel to convert the number in Column A to text and add the appropriate number of padding zeroes to get a total of 6 digits.  The ampersand (&) combines both pieces into a single string of text.

Next, copy the formula by finding the cross-hairs in the lower right-hand corner of cell B2 and double-clicking, or dragging them down:

Bates-copyformula

The copied formulas turn all of the raw numbers into properly-formatted Bates numbers:

Bates-allresults

That’s it!  As a housekeeping matter, it’s a good idea to get rid of the underlying formulas by copying the cells and pasting the values, like so:

First select the cells with the formulas/results, and then right click and choose Copy (or hit Ctrl+C)

Bates-copy

Then select the same range of cells and paste the values right on top of them:

Bates-psv

The cells will appear the same, but now the content of each cell is the resulting Bates number, rather than that funny formula–as reflected in the formula bar.

Bates-nomoreformula

Now these Bates numbers can be pasted into Relativity or other document review platform.  Of course, this trick will work if you have 8 numbers or 800.

Thanks for reading.  If you have a thorny Excel issue, please share it in the comments below.  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.

Posted in CLE, eDiscovery, formulas, Lit Support | Tagged , , , | 3 Comments

Analyze Matter Data with Pivot Tables

In this post I’ll explain how to use one of the most powerful data analysis tools in Excel – the Pivot Table.  My example involves a list of new pro bono matters, but pivot tables are also an effective way to analyze email metadata or any other large data set.

Suppose your firm signed up 140 new pro bono clients across nine different programs in the last quarter of 2014. Each matter is represented in a row of the spreadsheet shown below. For each new matter, there are fields for the program, responsible attorney, date opened, and several other data points.

PT1

Now suppose you want to know how many new matters were generated in each program, or which program had the highest average cost in this quarter, or see a breakdown of new matters by borough? By month opened? By advocate? You can answer all these questions with a Pivot Table.  Keep reading to learn how.

You can download the Excel file by clicking here.

First, for Pivot Tables to work properly, you should make sure your data is the form of a table. That means no blank rows or blank columns in the data you want to sort.

To insert a pivot table, start by clicking anywhere in your table. Then navigate to the Insert ribbon and select PivotTable on the far left:

PT2

By default, Excel selects all the contiguous data for inclusion in the Pivot Table, and places the table on a new worksheet. You will usually want to accept these default settings:

PT3

Excel takes you to the new worksheet where the Pivot Table has been created. All you see at first is this intimidating diagram:

PT4

The first thing we’ll do is tally the number of clients in each program. Drag the “Program” field from the list into the Row Labels field and into the Values field.

PT5

Like magic, Excel tallies the number of clients in each program, as well as the grand total:

PT6

Now you can get a breakdown of the programs by any given category. Let’s see how many of the clients from each program are from any given borough. The trick is to visualize how you want the data to look and anticipate what the Row and Column headers will be.

Since we want a separate column for each Borough, we drag the Borough field into the Column Labels area.

PT7

Voilà:

PT8

NB: You can change the appearance of the headers by choosing Design –> Report Layout –> Show in Tabular Form

You can even drill down on a certain data point to see which records it includes.

At the intersection of Queens and Bankruptcy Clinic there is a 5, indicating that there are 5 clients in the Bankruptcy Clinic from Queens. Double-click on the number 5 and something neat happens:

PT9

Excel copies those 5 records and pastes them into a new worksheet! It’s a good idea to rename the worksheet to remind yourself what this data is. Double-click on the tab name, type the new name, and hit Enter.

Group by Date

Now let’s generate a breakdown of all the new matters by month.

The first step is to pull the dates into the Column area and see a breakdown by date.

PT10

This is not very useful yet, because it creates a breakdown for every single day. To collapse the breakdown to months, click on one of the dates and then select Group Field from the Options ribbon:

PT11

Choose Months and Years and hit OK

PT12

Now the Pivot Table will break down the count by Month:

PT13

Tally the expense subtotals by program

Remove the Date field from the Column area and drag the Expense field to the Values area:

After formatting these values as USD, you will see the subtotal of expenses for each program, along with the number of clients.

PT14

NB: You can format all the sum and average cells as US Currency by selecting them and then hitting Ctrl+Shift+$

Calculate the Average Cost per Client in Each Program

Now let’s look at the average cost per client for each program. The first, counterintuitive, step is to drag the Expenses field into the Values field again.

PT15

Now you’ll have two columns in the pivot table representing the sum of the expenses for each program:

PT16

Double-click on the Header, the cell that reads Sum of Expenses2, and you will see the option to change the value to an average:

PT17

Now you’ve got the Total in one column and the Average in the other:

PT18

You can also sort these Pivot Table results. If you wanted to view the above table by most expensive to least expensive program, you would click in the Sum of Expenses column and click the Z–>A button on the Options ribbon (you should be there by default):

PT19

With this result:

PT20

To make the Pivot Table more presentable, you can get rid of the Row Labels title by hitting the Field Headers button, and edit the other column headers by adding your own titles:

PT21

One last thing to remember about Pivot Tables: they do not update automatically if you add, delete, or edit the underlying data. If you want the Pivot Table to update, Go to the Options tab and hit the Refresh button.

PT22

These techniques are covered in-depth in my new CLE course† Excel Essentials for Busy Lawyers. To learn more about hosting a session of Excel Essentials at your law firm, company, or public interest organization, please email Ben Kusmin at Excel Esquire.

†The required fine print: Approved in New York for 2.0 CLE credits; approved for both new and experienced attorneys; financial aid available.
Posted in Uncategorized | 1 Comment