Tag Archives: Microsoft Excel

Named Regions and Data Validation – Great Time Saver

I was given a curious problem the other day by a client who required some Microsoft Excel consultancy.  They had an issue whereby multiple worksheets referenced the same information in data validation drop downs.  So lets say one of these drop downs was people within the team and was used to assign work to individuals.

Continue reading Named Regions and Data Validation – Great Time Saver

Microsoft Excel Custom Formats – Celcius

Microsoft Excel LogoMicrosoft Excel is an amazing tool and provides a multitude of different formatting options.  However, there isn’t one for Celcius (or Fahrenheit for that matter).  Have you ever being in a situation where you need to compare temperatures?  Well I have.  A recent course I delivered required this exact thing.

Continue reading Microsoft Excel Custom Formats – Celcius

Basic data skills are needed EVERYWHERE

Microsoft Excel 2010 LogoI’m a data geek – always have been and am likely to always be one.  I love to mess around with data.  See trends.  Get the insight.  See something in the data that others cant.  The other side of what I like is to make my life as easy as possible.  I use VBA extensively in my reports so that all I need to do is review the data and press a button.

Now this may seem pretty dull and boring to some of you but, let me tell you a story. Continue reading Basic data skills are needed EVERYWHERE

Microsoft Office 2013 – the smart move

Office-365-LogoThere are very few businesses that don’t run Microsoft Office products.  This isn’t because of some subversive approach by Microsoft to pre-load Office onto everyone’s systems.  It is simply because, in my humbled opinion, the best suite of products on the market.

What’s more they are evolving!

Office2013 brings as many dramatic changes as 2003 to 2007 did.  Remember when the toolbar was removed in favour of the ribbon.  Come on how long did it really take you to get used to it?

Office2013 is built for businesses.  Why spend thousands of pounds on expensive shared storage and backup systems when Microsoft provide it for you free?  Office2013 ensures that all your documents are stored in the cloud by default and are accessible anywhere.

Continue reading Microsoft Office 2013 – the smart move

Microsoft Excel – Weekday Function and VLookups

Microsoft Excel 2010 - LogoMicrosoft Excel is the world’s most used spreadsheet tool.  But what do people use it for really?  I would admit that I began using it at school for creating lists and slight calculations.  However, now with what I have learnt in terms of automation, KPI creation, VBA and analysis. Continue reading Microsoft Excel – Weekday Function and VLookups

Microsoft Excel – Number of Working Days in a month

Microsoft Excel 2010 - LogoI came across a question on LinkedIn from someone who wanted to know how to count the number of working days in a month.  Now I know that there is a WORKDAY function within Excel which is supposed to take into account holidays but, this doesn’t take into account different national holidays in different time-zones (to the best of my knowledge).

Continue reading Microsoft Excel – Number of Working Days in a month

Getting Day Names and Month Names in Microsoft Excel using VLOOKUPS

Microsoft Excel 2010 LogoWhen creating Dashboards, insight reports and spreadsheets it is something important to give day names or month names rather than a specific date string.  Now this isn’t as hard as it looks but, all that Excel provides you with is a day number or month number.  So why not translate that into something more meaningful?

This post will show you how to change day numbers and month numbers into names in two methods.  The first by using VLOOKUPS (don’t worry they aren’t are terrifying as that) and secondly in VBA (visual basic for applications) which will be published in a second blog post.

Continue reading Getting Day Names and Month Names in Microsoft Excel using VLOOKUPS

Microsoft Excel – HMRC CIS how to record payments and aid reporting

Microsoft Excel – HMRC CIS how to record payments and aid reporting

I have a number of clients that need me to speed up their administrative tasks.  Now this may seem rather dull to some consultants, who much prefer writing use cases and looking into data, however I find that showing people how things can be done easier (Business Process Improvement) is fantastically rewarding.

In the UK’s construction industry scheme we have a method of paying subcontractors and short term employees.  The way it works is that you have a sub-contractor (who has already registered himself with HMRC) and we take their UTR (unique tax reference) their national insurance and then deduct 20% or 30% from their pay.  This deduction gets sent to HMRC each month.  So in fact main contracts managers become little tax collectors.

Continue reading Microsoft Excel – HMRC CIS how to record payments and aid reporting