Microsoft Excel – quickly unhide all columns or rows

If you have hidden multiple rows or columns in Excel, it can take a while to work out what you have hidden to unhide again. It is also hard to work out how to unhide column A or row 1!

To quickly unhide all columns or all rows, press Ctrl + A which will select the whole worksheet, then right-click over the data area and click Unhide from the shortcut menu that appears.

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

Advertisements
Posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Leave a comment

Outlook 2013 and 2016 – attachment reminder

It is so easy to forget to add the relevant attachment to an email when you are in a rush or have other things on your mind!

It may be worth adding the attachment before you write the body of the message. However, if you are using Outlook 2013 or 2016 and you include the words attachment or attached in the body of the email and then send the message without attaching the file, you will receive a message asking whether you have forgotten to attach a file. How useful is that!

Also when you go to the Attach File dropdown, you will now see a list of recently used files to easily attach instead of having to browse for the file.

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

Posted in Microsoft Office 2013, Microsoft Office 2016, Microsoft Outlook, Microsoft Outlook 2013, Microsoft Outlook 2016 | Leave a comment

Microsoft Outlook – changing default font for e-mails

Many companies have a specific font that they want people to use for sending out e-mails. To change the default font for your e-mails in Microsoft Outlook, click File – Options. Click Mail to display the mail options. Click Stationery and Fonts to open the Signatures and Stationery dialog box.

Outlook Signatures and Stationery dialog box

In the New mail messages section, click Font. The Font dialog box will be displayed. Choose your desired Font and Size then click OK. If required, do similarly for Replying or forwarding messages.

Click OK.

For any subsequent new e-mails and/or for replies to e-mails, your chosen font and size will automatically be used.

Find out more about our Microsoft Outlook training in the London area.

Find out more about our Microsoft Outlook training in the Dorset, Somerset, Hampshire and Wiltshire areas.

Posted in Microsoft Outlook, Microsoft Outlook 2010, Microsoft Outlook 2013, Microsoft Outlook 2016 | Tagged , | Leave a comment

Microsoft Outlook – do external meeting attendees see colour categories?

An interesting request recently was from a client where they wanted internal staff to know what room a meeting would be in but they didn’t want external attendees to know the meeting room!

The way we got round this problem was to colour categorise the various meeting rooms – actually the client already had the meeting rooms categorised in this way which was very handy!

When an invite is sent out the location can then just be the building location; internal staff will know which room the meeting will be in as their invites will show the appropriate colour category; external attendees will not see the colour coding.

Find out more about our Microsoft Outlook training in the London area.

Find out more about our Microsoft Outlook training in the Dorset, Somerset, Hampshire and Wiltshire areas.

Posted in Microsoft Outlook 2007, Microsoft Outlook 2010, Microsoft Outlook 2013, Microsoft Outlook 2016 | Tagged , | Leave a comment

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.

Custom Fields dialog box

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”,””)

Due Date formula

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.

Due Date field

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

Posted in Microsoft Project, Microsoft Project 2010, Microsoft Project 2013, Microsoft Project 2016 | Tagged , , | Leave a comment

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).

Forecast sheet 1

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.

Forecast sheet2

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.

Forecast sheet3

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.

Forecast sheet4

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.

 

Posted in Microsoft Excel, Microsoft Excel 2016 | Tagged , | Leave a comment

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.

Weeks between dates

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.

Weeks between dates

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

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

 

Posted in Uncategorized | Leave a comment

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.

Posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment

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.

Data Consolidate

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.

Data Consolidate

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

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

Posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment

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  text box icon 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.

Posted in Microsoft PowerPoint, Microsoft PowerPoint 2007, Microsoft PowerPoint 2010, Microsoft PowerPoint 2013, Microsoft PowerPoint 2016 | Tagged , , | 2 Comments