Tag Archives: Excel

Optimising your time when doing VAT Quarterly Returns

Now I am going to say this from the start.  I am not an accountant I just happen to be very good at finding solutions to problems and relatively good at Excel.

I say relatively good because I know people who are far better than me.

I help a small family run business optimise their VAT process.  Previously everything was written down on a bit of paper and then all the receipts put into a file with invoices and finally the analysis on top.

Continue reading Optimising your time when doing VAT Quarterly Returns

Microsoft Excel – Using VBA to check values in different columns and apply formatting

Microsoft Excel LogoExcel it a complicated beast.  When I first started with spreadsheets I was always putting things in columns and rows and not doing much with it.  Since learning VBA about 15 years ago I have taken my knowledge to the Nth degree.  Recently, I ran a VBA workshop and one of the delegates asked me a question which perplexed me for a while.

“I want to be able to check two values and if they are equal then I want the “check” column to turn green and a plus sign be the value.  Oppositely if the cell values don’t match then I want the “checked” cell value to be a minus sign and the cell colour to be red.”

Seems simple doesn’t it.  Now try and do it without conditional formatting because that was my first thought too.

Continue reading Microsoft Excel – Using VBA to check values in different columns and apply formatting

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

 

Microsoft Excel 2010 - LogoMy last post I blogged about how to get the Day Names and Month Names using VLOOKUPs.  However, today let’s look at doing exactly the same but, using VBA (Visual Basic for Applications).  Now for the non-coders reading this it may seem kinda scary but, give it a go and you may be surprised about how easy coding in Excel can be.

I would recommend though that you learn in a practice document that isn’t going to affect real data or an existing document.

 

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

Dashboards and Sparklines

 

Has anyone else been infuriated with the limitations of Excel in dashboard building?  Have you been tempted with solutions like Tableau and Xcelcius?  Well you wouldn’t be alone…  However, as most of my clients already have Microsoft Excel I have found that we have to work with what is available.  Recently, I can across Sparklines and almost feel invigorated with the new graphs and the scalability of said graphs. Continue reading Dashboards and Sparklines