You are here: Home / Data

How to import Access table into Excel 2007

July 14, 2011 By
  1. Open a new workbook, or an existing one and select a worksheet.
  2. On the Data tab, Get External Data group click From Access.

  3. The Select Data Source window will open. Select the Access database file and click Open.
  4. The Select Table window will open. Select the table you want to import and click OK.

  5. The Import Data window will open. Specify how you want to view the data and whether you want it in the existing worksheet or a new one. Click OK.

  6. Here is how your data will look like in Excel:

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

Automatically insert decimal points in Excel 2007 or 2010

April 13, 2011 By

To automatically insert decimal points when entering a number in a cell, follow these steps:

  1. Click the  Microsoft Office Button (File tab in case of Excel 2010).
  2. Click Excel Options (at the bottom right of the menu).

  3. Excel Options window will open.

  4. In Excel Options window (left side) click Advanced.
  5. On the right side  check the box labeled: Automatically insert a decimal point , then specify the number of decimal points you want in the combo box labeled: Places (next line).
  6. Click OK.

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

How to summarize data using a pivot table in Excel 2007

February 11, 2010 By

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

Hide rows based on cell value in Excel 2007

December 18, 2009 By

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

Check formula errors in Excel 2007 using IFERROR Function

November 6, 2009 By

One of the newly introduced functions in Excel 2007 is the IFERROR Function. If you are using a formula in a cell, this function allows you to trap formula errors and specify a value to be substituted in the cell in case of an error.

Syntax

IFERROR(value,value_if_error)

Value is the argument (formula) that is checked for an error.

Value_if_error is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Example:

I have:

  1. Numeric values in cells A1, A2 and B2.
  2. In C2 I have the following formula:

=B2/A2*$A$1

If cell A2 has a value of 0 (zero) the formula will return #DIV/0. To trap this error in Excel 2003 you will use a formula like this:

=IF(ISNUMBER(B2/A2*$A$1);B2/A2*$A$1;0)

The ISNUMBER function, part of the first argument to the IF function; will decide if the value returned from our formula is a number. In this case it will accept the result; otherwise it will substitute a 0. Notice that our formula is supplied twice: as part of the first argument to the IF function, and as a second argument.

The IFERROR function is much simpler.

=IFERROR(B2/A2*$A$1;0)

In this example if there is no error the result of the formula: B2/A2*$A$1 will be returned and accepted, otherwise a 0 will be substituted.

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

Paste data as a picture (image) in Excel 2007

October 30, 2009 By

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 validation in Excel 2007 – Date range

September 10, 2009 By

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 validation in Excel 2007 – Drop down list

August 13, 2009 By

If you want to validate a cell to restrict data entry to values in a drop-down list 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. xl7validation2

  4. In the Data Validation dialog box, click the Settings tab.
  5. Click on the Allow box then select List from the drop-down list.
  6. Click the Source box and then type the valid values separated by the appropriate list separator character depending on your installation( usually a comma “,” or semicolon “;”). For example if the cell is for a color of a car then you can limit the values by entering : Silver, Green, Blue.
  7. Instead of typing your list manually, you can also create the list entries by referring to a range of cells in the same worksheet or another worksheet in the workbook.
  8. To specify the location of the list of valid entries, do one of the following:
    1. If the list is in the current worksheet, enter a reference to your list in the Source box, for example enter: =$A$1:$A$6.
    2. If the list is on a different worksheet, define a name for your list then enter the name that you defined for your list in the Source box, for example, enter: =ValidProjects.

Notes:

  1. Make sure that the In-cell dropdown check box is selected. Otherwise, you won’t be able to see the drop-down arrow next to the cell.
  2. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
  3. 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: , ,

How to calculate a running average in a column

August 7, 2009 By

To calculate a running average, or a moving average, for a range of cells (a column), you use the AVERAGE function. The only trick you need to apply is to make your range changing continuously.

In the example below we want to calculate the running average of the sales from January to June.

Our ranges for the months will be as follows:

January – one cell : B2:B2
February- 2 cells B2:B3
March – 3 cell2: B2:B4 … and so on.

The first cell of the range is always the same for all months : B2, so we will make it absolute reference like this: $B$2. Therefore our formulas , in C column will be like this:

January: =AVERAGE($B$2:B2)
February: =AVERAGE($B$2:B3)
March: =AVERAGE($B$2:B4) …. Ans so on.

Enter the first formula in cell C2 and autofill down the rest of the range.

running-average1

Data

running-average2

Formulas

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

Average cells based on multiple criteria in Excel 2007

July 9, 2009 By

To average cells based on multiple criteria in Excel 2007 you use the AVERAGEIFS function.

Syntax

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Average_range is the range of cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.

Example:

In this example we will average the total sales for Jim in quarter 2 (olive green rows). So our criteria are:

  1. “Jim” in column B.
  2. “Q2″ in column C.

Our formula will be like this:

=AVERAGEIFS(D2:D23;B2:B23;”Jim”;C2:C23;”Q2″)

This formula returns a value of 1094, which is (2540+1250+178+408)/4

xl7averageifs

Notes:

  1. If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
  2. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
  3. Each criteria_range must be the same size and shape as sum_range.
  4. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.

Filed Under: Data, Excel 2007, Formulas, Functions Tagged With: , ,
« Older Posts