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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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