****Please enjoy this quick cheat sheet posted on behalf of the author, Denise Ash, Event Education Manager, ILTA.
Concatenate
Have you ever been tasked with having to combine multiple fields? If so, hopefully you used a formula to do it! If not, you should definitely check out the Concatenate function. Here is how I always get to it because it’s how I found it initially.
1. Click the
Insert Function button (it is an
fx) to the left of the Formula Bar.
2. The
Insert Function box will appear. Type the word
Join into the top text box and click
Go.
3. Several options should appear; choose
Concatenate and the following dialog box will appear:
4. Click in the Text1 box and click on the first cell you want to use in the formula (in the video, I clicked on A1 which contained the first name). Continue this for as many fields as you need to concatenate, but do not forget to include spaces and/or commas if needed.
5. Ensure that the example in the bottom right-hand corner looks correct and click OK.
6. Use the AutoFill handle (bottom right-hand corner of the cell) to copy the formula to the other cells. (Double-clicking will do this easily!)
Text to Columns
Conversely, there are times when you need to take a column and separate the data (i.e., take a column with full names and separate them into first name, middle initial, and last name columns). That’s easy, too!
1. Highlight the column. Click Data, Text to Columns. The following dialog box will appear:
2. Choose Delimited and click Next. Select the delimiter(s); it was a space in the example in the video, but you can choose more than one. It will give you a preview of how the data will look. If that is correct, click Next.
3. The only change you might want to make on the next dialog box is if you want to keep the original data. In our example, the original data was in A2, so I changed it to B2 so that it would not override the Full Name column.
4. Click Finish.
Removing Duplicates
To take the previous skill one step further, you can automatically remove duplicates. You get to select which fields must be identical in order for a row to be determined a duplicate.
1. Highlight the range (typically multiple columns).
2. Click Data, Remove Duplicates. The following dialog box will appear:
3. Select the fields which should be identical in order for the entry to qualify as a duplicate.
4. Click
OK. It will give you a count of how many duplicates were deleted and how many unique values remain.
Creating Charts Quickly
1. Click anywhere inside the data you want to chart and press
Ctrl + A.
2. Press
F11 on the keyboard… (I remember this because 11 looks like a column chart).
Keyboard Shortcuts
There are many helpful keyboard shortcuts in Excel, but here are the ones highlighted in the video:
- Repeat Value from Above: Ctrl + Apostrophe (‘)
- Enter Current Date: Ctrl + Semicolon (;)
- Format Selection as Currency: Ctrl +Shift + 4
- Format Selection as Date: Ctrl + Shift + 3
|
Happy EXCEL-ling and please reach out to me if you have any comments or questions! denise@iltanet.org
|
|
#LitigationSupportoreDiscovery#ProfessionalDevelopment#Firm