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 […]
What formula could I use to extract only Unique items from a large table of data? Great Question! There isn’t an Excel function that’ll do this for you (yet). You could to it with a pivot table, but sometimes that’s not practical, so if you really need a formula to return the results, you’re going […]
Flash Fill is a feature that was new in Excel 2013 and it is absolute gold. If you’ve ever had to extract just a small part of a cell throughout an entire list or had to join together parts of multiple cells within a large list, you’re going to absolutely love Excel’s flash Fill feature. […]
How can I colour code cells in Excel so that -10 is red and +10 is green? With conditional formatting of course 🙂 Excel’s Conditional Formatting menu is choc full of tools to help you automatically format your cells based on their values or on the values of adjacent cells – or both! In this […]
How can I create a dependent drop down list? This is a question that I get all the time and while the question is simple enough – the answer is anything but. It’s going to require the creation of a dynamic named range that’ll use multiple functions nested within a single formula to make the […]
How can I import data from multiple source workbooks into separate worksheets in a single file? Although this can be achieved with links to the source file – links can easily be broken and can also make the file slow and cumbersome. So instead of creating links, I like to create processes that import the […]
INDEX & MATCH are two useful excel functions that provide a solution to VLOOKUPs limitations. Unlike VLOOKUP, INDEX & MATCH can lookup the column and the row matching the specified criteria and return the value at their intersection. Don’t be overwhelmed – watch the intro video below and sign up for our free course on […]
Left & Find Hi there! In this brand new tiny topic you're going to learn how to create a formula that will turn a name from First Name Last Name format into Last Name, First Name format, like this: There's just a few simple functions that you're going to need to know – in fact […]
Numbers stored as text (or numbers stored as numbers when you expect them to be stored as text) can be tricky little buggers that cause no end of confusion when cells that by all appearances should match – don’t match. Fortunately, just like everything else in Excel – once you understand the source of the […]