1--Select All with one click
There is a very easy way to select an entire worksheet. Click on the little gray box in the top left-hand corner of the sheet above the row numbers and to the left of the column letters. You do not have to be at the beginning of the worksheet for this to work...any time you click this box, you will select the entire sheet. You can also select your entire worksheet by clicking [Ctrl]A or by going to Edit|Select All on the menu.
2--One click data mining using AutoFilter
Go to Data|Filter|AutoFilter. When you click on AutoFilter, Excel will add drop down arrows to the first cell in each column of data in your work sheet. When you click on any of those arrows, Excel will show a list of all unique entries in that column. Select one of the unique entries to limit the display of records. When you do this, the drop down arrow will change from black to blue to remind you that your list has been filtered by a selection from that column. You can filter by multiple columns and Excel will display only those rows which match your selections from each column. To turn AutoFilter off, either click on blue arrow over each of the columns where you made the selection, and click (ALL), or go to Data|Filter|AutoFilter and click on AutoFilter again.
3--Generate a unique list of entries in a column that can be used for Copy/PasteClick on the column letter to select the
entire column (you need to include the column header) that contains your data. Paste the data in a new work sheet. Make sure that the data is still selected. Go to Data|Filter|Advanced Filter. By default Excel will suggest that you filter the data in place, which will work, but it is often preferable to put the new list into another column so that you can compare the lists side-by-side. Select the "Copy to another location" option, select the "Unique records only" checkbox, and type "C1" (or whatever column you want to paste the new list into) in the "Copy To" field. You now have a unique list of values for the column to use as you wish.
4--Transpose data from a row to a column (or vice-versa) without rekeyingAfter working with a table, you decide that you would rather have the current set of row labels running across the columns...don't rekey the data. You can easily make this change using Paste Special. Select the table data that you want to transpose. Go to Edit|Copy, then click on a new location in your work sheet. Go to Edit|Paste Special and select the Transpose check box. Click OK. Excel will place a transposed copy of your data at the new location.
5--[Ctrl] key magicThe [Ctrl] key makes short work of a number of tasks...
- Navigate quickly. When you press [Ctrl] and any arrow key, you jump to the last populated cell in that direction. This is much easier than scrolling through the workbook, using page down or tabbing. Also, if you hold down the Shift key with any [Ctrl]Arrow combination you select all of the cells between where you are and where you jump with the control key.
- Make non-contiguous selections. If you need to select non-contiguous cells, hold down the [Ctrl] key while you are making the selections. As long as you hold down the [Ctrl] key, you can click and select as many cells or groups of cells as you want.
- Fast Data Entry. If you need to put the same data in multiple cells, select all of the cells you need to populate, type the data you want to put into the cells but do not hit enter. Instead hit [Ctrl]Enter and Excel will put the data you typed into all of the selected cells.
6--Keeping row and/or column labels in view when browsing through a worksheetUse Freeze Panes to select specific rows and columns which will be locked in place to remain visible when you are scrolling. To lock rows, select the row below where you want the split to occur. To lock columns, select the column to the right of where you want the split to occur. To lock both rows and columns, select the cell below and to the right of where you want the split to occur. Then go to Window|Freeze Panes. Click on Freeze Panes. To undo this operation, go to Windows|Unfreeze Panes and click on Unfreeze Panes.
7--Print worksheet row and/or column labels on every page
To print column headings, click anywhere on the sheet and go to File|Page SetUp. Click the Sheet
tab. Under "Print Titles" click the little icon at the end of the "Rows
to Repeat at Top" text box. Select a cell in the title row. The range
$1:$1 appears in the text box. Click on the little icon at the end of
the text box. Select Print. When you click Print, row 1 will be printed
as the horizontal title row on each page. To print row labels, follow this same procedure but use the "Columns to Repeat at Left" text box.
8--Split one field into two fields or get rid of unneeded data in a fieldWhen importing data, you may have a data field that contains more data than you need. You may need to either split the data into two fields or to simply discard the extra data. To split the field, add a blank column next to the column with the extra data. Highlight the column with the extra data. Go to Data|Text to Columns and a dialog box will pop up. In the Original Data Type box, select either Delimited or Fixed Width. Click Next. Either select the delimiter or set the field widths. Click Next. Select the Column Data Format for each column you are creating. Click Finish and the data will be split into separate columns. If you simply want to discard some of the data in your field, you do not need to create the new column, and instead of selecting a Column Data format for this extra data, just select "Do not import column (skip)" instead of a data format. Click Finish and the extra data will be gone.
