A Self Colouring Waterfall Chart

• March 13, 2015
• Marcus Small

The waterfall chart has been around a while and whenever using them the charts have always frustrated me because they were coloured manually.  I decided to have a crack at the problem to produce a chart with 3 different colours, red for when there is a downward trend, green for when there is an upward trend and no colour to help emphasise the movement.

I have done some work in the past and these are the waterfall models I have linked to the Smallman.com

Showing Trends with a Chart

• March 5, 2015
• Marcus Small

Adding a bit of pizazz to charts can be difficult at times.  I tend to stick to what I know works when comparing say budget to actual.  What is nice is to add some trending data to the conversation.  I think it adds value from a visual perspective.  It is nothing the user cannot see when actual is against budget or forecast.  It just adds an extra element to the chart.  I prefer to add trend data as a Line chart rather than use Excel’s inbuilt trending tool.  There is more flexibility in the formatting of a Line chart.

Filter Data in VBA without an Excel Filter

• March 4, 2015
• Marcus Small

In Excel VBA it is possible to apply a filter directly to items contained within an array.  This method, little known, is a way of filtering without the more traditional auto filter.  I have not conducted any tests but as the filter is performed in memory I assume the process happens more quickly than a regular Excel auto filter.  This would make a nice Excel case study.

Excel VBA to Sort an Array

• February 23, 2015
• Marcus Small

I was recently researching the sorting of an array.  I sat in my reading chair and read through John Walkenback’s excellent Power Programming tome on the subject and did some research online when I came across the brilliant .Net feature System Collections ArrayList.  What a find it was as this feature allows you to sort an array and send that array right back into Excel.

Excel Merge Sheets to Master

• February 23, 2015
• Marcus Small

This article draws on the post from the site which is dedicated to merging worksheets in a workbook into a single master sheet Consolidate Sheets.  This technique can be significantly simplified with a VBA macro.   I have posted this type of reply on forums dozens of times.  Howeve this reply differs just slightly.  The following will consolidate data so the data tables appear side by side in the consolidaiton tab.  So if Sheet 1 appears:

Road Traffic Statistics in Excel

• February 21, 2015
• Marcus Small

While going through some road traffic information on the Department of Transport's website it occurred to me that the data was perfect to create a dashboard.  It was information rich and there were very little gaps in the data.  I decided to use quite a few pictures in conjunction with the charts to try and spice them up.  I have used actual data but had to ‘normalise’ some of the data as there were a few gaps.

List All Named Ranges with VBA

• February 14, 2015
• Marcus Small

There are times where you may have a large number of named ranges in a workbook.  I like to have a general rule when creating a workbook.  No more than half a dozen named ranges.  Formula auditing turns into a nightmare if there are too many named ranges.  You may well know what CapitalExpense refers to when you create the named range but as your file gets 20-30 tabs and a dozen other named ranges, finding CapitalExpense can prove time consuming if you have to go to the name manager continually to tell you the location of the named range.  The following is a simple piece of code which will identify all of the named ranges in a file and show what range the named ranges refer to.

Excel Like a Boss

• February 13, 2015
• Marcus Small

Here is a question I see quite a bit on the Excel forums of the world.  It goes a bit like this;

“I am looking to improve my Excel skills, is there a good book you could recommend or any hints which are particularly helpful?”

It is a curious question as I have been asked it many times in my work too.  “How do I get fantastic Excel skills?”  I get the impression people want me to deliver that magic bullet, a trinket of information which upon leaving my mouth and entering their ear canal will enable them  to obtain an Excel mastery in a short timeframe which will have their colleagues going W.O.W!!! 

Spell Check with VBA

• February 12, 2015
• Marcus Small

I recently responded to an interesting post where someone asked for assistance with some code to check spelling for a words in specific cells.  The poster wanted the cells with the errors to have the font highlighted in red if the word was spelt incorrectly.  Here is the code I came up with.

Update Caption with VBA

• February 5, 2015
• Marcus Small

The following Excel VBA procedure is very similar to the article on Status Bar Update with VBA.  The primary point of difference being where the message appears.  The caption which will update automatically is at the top of the screen while the Status Bar is at the bottom of the screen.  As with the status bar procedure it is of particular us in Excel for lengthy VBA procedures.     The following is an example of a caption update in Excel....

The Excel Choose Function

• January 22, 2015
• Marcus Small

Split Text in Excel VBA

• January 19, 2015
• Marcus Small

Excel VBA offers a customised way to split out text within a cell.  Larger systems don't always talk to Excel as we would like and it is sometimes necessary to split the contents of a cell into many parts.  

Split (string, delimiter, limit, compare)

• String - the string you wish to split or separate.

• Delimiter - the character which divides the string into segments

• Limit - The default for limit is -1 which means every delimite...

Excel VBA Slicer Selection

• January 15, 2015
• Marcus Small

Slicers are one of the most attractive ways to show summary data. Introduced in Excel 2010, slicers are a way to show a list of data as buttons on a page. This can be used as criteria in formulas or to filter a pivot table or table.

Starting a Blog

• January 4, 2015
• Marcus Small

I have been toying with the idea of a blog for some time now.  Fact is my website is too large and the menus have too many items so on most screens the material drops off the page.  I am limited as my choice of website builder does not offer scrolling drop down menus.  It might be for the best as having 50 or more items under one menu is less than ideal.