Excel Help | Excel Digest

AutoFit row heights in Excel 2007

August 2, 2010 By admin Leave a Comment

If you have a number of rows in your sheet and each one has a different size of data. For example one row may have a wrapped text or a text with big font size that does not match with the current row height, then you can adjust the row height.

To adjust the height of each row to fit the entered data:

Select the rows to be adjusted.
In the Home tab, Cells group, click Format.
From the Format menu Click AutoFit Row Height.

Alternatively you can do the same by following these steps:

Select the rows to be adjusted.
Point to the lower border of anyone of the selected rows (at the left of the row where the row number is). The mouse pointer should change to a cross with vertical arrows at the top and bottom.
Double click on the row border.

Before:

After:

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

AutoFit Column widths in Excel 2007

July 15, 2010 By admin Leave a Comment

You may have a number of columns in your sheet and each one has a different size of data. For example one column may have only a 2 digit sequence number but another column has a long text.

To adjust the size of each column to fit the entered data:

Select the columns to be adjusted.
In the Home tab, Cells group, click Format.
From the Format menu Click AutoFit Column Width.

Alternatively you can do the same by following these steps:

Select the columns to be adjusted.
Point to the right border of anyone of the selected columns (at the top of the column where the column label is). The mouse pointer should change to a cross with horizontal arrows to the left and right.
Double click on the column border.

Before:

After:

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

How to insert a shape in Excel 2007

March 30, 2010 By admin Leave a Comment

To insert a shape (rectangle, circle, line … etc.) follow these steps:

Select the Insert tab, then Click Shapes.

XL7Shapes1

A drop down menu will show different types of shapes.

XL7Shapes2

Select the shape you want. The mouse pointer will change to a cross like this :
Draw the shape by pressing the left mouse button then dragging the mouse.
Release the left mouse button when finished.

Filed Under: Excel 2007, General Tagged With: Drawing, Excel 2007, Excel help

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:

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

XL7Pivot2

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

XL7Pivot3

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:

XL7Pivot4

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

XL7Pivot5

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:

Prepare your data including a header row.
In the Home tab, Editing group, click Sort and Filter then select Filter from the menu.
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.

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

XL7HideRows2

The Custom AutoFilter window will open.

XL7HideRows3

Enter 2000 in the Combo box to the right and click OK.
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

How to some times greater than 24 hours in Excel 2007

November 29, 2009 By admin 6 Comments

To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :

A1 = 02:30

A2 = 10:15

A3 has the formula: =SUM(A1:A2)

then A3 will have the correct values of 12:45.

However if your total times are greater than 24 hours then things get tricky.

If you have :

A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.

To retain the hours beyond 24, you have two alternatives:

1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)

2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.

In both cases the formula will remain as it is. Only the cell format differs.

See another example below:

XL7SumHrs

Copy Excel 2007 data to a Word document

November 24, 2009 By admin

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

In Excel

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

Filed Under: Excel 2007, General Tagged With: Copy, Excel 2007, Excel help

Check formula errors in Excel 2007 using IFERROR Function

November 6, 2009 By admin Leave a Comment

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:

Numeric values in cells A1, A2 and B2.
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: Excel 2007, Excel help, Formulas, Functions

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:

Select the range you want to copy.
In the Home tab, Clipboard group, click Copy.
Select a cell in the range where you want to paste the picture.
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

How to hide error indicators in Excel 2007 cells

October 28, 2009 By admin Leave a Comment

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.

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

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