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.