Monday, August 20, 2012

Excel Cookbook

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:
  1. Open the worksheet.
  2. Select the cell range that you want to shade, or press Ctrl+A to select the whole worksheet.
  3. Click the Home tab.
  4. In the Styles group, click Conditional Formatting, and then click Manage Rules.
  5. Click New rule.
  6. Click Use a formula to determine which cells to format.
  7. In the Format values where this formula is true box, type =MOD(ROW(),2)=1, and then click Format.
  8. On the Fill tab, click the color that you want to use to shade every other row, and then click OK.
  9. Click OK to close the New Formatting Rule dialog box.
  10. 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

11 comments:

  1. There are some pretty cool things you can do with excel, when you know how to do it.

    ReplyDelete
  2. Thanks for this guide man. Excel kind of melts me unfortunately at the best of times so hopefully this is of some help for me.

    ReplyDelete
  3. This.. could be handy actually. Thank you.

    ReplyDelete
  4. Thanks for the lesson. But knowing me I would mess up anyway.
    www.thoughtsofpaps.com

    ReplyDelete
  5. Never heard of excel but il check it out. I'm following you for more. Check me out if u dont mind:D

    ReplyDelete
  6. Youtube always helps me out with stuff I want to learn

    ReplyDelete
  7. I am definitely bookmarking this, because I have so many things I've wanted to put in spreadsheets, but I've been too lazy/daunted by formulas to actually attempt learning it.

    ReplyDelete
  8. Even some of your basic stuff is over my head! But then again I only venture into excel several times annually! The best way to learn for sure is to spend time. Cheers

    ReplyDelete
  9. This is your friendly reminder that the fantasy draft is tonight. 7:30 pm EDT.

    I only use Excel when I'm at work...after that, anything else is over my head and useless!

    ReplyDelete
  10. Interesting. Does this word for Word as well?

    ReplyDelete