Office 365 Archives

Microsoft 365 Apps Certification Update

Microsoft Excel Flash Fill

Excel – Flash Fill

Sometimes, half the problem is getting data into Excel so that you can work on it. You run a query on a database and get a mishmash of poorly formatted data. Hundreds of rows full of data you don’t want mixed with the few odd bits that you do. Been there? This is where you need Excel 2013’s Flash Fill to come to your rescue.

To make sense of your data, type in a few row’s as an example of what you need and then click Flash Fill to do the rest of the work for you. Excel applies the lessons learnt from your examples to the rest of the data, with all the columns below filling with the correctly formatted data. No complicated formulas and no macros required. And no retyping!

You can extract simple patterns from your data, for example extract the first name from a full name or you can extract multiple patterns. For example, you can get the date, the business name and the amount from a credit card statement. All by typing a couple of examples for Excel to use as a template.

The Microsoft Excel Flash Fill control is on the Home tab of the ribbon in the Fill group. It is also found in the Data Tools group on the Data tab. And, of course, you can add it to your Quick Access Toolbar if you find yourself using it frequently.

Flash Fill Example

Here’s a simple exercise; joining first and last names together in the same cell. Type the example “John Smith” into column C and then click Flash Fill to do the rest.

That’s an easy one. If you know Excel formulas you could do that as
=A1&B1 or =CONCATENATE(A1, B1)

Let’s try the Microsoft Excel Flash Fill challenge, a really hard one. In this exercise we need to parse the data in the first column so that a series of numbers appearing after a series of letters is identified and the text after the series of numbers is extracted.

I am getting a headache just thinking about how I would do this one with a formula…

Begone with your complicated formulas and Text-to-Columns routines. Type-in a couple of examples and let Flash Fill take over. The results are seriously impressive.

Try doing that with a formula. I’ve still got that headache.

Let’s try one more; here we need to remove the two-letter prefix “PR” from the front of the entries in column B and replace it with the first

two letters of the country entered in column A. As capital letters.

Enter the first item in column C to set the pattern and then click Flash Fill to do the rest. Fantastic!

Well, I think that we could have done this one using the LEFT, RIGHT and UPPER functions to extract and convert the relevant data. Then concatenated the results to produce the new transaction code.

But the point is, we don’t have to do that sort of thing any more and I for one will not miss it at all. This is the kind of machine learning and artificial intelligence that we all appreciate.

Related Courses

View Excel Training Courses – Link

Switching Excel Columns to Rows

Switching Excel Columns to Rows. You should really call this Transposition if you want to impress. Excel data can be rearranged from columns to rows and vice versa. You can transpose as many rows or columns as you like all in one go. You need to decide whether you want to do the transposition just once or have the transposed data update to reflect any changes made to the original.

Static Transposition

Static transposition is where the data is rearranged just once and it’s really easy to do. However, dynamic transposition, where you have two sets of Excel data; one arranged in columns and the other in rows, is much more difficult and involves your entering an array formula.

If you know how to Copy and Paste then you’ll find static transposition a breeze. The copy bit is as normal but there is a variation to the paste bit. Select the original range of cells and Copy them. Then click a blank cell that is away from the original range and Transpose; there is no need to select the entire range for the transposition, a single cell is all you need.

Finding the Transpose command depends on which version of Excel you are using. If you have a modern version of Excel with the fancy ribbons then you should be able to find Transpose in the Paste control on the Home tab or in the shortcut menu when you right-click.

Should you have one of the good old fashioned versions with the drop-down menus then look for Paste Special which is found in the Edit menu. Failing that right-click after you have done your Copy and you may very well find Paste Special in the shortcut menu.

When the Paste Special dialog appears you need to find the Transpose check box, give it a click and then click the OK button. Sounds easy doesn’t it? But I often find myself staring at the screen muttering “now, where’s that Transpose thingy…”. Because it’s right down the bottom, where it always has been.

Dynamic Transposition

In the previous example we transposed our data and ended up with two independent ranges of cells, one of which you would probably want to delete. You might want to keep both ranges and have the transposed data change when changes were made to the original. This where you need to have a formula. The most painful method would be to go through each cell, enter an equals sign and click the corresponding cell in the original range. Very tedious indeed.

A much better method would be to create an array formula using the TRANSPOSE function. Array formulas are not easy to enter and most sensible people run screaming from the room at the mere mention of them. So don’t get annoyed if this formula takes a few goes to get right. Like most Excel formulas you have to persevere and suffer for a bit until you feel confident.

Entering an Array formula

Array formulas are entered into ranges of cells in one go. They are not entered into single cells and then copied which is what we are used to. You select the range, enter the text of the formula and finally, press CTRL+SHIFT+ENTER on your keyboard to enter your formula into the selected range.

The first job is to count the number of rows and columns in the range that you wish to transpose. Then select a range of empty cells whose dimensions correspond to the inverse of the original range. For example, I want to transpose the range D4:G6, which is a range with 5 columns and 3 rows, so I select an range of 3 columns by 5 rows.

Now we enter the required formula. The formula is as follows “=TRANSPOSE(D4:G6)”. Then, holding down the CTRL and SHIFT keys, press ENTER or click the Enter box in the formula bar.

If you have a version of Excel that pops up the list of functions as you type then you can accept TRANSPOSE from the list by pressing the TAB key. Array formulas are identified in the formula bar enclosed in braces (the squiggly brackets) but you do not type in the braces when you enter the formula.

Working with Array formulas

You may not change part of an array formula. If you want to delete your formula, select the entire formula first before pressing the Delete key. To edit the formula there is no need to select the whole array first but don’t forget to press CTRL+SHIFT+ENTER to accept the edit.

The Excel shortcut key to select the current array is CTRL+/ (front slash). If you have a huge transposition just click one cell and then the short cut key will select the rest of it for you.

When you are counting the number of columns or rows in a large range it is all too easy to lose count and very frustrating when you have to start over again. “One, two, three, four… ” Such fun. Use the Excel functions ROWS or COLUMNS to calculate the dimensions of large ranges rather than count them. For example, the formula =ROWS(A1:D50) returns the value of 50.

Transposition formulas are not the easiest of formulas to get right but, like all formulas, once they are done they will look after themselves and update automatically.

Any changes made to the original range are immediately reflected in the transposition.

Related Courses

Microsoft Excel Intermediate – Link

Microsoft Excel Advanced – Link

Training Courses

If you’ve still got that “I just don’t know what I’m doing” feeling then you might like to arrange an Excel training course for yourself or with some of your colleagues. It’s really easy to book one of our courses and they’re great value for money. See our website for full details.

Switching Excel Columns to Rows by Mouse Training London

Office 365 Groups

Microsoft Office 365 Groups

What are Office 365 Groups?

Microsoft Office 365 Groups lets you to collaborate with your teammates when writing documents, creating spreadsheets, working on project plans, scheduling meetings and sending email. Office 365 release 2019 is a new service available to 365 and 2019 Outlook users.

Groups lets you choose a set of people that you wish to collaborate with and also set up a collection of resources for them to share. For example; a shared Outlook inbox, a shared calendar or a document library for collaborating on files.

You don’t have to manually assign permissions to all these resources because you automatically give the required permissions when you add members to the group. Any member of the group can use the tools that your group provides. Groups are the new and improved method for what we used to do using distribution lists or shared mailboxes.

Office 365 Groups can be created from:

Outlook on the web
Outlook Mobile
SharePoint
Planner
Teams

Which tool you choose to start from depends on what kind of group you’re working with. For example, most will tend to start from Outlook when creating a Group organised around email and calendar. If the Group is for company wide communication you would tend to start with Yammer. For chat-based collaboration start your Group from Microsoft Teams.

Creating Groups

When creating a group where you can collaborate with your teammates you need to decide if you want it to be a private group or a public group. Content in a public group can be seen by anybody in your organisation and anybody in your organisation is able to join the group. Whereas content in a private group can only be seen by the members of the group and people who want to join a private group have to be approved by a group owner.

Neither public groups nor private groups can be seen or accessed by people outside of your organisation unless those people have been specifically invited as guests.

Adding guests to Office 365 Groups

Guest access

Office 365 Groups lets you and your team collaborate with people from outside your organisation by granting them access to group conversations, files, calendar invitations and the group notebook. Access can be granted to a guest—for example, a partner, vendor, supplier, or consultant—by any group owner.

How it works?

People using Office 365 Groups can use Outlook on the web or Outlook for Windows to add and manage guests in their Office 365 groups. Guests can have any email address, and their email account can be a work, personal, or school account.

Note: Guest access is set up by the IT administrator.

A group owner adds a guest to the group or a guest is nominated by a group member. The group owner approves the nominees.
The group owner is informed of which content and resources the guest can access within the group. A combination of text and icons gives all group members a clear indication of guest participation.
The guest receives a welcome email and can participate in group conversations, receive and respond to calendar invitations, and access the group files.
Guests can leave the group at any time via a link in the footer of all group email messages and calendar invitations.

Admins can manage guests and their access to Office 365 group resources using PowerShell. See Manage guest access in Office 365 Groups for instructions.

When a guest is invited to join a group, they receive a welcome email message that includes a little information about the group and what they can expect now that they’re a member. The message also includes a set of links to help them get started and connect to group resources.

The guest member’s interactions all occur through their email inbox. They can’t access the group site but can receive calendar invitations, participate in email conversations and, if the admin has turned on the setting, they can open shared files using a link or attachment.

All group email messages and calendar invitations the guest receives will include a reminder to use Reply all in responses to the group, along with links to view group files and leave the group.