Index-Match is obsolete! Welcome to xLookUp!
I'll likely expand on this shortly- but this is just a quick overview of the new xLookUp feature in Excel 365. ... Read more
Instant Excel
Go forth and index(match) !
Video: Why Excel isn't a database
In the context of the recent controversy surrounding one of the reporting sources for the various Coronavirus Positive tests having been miss-counting data due to truncation in Excel. What's not mentioned in the news stories , or poorly understood is what went wrong and why. It's nothing to do with... Read more
Video : Banded Performance charts ( RAG )
Subscribe to the YouTube Channel for more. Instant Excel Channel ... Read more
64 bit error, clipboard error vba, copy string win10, error, paste text, string to clipboard, win 10, windows, windows 64
Text to Clipboard with Office 365 / 64-bit
Depending on which version of Excel you're using, you may find you're no long able to dump out text into the clipboard in VBA like you used to. I found an interesting bit of Win 10 /64-bit safe code on Stack Overflow -Original Stack Overflow Post. I'm presenting here in... Read more
arrows, category, cell background rgb, color macro, colour, colour macro, copy colour excel vba html, list color, vba color
Colour by code in Excel with VBA - Part 3
Following on from the previous entries ( * see page list ) , in this post, we'll be looking at a few more methods to colour ranges / cells to achieve easy visual categorisation. Here's a few quick scripts / examples that you can use in your own projects. These... Read more
Colour by code in Excel with VBA - Part 2
Have you ever wanted to sample colours from a workbook to then use for other applications , like web pages, BI dashboards like QlikSense / OBIEE etc. , or even in Excel / VBA forms themselves? Look no further, the following quick bit of code should help you achieve that... Read more
Conditional colour to normal colour
Have you ever wanted to 'set' the colours / fonts etc from conditional formatting ? If so, the below script should help with turning a selection from dynamic to fixed colours. The below example - we can set a basic colour scale on a range : On the home menu... Read more
Numbers to text in Excel
Have you ever wanted to spell out a number as words , perhaps for reporting / automated form filling ? This script / UDF takes some cues from the various scripts out there, however they didn't cover numbers beyond a billion, decimal places beyond two, and often appended currency names... Read more
arrows, be quicker in excel, excel, shortcuts, speed, top 10 shortcuts in excel, work faster in excel, workflow
Get fast in Excel! Shortcuts to speed up your workflow.
Have you ever been frustrated scrolling through a huge spreadsheet, or filling out blocks of data a cell at a time? Read on, as below are a number of shortcuts that'll save you time and energy in your daily chores! CTRL + Spacebar / SHIFT + Spacebar Tired of clicking... Read more
SQL connections and data import into Excel
You might have some sophisticated BI tools like OBIEE / QlikSense / Power BI etc. , however sometimes it's useful to be able to prototype, or access tables / stored procedures etc directly through Excel. This can be slow and cumbersome working through Excel's query editor, especially if you're already... Read more
Colour by code in Excel with VBA - Part 1
Want a quick way to highlight cells by their content ? Read on! Before: After: As of the writing of this article, Excel doesn't have much by way of separating out text fields by colour, despite having had a variety of numeric gradient formatting for a number of... Read more
Selectively Delete Columns/Rows by content
Do you often download or received spreadsheets with a large number of rows/columns that are surplus to requirement? If so, read on, as these two UDF methods can save a lot of manual filtering and deleting! Prerequisites You'll need two additional functions, Ron De Bruin's 'Last' function that picks the... Read more
Useful date functions
Here are a selection of date functions I regularly use to either transform text into date values , or vice versa. Paste these into your project if you're presented with a sheet where users have manually typed such as '31st January 2012' or similar. A quick rundown of what each... Read more
Email selection as picture
I've used this a lot in the last few years to quickly sending out a selection of charts and tables via email in a format that doesn't require the end user to have office installed to view it correctly. This can be called from a in-sheet button / or ribbon... Read more
Cleaner Code Part 1
When revisiting old functions, you'll often find some truly brute force methods used, usually due to time restrictions and trying to push together a solution as quick as you can. Something I recently came across was a quick UDF ( User defined function ) to return an integer month from... Read more
Cell based web-search in excel
Ever wanted to just right-click a cell and search it, the same way you can on most modern browsers? Look no further - within this article, I'll do my best to show you how to add this functionality to your excel with an addin. Step 1 - How are... Read more
Managing Add-ins for Excel
List all Addins into a sheet ( COM and XLAM ) Copy and paste the below into a module , when run it'll list all active addins into a table. This can be a foundation to build other tools, like version testing scripts to ensure the latest instance is loaded... Read more
Max Length of strings in cell range.
When transferring tabular data from excel or csv into an SQL table you can run into truncation issues, this is usually due to SQL's importer only testing a few rows and predicting max field length, which can throw errors later on in the script. For small ( less than 1m... Read more
Vlookup vs Index-Match - a better way of searching.
So you want to vlookup some stuff, and it's going wrong huh? You're frustrated by having to count along the column headings to find the right column number to return the data you need? Worry no more, there's a better way of finding things, and once you've mastered it, you'll... Read more
Highlight words in cells with VBA.
Ever wanted to highlight words within text similar to how most web-browsers will now highlight? Now you can do the same in excel. Note that this only works on static text, and not on text generated by formulas. Here's an example of it in action , some generic text... Read more
Go forth and Index(Match)
The blog crashed, and the database corrupted. A harsh lesson has been learned about keeping backups of the database. This is version 2. Watch this space!... Read more