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.

Advertisements

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.

Microsoft Excel – copying and pasting between two filtered workbooks

I was at client site yesterday and one learner said that they were having great difficulty copying and pasting information between two filtered workbooks.

I said he should use Find and Select – Go To Special – Visible cells only before copying the data – however we found that didn’t work but only filled in 4 cells!

So back to the drawing board.

What we did instead was:

1. Made sure the relevant filter was applied to both workbooks.
2. Click in the cell we wanted the first of the pasted answers, typed an = sign, then clicked in the cell in the other workbook. So my formula in this case looked like:
   =[Orders2.xlsx]Orders!$H$13
3. We need to be able to copy down from row 13 to row 14 etc, but just work in column H so we want to get rid of the $ in front of the 13 (mixed reference).
4. Copy that cell, then select all the required cells that you want to paste to in that column.
5. Go to the Paste dropdown and select Paste Formulas from the dropdown list. The cells will be filled as required.

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.

Working with Microsoft Teams – FAQ

How do I make a public team private or vice versa?

Go to the team name and click More options – Edit team. In the Edit team dialog box, under Privacy, select either Public or Private.

How do I rename a file?

With the file open, go to File – Save As – Rename.

What does Wiki stand for?

What I know is.

How can I add Planner to my Team?

Where it says Conversations, Files, Wiki, click the + sign and select Planner from the available apps.

How do I forward an email to a Team site?

In order to forward / send an Email to an MS Teams Tab you need to know what the Email address is. Find the Tab that you’d like to Email to, find the ellipsis and then select “Get Email address”.

Make a copy of the email address.

Forward the email.

It shows up in the Conversations Tab.

Any attachments are extracted and saved to the SharePoint document library for the channel.

How do I make an Office 365 group into a Team?

In Teams, choose to add a team and you’ll see an option to add it to an existing private group.

Can I have a Team Calendar

Because each team has a SharePoint site, you can set up a Calendar app in the related SharePoint site. Go to the site, then click on the New button towards the top of the page (Office online version), select App from the dropdown list, then find the Calendar. You now have a team calendar of upcoming meetings, deadlines or other events. Calendar information can be synchronised with Microsoft Outlook or other compatible programs.

Find out more about our tailored Office 365 upgrade training in the London area or our Office 365 upgrade training in Dorset, Hampshire, Wiltshire and Dorset.

 

Project 2016 – recurring tasks issues

I was training Microsoft Project recently and when we input a weekly recurring task to our project plan, most computers crashed. This is on a specific release of Project 2016. You should be able to obtain a patch/update but we came across a suitable workaround. Assuming you are working a 5 day week, insert a recurring task to happen on a daily basis, but set that it occurs every 5 working days.

This did not cause the system to crash!

Find more details of our Microsoft Project training courses in the London area or our Microsoft Project training courses in Somerset, Dorset, Wiltshire and Hampshire

Microsoft PowerPoint – entering text in Outline view

If you are a speedy typist, you don’t want to have to keep reaching for the mouse every time you want to move from a title to a bulleted list on a PowerPoint slide, or even from one slide to the next. Using the Outline pane saves you using the mouse.

To see the Outline pane, click the View tab, then in the Presentation Views group, click Outline View. The Slides pane will then display an outline of the content of your slides.

One you have typed a title, pressing Ctrl + Enter will take you to the sub-title or content level depending on type of slide chosen. In a bulleted list, press Enter at the end of each item, to move you down to the next bullet.

To move to a new slide, press Ctrl + Enter.

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

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

 

Microsoft PowerPoint – inserting several pictures all same size

When working with Microsoft PowerPoint, you may want several pictures on a slide to all be of the same size. Insert your first picture, then on the Picture Tools Format tab, in the Size group, select your required size, or open up the dialog box launcher to select a specific % of the current size.

Now duplicate the picture by selecting it and using the keyboard shortcut Ctrl + D.

Now with the second picture selected, on the Picture Tools Format tab, in the Adjust group, click on the Change Picture dropdown, then select one of From a File, From Online Sources or From Icons. Select your required picture, then click Insert. Your picture will be inserted into the slide at the same size as the initial picture.

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

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