Microsoft Excel – XLOOKUP

If you are using Excel 365 or Excel 2019, you will be able to use the new XLOOKUP function. This has many uses – I am outlining one of them here.

Previously if I was using a VLOOKUP function and wanted to use the function across several columns, I would use a VLOOKUP function combined with a COLUMN function. And if I also wanted to add in a message if there was no match, I also included the IFERROR function.

So if my original date looked like

and I had a list of Wine numbers that a client had asked for and I wanted to find the relevant Name, Quantity and Unit Price, my formula, in cell H2 if the Wine number that I was trying to locate in this data set was in cell G2 would be:

=IFERROR(VLOOKUP($G2,$A$2:$D$10,COLUMN(B1),FALSE), “Not in stock”)

To achieve a similar result with an XLOOKUP, the formula would be:

=XLOOKUP(G2,$A$2:$A$10,$B$2:$D$10,”Not in stock”)

Which is rather easier. When I use this in cell H2, it automatically also fills in I2 and J2 as it is looking at the information in 3 columns, B to D.

The syntax for the XLOOKUP used here is

=XLOOKUP(lookup value, lookup array, return array, if not found)

There are other options for closest rather than exact match, etc, but I’ll look at them at another time!

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

Microsoft Outlook – left Caps Lock on by mistake?

It is very easy when typing an email in Microsoft Outlook to suddenly find that you have been happily working away with Caps Lock on by mistake. The last thing you want to do is delete everything you have done and start again!

Select the text that is in the wrong case, then on the Format Text tab, in the Font group, click the Change Case button dropdown.

Select tOGGLE cASE. All upper case letters will become lower case and all lower case letters will become upper case. Problem solved!

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

Microsoft Excel – Changing font size in Slicer

If you are using slicers in Excel, and you try to go to the Home tab to change font size, you will find the options are all greyed out.

On the Slicer Tools Options tab, click the dropdown in the Slicer Styles section to open up the Slicer Styles Gallery and click New Slicer Style. The New Slicer Style dialog box will be displayed.

If required, give a name to the style you are creating in the Name box.

If you are likely to be creating more than one slicer and want all to have the same formatting, check the Set as default slicer style for this document.

You can then decide whether to change the font attributes for the whole slicer or specific elements of the slicer.

Click Format.  Select your required formatting then click OK. Now go to the Quick Styles dropdown and click Custom. Your chosen formatting will be applied to the filter.

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

Microsoft Excel – finding a date exactly two months after another date

To find a date exactly two months after another date, you can use the EDATE function. This returns the serial number of the date that is the indicated number of months before or after the start date.

Now, on the Home tab, in the Number group, click the dropdown where it says General and select Short Date. You will have the required dates.

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

Microsoft Outlook – always check spellings before sending

It is so easy to send an email and forget to check the spelling and we don’t always notice the red wriggly lines that warn us of a mis-spelling. Why not change the settings so that Outlook will check the spelling automatically for you?

To do so, from within Outlook, click File – Options, then at the left hand side, click Mail.

In the Compose messages section, put a tick by Always check spelling before sending.

Click OK.

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

Microsoft Project – Preferred Date 85% between Start and Finished Date

A client had a project plan where he had entered durations and had input the necessary links between tasks. Although he then had expected Start and Finish Dates, he wanted to set up a field Preferred Date which was 85% through the duration of the task.

Go to the Project tab, then in the Properties group, click Custom Fields. The Custom Fields dialog box will be displayed.

From the Type dropdown, click Date.

Click Rename and rename the field to Preferred Date. Click OK.

Click Formula. The relevant formula is

ProjDateAdd([Start],[Duration]*.85,”Standard”)

Click OK twice.

Return to your Gantt Chart and add the new column.

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

Microsoft Access – automatic numbering using set pattern e.g. P-001, P-002, etc

If you use Microsoft Access you can use Data Type AutoNumber to produce a sequence 1, 2, 3 etc for your records.

If you want to instead use a sequence such as P-001, P-002, etc, then in Design view. go to the Field Properties, then in the Format box, enter the syntax “P-“000.

It will display exactly as you wish but there are two things to bear in mind:

1. it will be for display purposes only – the information stored in memory will still be numbers.

2. Auto numbers are never re-assigned. So if some records are deleted you will have gaps: P-001, P-002 and then P-005

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

Crystal Reports – hiding zero values in charts

I was training Crystal Reports this week and we produced a chart where a lot of the values were zero which made the chart rather messy.

We were charting Last Year’s Sales by field Region from the Customer table.

Create a new formula field, No Sales Region

If Sum({Customer.Last Year’s Sales},{Customer.Region})=0 then {Customer.Region}

Go into the Chart Expert, on the Data tab, select Advanced for the Layout, then for the On change of , select @No Sales Region and for Show value(s) select Sum of Customer.Last Year’s Sales. Click OK.

The next question was what if we only wanted to see on the chart Regions with Last Year’s Sales >10000.

Create a new formula field, Large Sales Region

If Sum({Customer.Last Year’s Sales},{Customer.Region})>10000 then {Customer.Region}

Go into the Chart Expert, on the Data tab, select Advanced for the Layout, then for the On change of , select @Large Sales Region and for Show value(s) select Sum of Customer.Last Year’s Sales. Click OK.

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

Crystal Reports – grouping by Quarter where Quarters are Aug to Oct, Nov to Jan etc

I was training Crystal Reports where the company concerned have their Quarters Aug to Oct, Nov to Jan, Feb to Apr and May to July. They wanted it grouped so that it Aug to Nov would be Qtr1 20/21, Nov to Jan would be Qtr2 20/21, Feb to Apr would be Qtr3 20/21 and May to Jul Qtr4 20/21.

The field we were working with was Order Date from table Orders.

My first attempt at creating the relevant formula field Quarter was

if month({Orders.Order Date})in [8 to 10] then “Qtr1 “&right(totext(Year({Orders.Order Date}),0),2)&”/”& right(totext(Year({Orders.Order Date})+1,0),2)

else if  month({Orders.Order Date})in [11 to 12] then “Qtr2 “&right(totext(Year({Orders.Order Date}),0),2)&”/”& right(totext(Year({Orders.Order Date})+1,0),2)

else if month({Orders.Order Date})=1 then “Qtr2 “&right(totext(Year({Orders.Order Date})-1,0),2)&”/”& right(totext(Year({Orders.Order Date}),0),2)

else if month({Orders.Order Date}) in [2 to 4] then “Qtr3 “&right(totext(Year({Orders.Order Date})-1,0),2)&”/”& right(totext(Year({Orders.Order Date}),0),2)

else if month({Orders.Order Date}) in [5 to 7] then “Qtr4 “&right(totext(Year({Orders.Order Date})-1,0),2)&”/”& right(totext(Year({Orders.Order Date}),0),2)

We then used the Group Expert to insert Quarter as the group. The relevant groupings were then not in the right order as it put all the Qtr1 groupings before the Qtr2 groupings etc. To adapt this go back into the Group Expert, select Options then from the dropdown on the Common tab, make sure in specified order is selected, click on the Specified Order tab and move groups around until they are in correct order. Click OK twice.

The above formula is quite longwinded so my next attempt was with Variables and looks like:

Stringvar Yr1:=right(totext(Year({Orders.Order Date}),0),2)&”/”& right(totext(Year({Orders.Order Date})+1,0),2);

Stringvar Yr2:=  “&right(totext(Year({Orders.Order Date})-1,0),2)&”/”& right(totext(Year({Orders.Order Date}),0),2);

Stringvar result;

Result:= if month({Orders.Order Date})in [8 to 10] then “Qtr1 “& yr1

else if month({Orders.Order Date})in [11 to 12] then “Qtr2 “& yr1

else if month({Orders.Order Date})=1 then “Qtr2 ” &yr2

else if month({Orders.Order Date}) in [2 to 4] then “Qtr3 “&yr2

else if month({Orders.Order Date}) in [5 to 7] then “Qtr4 “&yr2;

Slightly neater!

Third attempt:

Stringvar Yr1:=right(totext(Year({Orders.Order Date}),0),2)&”/”& right(totext(Year({Orders.Order Date})+1,0),2);

Stringvar Yr2:=  “&right(totext(Year({Orders.Order Date})-1,0),2)&”/”& right(totext(Year({Orders.Order Date}),0),2);

Select Month({Orders.OrderDate})

Case 8, 9, 10: “Qtr1 “&Yr1

Case 11,12: “Qtr2 “&Yr1

Case 1: “Qtr2 “&Yr2

Case 2, 3, 4: “Qtr3 “&Yr3

Case 5, 6, 7: “Qtr4 “&Yr4;

The last line of this code could also have been written as

Default: “Qtr4 “&Yr2;

I may be able to improve on it and will update this blog if I do, but it does work which is the main thing.

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

Microsoft Project – showing Task name next to Gantt Chart bar

I had an email from a client recently asking how they could see the Task name next to the bar on the Gantt Chart.

On the Gantt Chart Tools Format tab in the Bar Styles group, click the Format dropdown and select Bar Styles. Select the relevant bar (probably Task), go to the Text tab near the bottom and choose where you want the text to appear (Left, Right, Top, Bottom or Inside) and select Name from the dropdown list at the right.

 

Click OK.

The task names will appear next to your Gantt bars.

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