Excel Macros Tutorial – Remove Blank Rows Macro

Blank rows in your Excel spreadsheet are undesirable and can make it difficult to work with. Because of this, Excel provides many ways that we can locate and then delete the blank rows.

These techniques can include sorting the list to group the blank rows together, filtering for blanks and using Go To Special to locate blank cells for deleting.

However these can be time consuming and may not be the best approach for you. For example, sorting the list will change the order of the list. You may not want this.

By creating a macro to delete the blank rows of a spreadsheet this process will be effortless, and can also be performed by anyone when the macro is made available with a button. In this Excel Macros tutorial we show you how.

Continue reading

PDF to Excel Conversion Tool

Last week I received an email regarding a PDF to Excel conversion tool. I’m regularly asked about converting PDF docs to Excel so thought I would check it out.

It works very well and has the following benefits:

  • 100% free
  • Safe and easy to use
  • Unlimited number of conversions
  • Hassle-free: no need to buy or install anything on your computer
  • Fast and accurate conversion results

You can learn more and test it here http://www.pdfconverter.com/pdf-to-excel-online-free/

Basic Excel Skills – Insert a Happy Face using the IF Function

I was asked today if it was possible to write an IF function that could insert a happy face if certain criteria was met. This would be used to show good performance. An unhappy face would be inserted for poor performance.

To insert a happy face in Excel we need to enter a capital J and format the cell in a Wingdings font. For an unhappy face, it’s a capital L formatted in the Wingdings font. Continue reading

Lotto 6/55 Number Generator Spreadsheet

Following my Excel lottery number generator video I have had frequent requests for a Lotto 6/55 version. So here it is.

This spreadsheet demonstrates some fantastic Excel techniques, and as always the file is unprotected so that it can all be investigated.

The following Excel features are used:

  • The RAND function is used to generate the random numbers
  • The RANK and COUNTIF functions are used to rank the random numbers from 1 to 55 for retrieval of the balls
  • The Vlookup function is used to retrieve the ball numbers
  • Some VBA is used to perform the formula calculations when a button is clicked (Manual calculation has been chosen over automatic for the workbook)

Download the Lotto 6/55 number generator spreadsheet.

Excel Templates – Mileage Claim Form

Many people use Excel to create forms such as invoices, holiday requests and mileage claim forms. Excels’ various tools to enable calculations, powerful formatting and validation rules make it perfect for form creation.

This post provides a mileage claim form template that demonstrates many useful Excel features in all their glory. Please download and check it out.

Excel templates – Download the mileage claim form template.

The spreadsheet has both worksheet and workbook protection applied for improved functionality. It can easily be unprotected and explored though as no password has been used.

This spreadsheet makes use of the following Excel features;

  1. Data Validation has been used to create a drop down list of offices to select from, and also to select either own car or company car.
  2. A range name is used so that the Data Validation tool can access the list of office on the hidden sheet.
  3. The Vlookup function is used to return the distance in miles of the selected office
  4. If functions are applied to remove the #N/A error message from the Vlookup when the form is blank, and also to control when the calculation of expense is applied.
  5. Workbook protection is used to prevent the hidden sheet from being shown (however there is no password on the download so you can unhide it)
  6. Worksheet protection is used to lock the cells containing formulas and to generate a tab order making the form more user friendly to complete.

Learn Excel Basics – New Baby Checklist Spreadsheet

My wife and I are expecting our first child in just 5 days now. It’s a very exciting time in our household and it has taken a few months to get everything ready for the newborn.

To ensure I have everything I need, I made a checklist (in Excel of course) and worked my way through it.

If you want to learn Excel basics, this spreadsheet features a few fantastic tools that I thought you may be interested in seeing.

Download the new baby checklist.

  1. Check boxes to make it easy for the user to mark of items as they go
  2. Conditional Formatting to cross out the items with a strikethrough when it is checked
  3. A Thermometer Chart to show progress through the list and how close it is to completion (See how to create a thermometer chart in Excel).
  4. The COUNTIF function to count the items as they are checked off

The items in the checklist have come courtesy of www.newparent.com.

Excel Tips 2010 – Where is the Excel 2010 Data Entry Form?

The popular data entry form from Excel 2003 was removed from the Ribbon in Excel 2010. I am often asked where this feature went. So what is this feature and where can it be found? Find out in this Excel tips 2010 guide.

What is the Data Entry Form?

The Excel 2010 data entry form makes it easy to;

  • Add new records to a table
  • Edit and delete records from a table
  • Search for records in the table

The data entry form is very user friendly and is a must for anyone who performs alot of data entry, especially in a large table.

Continue reading

Locate the Locked Cells on a Worksheet

If you wish to protect the cells on a worksheet that contain formulas you will need to lock the cells first, whilst also ensuring that the cells users should be able to change are unlocked.

It may have been a while since you worked on this worksheet and you are not sure which cells are locked and which are not. You need a fast method of locating the locked cells. Let’s use Conditional Formatting to highlight the cells that are already locked so they are easy to identify. Continue reading

Unleash the Power of Excel’s CELL Function

A very powerful function in Excel that is unknown by many is the Cell function. Use the Cell function to find information about a cell. This can include the cell’s number format, row number or whether it is locked or protected.

There are times when this kind of functionality becomes extremely useful. Especially whilst Excel does not provide many other means of finding the number format of a cell, or whether it has been locked. Continue reading