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 […]
How can I do a VLOOKUP using 2 cells for the Lookup Value and 2 lookup columns in the table array? That’s a tricky question, as VLOOKUP is designed to work with 1 lookup value and 1 lookup array, but where there’s a will; there’s a way. The way I’m going to show you is […]
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 […]
Within a large range of tabular data, you may want a list containing only data sets that have a specific value. This list could contain the value of the first column of all the rows with a specific date in the third column. For example, here’s how to list all the names with a blank […]