Manage Your Excel Spreadsheet with a Table

If you’re working with a list of data, you can turn it into a Table.  Excel gives you some features that make manipulating this data much easier. Your column headings will always be visible, and your rows are now formatted with alternating, colored ledger lines (like a checkbook register), which makes your data easier to see. Each column now has the AutoFilter dropdown menu. You can use AutoFilter to filter or sort columns of your data. It will sort everything all together at once, so you don’t run into the common problem where you sort one column, and then everything is no longer corresponding to the cells around it.

 

To convert your data into a Table, select your data. On the Home tab, under the Styles group, select “Format as Table” and choose the table formatting that looks best to you.

 

1

Click to enlarge

 

Now that you’ve converted your list into a Table, you’ll see that the Ribbon has now given you a new tab called Table Tools Design. You can check “Total Row” in the Table Styles Options group. When checked, a row will appear at the bottom of your data that calculates different totals you can toggle for sum, average, and count.

To take advantage of the AutoFilter feature, scroll up to the top with the column labels. Click the dropdown arrow of the column you want to sort. In my this case, I will sort the Date Opened column.

2

Click to enlarge

As you can see in the dropdown menu, I can sort Oldest to Newest or the opposite, but what’s cool is that I can quickly check and uncheck the months. If I only want to see what happened in November, I just uncheck October and December and now my table is only showing November.

3

Click to enlarge

If I want to see October and December data again, I just go back to the dropdown menu and recheck the boxes. Nothing’s been lost!

 

You can go back to a regular Excel range by clicking “Convert to Range” in the Tools group of the Design Table Tools ribbon.

 

For my example, I am using a spreadsheet of 140 pro bono clients that are in nine clinics. You can get a copy of this spread sheet to fiddle around with yourself here: https://excelesquire.wordpress.com/2015/07/19/analyze-matter-data-with-pivot-tables/

Quickly Change Letter Casing in Microsoft Word

It was there all along, but you may not have noticed it: the capitalization menu in Microsoft Word.

1

Click to enlarge

 

Quickly change the casing in any word document by highlighting the selected text, mousing over to the Change Case menu and click the dropdown menu.

2

Click to enlarge

You can select sentence case, lower case, upper case, capitalize each word, or try the odd toggle case. Simple and no more retyping.

Use Ctrl+F to Search Data with Ease

If you’re confused about what CTRL+F is, you are not alone – according to Dan Russell, a search anthropologist at Google, 90% of people do not know how to properly use CTRL+F to locate a word in a document or on a webpage. This tool will save you time and the embarrassment of missing a keyword or phrase that should’ve been replaced. Whether you are using your browser or your word processor, CTRL+F will speed your work up immensely.

Ctrl+ F works wherever you are: web browsers (Google Chrome, Firefox, Safari, etc.) word processing programs (Microsoft Word, WordPerfect, etc.), or  PDFs viewers like Acrobat Reader. Pressing the keyboard keys “CTRL” (“Command” on a Mac) and “F” at the same time will help you find any specific phrase, clause, or word you are looking for by typing them into a box that appears on your screen. The “find” box may appear in different locations on the screen depending on which program you are using – but it will always appear.

In A Web Browser

When searching through an online document or a webpage for specific mention of a word, phrase, or number, CTRL+F can save a lot of time. Simply press the keys CTRL and F and enter the desired term, phrase, number, word, or clause into the Find box that appears. In Internet Explorer your search term will be highlighted and each time you select “Find Next” the search will move through the document highlighting the usage of the designated word, number, or phrase.

1

Click to enlarge.

For instance, say you need to find the contact information for a specific judge in the Criminal Division of the Circuit Court of Cook County, but you only know that his first name is John. Go to the court’s website listing of judges in this division, and then you can use CTRL+F to accelerate your search. Enter “John” in the search bar and press <Enter>. The search will bring you directly to any mention of “John” on the page. Pressing <Enter> again will bring you to the next entry of “John” on the page if there is one.

In Microsoft Word

When working in Microsoft Word, CTRL+F opens the Navigation pane and you can search for a word, phrase or number. You will see all of the instances of your search term appear in the Navigation pane with the word in bold and can quickly jump to that portion of the document. Take it one step further and use the Navigation pane to find a word or phrase – and then replace it! From the Navigation pane click on the “carat” (arrow) to open a toolbar with more options, including Replace.

2

For example, the client’s total damages have increased and a complaint must be updated before filing suit. Rather than locating each time the initial damages are mentioned within the complaint by scrolling through the document, you can quickly make the corrections by using CTRL+F. Just key CTRL+F,  type in the original dollar amount, click on the carat to show Options and click on Replace. The original amount will appear in “Find What”. You can then add the updated amount in the “Replace With” field. If you want to replace any incident of the number, phrase, or word in the “Find What” field, select “Replace All.”

3

There may be cases where you do not want to “Replace All” but only “Replace” some. For example, a brief cites Wickard v. Filburn in several locations, however sometimes the citation is not as appropriate as that of say, Horne v. Dept. of Agriculture.

“Replace All” would not work in this example because the initial phrase of “Wickard v. Filburn” would still apply in many of its uses. Instead, select “Replace” and let the find feature bring you through the entire document. When you find items you would like to replace, select “Replace” again; when you find items you want to leave as is, select “Find Next” and move on.

4

Conclusion

Once you get comfortable using CTRL+F in your searches and writing, you’ll wonder what took you so long to learn this important skill.

Never Miss a Message with Outlook Search Folders

One of the biggest problems with email is that not everything that comes to your inbox is of equal importance. Email from certain clients needs to be addressed right away; messages from co-counsel need to be referred to repeatedly, yet all of this correspondence gets buried under a barrage of e-newsletters and less important correspondence. Outlook “Search Folders” solves this problem. With Outlook “Search Folders,” specified emails are always available no matter where the emails are located (folders, inbox, sent mail). Microsoft by default creates generic search folders such as “Unread mail” and “mail flagged for follow up”. Users can customized more search folders by following instructions to create “Mail from and to specific people,” or “Mail from specific people”. However, the most powerful and useful search folders are likely to be custom search folders. Here’s how to create them:

 

In Mail, click “Search Folders.”

1

Click New Search Folder, and a new window will appear. From the “Select a Search Folder” list, you can select several default options, like “Unread mail,” “Mail from and to specific people,” “Mail from specific people” and “Mail sent directly to me.” To create a Custom Search Folder scroll towards the bottom and select ” Create a custom search folder” and then select the “Choose” button.

2

 

In the window that appears, type a name for your custom Search Folder, and click Criteria, and then select the options that you want.

3

 

The Messages tab contains criteria on the message content or properties, such as sender, keywords, or recipients.

4

 

The More Choices tab contains criteria on other message conditions, such as importance, flags, attachments, or categorization.

5

 

Enter in your specific criteria, and then press “OK.” You’ll see the new Search Folder listed below “Search Folders,” but you can drag it under “Inbox” or add it to “Favorites” by right clicking and choosing “Show in Favorites” in the menu so that the search folder is always at the top of your screen.

How many days do I have again?

It’s hard to calculate how many days are between a certain day and another, especially if they are far apart. If your deadline to file a motion is 60 days before your hearing date on October 12th, 2015, what date is that? Luckily, there are several ways to determine that calculation. We’re going to cover Excel, Outlook, and a web tool called Timeanddate.com.

If you frequently need to keep track of court calendar deadlines, services like Deadlines.com (formerly Deadlines on Demand) or Juralaw (if you practice in Illinois) can keep the dates straight for a price. There are several ways to count how many days for free, however, and best of all you can use these tricks for all your deadlines—not just court specific ones.

 

The first is with Excel. In cell A1 type the date of your hearing: 10/12/2015. In cell B1 type “=A1-60” and hit enter. Excel will calculate the date automatically, changing the cell contents to say “8/13/2015.” If the date of your hearing changes, replace the date in cell A1 and the date of your filing deadline will automatically change with it.

excel screenshot

 

To find out if that date falls on a weekend, click cell B1, go to the cell formatting drop down menu under the Home tab and select “Long Date.”

long date excel spreadsheet

 

The cell data will change to tell you what day of the week 8/13/2015 is. In this example it is Thursday, but if it were on a weekend you could adjust your deadline as needed.

If you don’t have Excel handy, you can also add or subtract a date on the web. Timeanddate.com has several different calendaring calculators. Use their date calculator to add or subtract from a date. Still using the example of 60 days until your hearing date on 10/12/2015, enter 10/12/2015 as your start date. Select “(-) Subtract” from the dropdown menu, and enter “60” under “Days.”

subtractdateweb

Press “Calculate new date.” The result will be “Thursday, August 13, 2015.” Timeanddate.com also features calculators to determine the number of days between two dates, time duration between exact times and finding the week number of any date.

Finally, there’s Outlook.

Enter the October 12, 2015 hearing for your client on your calendar. After the appointment is on your schedule, bring up the Go To Date box by right clicking the date (or typing Ctrl – G if you’re a keyboard shortcuts person).

hearing for my client

In the box that appears, place your cursor before the date and type “60 days before”

60 days before

Press OK, and you will be brought to August 13, 2015, where you can now enter a new event for your motion filing deadline. You can also find out dates in the future by typing “x days after.”

Get on Track (With Track Changes)

Microsoft Word provides a handy feature for document collaboration called “Track Changes”. Used by professionals the world over, this functionality has many benefits and a few pitfalls. For instance, when receiving a document that has Track Changes enabled it is often difficult to read because of all the markup. Did you know that you can make it much easier to focus on important text changes by going to “Show Markup” and uncheck “Formatting”?

Turn off formatting markup in Track Changes

Turn off formatting markup in Track Changes

Track Changes with no format tracking is much easier to read

Track Changes with no format tracking is much easier to read

When you send out a document with Track Changes enabled you can click on the arrow in the Track Changes button and select “Change Tracking Options” to toggle off “Track Formatting” so your collaborator can have a cleaner copy of the changes too!

Turn off formatting in Track Changes when sending a tracked document

Turn off formatting in Track Changes when sending a tracked document

What if you forgot to turn on “Track Changes” when you sent out a document and now you want to see if there are changes in the returned document? No problem! In the Review tab, Compare group click Compare and choose “Compare” to do a side by side blackline of your document versus the one you’ve just received. In the options you can toggle off all formatting differences so you will have a clean comparison of the differences in the text, without formatting adding an unnecessary layer of complexity.

Options for Document Comparison

Options for Document Comparison

Compare feature show blackline, plus original and updated document in three panes

Compare feature show blackline, plus original and updated document in three panes

Want to learn more about using Track Changes? Check out this useful article from PC World article “How to track changes in Microsoft Word without going insane”.

Know What You Are Sending (With A Little Help) in MS Word

Microsoft can warn you  before sending, saving or printing a document with comments or tracked changes

Microsoft can warn you before sending, saving or printing a document with comments or tracked changes

Eighteen jurisdictions have ethics opinions on metadata.  All of them suggest that a lawyer who is sending an electronic document should take reasonable precautions to prevent disclosure of confidential information. While technically not metadata, the comments and tracked changes in a Microsoft Word document do present a significant opportunity to unintentionally expose  confidential information.  There are many ways to remove and reduce exposure of metadata, but one very important aspect is that the sender is aware of what is being sent. In some cases you may fully intend to send a document with comments and tracked changes.

Whether intentional or not, a notification that a document you are saving, sending, or printing has tracked changes or comments could be useful.  Fortunately you can do exactly that with a setting in MS Word.  In Word 2010-13 go to File – Options – Trust Center – Trust Center Settings – Privacy Options and check the box that says “Warn before printing, saving, or sending a file that contains tracked changes or comments”.

Once this option is checked if you click to save or save as, print to a physical printer or to PDF,  save to PDF or use a PDF conversion tool like Acrobat or Nuance, or use any command under the “Save and Send” menu you will get a warning that the document has either comments and/or tracked changes and asks you to click yes to continue.  However, the warning system is fallible because if you attach the file from an email program, such as MS Outlook you will get no notice.

Disaster Planning: Turn Off Email Address Autocomplete

February LPMT Tech Tip

Headline after headline after headline reveal attorneys suffering disaster because of mis-sending email. While slowing down and paying more attention can help, turning off some of the convenience features built into email applications can’t hurt. In MS Outlook (2010 & 2013) go to File – Options – Mail – Send Messages and uncheck “Use Auto-Complete List to Suggest Names when Typing in the To, CC, and BCC Lines”.

autocomplete

 

 

 

 

Then click on “Empty Auto Complete List”.

autocomplete button

 

 

 

 

If that seems a bit too nuclear you can selectively remove old or easy to abuse AutoComplete email addresses that appear in email by clicking on the X next to the name that appears. This will clear it from your auto-complete list.removefromlist

 

 

 

 

 

If you use keyboard shortcuts like <Cntrl + Enter> to send an email you can turn it off. Why? Because this method  is so quick that it can be dangerous! You can turn off that shortcut by unchecking the option box, which appears in the same options menu as turning off AutoComplete. Now you won’t be able to create a disaster in the blink of an eye.

cntroenter

 

 

 

 

For Gmail you must delete individual contacts for them not to show up in AutoComplete, though you can go to Settings and choose to add contacts youself instead of the default “When I send a message to a new person, add them to Other Contacts so that I can auto-complete to them next time”.

gmailcontacts

 

There are other remedies for common mistakes like the “Reply All” monitor from Sperry for MS Outlook or Google’s “Undo” option in Labs (which can also be done in MS Outlook and is actually just putting a short delay on the “send” time). However, the main way to having embarrassing, costly or worse things happen from misuse of email is just to slow down on the send button.

 

Directory powered by Business Directory Plugin