Essential Conditional Formatting Tricks

Conditional Formatting is an amazing feature of Excel. It is sure to create a spark of interest and questions during training. People see the potential in their spreadsheets and how easy their team could visualise their data and create engaging forms and reports.

This tutorial looks at the two most requested Conditional Formatting tricks asked by Conditional Formatting enthusiasts.

Continue reading

Create a Fire Evacuation Plan in Visio

It is essential to have a fire evacuation plan. As a trainer I am regularly visiting different offices and other locations. On arrival I need to make myself aware of the emergency evacuation procedures and pass this information onto my delegates.

These diagrams should be kept simple so that it provides clarity at a time when people are not thinking clearly. Too many that I see provide cute but unnecessary detail.

You can create a fire evacuation plan in Visio. Visio provides emergency response shapes that can be added to a floor plan. This diagram can then be printed and displayed in the required areas of a building.

Continue reading

Excel Training – Lookup your Data with the DGET Function

A key skill in Excel training is to be able to lookup and retrieve data from a range of records. The most popular way of achieving this is to use the Vlookup function. The Vlookup function on Excel is awesome and easy to use, but it has its drawbacks.

Cue the DGET function. A very powerful Excel function that will retrieve data from a record without the limitations of Vlookup. Advantages of using the DGET function include:

  • It can retrieve data from a column to the left of the column you searched within.
  • It can lookup data based on multiple conditions.
  • It can handle both AND and OR logic.

Continue reading

Format Footnotes in a Word Document

Footnotes are text displayed at the bottom of a page used mainly for references or comments. When footnotes are added to a document in Word they appear in the default font and size. For Word 2010 this is the Times New Roman font and size 10.

The footnote text and reference can be formatted to be consistent with the documents body text or to improve clarity. The footnote separator can also be changed or removed if no longer desired.

Continue reading

Working with Constraints and Deadlines

A constraint is a restriction on the start or finish date of a task in the project. Setting constraints is useful when external factors affect the start or finish date of a project. For example, vital supplies are not available to begin work on a task until the 10th September.

Every task in a project has a constraint applied to it already – the default As Soon As Possible constraint is applied when scheduling from the project start date, and the As Late As Possible constraint is used when scheduling from the project finish date.

These constraints are often not considered true constraints. They indicate that the task follows the working calendar, duration and task dependencies.

Continue reading

Spell Check a Protected Excel Worksheet

Worksheet protection is used in Excel to protect cells containing formulas, hide sensitive data and much more.

Unfortunately one of the problems that arises from protecting a worksheet is the inability to spell check a worksheet.

To be able to spell check a protected worksheet you need a macro to unprotect the sheet, perform the spell check, and then protect the sheet again. Continue reading

Christmas Movie Quiz Spreadsheet – Test your Knowledge

Think you know your Christmas movies? Why not try this Christmas movie quiz.

There are 20 movies to recognise from a picture. Select the film from the list below the picture. A running total is kept at the top of the sheet.

Download the Christmas movie quiz spreadsheet.

Continue reading

Count the Number of Words in a Cell

Working recently at a large Internet company they needed to find how many words were in a cell. This was because they had imported hundreds of thousands of keywords that customers had used to find their site through search engines.

To analyse this data they wanted to count how many words were in each cell containing keyword searches. provides many text functions for managing and manipulating the text in the cells of your spreadsheet. The following formula did the job.

Continue reading

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