Microsoft Word – fitting document on one page for printing

Last week, I was asked how to get a Word document to just fit one page. It is not obvious!

What you need to do is go to File – Options – Quick Access Toolbar.

From the dropdown at the Choose commands from box, select All Commands.

Scroll down in the section below until you get to Shrink One Page. Click Add. Click OK.

The Shrink One Page button will then be added to your Quick Access Toolbar. Next time, you want a document to fit on one page, click on the button and it will do it for you.

Hope this helps.

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

Advertisements

Microsoft Excel – the IFS function

Are you using Office 365? If so, instead of a nested IF, you can use the new IFS function.

Consider the example below:

We would generally use a nested IF to work out the exam score, the formula being:
=IF(B2>80,”A”,IF(B2>70,”B”,IF(B2>60,”C”,”FAIL”)))

Now, using Office 365, we can use an IFS function, where the syntax is
=IFS(logical test1, value if true, logical test2, value if true, logical test3, value if true, ……)
so in this case:
=IFS(B2>80,”A”,B2>70,”B”,B2>60,”C”,B2<=60, “FAIL”)

Find out more about our Microsoft Excel training or our Office 365 training in the London area.

Find out more about our Microsoft Excel training or our Office 365 training in Wiltshire, Dorset, Hampshire or Somerset.

Microsoft Excel – working out school year from date of birth

School years run from September to August, so in general it depends on both the month that a child is born in as well as the year.

So if you have a list of dates of births and need to work out the school year that a child would be in for the next school year i.e. 2019 – 2020, what formula would you need?

An IF statement will solve the issue. We need to see whether the month of someone’s date of birth is less than or equal to 8.

The formula to use is

=IF(MONTH(A2)<=8,2019-YEAR(A2)-4,2019-YEAR(A2)-3)

This works well for the school year beginning Sep 2019 but what if I wanted a formula that would work for any school year in the form of Sep 20xx to Aug 20xx+1.

If we put the school year in the form 20xx – 20xx+1 in the top cell of a column.

The formula would become

=IF(MONTH(A2<=8),LEFT($C$1,4)-YEAR(A2)-4,LEFT($C$1,4)-YEAR(A2)-3)

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

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

Microsoft Project – easily change constraint types and dates

By default, providing you schedule from the project start date, all tasks are constrained to start as soon as possible. However, there are seven other constraint types and if you are changing the constraint type, you also need to add in a constraint date.

You can do this for individual tasks, by double-clicking on the task name to open up the Task Information dialog box and going to the Advanced tab and selecting the relevant Constraint type and Constraint date.

However, if you need to do this for several tasks, it may be easier to insert two extra columns, Constraint Type and Constraint Date. (To do so, right-click on the column name to which you would like them inserting at the left, then click Insert column.)

Once you have finished amending your constraint types and dates, you can then hide the columns again.

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

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

 

OCD – intrusive thoughts

This is not something I would generally write about in this blog but “If Only I’d Known That” I may have saved my son from some very troubled teenage years. When people think about OCD, they think about people who are obsessed with cleanliness or whether they have looked the door. What a lot of people don’t realise is that there is a form of OCD where people have very disturbing intrusive thoughts. Many people with this condition suffer for many years without telling anyone as they are so ashamed of their thoughts and have no idea why they feel this way.

Our son suffered in silence for years.

He had recently given us a book ‘Pure – A Memoir’ by Rose Bretecher to help explain what he has been through – it can be found on Amazon at

https://www.amazon.co.uk/Pure-Rose-Bretécher/dp/1783521120

I wrote a quick review of it here:

https://www.goodreads.com/review/show/2683508416

“My son gave me this book to explain what he had been through. He didn’t have the courage to talk to us about it but can understand why not. It has helped me to come to a realisation of what he went through as a teenager. Well done to Rose for having the guts to write this book. It should be available in all secondary schools as there is so little knowledge about intrusive thoughts and those suffering think they are the only one. “

An adaptation of the book is  going to be televised next week and some of those involved with youth may be interested in watching so they will have an understanding. Mental health is not in general talked about in schools.

Link to bit about TV showing:

 https://inews.co.uk/culture/television/pure-channel-4-cast-trailer-when-start-pure-o-ocd-explained/

If only I had known about the Intrusive thoughts syndrome within OCD, perhaps I would have been able to help our son a lot more and saved him a lot of suffering.

Microsoft Excel – conditionally formatting a whole column range if any cell in that column is blank

This is a question that we were posed at training recently.

First select the data area to be formatted. In the example here, I have selected B2:D7.

Then on the Home tab, in the Styles group, from the Conditional Formatting dropdown, click New Rule.

Select Use a formula to determine which cells to format.

The formula in my case is:

=COUNTBLANK(B$2:B$6)>0

Click Format.

Select your required formatting, then click OK twice.

The columns containing blank cells will be highlighted.

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

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

Microsoft Excel – using icon sets to compare two sets of figures

If you are looking at data over time, you often want to see visually whether numbers have gone up, down or remained static.

Create a third column to show the difference between the two sets of data, in this case =C3 – B3, then on the Home tab, in the Styles group, go to the Conditional Formatting dropdown, go to Icon Sets and select your preferred choice of icons.

This will not at present give you exactly what you want, so now go to the Conditional Formatting dropdown and select Manage Rules. Click Edit Rule and adapt as shown below.

Click OK twice.

You should get the required icons.

 

If you only want to see the icon and not the difference between the columns, in the Edit Formatting Rule dialog box, check the Show Icon Only box.

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

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

 

Microsoft Excel – Don’t want to see Page Breaks?

I always thought it was good to see the Page Break dotted lines in an Excel worksheet – they are shown if you have gone to File – Print or to Page Layout view or Page Break Preview. However, one client this week said he found them a nuisance because he often wanted to print on an A3 printer.

To get rid of the dotted lines, for either the worksheet you are working on, click File – Options and go to the Advanced tab. Scroll down until you find the section on Display options for this worksheet. Clicking the dropdown there will allow you to select the whole workbook instead of just the relevant sheet. Get rid of the tick by Show page breaks.

Click OK. There will no longer be any page breaks displayed for that worksheet or for the whole workbook depending on what option you have selected.

Alternatively for a one-off quick fix, go to Page Break Preview, then click the Undo button or click Ctrl + Z. The page breaks again disappear.

Find out more details of our tailored Microsoft Excel training in the London area.

Find out more details about our tailored Microsoft Excel training in Dorset, Wiltshire, Somerset and Hampshire.

 

Microsoft Excel – pictures not moving with sorted or filtered data

Some organisations have pictures of products in their workbooks. A client I visited this week was complaining that sorting and filtering data did not move their pictures with the data.

To overcome this, select the first picture, then click Ctrl + A, which will then select all the pictures.

On the Picture Tools Format tab, go to the Size dialog box launcher. This will open up the Format Picture task pane.

Click where it says Properties and select Move and size with cells.

Now if you sort or filter the data, the pictures will move accordingly.

Find out more details about our tailored Microsoft Excel training in the London area.

Find out more details about our tailored Microsoft Excel training in Dorset, Wiltshire, Somerset and Hampshire.

Outlook Online (OWA) – setting email signature

If you want to set up an email signature in Outlook Online, click the Settings button – the one that looks like a cog, then scroll right down to the bottom and click Mail. A list of options will appear on the left hand side of the window. Scroll down until you get to the Layout section and then click Email signature.

Check the appropriate boxes as to whether you want to Automatically include my signature on new messages I compose and/or Automatically include my signature on messages I forward or reply to. Type in your signature in the main box, formatting as required. Click Save.

Your email signature is now set up.

Find out more about our Office 365 training in the London area.

Find out more about our Office 365 training in Dorset, Wiltshire, Hampshire, Somerset.