Use the Offset Property in VBA

Excel VBA offers many different ways to refer to ranges in a worksheet. One very popular way is to use the Offset property of the Range object.

The Offset property allows you to move around a worksheet with ease using ranges relative to your current position, or to a specific range.

The Offset property takes two arguments. These relate to the upper left hand corner of the specified Range object. The first argument is the number of rows to offset, and the second is the number of columns. Continue reading

Dynamic Grid in Visio 2010

Visio 2010 makes it even easier to create drawings, and one of the main reasons for this is the improvements to the Dynamic Grid.

The Dynamic Grid assists you with the sizing, aligning and spacing of shapes in relation to one another. It is an extremely useful tool, and one that takes arranging shapes to another level of simplicity. Continue reading

Convert Text into a Date Format

If you import dates into Excel from another system, or from the web, they may appear in Excel using a text format. This looks great but will not provide full functionality. Unless you can convert the text into a date format you will not be able to use that data in formulas, PivotTables and other Excel features.

Fortunately Excel provides a function that can convert text into a date format, and it is very easy to use. Continue reading

Use Filters to Analyse Project Data

Microsoft Project has two ways of filtering its’ tables. There is the AutoFilter, similar to what you would see in Excel, and there is the in-built filters.

Filtering allows you to perform some simple analysis of your project data. It can be used to find which tasks begin this week, which tasks have slipped, or the most expensive tasks. Continue reading

Find the Weekday of a Date using Excel

When you enter a date into Excel, Excel will format it using its default date format. Excel provides plenty of alternative date formats for you to choose such as 14.03.01 and 14 March 2001.

We want to use a format that will tell us the weekday of the date, whether it is a Monday, Tuesday etc.

Formatting the Date

Type a date into a cell of the spreadsheet. This can be any date you want post 01/01/1900. It can be the date you were born, date you were married, or the date the 2012 Olympics starts. For this example, I will use Christmas day 2001

  1. Select the cell where the date was entered
  2. Open the Format Cells dialogue box. In Excel 2003 click Format > Cells, or in Excel 2007 click the dialogue box launcher arrow in the corner of the Number group of the Home tab. You could also press Ctrl + 1
  3. Select the Number tab, if necessary
  4. Select Custom from the Category: list
  5. In the Type: box, replace its contents with dddd

Format the date to show a weekday

  1. Click Ok

The weekday is now visible in the cell. Notice how the Formula Bar still displays the date as 25/12/2001. Formatting cells changes the values appearance, but the cell still contains the original value.

View the weekday of a date

Three Unknown Uses of the AutoFilter Tool

The AutoFilter tool in Microsoft Excel enables you to filter a large list of data to see only the records you want to see. It is a popular everyday tool for working with large Excel lists and helping you to find records and analyse your data.

This tutorial looks at three relatively unknown uses of the AutoFilter tool. Continue reading

How to Trim a Video in PowerPoint 2010

The ability to insert video into presentations has always been an incredibly useful feature of Microsoft PowerPoint. PowerPoint 2010 now handles more video formats than ever before including MP4 and Flash movies.

You can even embed online videos from sites such as YouTube, MetaCafe and Daily Motion into your presentations in PowerPoint 2010. All you need is the embed code available from the video publisher.

The improvements to using video in PowerPoint 2010 do not stop there though. One brilliant new addition to the wealth of new video editing features is the ability to trim a video.

Trimming a video enables you to use a specific section of a video. You can cut off time at the start and end of a video. And it really could not be easier to do.

Trim a Video in PowerPoint 2010

  1. Select the video in the presentation that you want to trim
  2. Click the Playback tab on the Ribbon
  3. Click the Trim Video button in the Editing group
  4. The Trim Video window appears. The filename and video duration are shown at the top.
  5. To trim the video you can either click and drag the required marker at the beginning or end of the video timeline, or enter a time in the Start Time or End Time boxes
  6. Click Ok

When the video is now played it will start and end at the times specified.

Automatically Update Fields on Opening a Document

To take advantage of some of the more advanced features of Word, you require the use of fields. Table of contents, cross references, captions, formulas etc. all use fields to provide automation to the document.

When a document changes these fields need to be updated. This can be done by pressing the F9 key, or by right clicking on the required field and choosing Update Field.

If the document changes regularly, or is large and time consuming to keep track of the many fields used, it could be a good idea to set Word to automatically update the fields on opening the document. Continue reading