You are here: Home / Archives for Excel help

Copy Excel 2007 data to a Word document

November 24, 2009 By

If you want to copy data from Excel to Microsoft word then follow these steps:

In  Excel

  1. Select the worksheet data (range) that you want to copy to a Word document.
  2. On the Home tab, Clipboard group, click

Filed Under: Excel 2007, General 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: , ,

How to hide error indicators in Excel 2007 cells

October 28, 2009 By

I find it annoying sometimes when I enter a correct formula in a cell and Excel gives me an error like “Inconsistent Formula” or “Formula Omits Adjacent Cells”.

XL7Error

Fortunately you can hide these error indicators through Excel Options.

  1. Click the Microsoft Office button. XL7Office
  2. Click Excel Options.
  3. In the left pane of Excel Options click Formulas.
  4. In the  Error Checking section, clear the Enable background error checking check box.
  5. Click OK.

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

How to change Excel 2007 default file location

October 15, 2009 By

To change Excel 2007 default file location, follow these steps:

1. Click the  Microsoft Office Button.

2. Click Excel Options.

3. In Excel Options window (left side) click Save.

4. In the text box labeled Default File Location , specify the path for the required location.

XL7Save

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

Save an Excel 2007 workbook to Excel 97-2003 format

October 7, 2009 By

To save an Excel 2007 workbook to Excel 97-2003 format follow these simple steps:

1. In Excel 2007, open the workbook that you want to save in Excel 97-2003 format.

2. Click the Microsoft Office Button XL7Office and point to the arrow next to Save As.

3. Under Save a copy of the document, click Excel 97-2003 Workbook.

The file will be saved in the Excel 97 – Excel 2003 file format. This will make it possible to open the file in versions of Excel 97 through Excel 2003.

XL7to2003

Filed Under: Excel 2007, General 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: , , ,

Find cells that meet specific criteria in Excel 2007

September 6, 2009 By

To find cells that meet a specific criteria, for example cells with conditional formatting or cells which have data validation, proceed as follows:

  1. In the Home tab Editing group click Find and Select.

    2. xl7find

  2. Click on:
    1. Formulas : to find cells that contain formulas.
    2. Comments : to find cells that contain comments.
    3. Conditional Formatting : to find cells that have Conditional Formatting.
    4. Constants : to find cells that contain constants.
    5. Data Validation : to find cells that have data validation.
  3. More options are available if you click Go To Special. Then you will get the Go To Special dialogue where you can specify specific criteria for the cells to be found and selected.

Note:

You can search the entire sheet for the specified criteria or you can limit your search by selecting a range of cells.

Filed Under: Excel 2007, Formatting, Formulas, General, Validation Tagged With: , , , ,

Sum cells with multiple criteria in Excel 2007

August 25, 2009 By

In an earlier post I demonstrated How to sum cells with multiple criteria in Excel 2003. I used an Array Formula consisting of the SUM function and the IF function.

In Excel 2007 however a new function is introduced that facilitates sum with multiple criteria: the SUMIFS function.

Syntax

SUMIFS (sum_range, criteria_range1, criteria1, criteria range2, criteria2…) … up to 127 range/criteria can be specified.

sum_range : Required. One or more cells to sum.

criteria_range1: Required. The first range in which to evaluate the associated criteria.

criteria1: Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added.

criteria_range2, criteria2, … : Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

In the example below we want to sum the sales for “John” in quarter 2 (Q2). I.e. we have two criteria:

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

We will use the following formula for this purpose:

=SUMIFS(D2:D17;B2:B17;”John”;C2:C17;”Q2″)

Only rows 3, 10 and 14 will qualify for this formula (yellow color) and it will give us a result of 9547 as you can see.

xl7sumifs


Filed Under: Excel 2007, Formulas, Functions 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: , ,
« Older Posts
Newer Posts »