You are here: Home / Data

Average values based on a given criteria in Excel 2007

June 15, 2009 By

To average a range of values that meet a given criteria or condition, you use the AVERAGEIF function.

Syntax

AVERAGEIF(range,criteria,average_range)

Range : The group of cells to be evaluated for criteria.

Criteria: is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.

Average_range: is the actual set of cells to be averaged. If omitted, range is used.

Example:

In the example below we want to average the total sales for a particular salesman (Jim). So our range is B2:B13 and our criteria is “Jim”. The range to be averaged is C2:C13. The formula will be:

=AVERAGEIF(B2:B13;”Jim”;C2:C13)

The result is 1005, i.e. (2300 + 1100 + 200 + 420) / 4

xl7average1

Notes:

1. Cells in range that contain TRUE or FALSE are ignored.

2. If a cell in average_range is an empty cell, AVERAGEIF ignores it.

3. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.

4. If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.

5. If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.

6. You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. In the previous example if you use “J*” in criteria then both “Jim” and “John” will be included in the average.

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

How to delete duplicate rows from a range in Excel 2007

April 28, 2009 By

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: ,

Sort data by multiple columns in Excel 2007

April 26, 2009 By

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: , , ,

How to protect formula cells but allow access to data cells

February 5, 2009 By

By default all cells in a worksheet are locked (see image below). I.e when you protect a worksheet every cell in the worksheet will be protected.

protect1

To handle cell protection differently then you have change the default settings for the required cells.

Here is a scenario to protect formula cells but allow access to data cells:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Uncheck the Locked and Hidden check boxes, then click OK.

These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to protect the formula cells, and here is how we do it:

  1. Select all cells that have formulas.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Check the Locked and Hidden check boxes, then click OK.
  5. On the Tools menu click Protection and select  Protect sheet.

Applies to: Excel 2003

Filed Under: Data, Formulas, Security and Protection Tagged With:

Copy and paste data as a picture (image)

January 17, 2009 By

To copy and paste data as a picture (image) follow these steps:

1. Select the range you want to copy.
2. While holding down the SHIFT key, Click on the Edit menu.
3. Click Copy Picture.
4. Select a cell in the range where you want to paste the picture.
5. Click Paste Picture in the Edit menu ( while holding down the SHIFT key).

Copy Picture

Applies to: Excel 2003

Filed Under: Data, General Tagged With: ,

How to clear conditional formatting of a cell

January 5, 2009 By

  1. Select the cell(s) for which you want to clear conditional formatting.
  2. On the Format menu, click Conditional Formatting.
  3. The Conditional Formatting dialogue will open, and the applied conditions will be listed.

    4. Conditional formatting

  4. Click Delete.
  5. The Delete Conditional Format window will open.

    6. Delete format

  6. Select the condition(s) to delete then press OK.

Applies to: Excel 2003

Filed Under: Data, Formatting Tagged With: , ,

How to show the top 10 items in a range

November 22, 2008 By

The autofilter feature in Excel allows you to show a subset of your data based on a specified criteria. The top 10 is such a criteria.

To show only the top 10 values (or any other number) for a particular column follow these steps :

1. Click Data –> Filter –> Autofilter.

2. In the column which have the data to be evaluated, click the arrow of the autofilter.

3. From the drop-down list select Top 10.

4. The Top 10 window will open.

The Top 10 Window has three combo boxes which allow a lot of flexibility in specifying the required subset of data. To avoid confusion I will take some possible scenarios as follows:

1. You want to show the top 10 items (default). Leave everything as it is and click OK. The top 10 items will be displayed.

2. You want to show the top 15 items. In the middle combo box click the bottom arrow until you reach 15, or just type 15 then click OK. The top 15 items will be displayed.

3. You want to show the bottom 10 items. In the left combo box select “Bottom” then click OK. The bottom 10 items will be displayed.

4. You want to show the top items, up to 25% of your data (i.e. the top quarter of your data). In the left combo box select “Top“, in the middle combo box type 25 and in the right combo box select “percent“, then click OK. The top quarter of your data will be displayed.

Note:

No sorting is performed here. The top/bottom items will be shown as they appear before the filtering.

Applies to: Excel 2003

Filed Under: Data Tagged With: , ,

How to sum values based on multiple criteria

November 18, 2008 By

The SUMIF function is a direct way to sum values based on a single criteria. If we want to sum or add  values based on multiple criteria  however, then we need to take extra steps. One alternative for this is to use the SUM function and the IF function.

In the example below we want to sum up the total sales for “John” in quarter 2 (Q2). i.e. our criteria is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))}

This formula will give us a result of 9547 (summing sales in the yellow rows).

Note:

The formula presented in this post is generated using the conditional sum wizard. You can try the wizard for yourself.

If you have the Analysis Toolpak installed, then the wizard should be available in the Tools menu. If not then read more on: How-to-install-and-load-the-analysis-toolpak.

Filed Under: Data, Formulas Tagged With: , ,

How to summarize data using subtotals

November 10, 2008 By

One of the options in the Data menu is the Subtotals. It allows you to create subtotals for a specific column in your worksheet, based on a key in another column.  The subtotals will be added to the original data which will be retained as it is.

The best way to explain how to create a subtotal is by using an example.

In the image below you see a list of products, salesmen and total sales for a product / salesman.

To create subtotals of the “Total Sales” by product, you perform the following steps:

1. Prepare your data table, make sure you have columns headings and sort the data by your key or category (Product in our example).

2. Select the data range or simply click on a cell within the range.

3. In the Data menu click Subtotals. The Subtotal dialogue will open.

4. From the combo box labeled “At each change in:” select the category on which you want to base the subtotals.

5. From the combo box labeled “Use function:” select the function to be used in calculating the subtotals. Usually you will use the SUM function, but in some cases you may just want to know “how many items” so you will use the COUNT function instead.

6. From the list labeled “Add subtotal to:” select the column to be summarized into subtotals.

7. Click OK.

8. The new list of your data will look like this.

9. When the subtotals are created, Excel also defines groups based on the rows used to calculate the subtotals. The groupings form an outline of your worksheet based on the criteria that you used to create the subtotals.

At the top left corner of the image above you will see the numbers 1 2 3. This is the outline of your subtotals.

  • Click on 3 and you will get all details of your data.
  • Click on 2 and you will only get the subtotals by category.
  • Click on 1 and you will only get the grand total.

This is what you get when you click “2″.

Note:

To remove subtotals:

1. In the Data menu click Subtotals.

2. When the Subtotal dialogue opens, click the button labeled “Remove All”.

Filed Under: Data Tagged With: ,

Count how often a value occurs – multiple criteria

October 20, 2008 By

In a previous post I covered counting how often a value occurs within a range. In that post we looked into one criteria, and we used the COUNTIF function.

But what if we want to count cells that meet multiple criteria. We can not use the COUNTIF function  in this case, so we will take a different approach and use the SUM function.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2). i.e. our critera is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM((B1:B17=”John”)*(C1:C17=”Q2″))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM((B1:B17=”John”)*(C1:C17=”Q2″))}

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

Filed Under: Data, Formulas, Functions Tagged With: , ,
« Older Posts
Newer Posts »