Useful Tips in Excel – Deleting blank rows, Inserting blank rows, finding out difference in Time

June 4th, 2011 by Tips Master | Posted under Excel.

Helpful Tips in ExcelYou will find some easy and quick Excel tips which will help in your routine work.

Tips explained here will help you when

1. Working with dates and time

2. Inserting blank rows within data

3. Removing blank rows from data

 

 

1. Working with Excel dates and time

A. Computing Difference in time in a single day

The below screen shot shows the start time and the end time and the difference in the time. All these happens in one day and we were able to calculate the difference in time by just adding a formula.

 

Difference in Time in Excel

Please note the Custom format need to be changed as h:mm:ss else what you get will be the difference in days.

 

Custom formatting

 

B. Computing Difference in time in two different days

See the below screen shot, i have changed the End date to a different date, but the time is same. What is happening to Excel?  I am getting the same difference in time as before…….

 

Difference in Days

What you need to do is to change the custom formatting to [h]:mm:ss. Add the square brackets to hours.
See the result after updating the custom format.

After updating the format

2. Insert a blank rows in between data

There are a set of rows with data and we need to insert some blank rows in between the rows

 

Before inserting blank rows

Do the following steps

1. Add serial number to the left of the data

 

Add serial number

2. Copy the numbers and paste in below the data
Paste the number below
3. Now sort the data
Sort the data
4. We have done it . Remove the seriel number and delete the column we created for the serial number

Final list
3. Delete the blank rows in the data

 

To remove the blank rows we created earlier, do the following steps

1. Select the first column

 

Remove blank rows in Excel

2. Press Go to – Special – Blank cells (shortcut for Go to – Ctrl+G)

 

Go to blank cells

3. Press Alt+E+D to delete the selected rows

Delete entire rows

4. This will remove the blank rows
Final list

Alternatively you can also use the auto filter to select the blank rows and delete them to remove the blank rows.

 

Share your Tips

Did you enjoy the tips?  Do you know other simple tips that you use in your daily work? Share it with the readers in comments.

Comments

4 Responses to “Useful Tips in Excel – Deleting blank rows, Inserting blank rows, finding out difference in Time”
  1. Michael Viola says:
    June 5, 2011 at 7:48 pm

    Using serial tagging of rows is a great habit for any analysis of data. By getting in the habit of doing first before any manipulation is done in the data, you can always get back to the native form.

    Reply
  2. Jeff Ceccola says:
    June 7, 2011 at 11:15 pm

    Unsure how well known this is but:

    When freezing cells in a formula for ex $A$1, its not necessary to enter dollar sign. Highlight the cell or range of cells you want to freeze and hit F4. That will freeze both column and row. Hitting it twice will freeze row only (a$1) and three times, column only ($a1)

    Reply
  3. Jenny Xu says:
    June 23, 2011 at 1:23 pm

    Rather useful tips,thanks for sharing.^_^

    Reply

Do you have any comments on Useful Tips in Excel – Deleting blank rows, Inserting blank rows, finding out difference in Time ?