Excel help for the rest of us
To change Excel 2007 default color scheme, follow these steps:
If you have a cell, or column, that contains some text and you want to distribute that text into the adjacent columns, then :
1. Select the cell, range of cells within the same column or the entire column that you want to distribute its data.
2. On the Data tab, Data Tools group, click Text to Columns. This will start the Convert Text to Columns Wizard .
3. In the first step of the wizard specify your data type then click Next :
a. “Delimited” means data fields are separated by special characters like commas. E.g ” Shakespeare,William “.
b. “Fixed” means fields are aligned in equal size columns separated by spaces. E.g ” Shakespeare William “.
4. In the second step of the wizard you specify the field delimiters, if you choose “Delimited” in the previous step, or specify the field widths, if you choose “Fixed” in the previous step. Click Next when you are done.
5. The last step lets you specify the data format of each column, if it is different from the default set by Excel. Click Finish when you are done.
6. The data will be distributed into a number of columns depending on the number of separators or spaces in the original column.
Suppose you have a rang of cells, say A1:A20, and you want to sum only cells A1, A4 , A6, A7and A8 which are non-adjacent. You can write a formula like this:
=A1+A4+A6+A7+A8
This is OK for a few cells but for large range, it is better to write the formula as follows:
=SUM(A1,A4,A6:A8)
Which means some A1, A4 and the range A6 to A8.
You can highlight duplicate values in a data range by using conditional formatting. Follow these steps:
Applies to: Excel 2007, Excel 2010
You can save an Excel worksheet as a comma delimited file. I.e. the worksheet columns will be saved in the file separated by commas. This will allow you or someone else who has no Excel, to browse the file in any text editor.
To save the worksheet as a comma delimited file:
1. In Excel menu click File.
2. Click Save As. The Save As window will open.
3. In the Save as type drop down, select CSV (Comma delimited).
4. Any row in the worksheet will be transformed like this:
“ABC;7268;87368;XYZ;373″
The delimiter is either a “,” or “;” depending on your installation of Excel.
Applies to: Excel 2007, Excel 2010
If you have a workbook with multiple worksheets and you want to hide some worksheets, e.g. to protect data from accidental change, you can do that as follows:
To display or unhide a worksheet:
Applies to: Excel 2007, Excel 2010
To clear conditional formatting from a range of cells follow these steps:
To clear conditional formatting from the whole worksheet (no selection is required):
It is fairly easy to insert a file into an Excel worksheet as an attachment. That file can be a word document, PowerPoint presentation or even another Excel workbook.
Follow these steps:
If you have a number of rows in your sheet and each one has a different size of data. For example one row may have a wrapped text or a text with big font size that does not match with the current row height, then you can adjust the row height.
To adjust the height of each row to fit the entered data:
Alternatively you can do the same by following these steps:
Before:
After:
You may have a number of columns in your sheet and each one has a different size of data. For example one column may have only a 2 digit sequence number but another column has a long text.
To adjust the size of each column to fit the entered data:
Alternatively you can do the same by following these steps:
Before:
After: