Microsoft Excel – comparing two columns using conditional formatting

Due to data entry issues, a client had instances where she had the same information for some records in both the Address2 and the Town column. She wanted a quick way of seeing where this was happening. Information stored in two cells can quickly be compared using conditional formatting, highlighting instances where the record has the same results in two columns. Although her issues were not as bad as that shown below it will do as an example!

Select the information in the Address2 column, then on the Home tab, in the Styles group, from the Conditional Formatting dropdown, select New Rule.

Select Use a formula to determine which cells to format.

In the Format values where this formula is true box, type

=$c2=$d2.

Click Format. Select the required formatting, then click OK twice.

The duplicated cells are shown in your chosen format. You can then always filter by colour to see just those records.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Microsoft Excel – VLOOKUP with prices and Exchange rates

If you have a spreadsheet of items in different currencies and are doing a VLOOKUP to find the prices, you may want to return the items in £ rather than in the mixture of currencies.

In the example below, I want to create an invoice showing prices in £ but on my Items_list, they are shown in a multitude of currencies.

I have named the above as ITEMS_LIST.

I have created a Named Range called XRATE of my exchange rates.

On my invoice sheet, I am using VLOOKUP to find the relevant description for each item.

To find the price I am using a VLOOKUP to find the price in the relevant currency and combining it with an INDEX and MATCH to return the answer in £.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Microsoft Excel – showing column letters in a row of a worksheet

I was asked on a training course recently whether they could show column letters in a row in their Excel spreadsheet. There is probably more than one way to do this, but I did it with a combination of three functions, ADDRESS, LEN and LEFT. The ADDRESS function gives the cell reference itself but you have to decide whether you want that returned as an absolute or relative reference i.e $A$1 or A1 or a mixed reference.

The syntax is =ADDRESS(row number, column number, absnum). Absnum should be 1 for absolute, 2 for absolute row and relative column, 3 for absolute column and relative row and 4 for relative.

In this case it makes most sense to use 4, so =ADDRESS(1,1,4) will return A1, etc. To be able to get cell B1, we can combine this with the COLUMN function which will make the second argument become 2, 3, etc.

=ADDRESS(1,COLUMN(A1),4)

We now need to get rid of the number 1 from each of these. That would be easy if we were only interested in the first 26 columns as we could just take the leftmost character, but after that we need AA, AB, etc.

So we need to find the length of the address using the function LEN and then keep the number of characters one less than the length. The formula is then =LEFT(A1,LEN(A1)-1) which can then be copied across the remaining columns.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Microsoft Excel – sorting within a pie chart

I was asked by a client whether she could sort her pie chart so the largest number was at the top. You cannot really do this within the pie chart itself but you can do it by sorting within the Excel spreadsheet first.

Here is my pie chart before sorting the data.

I then sorted the data from largest to smallest. My pie chart now looks like this.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Microsoft Project – colour coding by Resource Group

I was asked by a client recently as to whether she could colour code by Resource Group.

First add column Resource Group to your view. You can always hide it again afterwards. You may also want to got to Gantt Chart Tools Format tab and get rid of the ticks by Project Summary Task and Summary Tasks for the time being though you will want to put them back afterwards.

Then from the Resource Group filter dropdown, get rid of the tick by Select All and put a tick by the first of your groups. If you want to change text colours of background colours in the entry table, select the filtered rows, and on the Home tab in the Font group, select your required font colour and/or background colour.

If you want to change bar colours, go to the Gantt Chart Tools Format tab and in the Bar Styles group, from the Format dropdown, click Bar, select your required colour.

Go back to the Resource Group dropdown, clear the filter and select your next group and follow the above instructions for colouring each group in turn.

This should work as long as not more than one Resource Group is assigned to any task!

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

Find out more about our Microsoft Project training in Dorset, Wiltshire and Hampshire areas.

Sending a folder by email

A client just contacted me to ask how she could send a whole folder by email.

The answer is that you will need to ‘zip’ the folder.

Right-click on the name of the folder, click Send to, then select Compressed (Zipped) Folder. You can then email the zipped folder.

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

Find out more about our Microsoft Office training in Dorset, Wiltshire, Hampshire.

Time Management – Turning off Microsoft Outlook e-mail notifications

ifonlyidknownthat

If you are busy concentrating on writing a report, a proposal, etc, you need to concentrate on the task in hand, not constantly be distracted by an e-mail notification symbol becoming visible at the bottom right of the screen. Believe it or not, most e-mails do not need an instant reply – if we are in meetings, we can usually manage to ignore our e-mails, so why not when we’re busy on a vital piece of work?

Why not turn off your Outlook notifications?

If using Microsoft Outlook 2007 or earlier versions:

Click Tools – Options. The Options dialog box will be displayed.

On the Preferences tab, click E-mail Options, then click Advanced E-mail Options. The Advanced E-mail Options dialog box will be displayed.

In the When new items arrive in my Inbox section, remove the ticks from Play a sound, Briefly change the mouse cursor

View original post 187 more words

Microsoft Access – finding duplicate records

ifonlyidknownthat

If you want to find duplicate entries in a field in a table or query, the easiest way to do this is to use the Find Duplicates Query Wizard. For example, you may wish to find records with the same name but different addresses, or as in the example below, which suppliers are in the same city.

To use the Find Duplicates Query Wizard

If using Microsoft Access 2007 or Microsoft Access 2010, click the Create tab, and then in the Other group, click Query Wizard. If using Microsoft Access 2013 or Microsoft Access 2016, click the Create tab, then in the Queries group, click QueryWizard. If using Microsoft Access 2003 or earlier, from the Database window, click the Queries object button, then click New.  In the New Query dialog box, select Find Duplicates Query Wizard, then click OK.

The New Query dialog box will be…

View original post 96 more words

Microsoft Excel – #### in a cell

ifonlyidknownthat

You may have had the experience of think you are doing something wrong, because you see an Excel cell filled with hashes. In fact this is not an error at all, but instead means that your column is not wide enough to display all the data in the cell.

So, if this happens:

Navigate to the first cell where the problem occurs.

Move the mouse to the top of the column, and place it over the right-of-column marker, where it changes shape to a double-headed arrow. Double-clicking the column marker using the left mouse button automatically resizes the column to fit its widest entry. The hashed-out entries should now display numbers correctly.

There are two other ways to adjust column widths.

First, you can manually resize a column. Hover the mouse pointer at the right-of-column marker, where it changes shape to a double arrow. You can then click and drag with…

View original post 192 more words

Microsoft Outlook – greying out previous appointments

I was asked recently whether there is a way to show past appointments in Microsoft Outlook in a different colour to future appointments.

From within the Calendar, go to the View tab, then in the Current View group, click View Settings.

Click Conditional Formatting.

Click Add. Type in a name for the rule in the Name box. From the Color dropdown, select the required colour, then click Condition. Click the Advanced tab.

From the Field dropdown, select the End field. From the Condition dropdown, select on or before. In the Value box, type Yesterday.

Click OK three times.

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

Find out more about our Microsoft Outlook training Dorset, Wiltshire and Hampshire.