Data Validation – Drop Down Lists

For those of you currently using Data Validation, you will know that it is a great Excel feature.  It allows you to restrict data values entered within cells, on your spreadsheets, by providing a drop-down menu to select limited and defined data from.  This not only keeps the integrity of the data intact but can […]

Read More

Tidy up your Data

I was asked by a client recently how to tidy up text on their data lists stored in Excel.  These errors were made during the data entry process or when the data was imported from another database package. In cell B1 and B2 I have used the formula =PROPER(A1) to capitalise each letter in the […]

Read More

INDEX and MATCH Functions

These functions give you greater flexibility then the Vlookup or Hlookup functions because they allow you to do a two-way (Matrix) lookup. Therefore, you can search for two lookup values in one table (data). The table lists the number of Attendees as the Vertical lookup and the Time Slots as the Horizontal Lookup. Note: A […]

Read More

Ordinal Suffix

Ordinal Suffix I was asked on a recent Word training course how to control the th, st, nd and rd text that you type after a number.  E.g  4th, 1st, 2nd and 3rd My first challenge was to find out what they were called! Word, uses the “Autoformat as you type” feature to automatically change […]

Read More

Unhide cell A1, row 1 or column A

Excel – Unhide Row 1 or Column A To show hidden rows and columns you have to select the columns or rows either side of the hidden row(s) or column(s). However, if row 1 or column A is hidden then this is not possible. Here’s a few tips on how to achieve this. Click on […]

Read More

Changing text case in Excel

Excel does not have the same options as Word to change text case. However, it doesn’t mean you can’t change text case it just means you use a different method to do it and this is by using functions. PROPER          Will capitalise the first letter of each word UPPER             Will change all letters to upper […]

Read More

Excel 2013 – Find using wildcard characters

Find– Using wildcard characters You can use wildcard characters in the Find and Replace feature in Excel to search for particular numbers or text. Two of these are the asterisk (*) and the question mark (?). The asterisk (*) can be used to find any string of numbers   e.g. 1*1 will find 101, 1231 and […]

Read More