You are here: Home / Archives for Data

How to count cells that contain numbers or dates

August 24, 2008 By

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

How to Find cells that have data validation

August 10, 2008 By

If you want to find cells in your worksheet that have data validation, then follow these steps:

1. On the Edit menu, click Go To. The Go To window will open.

2. Click Special. The Go To Special window will open.

gotospecialv

3. Click Data validation.
4. At the bottom of the window click on the radio button that says All.

All cells with data validation will be highlighted.

To find cells that match certain data validation settings then you follow similar steps with slight variations:

1. Click the cell that has the data validation settings for which you want to find matches.
2. On the Edit menu, click Go To. The Go To window will open.
3. Click Special. The Go To Special window will open.
4. Click Data validation.
5. At the bottom of the window click on the radio button that says Same.

All cells with matching data validation will be highlighted.

Filed Under: Data, Validation Tagged With: , ,

How to perform autofill without dragging the fill handle

July 26, 2008 By

If you have a large worksheet with formulas and you want to perform an autofill, then it may be a tedious job to drag the fill handle across hundreds of rows or columns. Here is a simple procedure to make your life easier:

1. Select the first cell in your range (the one which has a formula already).
2. While holding down the shift key go to the last cell in the range and select it. Now you should have all your range selected.
3.In the Edit menu click Fill then Series.

Fill Series 1

4. When you get the Series window click Autofill.

Fill Series 2

All cells in the range should now be updated with formulas.

Filed Under: Data, Formulas Tagged With: , ,

How to hide rows based on a cell value

July 21, 2008 By

The autofilter feature in Excel allows you to show a subset of your data based on a list of unique values derived by the auotfilter. You can also use this feature to hide rows of data based on certain conditions you specify. here is how you can do it.

1. Click Data –> Filter –> Autofilter.


2. In the column which have the criteria for hiding rows,click the arrow of the list box.

3. From the drop-down list Select custom.
4. The Custom Autofilter window will open.

4. Suppose in this example we want to hide all rows with value less than 10, so In the list where it says “Show rows where” , select “is greater than or equal”.
5. In the list to the right select the value which qualify the row to be shown. In our example we will select 10.

This will hide all the rows which have a value less than 10.

Applies to: Excel 2003

Filed Under: Customization, Data Tagged With: , , ,

How to convert columns to rows, or rows to columns

June 14, 2008 By

If you have a column of text like this:

Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

And you want to put this data in a row like this:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday

Instead of moving the data manually, you can use an option of the Paste Special command as follows:

  1. Select the data in the column.
  2. Right click and then select copy.
  3. Select the first cell in the destination row.
  4. Right click and then select paste special.
  5. The paste special dialog box will open.
  6. In the lower right corner select the check box labeled “transpose” and click OK.
  7. Your data will be transposed into a row.

Notes

  1. You can follow the same steps to transpose rows into columns.
  2. Transpose is not limited to one column or one row. Select any area in your worksheet, copy it and do a transpose as mentioned earlier. see the example in the image below.

Transpose

Filed Under: Data, Formatting Tagged With: , ,

How to collect and paste multiple items

May 29, 2008 By

The Microsoft Office Clipboard allows you to collect text and graphic items from any number of Office documents or other programs and then paste them into any Office document. Examples are Microsoft Excel, Microsoft Word and Microsoft PowerPoint.

To use the Office Clipboard you must open it in the task pane of an Office program.

  1. On the Excel menu click Edit.
  2. In the Edit menu click Office Clipboard.

    3. offclip.gif

  3. The Office Clipboard will open on the task pane.

    4. offclip1.gif

Now whenever you copy an item, it will appear on the Office Clipboard with the latest item on top.

To copy an item to Excel:

  1. Click on the cell where you want to copy the item.
  2. Click the required item on the Clipboard.

Notes:

  1. You can have up to 24 items collected in the Clipboard. If you copy more than that the oldest item will be dropped automatically.
  2. You can control the Clipboard behavior by using the Options drop down menu at the bottom of the Clipboard.

Applies to: Excel 2003

Filed Under: Data Tagged With: , , , , ,

Autofill, formulas and cells references

April 24, 2008 By

In a previous post I explained How to use Autofill with formulas.Today I will cover the same subject but from a different perspective.

Suppose you have a list of values in a certain currency and you want to convert those values to another currency based on a given currency rate. If we follow the same scenario as in the previous post then it will go like this.

  1. Enter the formula in the first row of data as follows:

Formula Cell refrence 1

  1. Drag the fill handle across the rest of the rows to generate the formulas.

Formula Cell refrence 2

  1. Your data will now look like this.

Formula Cell refrence 3

What is wrong?

The cell reference style we are using here e.g. A3, B1 is called Relative Cell Reference. When you perform autofill downwards with relative cell reference, Excel will increase the row number by 1 successively e.g. A1, A2, A3 or B1, B2, B3 … etc.

In our case we want cell B1, which contains the rate, to be fixed. In this case we use the Absolute Cell Reference which means whatever cell reference you specify will remain fixed when you use autofill. The B1 cell will be coded like this: $B$1.

Now back to our example. This is how the formulas look after modification. $B$1 is the same in all rows.

Formula Cell refrence 4

And this is how our values will look like.

Formula Cell refrence 5

Related Links:

How to use Autofill with formulas


Applies to: Excel 2003

Filed Under: Data, Formulas Tagged With: , , , ,

How to apply a cell format to other cells

April 18, 2008 By

1. Select the cell which you have already formatted.

2. Click Format Painter Format Painter in the standard toolbar.

3. Click the cell on which you want to apply the format. The format of the first cell will be applied to the new cell.

4. If you want to apply the format to more than one cell then select all the cells before releasing the mouse button (drag the mouse pointer across the cells).

Tip.

If you have none adjacent cells or ranges for which you want to apply the format, then:

  • Double click the format painter. This way it will remain active until you deactivate it again.
  • Apply the format as many times as you want (see 3 and 4 above).
  • When you are done double click the format painter to deactivate it.

Applies to: Excel 2003

Filed Under: Data, Formatting Tagged With: , , ,

How to print part of a worksheet

April 10, 2008 By

If you have a worksheet and you only want to print part of that worksheet then follow the steps below:

Method 1

  1. Click File then click Page Setup .The Page Setup window will open.
  2. Click the Sheet tab.

    3. Print Area0

  1. In the Sheet tab click Print Area
  2. Now go to your worksheet and highlight the cells you want to print. The Print Area in the Page Setup window will be updated automatically with the cells references.
  3. Now every time you print the worksheet only the specified area will be printed.
  4. To print the whole sheet, clear the Print Area before you start printing.

Method 2

  1. In your worksheet, highlight the cells you want to print.
  2. Click File –> Print Area –> Set Print Area.
  3. The print area is now set to your selection.
  4. To print the whole sheet, Click File –> Print Area –> Clear Print Area.

Print Area1

Applies to: Excel 2003

Filed Under: Data, Printing Tagged With: , , ,

How to format a cell based on its value (conditional formatting)

April 10, 2008 By

  1. Select the cells for which you want to clear the 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. Click Cell Value Is.
  5. In the combo box next to it select the comparison phrase e.g. less than.
  6. Next enter a constant value (0 in this example).
  7. Click the format button, the Format Cells dialogue will open.

  1. Select the formatting you want to apply when the cell value meets the condition e.g. you can set the font color to red.

Applies to: Excel 2003

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