I like to think of myself as an advanced computer user and Excel is one of the programs that I feel moderately comfortable using.
Some of the more advanced features of Excel can be confusing and, like anything, the best way to learn how to use it is to spend time interacting with the software. If you're not big on time, you might find the following Excel recipes to be worth following until you start to really "get" Excel:
To extract the last word in a string of text, place the following in cells to the right of the text. This is helpful for extracting last names from a column of names
=RIGHT(B75,LEN(B75)-FIND("*",SUBSTITUTE(B75," ","*",LEN(B75)-LEN(SUBSTITUTE(B75," ","")))))
First Name Isolation:
Or how to remove everything from a cell after the first space:
=LEFT(A1,Find(" ",A1)-1)
Remove Duplicates
Be aware that using this may destroy your data. Work from a copy, not the original data, when attempting this:
- Copy
your range of data to a blank section of the worksheet
- Select
a cell in your data set.
- From
the Data ribbon, choose Remove Duplicates.
- The
Remove Duplicates dialog will give you a list of columns. Choose the
columns which should be considered.
Deactivate several hyperlinks at once
To the uninformed, this one makes about as much sense as mixing non-dairy creamer with fire but the results are just as impressive:
- Type the number 1 in a blank
cell, and right-click the cell.
- Click Copy on the shortcut
menu.
- While pressing CTRL, select
each hyperlink you want to deactivate.
- Click Paste Special on the Edit
menu.
- Under Operation, click Multiply
and then click OK.
Highlight Every Other Row Quickly
Use the "Conditional Formatting" option
To use the Conditional Formatting option to shade rows in a worksheet, follow these steps:
- Open the worksheet.
- Select the cell range that you want to shade, or press Ctrl+A to select the whole worksheet.
- Click the Home tab.
- In the Styles group, click Conditional Formatting, and then click Manage Rules.
- Click New rule.
- Click Use a formula to determine which cells to format.
- In the Format values where this formula is true box, type =MOD(ROW(),2)=1, and then click Format.
- On the Fill tab, click the color that you want to use to shade every other row, and then click OK.
- Click OK to close the New Formatting Rule dialog box.
- Click Apply, and then click OK to close the Conditional Formatting Rules Manager dialog box.
Combine the Contents of Two Cells
Useful in creating one cell with a person's full name when you have their first name in one cell and their last name in another
=CONCATENATE(A2," ",B2)
The above formula will combine the contents of two cells, in this case those in cell A2 and B2 and add a space between the cells
Splitting Cells Where There are Commas
When you have "Last Name, First Name" in a single cell, you might try moving them into different cells by using the following method to split the data split into multiple cells:
Data - Text to Columns
Get Rid of Data Between Parentheses
To remove data between parentheses from cells:
Just use the "Find/Replace" tool found on the "Edit Menu"...
1. Ctrl / Cmd + F or Select Find / Replace in the Edit menu
2. In the "Find What" box type "(*)"
3. Leave the "Replace With" box empty
