Instant Excel

coronavirus, covid, datablunder, truncation

Video: Why Excel isn't a database

0 0 Comments — by John — 8 days ago · 2 min read

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 : Simple GANTT chart

0 0 Comments — by John — 8 days ago · 1 min read

Video: Time series + Snapshot Pie Chart

0 0 Comments — by John — 8 days ago · 1 min read

Video : Banded Performance charts ( RAG )

0 0 Comments — by John — 2 months ago · 1 min read

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

0 0 Comments — by John — 9 months ago · 3 min read

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

0 0 Comments — by John — 11 months ago · 4 min read

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, excel vba background colour, vba colour

Colour by code in Excel with VBA - Part 2

0 0 Comments — by John — 1 year ago · 4 min read

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

Conditional colour to normal colour

0 0 Comments — by John — 2 years ago · 3 min read

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

addins, arrows, macro, Numbers to text in excel, numbers to words, spell numbers, vba

Numbers to text in Excel

0 0 Comments — by John — 2 years ago · 12 min read

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.

0 0 Comments — by John — 2 years ago · 6 min read

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

0 0 Comments — by John — 2 years ago · 4 min read

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 CELL, TEXT TO COLOR, TEXT TO COLOUR

Colour by code in Excel with VBA - Part 1

0 0 Comments — by John — 2 years ago · 5 min read

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

Columns, contextual delete, delete entire column, delete rows, selection

Selectively Delete Columns/Rows by content

0 0 Comments — by John — 2 years ago · 4 min read

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

background, Calendar, dates, user defined functions

Useful date functions

0 0 Comments — by John — 2 years ago · 3 min read

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 range as picture, excel, outlook, platform indepenent

Email selection as picture

0 0 Comments — by John — 2 years ago · 2 min read

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

clean code, efficiency, messy code

Cleaner Code Part 1

0 0 Comments — by John — 2 years ago · 2 min read

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 websearch, context menu, right-click, rmb

Cell based web-search in excel

0 0 Comments — by John — 2 years ago · 5 min read

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

addins, macro, macro distribution, ribbon, xlam

Managing Add-ins for Excel

0 0 Comments — by John — 2 years ago · 3 min read

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

udf, user defined functions, vba, visual basic

Max Length of strings in cell range.

0 0 Comments — by John — 3 years ago · 2 min read

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

index-match, lookups, range, vlookup

Vlookup vs Index-Match - a better way of searching.

0 0 Comments — by John — 3 years ago · 4 min read

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

cell-based highlighting, code, find words, highlight words, highlighter

Highlight words in cells with VBA.

0 0 Comments — by John — 3 years ago · 3 min read

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

excel, excel extensiblity, inspirational quotes, starting over, vba, visual basic for applications

Go forth and Index(Match)

0 0 Comments — by John — 3 years ago · 1 min read

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

Instant Excel

Featured Posts