Microsoft Excel – arranging countries into regions in a PivotTable

If you are a global company and are analysing sales by region, even if the name of the region isn’t in the raw data, you can group countries together by region as long as you know the appropriate regions to group them in to.

In the example below, I have various countries and their last year’s sales. I want to find sales by continent.

The first country, Argentina, is in South America.

Click in the cell containing the first country, then hold down the Ctrl key, click in the cells containing the other South American countries.

On the Pivot Table Analyze tab, in the Group group, click Group Selection.

Click where it says Group1 and type in South America for the group name.

Continue similarly for each set of countries.

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

 

Microsoft Excel – freezing top row in a Pivot Table

I was at client site recently and someone had a Pivot table with a lot of rows. It was therefore hard to see what the column headings were.

Just as when you are trying to freeze rows in any Excel data set, click in the row under the top row of the pivot table. Go to the View tab, then in the Window group, from the Freeze Panes dropdown, select Freeze Panes. That should do the trick.

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

Microsoft Excel – using Icon Sets to show whether figures have gone up, down or stayed the same

In the example above, I would like to use icon sets to show whether sales figures have increased, decreased or stayed static.

In cell D5, I created a formula =if(c5>b5,3,if(c5=b5,2,1)) – i.e. if the figure had increased, D5 becomes a 3, if it is static, it becomes a 2, and if the figure has decreased, it becomes a 1.

I then selected the numbers in column D, then on the Home tab, in the Styles group, I clicked the Conditional Formatting dropdown, clicked Icon Sets and selected the first of the Directional options.

Then from the Conditional Formatting dropdown, select Manage rules, then Edit rules and select Show icon only.

Click OK twice.

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

Microsoft Excel – Charts with missing values

If you create a line chart where there are blanks in the data, the resulting chart can look rather odd!

On the Chart Design tab, click Select Data.

From the Select Data Source dialog box, click Hidden and Empty Cells.

Choose from one of the options. Here, I have chosen Connect data points with line. Click OK. Your chart will no longer have gaps.

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

 

Microsoft Excel – The UNIQUE function

If you are an Excel 365 user, you will have the UNIQUE function, which looks at a range of cells and produces a list of unique values. This may, be useful, for example, if you wanted to create a Data Validation List from data that is already there.

In the example below, I have a list of orders, but some customers have made more than one order and I just want a list of customer names.

In cell H2, I use the formula

=UNIQUE(B2:B154) which is the range that I am searching through.

I get a list of unique names in column H.

The syntax for the UNIQUE function is UNIQUE(Array, [by column], [occurs once]).

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

Microsoft Excel – TEXTJOIN function

When storing names in Excel, it is usual to create three separate fields – Title, FirstName and Surname. Sometimes we want to join the words together, for example, for sending out an invoice. Joining two or more words together into one string is called concatenation.

The CONCAT function was introduced in Excel 2016 and replaces the earlier CONCATENATE function.

The syntax for this is CONCAT(text1, [text2],…) where text1, text2 etc are the strings you want to join together.

We can concatenate a space character within the CONCAT function as follows:

=CONCAT(A2,” “,B2,” “,C2)

Another way of typing this is =A2&” “&B2&” “&C2.

With the new TEXTJOIN function, the same process would look something like this:

=TEXTJOIN(” “, TRUE, A2:C2). Even more usefully, the TEXTJOIN function allows you to ignore empty cells.

The syntax is:

TEXTJOIN(delimiter, ignore_empty, text1, [text2],…)

The delimiter is the separator required between each text string and needs to be enclosed in double quotes.

If you want to ignore empty cells in a range, then ignore_empty should be True, otherwise False.

Text1, Text2 etc are the parts of text you want to join and can be actual text or a cell range.

If you were using a Slicer in an Excel pivot table, the TEXTJOIN function would allow you to create a title for the table.

If I remove the Grand Total and then adapt my slicer my title will change accordingly.

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 Outlook – adding contact lists to the Address Book

When you are trying to address an email and click the To button, the Select Names dialog box displays with a dropdown list in the Address Book area, usually showing the Global Address Book and various other contact folders. At a client site recently, they had various contact folders that were not showing up on the dropdown list.

To add to the list, right-click on the Contact folder, then click Properties, click Outlook Address Book, then put a tick by Show this folder as an email Address Book. Click OK.

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

Microsoft Outlook – Classic Ribbon vs Simplified Ribbon

From Office 2007 onwards, there has been a ribbon structure at the top of the opened package with various tabs showing various commands.

On opening Microsoft Outlook on a computer belonging to a client recently, the ribbon showed some icons but not all of them.

Outlook 2016 and Outlook 365 have the options of showing a Simplified ribbon which just shows your most frequently used commands on one line.

To switch between the standard or Classic ribbon and the Simplified ribbon, right-click on the ribbon. Three options will appear – Customize the Ribbon, Collapse the Ribbon and Use Simplified Ribbon.

Classic Ribbon
Collapsed Ribbon

Simplified Ribbon

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

Microsoft Outlook – adding contacts to AutoComplete

Generally when you are using Microsoft Outlook, recently used contacts come up under the AutoComplete list when you are using the To box to address a new email. At one client site recently this wasn’t happening for a few contacts – and ones they did use on a regular basis.

The solution involved going offline as you have to seem to send an email to the added contacts – click the Send/Receive tab, then in the Preferences group, click Work Offline. Now go to the relevant Contacts folder and select the required contacts by holding down the Ctrl key and clicking.

Then on the Home tab, in the Communicate group, click Email. The relevant email addresses will now show in the To box. Click Send.

Because you are offline, the message will now be in the Outbox. Go to that and delete the message. Then go back online, by going back to the Send/Receive tab and in the Preferences group, clicking Work Offline again.

Next time you try to send an email, the required contacts should be in the AutoComplete list.

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

 

 

Microsoft PowerPoint – how can I stop my hyperlink changing colour when it is clicked

Not everyone is using PowerPoint for presentations; some use it to produce notices or reports. If you are creating something to put on a wall, you don’t want some hyperlinks to be differently coloured to others. The reason this happens is because you, or someone else, has clicked on the hyperlink at some point.

The colours for hyperlinks are linked to themes. Go to the Design tab, then open up the Variants gallery, click Colors – Customize Colors. The Create New Theme Colors dialog box will be displayed.

From the Followed Hyperlink dropdown, select the same colour as for Hyperlink. Click Save. The colours of your hyperlinks will then remain the same.

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