 Getting Rid of Spaces in Cells
|
Summary: Many times when you import data into Excel from other programs, some of the cells will contain extra spaces. The cells appear to be empty, but in reality spaces are there and will give you incorrect answers when you do things like "counts". Here is how to get rid of these annoying extra spaces:
The easiest solution is to use the Find and Replace dialog box in Excel.
- Click on the Editing Group on the Home tab of the ribbon.
- Click on Find and Select.
- Click on Replace.
- In the Find What box, enter two spaces.
- Leave the Replace With box empty.
- Click on the Options Button.
- Select the Match Entire Contents Check Box.
- Click on Replace All.
- Repeat Steps 1 through 5, but this time only use one space in Step 2.
- Close the Find and Replace Dialog box.
The spaces have now been removed from the selected cells.
|
Deleting Duplicate Columns
|
Summary: With large worksheets you sometimes have two columns with exactly the same data in them. Before you delete one of the columns you want to make sure the data is exactly the same. Here is how to do this:
The first step is to check if the two columns are identical. Let's assume you have 1,000 rows of data in column A and column B and you think the data is identical. We will use an array formula to confirm that the data is identical as follows:
{=AND(A1:A1000=B1:B1000)}
Instead of hitting the enter key after we have entered the formula we enter Shift+Ctrl+Enter. When you do this note the { symbol at the beginning of the formula and the } symbol at the end. This turns it into an array formula. This array formula then compares all the values in the first 1,000 rows of Columns A and B. If they are the same the formula will return TRUE. If any of the cells are different, then the formula returns FALSE. If the answer is TRUE you can then safely delete one of the columns.
| |
Back to top
Summary: Calculating the Median of a set of numbers is easy with Excel's =MEDIAN function. Here is how to do this:
Let's assume you have a column with 1,000 rows of data and you need the median of the set. Here is the formula to do the calculation:
=MEDIAN(A1:A1000)
It's that easy.
|
Summary: Normally, we select a character, a word, a sentence or a paragraph. These are all horizontal selections. Sometimes you may need to select vertically. Here is how to do this:
1 Text 1
2 Text 2
3 Text 3
4 Text 4
5 Text 5
To select text vertically, hold down the ALT key and click and drag vertically to make the selection. Remember to release the ALT key before you release the mouse.
|
