2009 April | Excel Digest

How to delete duplicate rows from a range in Excel 2007

April 28, 2009 By admin Leave a Comment

1. Select the range from which you want to delete duplicate rows.

2. In the Data tab, Data Tools group click Remove Duplicates.

xl7dup1

3. The Remove Duplicates window will open.

xl7dup2

4. Specify whether your columns have headers or not.

5. Select the column(s) from which you want to remove duplicates then click OK.

6. The rows with duplicate data will be deleted and you will get a message like the one below.

xl7dup3

Note:

The rows with duplicate data will be deleted permanently. So make sure you backup your data before performing this task.

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

Sort data by multiple columns in Excel 2007

April 26, 2009 By admin 6 Comments

To sort a data range by multiple columns, or keys, follow these steps:

1. Select the data range you want to sort.
2. On the Home tab, Editing group click on Sort & Filter.

xl7sort

3. Select Custom Sort.

xl7custsort1

4. The Sort window will open, with 3 combo boxes (drop-down) labeled Column, Sort On and Order respectively.

xl7custsort2

5. At first you will see one row of “Sort by” drop-downs.

a. In the Column drop-down specify the column name to use as your key.
b. In the Sort On drop-down specify whether to sort on cell value, cell color, font color or cell icon.
c. In the Order drop-down specify whether to sort on ascending, descending or based on a custom list.

6. To add another sort level (another column) click the Add Level button. A new row of drop-downs will be added in the sort window. Repeat the process until you are done.

7. Click OK.

Filed Under: Data, Excel 2007 Tagged With: Data, Excel 2007, Excel help, Sort

Save Excel 2007 workbook as PDF file

April 26, 2009 By admin Leave a Comment

You can now convert Excel 2007 files directly to PDF format without the need for a third party software. However to be able to do that you need to download and install Microsoft Office “Save as PDF” Add-in (SaveAsPDF.exe) from Microsoft web site.

Check SaveAsPDF.EXE download page Here.

After the add-in is installed:

1. Click the Office button in Excel 2007.
2. Point to Save As then click PDF or XPS.

SavePDF

3. The window Publish as PDF or XPS will open.
4. In the Save as type drop down select PDF.
5. Click the Publish button.

Filed Under: Excel 2007, General Tagged With: Excel 2007, PDF, Save

How to change tab color in Excel 2007

April 22, 2009 By admin Leave a Comment

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:

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

How to customize the quick access toolbar in Excel 2007

April 19, 2009 By admin

The Quick Access Toolbar, which is part of excel 2007 interface is a customizable toolbar.It comes handy for commands that you use more often.

You can customize the Quick Access Toolbar according to your needs and style by adding the commands you need from the available lists.

Follow these steps to customize:

1. Right click on the Quick Access Toolbar.
2. Click Customize Quick Access Toolbar.

xlqaccess1

3. The Excel Options window will open.

xl7options

4. On the left pane of the Excel Options window click Customize.
5. Click on the combo box labeled “Choose commands from” and select the appropriate tab or group that have the command or button you want.
6. Locate the command you want in the list below the combo box and double click on it. It will be moved to the quick access tool bar list to the right.
7. Repeat the process as long as you want. When you are done click OK.

Tip

You can rearrange the Quick Access commands by using the two buttons with arrows to the far right. Select the command first then click the appropriate button to move up or down in the list.

xlqaccess2

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

Count cells with multiple criteria in Excel 2007

April 18, 2009 By admin

In an earlier post I demonstrated How to count cells with multiple criteria in Excel 2003. I used an Array Formula together with the SUM function.

Fortunately enough in Excel 20007 we do not need that workaround. Now there is a direct function that count cells with multiple criteria: the COUNTIFS function.

Syntax

COUNTIFS(range1, criteria1,range2, criteria2…) … up to 127 range/criteria can be specified.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2), with total sales that exceed 1000. i.e. we have three criteria:

“John” in column B.
“Q2″ in column C.
“>1000″ in column D.

We will use the following formula for this purpose:

=COUNTIFS(B2:B17;”=John”;C2:C17;”=Q2″;D2:D17;”>1000″)

This formula will give us a result of 2 as you can see.

xl7countifs

Filed Under: Excel 2007, Formulas, Functions Tagged With: Excel 2007, Formulas, Functions

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

April 16, 2009 By admin

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 Formating, Excel 2007, Excel Formatting

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

April 15, 2009 By admin

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