I have created a data validation rule that checks new entries to ensure they haven’t already been recorded, but when a user pastes into this worksheet, the data validation doesn’t work. How can I prevent people using paste to circumvent my data validation? Unfortunately when cells are pasted over in Excel, it’s not just the […]
How can I prevent people entering a date that’s already been finalised? Oooh… Usually data validation questions are all about creating drop-down lists, so this is a nice change. Data validation is actually a tool that’s used to restrict what can be entered in cells to criteria defined by the user. Although it’s commonly used […]
Hi there and welcome to Pivot tabling. In this short tutorial, I’m going to show you how to create a pivot table to provide summary information from your data. This is just the first lesson in a small series of lessons on Pivot Tables. If you find that you want to know more, you can […]
In this lesson we’re going to explore and explain an Excel VLOOKUP function. I want you to think of the process of your VLOOKUP like your manual process for searching a phone book. What are you looking for ? (i.e. the name), Where are you going to look? (i.e. which page), What do you want […]
Great question from Lisa, who is extracting all information about upcoming events from a database. The database extract contains information about all events – even those that have been cancelled. We can make use of the Find tool to delete all rows recording cancelled events. To delete all rows that contain the word cancelled (in […]
Converting text from lower case or sentence case into upper case in Excel can be achieved either with a formula or with a custom VBA function. If you’re just doing it the once, you’ll probably just use a formula (=UPPER), but if you’re doing it regularly – try creating a custom VBA function like this… […]
If you’re working with dates in VBA – it can be a little painful when the date format defaults to US format (mm/dd/yyyy). Sure when you enter 30/10/1979 (my birthday incidentally), it knows it’s the 30th October because there is no 30th month, but when you enter 03/05/1957 (my dad’s birthday), it reads it as […]
The CONCATENATE function in Excel has never been one of my toolbox – too cumbersome, but the newest version of Excel includes the TEXTJOIN function… like Concatenate but so much more intuitive. Check it out!
Array formulas are pretty handy. This example looks for records that match columns A, B & C between 2 sheets, returns the text “Duplicate” only if there is a record that matches all 3 columns and nothing if it doesn’t… There aren’t many Excel instructors who teach Excel to this level – but I do. […]