You are here: Home / Archives for Data

How to summarize data using a pivot table in Excel 2007

February 11, 2010 By admin Leave a Comment

Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate.

The best way to explain how to create a pivot table in Excel 2007 is by using an example.

Below is a list of products, salesmen and total sales for a product / salesman.

XL7Pivot1

To create a pivot table to summarize the total sales by product, you perform the following steps:

  1. Prepare your data table and make sure you have columns headings.
  2. Select the data range or simply click on a cell within the range.
  3. In the Insert tab click Pivot Table. The window Create Pivot Table will open.

    4. XL7Pivot2

  4. Make sure that Table/Range points to your correct range then click OK.
  5. Now you will get a rectangular area to the left that represents the pivot table, and a field list to the right.

    6. XL7Pivot3

  6. In the Pivot Table Field List to the right select Product and Total Sales. The pivot table that summarizes total sales by product will be created as follows:

    7. XL7Pivot4

  7. If you select Salesman instead of Product, this is what you get: a pivot table that summarizes total sales by salesman.

    8. XL7Pivot5

  8. You can plot a chart from the pivot table as shown below.

XL7Pivot6

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

Hide rows based on cell value in Excel 2007

December 18, 2009 By admin Leave a Comment

To hide rows based on cell value in Excel 2007 follow these steps:

  1. Prepare your data including a header row.
  2. In the Home tab, Editing group, click Sort and Filter then select Filter from the menu.
  3. Your data will now look like this:

XL7HideRows1

In this example we want to hide rows where the sales are greater than or equal to 2000. I.e. we will only show rows where the sales are less than 2000.

  1. Click on the filter arrow next to Sales.
  2. Point to Number Filters and select Less Than.

    3. XL7HideRows2

  3. The Custom AutoFilter window will open.

    4. XL7HideRows3

  4. Enter 2000 in the Combo box to the right and click OK.
  5. Now all rows with values greater than or equal to 2000 will be hidden. Here is the final result:

XL7HideRows4

Note:

If your data is text then you will see Text Filters instead of Number Filters in the menu.

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

Paste data as a picture (image) in Excel 2007

October 30, 2009 By admin 1 Comment

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

  1. Select the range you want to copy.
  2. In the Home tab, Clipboard group, click Copy.
  3. Select a cell in the range where you want to paste the picture.
  4. In the Home tab, Clipboard group, click on the arrow below the Paste icon, point to As Picture and click Paste Picture Link.

XL7PastePict

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

Data validation in Excel 2007 – Date range

September 10, 2009 By admin 2 Comments

If you want to validate a cell to restrict data entry to a date range then follow these steps:

  1. Select the cell you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.

    3. xl7validation

  3. The Data Validation dialog box will open.

    4. xl7validdate1

  4. In the Data Validation dialog box, click the Settings tab.
  5. Click on the Allow box then select Date from the drop-down list.
  6. Click the Data box and then select an option from the drop down list e.g. “Between” , “Greater than”, …etc.
  7. In the Start Date box enter a valid date compatible with your regional settings for the date.
  8. In the End Date box enter a valid date compatible with your regional settings for the date (this box is not applicable for some options).
  9. You can enter a formula that returns a date for Start Date or End Date, for example “=Today()+7″.

Notes:

  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
  2. If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

More Validation options:

  1. Display an input message when the cell is clicked.
    1. Click the Input Message tab.
    2. Select Show input message when cell is selected check box.
    3. Fill in the Title and text for the Input message.
  2. Display an error message when wrong data is entered.
    1. Click the Error Alert tab.
    2. Select Show error alert after invalid data is entered check box.
    3. Fill in the Title and text for the Error message.
    4. Select one of the following options for the Style box:
      1. Information: Display an information message. Does not prevent entry of invalid data.
      2. Warning: Display a warning message. Does not prevent entry of invalid data.
      3. Stop : Prevent entry of invalid data.

Tip

If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so

  1. Open the Data Validation dialog box.
  2. Click the Settings tab.
  3. Select the Apply these changes to all other cells with the same settings check box.

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

Average values based on a given criteria in Excel 2007

June 15, 2009 By admin Leave a Comment

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: Data, Excel 2007, Excel Formulas, Excel Functions, Excel help

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

How to summarize data using subtotals

November 10, 2008 By admin

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

How to summarize data using a pivot table

October 7, 2008 By admin

What is a pivot table?

Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate.

The best way to explain how to create a pivot table 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 a pivot to summarize the total sales by product, you perform the following steps:

1. Prepare your data table and make sure you have columns headings.

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

3. In the data menu click Pivot Table and then follow the wizard.

4. When you get the pivot table field list:

  1. Drop the “Product” field in the area where its says “Drop row fields here”.
  2. Drop the “Total Sales” field in the area where its says “Drop data items here”. You will get the pivot table as shown here:

5. You can then plot a chart using the pivot table instead of your detailed data.

Applies to Excel 2003

Filed Under: Charts, Data Tagged With: Data, Pivot Table

How to count cells that contain numbers or dates

August 24, 2008 By admin

To count cells, within a given range or list of arguments, that contains only numbers you use the COUNT function.

  • Dates and text that represent numbers are considered numbers and will therefore be counted.
  • Logical values, text, or error values are not counted

Syntax

COUNT(value1,value2,…)

Examples:

COUNT(A1:A20) :counts numbers in a range of cells.
COUNT(A1:A20, 7) :counts numbers in a range of cells plus the second argument (7) which is counted as 1.

In the simple example depicted by the image below, cell B7 contains the function: =COUNT(B1:B6). The result is 4. as you can see the cells that contain the numbers 1, 2 and 17 and the cell that contains the date are counted. The the cells that contain the text “ABC” and “XYZ” are ignored.

Applies to: Excel 2003

Filed Under: Data, Date and Time, Functions Tagged With: Count, Data, Functions
« Older Posts