Microsoft Project – field to show tasks due to finish this week

I was asked how to create a field that would show the text “Due this week” if the task was due to finish within the next week and leave it blank otherwise.

Go to the Project tab and in the Properties group, click Custom Fields.

From the Type dropdown, select Text, select a relevant text field and click Rename to rename it to something suitable, such as Due this week. Click OK.

Click Formula.

The required formula is

IIf([Finish]-[Current Date]>=0 And [Finish]-[Current Date]<=7,”Due this week”,””)

Click OK twice.

On your table, right-click over the column heading where you want to insert the new column, click Insert column and select the required column from the drop-down list.

You will then see all tasks that are due to finish within the next week.

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

Advertisements

Excel 2016 – Forecast Sheet

New in Excel 2016 is the Forecast Sheet capability which you will find on the Data tab in the Forecast group. This looks at past data and the trends within it, to allow you to forecast what is likely to happen in the future.

Open a workbook containing historic data. Here I am looking at International Visitors to London.

This is quarterly data starting in 2002 and finishing at Q3 2017 (the most up-to-date I could find on the Internet).

Click anywhere within the data set, then on the Data tab, in the Forecast group, click Forecast Sheet. A line chart will be displayed showing your past data and future forecast.

At the top right, you have the option to choose a line chart or a column chart. Towards the bottom, you can either accept their Forecast End date or select your own. Clicking Options brings up a greater list of options.

From here, you can accept their Forecast Start date or choose your own. You can also specify a different Confidence Interval if required. You can allow Excel to automatically detect the seasonality of your data or set it manually and you can also choose whether or not to Include forecast statistics. If necessary change the Timeline Range and the Values Range. In this forecast I am looking at predicted spend, but I could want to predict nights spent or number of visits.

When you are happy with your chosen options, click Create. As well as the chart, you will then have predicted figures.

Here using a 95% confidence interval, it shows the expected forecast spend, the minimum spend based on my data and the maximum spend based on my data.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

 

Microsoft Excel – number of weeks between dates

You may want to know how many weeks it is between two dates.

In the example below, I want to know how many weeks it is since I invoiced them.

I can combine the Weeknum function and the Today function to ensure that the number of weeks changes as the date changes.

The formula would be:

=WEEKNUM(TODAY())-WEEKNUM(F3)

The result will show a date. To change this to a number, on the Home tab, in the Number group, from the dropdown, select General. You will then see how many weeks there are between the two dates.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

 

Microsoft Excel – I’ve hidden column A – how can I get it back?

When you are working with Excel, you sometimes hide rows or columns but may well want to unhide them again. As long as it is not column A or Row 1 you have hidden – it is very easy – just select the columns or rows on either side of the hidden ones, right-click, then click Unhide.

It is not so obvious if you hide column A. In this case, select the whole worksheet (Ctrl + A), then on the Home tab, in the Cells group, click the Format dropdown, then click Hide & Unhide then Unhide Columns. The column will re-appear.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Microsoft Excel – Number of times delegates have attended conference

I have a client that organises an annual conference for its members. Each member has a unique Membership ID. Each conference delegate list is on a separate worksheet. The client wanted to know how they could work out how many conferences each member had attended.

This is an instance where Data Consolidation comes into its own.

Start a new worksheet within the workbook.

Click in cell A1.

On the Data tab, in the Data Tools group, click Consolidate.

From the Function drop-down, select Count.

Check the Left column checkbox.

Select the first year’s ID number and next column, click Add; continue until you have the relevant ranges for all data.

Click OK. You then have a count of how many conferences each member attended. If necessary you can then do a VLOOKUP to get more info about each member.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Microsoft PowerPoint – changing default font and font size in text boxes

I was asked by a client yesterday about whether she could change the default size for text within text boxes. In the most up-to-date versions of PowerPoint, the default font is Calibri, size 18. However, many companies expect employees to use particular fonts for a presentation.

To change font for all text boxes, first create a text box by clicking Insert, then in the Text group, click Text Box; alternatively, to create your text box, click the Text Box   button in the Drawing group on the Home tab.

Then type some text with the font type, size, colour, etc. that you require as the default for your text box font.

Now click on the textbox, right-click and from the shortcut menu that appears, click Set as Default Text Box.

The same font formatting will then be applied each time you create a new text box.

Discover more about our Microsoft PowerPoint training in the London area or our Microsoft PowerPoint training in Wiltshire, Dorset and Hampshire.

Report Writing – Defining the Purpose of a Report

It is vital to be clear about the purpose of the report:

 –       What do you want to achieve?

–       What response do you want from the reader?

–       What are your ambitions in producing this report?

Is the aim of the report to:

•  Inform
• Persuade
• Instruct
• Fulfil contractual obligations
• Build/ enhance profile
• Generate action / make decisions?

   Other?………..

 Find out more about our Report Writing training.

Crystal Reports – inserting missing field headings into page headers

When you drag a field into the Details section in Crystal Reports, the field name will automatically be placed in the Page Header. Occasionally you may place a field in the wrong section of a report and then realise, you want it in the Details section. Moving it will not cause a field name to be added to the Page Header. To get the field name showing, click on the field in the Details section, then click Insert – Field heading. The field heading will be added to the Page Header above its appropriate field.

Find out more about our Crystal Reports training in the London area or our Crystal Reports training in the Dorset, Wiltshire and Hampshire areas.

Microsoft Word – fine-tuning text on mailing labels

When you create mailing labels in Microsoft Word, you may find that the text for the Address block ends up with double spacing. To get round this, before starting your labels, go to the Home tab and in the Styles group, choose the No Spacing style. This will ensure single spacing between the lines of the address.

If you find you cannot see lines to see where each label will start and finish, go to the Table Tools Layout tab, and in the Table  group, select View – Gridlines.

If you want the address to move down slightly on the label, go to the beginning of the address and press the Spacebar and then press Enter. This will give more space at the top of the address.

Remember to press Update Labels to ensure you get multiple labels on one page!

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

Microsoft Excel – creating a 2-D map

In the newest versions of Microsoft Excel it is possible to produce 2- and 3-D maps of geographical data. In this blog post, I look at creating a 2-D map; in a future one, I will discuss 3-D maps.

In this instance, I have Last Year’s Sales figures by Country.

Select the data set with the regional category on the left and the data to be plotted as the second field; if the columns are not next to each other, hold down the Ctrl key to select the relevant data.

Go to the Insert tab and in the Charts group, click on the Charts drop-down and click Filled Map. A map will be displayed. Contextual tabs, Chart Tools Design and Chart Tools Format will also be displayed.

You may want to put the map on a sheet by itself, in which case from the Chart Tools Design tab, click Move Chart and move it to a suitable location.

To add labels to the map, indicating the data, click the + sign to the right of the map, select Data Labels. Suitable labels will be added. You could also add a suitable title to the map.

Find out more about our Microsoft Excel training in the London, Surrey, Middlesex areas.

Find out more about our Microsoft Excel training in the Dorset, Wiltshire, Hampshire areas.