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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
If you require duplicate data entered into a number of cells that are adjacent to each other – you can do this quickly and easily.
When entering repetitive text in Excel there is a keyboard shortcut that can help speed up the process.