Highlight Dates Over 30 Days Old

I was asked yesterday how to highlight dates that were over 30 days old. The situation was that the date of a transaction is recorded and the team need to be alerted when that date is older than 30 days.

Because they wish to highlight the cell, Conditional Formatting will be used. A new rule will be created with criteria to find those dates older than 30 days. Continue reading

Excel Fixtures and League Table Generator

Due to popular demand I have created an Excel fixtures and league table generator.

Following on from my fixture list creator and league table generator files, I was asked to create a combined file to create a fixture list and also generate a league table from results entered.

Download the Excel fixtures and league table generator.

To use the file;

  1. Enter the teams competing and how many times they will play each other on the “Teams” sheet
  2. Enter the results from fixtures on the “fixtures” sheet
  3. View the league table on the “League table” sheet

Change Presentation Slide Numbering to Start From 0

When inserting slide numbers at the bottom of each slide of a PowerPoint presentation, it is common to remove the slide number from the Title slide. However the numbering begins from number 2 on the second slide.

If you want the slide numbering to start from number 1 on the second slide. You first need to remove the slide number from the Title slide, and then set the presentation slide numbering from 0. Continue reading

Control the Flow of Text with Pagination Settings

Microsoft Word will control the flow of text across pages automatically using a number of settings. These include the paper size, the printer you are using and the margin sizes.

Another factor that will affect the flow of text across pages are each paragraphs pagination settings.The pagination settings are used to control how a paragraph breaks across pages. Continue reading

Perform Quick Calculations in Excel

It is possible to perform quick calculations in Excel without having to write any formulas. This technique is brilliant and provides the formula result you need quickly and easily.

You can count, sum, average and find the maximum or minimum number in a range simply by selecting the numbers you want to calculate. Continue reading

Outline Tasks in Microsoft Project

Outline tasks in Microsoft Project to organise your list of project tasks and make it more readable. Creating an outline requires indenting tasks. The indented tasks are known as subtasks and the task above them a summary task.

These groups of tasks can then be collapsed and expanded making the list of tasks easier to work with.

There are many approaches to organising the tasks in your project. You may decide to group tasks by the phases of the project, the teams who will be doing the work, or in the case of manufacturing a product the different parts that make up the product.

As well as making the list more readable and easier to use, the summary tasks also provide project data at a glance. Information such as the start and finish date of that project phase, its duration, cost and variance is made easily available. Continue reading

Validate an Email Address in Excel

If you collect email addresses in your spreadsheet. You may want to validate an email address in Excel.

Using Data Validation you can check the syntax of an email address to ensure it is legitimate. It cannot check the existence of an address, or who it belongs to. But it will ensure the existence of the @ symbol, a period (.) and no spaces in the address. Continue reading

How to use the Trunc Function in Excel

The Trunc function in Excel is used to reduce the number of decimal places on a number without rounding.

Excel provides a few different rounding functions such as Round, RoundDown and Ceiling to cater for all your rounding needs. However the beauty of the Trunc function is that you can set the number of decimal points for a number without rounding. Continue reading

Create an Organisation Chart in Visio from an Excel Spreadsheet

Got data in an Excel spreadsheet that you want to use to create an organisation chart. Visio makes it easy to create an organisation chart from an Excel spreadsheet.

This feature works very well in business situations to collect data from others who do not have Visio on their machines. By using an Excel template you can collect organisational data and incorporate it in your Visio diagram. Continue reading