Microsoft Excel – using wildcards with numbers in Data Validation

I was at a client recently and they wanted to limit data input in an Excel column to have a number starting with 4 and then five numbers after it.

I have worked out a way of doing this using Data Validation.

First, select the data range to which the data validation rule is to be applied and format it as text – Home tab – Number group and from the dropdown where it says General select Text.

Then with the data range still selected, go to the Data tab, then in the Data Tools group, select Data Validation. On the Settings tab, from the Allow dropdown, select Custom.

In the Formula box, the formula in this case is =COUNTIF(C1, “4?????), where C1 is the first cell in the selected range and I want the number to be 4 followed by 5 other numbers.

Click OK.

Although it will be formatted as text, if these are part numbers they are never going to have calculations done on them so works well for data input.

Find out more about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Somerset, Dorset and Hampshire.

Microsoft Project – creating link lines on Gantt Chart itself

I was asked at my last Microsoft Project training session whether tasks can be linked on the Gantt Chart itself rather than in the Entry table.

Although it is not my preferred way of working, it can be done.

Type tasks and durations in your Entry table first. Then on the Gantt Chart, select the successor task and drag it to where you feel it should start in relation to the predecessor task. You will then get a dialog box as shown:

You can create the link by selecting Link them. Then click OK.

If you drag a bar further it will allow you to keep the link but instead of adding lag time between the tasks it will instead create a Start No Earlier than constraint.

So it can be done, but I don’t find it the easiest way!

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Wiltshire, Dorset, Hampshire and Somerset.

Microsoft Project – changing colour of Deadline arrow on Gantt Chart

When you set a deadline for task in Microsoft Project, a green downwards facing arrow appears on the deadline date on the Gantt Chart. A client recently asked whether the colour could be changed – they felt that since it was a deadline, it might be more appropriate if the arrow was red.

If you double-click on the arrow in the Gantt Chart, the Format Bar dialog box will be displayed for the deadline arrow.

In the Start section, change the Colour to whatever you like. You could change the shape if you really wanted to as well. Click OK.

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Dorset, Somerset, Wiltshire and Hampshire.

Microsoft Office 365 – Quick Access toolbar – command labels

In Office 365 and the latest version of Microsoft Project, you can choose to show the Quick Access Toolbar above or below the ribbon.

To do so, click on the drop-down to the right of the Quick Access Toolbar.

If it is presently below the ribbon and you want to move it above the ribbon, click Show Above the Ribbon.

If it is currently above the ribbon and you want to move it below the ribbon, click Show Below the Ribbon.

If you choose to show it below the ribbon, you now have an additional option – Show Command Labels or Hide Command Labels. If you choose to Show Command Labels, you will see something like this, depending on what commands are on your Quick Access toolbar.

If you choose Hide Command Labels, you will just see the small icons.

To add any button to the Quick Access Toolbar, right-click on it and click Add to Quick Access Toolbar.

Find out more about our Microsoft Office training in the London area or our Microsoft Office training in Wiltshire, Somerset, Dorset and Hampshire.

Microsoft Office and Microsoft Project – removing border from Text box

I was working with a client in Microsoft Project recently and we had created a text box on the Gantt Chart to put a comment in as to why a task had been delayed. I was then asked how to remove the border from the text box.

Whether working in Microsoft Project or Microsoft Office, the border from a text box can be removed as follows.

In Microsoft Project, right-click on the edge of the text box and from the menu that appears, click Properties. The Format Drawing dialog box will be displayed.

On the Line and Fill tab, in the Line section, click None. Click OK. There will no longer be a line around your text box.

In Microsoft Office, right-click on the edge of the text box and from the pop-up menu that appears, click Format Shape. The Format Shape pane will appear to the right of your window. Click Line. Select No Line. There will no longer be a line around your text box.

Find out more about our Microsoft Office training in the London area or our Microsoft Office training in Wiltshire, Somerset, Dorset and Hampshire.

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Wiltshire, Somerset, Dorset and Hampshire.

Microsoft Project – inputting half a day of leave

When putting in holidays for individual resources, double-click on the resource name in the Resource Sheet to open the Resource Information dialog box.

Click Change Working Time. On the Exceptions tab, type Half day (or equivalent).

From the Start dropdown, find the date of the half day holiday, then click Details.

Click Working Time, delete the second row of information, and adapt the first row to be the hours they will actually be working on that day.

Click OK twice. They will then be on a half day of work on that date.

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Project – assigning work as overtime

If a resource is overallocated in a project plan within Microsoft Project, one of the ways to get round the overallocation is by assigning the work as overtime work.

To assign work as overtime, from within the Gantt Chart, click on the task that you want to allocate overtime work to, go to View and then in the Split View details, check the Details box. The Task Form will appear at the bottom of the screen. Right-click to the right of this form and click Work.

Having previously looked at the Task Usage view, I know that the Project Manager is overallocated by 2hrs work as they are supposed to be at a meeting at the same time. I therefore enter 2h into the Ovt. Work field and then click OK.

The Project Manager is no longer overallocated and when I look at the Task Usage or Resource Usage tables on a daily basis all looks fine. However, the client I was working with has short projects and looks at durations in terms of hours. When we do this it looks as though the Project Manager is working 3 hours between 11 and 12 on one day! For most people’s purposes it probably doesn’t matter where the overtime is shown but it may do from time to time.

Trying to move the numbers in the Work cells has no effect.

In a case like this, do not enter Ovt. Work in the Task Form. Instead in either the Task Usage view or the Resource Usage view we need to add the Ovt. Work field. To do this, right-click over the right-side of the window and from the pop-up menu, click Detail Styles.

From the Available fields list, select Overtime Work and click Show. Click OK.

We can see at exactly what time Project thinks the Project Manager is overallocated. Now we can move things in the Work cells to our own devices.

I have now moved the additional work to the end of the day. It still shows as being overallocated but I have made a judgement call as to when in the day the extra work should be done.

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Project – Flag field default change

When you use a Flag field in Microsoft Project, the default setting is N. I was asked recently if this could be changed to a blank. It can’t if you use a Flag field.

However, you could instead create a Text field and just have the Value X which can be found from a dropdown, otherwise the cell will be left blank.

To do this, on the Project tab, in the Properties group, click Custom Fields. The Custom Fields dialog box will be displayed.

Ensure Type is set to Text then in the Field selection, click on an unused Text field and click Rename to give it a sensible name. Click OK.

Now click Lookup. Type Y as the Value on the top row. Click OK twice.

Now insert the column into the relevant table in your project. Everything will initially be set as a blank. There will be a dropdown from which you can select Y as required for the various tasks.

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Wiltshire, Dorset, Somerset and Hampshire.

Microsoft Project – filtering by constraint type

I was working with a client recently and they were editing a project plan that had already been set up. It was difficult to see at a glance what constraints had been added to the various tasks.

To see the constraint types altogether. you can insert the Constraint type field into your plan – you can easily hide it again afterwards.

On any column heading, right-click, click Insert Column, then from the drop-down list select Constraint Type.

At the top of the column, there is also a Filter dropdown, so you can filter by Constraint Type to better understand how decisions were made.

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Dorset, Wiltshire, Somerset and Hampshire.

Microsoft Project – Saving large plans to pdf taking too long?

I was training at client site this week and they mentioned that if they tried to use File – Save As with file type pdf or File – Export to save a large project plan as a pdf, it takes ages. It could in fact be their Internet or server speed as interestingly I tried it at their offices and it did take ages but when I got home I tried again and it didn’t take too long at all!

However, if that happens to you, you could instead trying printing to pdf. This seemed to take less time. To do so, click File – Print, then from the Printer dropdown choose Microsoft Print to PDF or the equivalent. This should speed up the process.

Find out more about our Microsoft Project training in the London area or our Microsoft Project training in Wiltshire, Dorset, Somerset and Hampshire.