Articles Category: Excel
A datedif function for excel that calculates correctly
This is one of those little quirks in excel that's annoyed me for a while, there must be some method in their madness but I've yet to come across anybody that thinks it is logically the way forward; so I've written one for you to use. The way the in-built function works is that it reports back the answer as how many months apart are the two dates more »
Create custom Excel function using an array/range input
My new adage of "You only know what you know how to google" - Martin Childs, 2016 stood true for this little puzzler, the answer wasn't apparent from simply googling it! All I wanted was to do something to a range input in a custom Excel function, easy you'd think - not in this case. I toiled for a bit of time before conceding that I'd just have more »
Excel: How to calculate distances between longitude / latitude co-ordinates
Here's a blast from excel past, it was actually one of the more interesting projects I worked on. We were designing food waste recycling plants for the food manufacture and retail industries. The task was to calculate the most efficient solution ofhow many recycling plants to build and where to build them based on the waste arising of each shop more »
Using OR in SUMIF in Excel
Things have moved on since the days of DSUM, we now have the likes of SUMIFS from Excel 2007 onwards. Can't even remember life before SUMIFS having never being a fan on DSUM. But with all things in life everything moves on, everything is never good enough and now SUMIFS's shortcomings are beginning to pop up. The other week the need came up for a more »
"You only know what you know how to google"
"You only know what you know how to google" - Martin Childs, 2016 This post was supposed to be about how create a custom Excel VBA function, the origination of the idea was despite my lifelong career of tinkering with excel I'd never come across or had the requirement to have a function cycle through the cell you input as a range to perform a more »
Excel: convert Postcode in x,y grid co-ordinates
This is a cracking blast from the past, I was digging through my old personal macro excel backups when I came across this beauty.
It dates back to my recycling plant development days, I had to devise an algorithm to work out the most economical site placements for food waste recycling facilities for a given highly dispersion of waste arisings.
The more »
How to assign a shortcut key to the Explode Excel addin
Sorry to bore you with yet another Explode addin post, but I think this is worth it on a couple of fronts. Assingning a shortcut key to the explode plug-in makes more awesome - being able to devle into those dependants even quicker.
Also seeing as as my initial explode post is one of my most read posts, I know you'll all lap it up.
There are two more »
Create your own custom formatting shortcut in excel
Like with most things in life, the longer you do something for the more you get set in your ways. Excel is no exception and if I'm a sucker for anything it's number formatting.
Maybe it's the accountant in me, but I think negative numbers are easier to read and look far more stylish wiht a bracket around them. Then if you are going to use a more »
Explode Excel Addin - download Latest 2008 version
After many months struggling with my favorite excel explode addin, I've finally discovered the solution to my issues.
The problems started after I arrived at a new job - the computers were super locked down and they had Office 2010. I had problems installing the addin and even when it was working there was no 'Explode..' option on the right-click more »
How to reset the default cell formatting in an Excel spreadsheet
One of the more irritating things you can come across in someones elses spreadsheet (on the basis that I've never managed to reproduce it myself) is when somehow the default style has been changed to spit out a daft format of 00:00:00 for seemingly any number.
This is especially apparant of you are a regular user of the clear formats function. It more »
An enhanced name manager for Microsoft Excel
Named ranges are one of those marmite things in Excel, either you use them or you don't. I sit between the two camps and my utilisation depends on two things:
Firstly who the end user will be and whether they'll need to interact with them.
The other is a timesaving thing - their use will depend on if it will save me time over the long run.
My more »
Extract a Surname from a string in Excel
There comes a point in every Excel users workflow that they've got to work with a list of names. Usually the data you get is in the wrong format, or you have to compare two dataset of different formats. This can be frustrating but here is a quick guide of how to manipulate them.
This tutorial is based around extracting the surname / family name more »
Creating hyperlinks in Excel that are relative & actually work
If you've ever tried to create a hyperlinks in excel you'll know what a pain they are.
The hyperlink tool on the Insert ribbon is quite useful, but it is a pain to create a lot of them. The big problem however is that they are not relative - if you add more columns or rows suddenly your hyperlink is pointing to the wrong place.
You could use the more »
Excel function to list worksheet names
Ever wanted to list all the tabs in a workbook or reference a specific sheet by its placing, look no further.....
This funky little custom VBA function can be used simply as a contents sheet, This is a particularily useful tool tool when used with the indirect function, it will allow you to soft-link to any cell within your worksheet.
There are a more »
Explode - the must have excel add in
Excel's built in formula auditing tools is and always has been rubbish, sure you may have got used to how they work and feel quite competent/happy with it - but the Explode excel addin will blow your mind!
If you work with excel a lot and you don't use the add in Explode you're in one of two camps:
not a power user, or
both amazed at how you've more »
Make a cell look like a button in Excel
There a sometimes in excel where you want to create a button but you don't want to introduce VB into your workbook (there are many reasons not to) - heres how.
I'm a great advocate of not using VBA / macros when you don't have to - they are not widely understood, easy to break when you alter the workbook and introduce a plethora of security more »
Excel - Add comment directly into formulas
Ever known anybody who hardcodes numbers into formulas? Does it drives you mad because they leave no explanation of why? Well this may be the function you should tell them about.
You can use the N() function to add a comment inline with a formula. What it actually is mean't for is to try to convert a string into a number, but if you just put words more »
Excel - Calculating Pi
If you are finding the inbuilt excel function for =Pi() is just a bit too dull, there a handy function to calculate Pi on the fly.
You may want to use it sparingly though, too many times in one spreadsheet or to many decimal places and you'll grind to a halt as it is a bit processor hungry!:
Function myPi(places)
'Places is the number of decimal more »
Excel - Unhide all sheets
This little sub quickly unhides all hidden sheets (including xlVeryHidden). I use it frequently as people tend to hide worksheets which are no longer needed rather than just delete them, also especially useful when trying to dissect other peoples creations.
It also has some sneaky uses, I regularily come across people who like to protect the more »
- Getting ExifTool's move files to directories to work properly as Windows batch file
- How to easily & perfectly align your Stem
- How to remove bicycle tyre marks from painted walls
- What's your Robot name?
- Install apache php7 mySQL and phpmyadmin on Raspbian Stretch on a RaspberryPi
- How to clean the lid and nozzle of your Camelbak Podium bottle
- How to enable and change the Remote IR GPIO pin in OSMC
- Split and extract the first 1000 (N) rows from a text-csv-data file in Windows
- My new scale for measuring oldness
- A datedif function for excel that calculates correctly
- Raspberry Pi 3 HDMI overscan settings and how to correct them
- Create custom Excel function using an array/range input
- SSL on a RaspberryPi - does it slow your site down?
- Remove Real VNC from RaspberryPi Pixel and install TightVNC
- Running a SSL certificated/authenticated HTTPS website over dynamic DNS