Thursday, 14 November 2013

Excel tips

Here some quick and good excel tips which are very handy.
How to easily remove hyperlinks from multiple cells?
1. In any empty cell, type in the number 1 (it can be deleted later).
2. Right click the cell where you typed the number 1 and then click on Copy.
3. Select the cells with hyperlinks that you want to delete.
4. On the Home tab, in the Clipboard group, click the down arrow and then click on Paste Special.
5. Under Operation, click on Multiply, and then click OK.
6. On the Home tab, in the Styles group, click on Cell Styles. From the drop down menu, click on Normal.
7. Now the hyperlinks are deleted. You can now delete the number 1 used to perform this task.

How to Delete Empty Rows in Excel
1. Select the range containing blank rows you want to remove.
2. Click Home > Find & Select > Go To Special.
3. In the Go To Special dialog box, check the Blanks option.
4. And then click OK, all of the entire blank rows are highlighted. Then click Home > Delete > Delete Sheet Rows, the blank rows have been deleted from the selected range.

If you have a large Excel file - you may want to highlight a whole row, not just the cell.
Press the down arrow and then press Shift and Spacebar

Moving Cells with your Mouse
1. Moving cells with your mouse instead of key strokes can be convenient, here’s how.
2. Select the range of cells you want to move by highlighting the range with the mouse.
3. Position the mouse pointer over the heavy border that surrounds the selected range. The pointer should turn into an arrow (PC) or hand (mac).
4. Click and drag the range to a new location. As you move the mouse, the outline of the range moves.
5. When you are satisfied with the new location, release the mouse pointer. The cells are moved.

Selecting a Range of Cells
1. Selecting a long range of cells can be a pain. Here’s one trick that could help.
2. Move the cell pointer to the starting cell.
3. Press F8 to activate extend.
4. Either use your arrow keys or mouse to click on the ending row/column.
5. All cells will be highlighted.
6. Press Esc to end extend.

Keep Column Names Visible as You Scroll
1. Many Worksheets have Headers for each column. As you scroll thru a worksheet it’s helpful to be able to see those names. Here’s how.
2. Select the row immediately below the row you want to freeze.
3. In the Window menu, click on Freeze Panes. Excel will put a thick line under the row to freeze.
4. To unfreeze the row, go to Window menu again and click on Unfreeze Panes.

You Want an Unique List of Values
1. You have a column of data and would like only the unique values from it.
2. Select the column you want the uniqueness based on.
3. Choose Filter from the Data menu, and then choose Advanced Filter. A dialog box pops up.
4. I always like to choose the Copy to Another Location option.
5. In the Copy To Field, specify the cell where you want the list of unique, filtered values to be copied.
6. Make sure the Unique Records Only check box is selected and click Okay.

When Excel Chooses the Wrong Format for Your Data
1. You want 00125678, not 125678
2. The format of the cells that you are entering data in needs to be corrected.
3. Go to Format and click on Cells. Excel displays the Format Cells dialog box.
4. Click on the Number tab.
5. In the Category list, choose Text and click okay.

When Excel Chooses the Wrong Format for Your Data
1. The gene name is Oct4 not 4-Oct!
2. The previous fix for leading zeros will also work here.
3. There is one caveat, you must change the format of the cells before entering the data. Otherwise 4-Oct becomes 37167

1. You have 5 columns of data you want to sort and Excel only allows you to sort 3.
2. If you want to sort by columns A B C D E, select the whole spreadsheet, than sort by C D E, than A B. This will result in all five columns being sorted.

1. You have a column of Ids that are F1, F2,....F150 and would like to sort based on these ids. How?
2. The only way to make Excel sort the proper way is to change your ids to F001, F002, etc.
3. =LEFT(C1,1) & RIGHT("000" & RIGHT(C1,LEN(C1)-1),3)

Making Your Macro A Button
1. If you have a macro that you use frequently, going to the menus to run it can be a pain. Here’s how to create a button on your toolbar.
2. Choose tools, customize and click the commands tab.
3. In the categories list, click Macros
4. Drag the custom button or custom menu item to the desired spot on a toolbar
5. Right-click the button or menu item and choose Assign Macro, select the macro you want and then click OK and close.
6. When the button is clicked, Excel will run the macro.


No comments:

Post a Comment