Beware – copying a shortcut is not the same as copying a file!

I was doing Microsoft Access training last week and the client wanted us to look at making changes to their database. I said we needed to make a copy then if we did something that they decided they didn’t want, we could easily get back to the original.

Being helpful, one of the attendees told me she had made a copy for me.

I started making changes and then decided I wanted to look at the original. However, I then found that there was only one copy! What had happened was that she had copied the desktop shortcut to the database, rather than making a copy of the database itself!

Luckily we weren’t too far down the line and all was well.

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

Microsoft Excel – copying a number of rows x times

I was recently training at a client site and they said they regularly needed to copy a number of rows, say 100 times in a worksheet.

There is VBA code to help you undertake a task like this but if you are not a VBA programmer, there is an easier way.

Say, I want the information in cells A2:R52 duplicated 100 times.

First, I selected the range A2:52 and pressed Ctrl + C to copy the data.

I wanted it duplicated 100 times which should take me down to row 5152, i.e. (100*51) + the original 51.

In the Name box to the left of the formula bar where it will say A2 or the cell reference of the first cell in the range to be duplicated, type the cell range that you want the information to be duplicated in, in my case, A2:R5152. Press Enter.

I then clicked Paste or press Ctrl + V.

My data was duplicated as required.

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 – first x characters of a text string but cutting off at full word

I was working with a client recently who had long descriptions of products within cells in Excel. To upload the descriptions to the web site, they could only use 80 characters but wanted to ensure that there wasn’t part of a word at the end of the string.

I have to confess I ended up doing this in two stages – there may be an easier way but it works!

I have input some text as shown above.

In cell B1, I have found the first 80 characters by using the LEFT function

=LEFT(A1, 80)

I then copy down the formula for as many rows as there are in my worksheet.

The formula in cell C1 to cut off at the end of the last full word is rather more complicated:

=LEFT(B1,FIND(“/”,SUBSTITUTE(B1,” “,”/”,80-LEN(SUBSTITUTE(B1,” “,””))),1))

The SUBSTITUTE part of this looks for spaces and substitutes this with a / but only at the last instance of the space.

LEN looks at the length of a string.

The LEFT function then takes B1 and cuts it off at the /.

FIND returns the starting position of one text string within another text string, so in this instance looks for how many characters there are to the / then the LEFT function returns the characters up to that position.

Not terribly easy I admit but it does work!

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

Microsoft Access to Microsoft Word mailmerge – dates in wrong format

I was using data from a Microsoft Access query in a Microsoft Word mailmerge document the other day. However on returning dates they were in the American format rather than the UK one, although both Microsoft packages were showing my language as English UK.

I was very keen that the dates went out in UK format.

To change it, right click on the Date itself, then click Edit Field. The Field dialog box will be displayed.

Ensure MergeField is selected in the Field names section and that the relevant date field is selected in the Field name box.

Click Field Codes. In the Advanced Fields section type \@”dd/MM/yyyy” immediately after the field name.

Click OK. Your date will be formatted correctly.

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

Microsoft Outlook – Ribbon not showing all icons

One morning I opened Microsoft Outlook to see only a condensed version of the ribbon I am used to seeing.

I thought this may be something to do with a Microsoft upgrade but this isn’t really the case. The ribbon is collapsed – it is known in Microsoft parlance as the Simplified Ribbon. At the bottom right of the ribbon is a downward facing arrow, This gives you the option to toggle between the Simplified Ribbon and the Classic Ribbon.

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

XLOOKUP – finding first or last value in data set

Yet another blog about the XLOOKUP function which is so powerful.

Say I have a list of customers and I want to find the date of their first order or their last order.

I have more than one order for several customers.

The syntax for the XLOOKUP function is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. 

It is the Search mode that we need to think about in this instance. This is input as:

1 – Perform a search starting at the first item. This is the default.

-1 – Perform a reverse search starting at the last item.

2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

In the example I am using my Order Dates are in sort order; if not, you would need to sort before running the XLOOKUP. To find the earliest Order Date, we use 1 as our search mode and to find the latest Order Date we use -1 as our search mode.

So my first formula is =XLOOKUP(I2,B2:B154,D2:D154,,0,1)

and my second formula is

=XLOOKUP(I2,B2:B154,D2:D154,,0,-1)

Other examples would be finding the first or last sales amount for a particular client, etc.

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

XLOOKUP – finding the number of orders between two dates

We have already looked at using XLOOKUP to return data where columns are not in a contiguous order.

There are many other uses of the XLOOKUP function. One could be to return the number of orders between two dates or the value of orders between two dates.

In the spreadsheet shown we have order dates and individual orders. Say, I wanted to find how many orders came in in July 2020.

I have added the required Start Date in cell I2 and the required Finish Date in cell J2.

The relevant combination of the functions COUNT and XLOOKUP in cell K2 would be:

=COUNT(XLOOKUP(I2,D2:D154,A2:A154):XLOOKUP(J2,D2:D154,A2:A154))

Similarly, I could use the SUM function on a relevant column to find the total value of orders.

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

Microsoft Excel – XLOOKUP function – returning data from non-contiguous columns

In my recent blog on the XLOOKUP function I showed how useful it was rather than using the VLOOKUP function.

When training XLOOKUP recently, I was asked if it could return data on columns that were not next to each other.

Yes, you can, but it also involves using the CHOOSE function.

In the example below, the original column order was Wine Number, Wine Name, Quantity and Price:

I want to return Wine number, Price and Quantity – wine name Semillon 2009 is in cell G13, so I am putting my cursor in cell H13.

Going to XLOOKUP, the relevant syntax is

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],[match_mode],[search_mode])

In this example, we need cell G13 for the lookup value and $B$2:$B$10 is the lookup array.

It is the return array that is more tricky. The CHOOSE function allows us to specify the order of columns so I need CHOOSE({1, 2, 3}, $A$2:$A$10, $D$2:$D$10, $C$2:$C$10). We want to return 3 columns, that is why the CHOOSE function has the 1,2,3; the squiggly brackets indicate that we are returning an array of three columns.

So my formula is:

=XLOOKUP(G13,$B$2:$B$10,CHOOSE({1,2,3},$A$2:$A$10,$D$2:$D$10,$C$2:$C$10),”Not in stock”)

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

Microsoft Excel – adding company logo to header

When printing off an Excel spreadsheet, or indeed emailing it to a client, you may want to include your company logo as part of the header.

To insert a header, click the Insert tab, then in the Text group, click Header & Footer. (Alternatively, click on the Page Layout icon to the right of the Status Bar.).

Click in the Header section, in the area where you want to insert the icon, then on the Header & Footer tab, and in the Header & Footer Elements group, click Picture. Locate the logo you want to insert, then click Insert. Until you move your cursor away from the area you inserted the logo, all you will see is &[Picture].

Move your cursor to another part of the header and your logo will be revealed. It may well be too large, in which case, click on your logo again, then in the Header & Footer Elements group, click Format Picture. The Format Picture dialog box will be displayed. Click on the Size tab and select a suitable % in the Height and Width boxes. Click OK.

It may then be the size you require but still be covered by some of the data in your worksheet. In that case, go to File – Print and click Page Setup, then go to the Margins tab. Make the Top margin bigger, then click OK. This should mean you can see both logo and data appropriately.

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

Microsoft Excel – I get the same answer in every cell when I copy my formula down

I was training Excel and we were doing an IF statement in one cell and then copied down to the other cells in that column. One in the session got the same answers for all cells though looking at her formulas they all looked correct.

I then asked her to go to the Formulas tab and show me what was under the Calculation Options dropdown in the Calculation group. For some reason, Manual was ticked which meant that she would have to click Calculate Now to get the calculation to work. Sometimes, with very big workbooks, manual calculation saves processing time.

To change the default option, from the Calculation Options dropdown, click Automatic. Alternatively, go to File – Options, then select the Formulas tab. Select Automatic in the Workbook Calculation section, then click OK.

Formulas will then be automatically calculated.

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