Archive for the ‘Excel Tips’ Category

There may be times when you find yourself needing to separate a first and last name that are together into two separate columns.  Here is a quick and easy way to do this:

Note: Only do this if there is only a first and last name no middle initial, prefix, or Jr. Sr in the cells

Make sure there is a blank column beside the name

Highlight the cell or cells that you want to separate

Go to data on the toolbar>select text to columns

A box appears

Select fixed width>next

Click next on the next window

At the next window select finish

There you have it, instead of first and last name in one cell, they are now in two different cells.

Next week we will discuss separating names that have middle initials or middle names

Bookmarks

Tweet This Post

You have a spreadsheet and the first and last name are in their own cells but you would prefer them to be in a cell together but you don’t want to retype them all again.  Here is a simple solution:

Assuming you are using Column A and Column B:

Insert an empty column after column B (Highlight the next column>Click Insert>Column

Highlight the first cell in the empty column

At the top of the screen there is an area that is a formula bar

In the formula bar type the following: =A1&” “&B1

Click Enter

There you have it, the names are now combined.

To do this to the entire spreadsheet:

Highlight the cell of the combined names

Hold Shift>down arrow key

Highlight as far as you would like to go

Click>Edit>Fill>Down

There you have it, the whole spreadsheet now has the names combined.

Bookmarks

Tweet This Post

You have an excel spreadsheet that contains 500 names, addresses, and phone numbers.  The phone numbers don’t have the dashes in them so they look cluttered and you want them to have the dashes but you don’t want to manually edit each phone number.  What do you do?

If you don’t already have an empty column next to the phone number column, add one.

In the column you just added, put the number 1 in the first cell.

Highlight that cell and then click shift arrow button down to the number of cells you want.

Now go to Edit>Fill>Down

The number 1 is entered into every cell you highlighted.

Now go to the column that has the phone numbers in it.

Highlight the numbers you want to add dashes to.

Right click your mouse and select copy.

Click the first cell in the column with the number 1.

Right click your mouse and select paste special

A little box pops up with paste and operation headlines.  Under operation, select multiply and click okay.

Wait! They still look the same!

That’s okay continue on:

Highlight the column that you just pasted the phone numbers in.

Right click your mouse button.

Select Format Cells

A box pops up, make sure you are looking at the numbers tab, if you aren’t just click that tab in the box.

Go to special>phone number click okay.

Now all your phone numbers should have parentheses around the area code and a dash between the prefix and the ending.

Once you verify that the phone numbers are correctly formatted, delete the column of the old phone numbers.

There you have a quick 5 minute shortcut to getting phone numbers to look as they should.

Tracy

Bookmarks

Tweet This Post

Okay, so it’s been a couple of weeks since I posted how to find duplicates using conditional formatting in Microsoft excel. Sorry, for the delay. I decided to transfer my blog to my own hosting so that was the reason for the delay. Now I’m back with how to remove conditional format.

Highlight the column you used to find duplicates
Click Format>Conditional Formatting
A small box appears with the formatting information
Find the button that says delete in the box.
Click delete
A box will come up asking which condition to delete>select Condition 1

There you have it, you have just successfully created a conditional format and deleted it.

Bookmarks

Tweet This Post

Welcome to the newest category of my blog, business tips.  This category is where I will post a weekly tip about anything from general business tips to tips on different software that is used.

For the first tip, I would like to show you how to search for duplicates in excel when you are unable to sort your document.

Open your document:

Highlight the column that you want to use to test for duplicates

Click on Formats

Click on Conditional Format

Where it says “Cell value is” Change to “Formula is”

(Assuming you are using column A) In the box to the right, type in the following formula:

=COUNTIF(A:A, A2)>1

Click on the button that says Format

Another box opens that says Format cells.

Click on the patterns tab

Select the color you wish for the duplicates to appear in, click ok

Then on the conditional formatting box, click Ok.

Scroll down and you will see all instances of duplication are highlighted in the color you chose.

This tip comes in handy when you have a large spreadsheet that you know has duplicates but you do not want to spend the time going through each one manually.

Next week: Removing the conditional formatting

Bookmarks

Tweet This Post