You are here: Home / Formatting

How to change or adjust the column width in Excel 2007

June 6, 2009 By

1. To adjust the column width to fit the current text, move the mouse pointer to the right border of the column until it changes to a cross as shown and then double click on it.

2. To adjust the column width manually, move the mouse pointer to the right border of the column until it changes to a cross as shown, hold down the left mouse button then drag the mouse until you get the right width.

xl7colwidth1 Before

xl7colwidth2 After

3. To adjust the column width to give it fixed size:

  1. In the Home tab , Cells group click Format then Column Width

    2. xl7colwidth3

  2. The Column Width window will open.

    3. xl7colwidth4

  3. Enter the required column width.

    4. Notes:

    1. The units shown are the number of characters in the default text format.

    2. You can enter any number between 0 and 255.

    3. If you enter 0 the column will be hidden.

Filed Under: Excel 2007, Formatting Tagged With: , ,

How to insert background image in Excel 2007

May 30, 2009 By

To insert a background image in Excel 2007 follow these steps:

1. Click on the Page Layout tab.

xl7background

2. In the Page Setup group click Background. The Sheet Background window will open.

xl7background2

3. Switch to the folder where you have the image, select the image and then click Insert.

4. The selected image will show as a background in your sheet.

Note:

When you insert a background image the Background button in the Page Setup group will turn into Delete Background. You can then click on that button to remove the background.

Filed Under: Customization, Excel 2007, Formatting Tagged With: , , ,

Conditional formatting with multiple rules in Excel 2007

May 22, 2009 By

I have two previous posts on Excel 2007 conditional formatting: One based on cell’s own value and the second based on value of another cell. This post will cover conditional formatting with multiple rules or conditions.

Suppose you have a cell formatted with this rule:

If cell value is less than zero format with Light Red Fill …. Etc.

xl7cfrule1

Now you want to add another rule:

If cell value is greater than 100 format with green Fill.

Follow these steps:

1. In the Home Tab, Styles group click Conditional Formatting, then Manage Rules.

xl7cfrule2

2. The window Conditional Formatting Rules Manager will open, and it will show the current rule.

xl7cfrule3

3. Click the New Rule button. The New Formatting Rule window will open (not shown here).

4. Add the new rule. If you need help refer to the other two posts I mentioned earlier.

5. After finishing, your list of rules will look like this:

xl7cfrule4

Note:

You can apply the same steps if you have conditional formatting based on the value of another cell.

Filed Under: Excel 2007, Formatting Tagged With: , ,

How to create a custom cell style in Excel 2007

May 19, 2009 By

Excel 2007 cell styles are helpful in giving your worksheet formatting a consistent look and feel. Excel comes with a number of built in styles. If you are not satisfied with those you can still create and modify your own styles. Here is how you can do it:

1. In the Home Tab, Styles group click Cell Styles.

xl7styles1

2. A list of the built-in styles will be shown in a grid-like format.

xl7styles2

3. Click on New Cell Style at the bottom of the window. The Style window will open.

xl7styles3

4. In the Style name textbox give a name for your style.

5. Click Format in the Style window and specify the formats you want. This include Alignment, Font, Border … etc.

6. Click OK in the Format window, Then OK in the Style window.

7. Your style will be added to the custom styles at the top of the built-in styles. From here own you can apply it to any cell you want.

Filed Under: Customization, Excel 2007, Formatting Tagged With: , , ,

How to change tab color in Excel 2007

April 22, 2009 By

To change worksheet tab color in Excel 2007 :

1. Select the tab whose color is to be changed.
2. In the Home tab, Cells group, click on Format.
3. Under Organize Sheets, point to Tab Color and select the color you want.

xl7tabcolor1

This is how your tabs will look like:

xl7tabcolor2

Filed Under: Customization, Excel 2007, Formatting Tagged With: , ,

Conditional formatting in Excel 2007 (based on value of another cell)

April 16, 2009 By

To format a cell based on the value of another cell follow these steps:

1. Select the cell you want to format, “A1″ in this example.
2. In the Home tab, Styles group, click on Conditional Formatting.
3. Select New Rule.

xl7cformat3

4. The New Formatting Rule window will open.

xl7cformat4

5. Click Use a formula to determine which cells to format.
6. The New Formatting Rule window will now look like this:

xl7cformat5

7. In the edit box labeled “Format values where this formula is true“, enter “=” followed by a formula referencing the other cell.

In this example the formula: “=B1<0″ is entered.
8. Click the Format button. The Format Cells window will open.

xl7cformat6

9. Specify the formatting you want. In this example I changed the cell color to “RED”.
10. Click OK to close The Format Cells window.

11. Click OK to close The New Formatting Rule window.

To summarize this example: cell A1 will have a RED color when cell B1 value is less than zero.

Filed Under: Excel 2007, Formatting Tagged With: , ,

Conditional formatting in Excel 2007 (based on value of the current cell )

April 15, 2009 By

To format a cell based on its value follow these steps:

1. Select the cell you want to format.
2. In the Home tab, Styles group, click on conditional formatting and point to Highlight Cells Rules.
3. A number of rules will show to the right. Select the rule you want to apply.

xl7cformat1

4. I will use Greater Than as an example. The Greater Than window will open.

xl7cformat2

5. Enter the value in the text box to the left
6. Select the required formatting from the drop down to the right.
7. Click OK.

Filed Under: Excel 2007, Formatting Tagged With: , ,

How to rename a worksheet

March 26, 2009 By

To rename the active worksheet use one of these two methods:

Method 1

1. On the format menu point to Sheet then click Rename. The sheet tab name will be highlighted (edit mode).
2. Type the new name, then click anywhere outside the tab name.

rename1

Method 2

Simply double-click the sheet tab name, to get into edit mode, and then type the new name.

rename2

Filed Under: Customization, Formatting, General Tagged With: , ,

How to display leading zeros in a numeric value

February 17, 2009 By

If you want to display leading zeros in a numeric value, then  you can create a custom format for the relevant cell as follows:

1. Right-click on the cell where you want display leading zeros.
2. Select Format Cells.
3. When the dialogue box opens select the Number tab.
4. In the Category list to the left select Number. A default display will show on the sample area to the right.
5. In the Category list to the left select Custom. A list of custom formats will show to the right, and a default format will show in the Edit Box above.
7. Modify the format in the Edit Box by entering a number of zeros equal to the maximum number of  digits possible  in your value, and watch the sample.
8. When you are satisfied click OK.
9. The new format will be applied to the cell and will also be added to the list of custom formats.

In the example below, the maximum value expected is seven digits long. We entered the format as “0000000″, and because the actual value in the cell 5 digits “12345″, the display will show as “0012345″.

leadingzeros1

Applies to: Excel 2003

Filed Under: Formatting Tagged With:

How to insert current date in worksheet footer

January 27, 2009 By

To insert the current date in worksheet footer, follow these steps:

1. In the File menu click Page Setup. The Page Setup window will open.
2. Select the Header/Footer tab.
3. Click the Custom Footer button. The Footer window will open.

footicons

As you can notice the footer is divided into three sections; left, center and right. Above those sections there is a number of icons for page number, pages, date, time and more other icons.

To insert the current date:

1. Click on the section where you want the date to be inserted.
2. Click the Date icon footerdate.
3. The following code will be inserted in the selected footer section: “&[Date]“.
4. Click OK and you will see the current date in the preview window.
5. Click OK again to finish the dialog.

Notes:

1. You can also go to Header/Footer by clicking  Header and Footer in the View menu.
2. Use File –> Print Preview to see how your footer will look like in the printer.
3. You can use a similar procedure to insert the date in the header if you want to.

Applies to: Excel 2003

Filed Under: Customization, Date and Time, Formatting, Printing Tagged With: , , , , , ,
« Older Posts
Newer Posts »