Life on the Road

• May 1, 2017
• Marcus Small

This years travel has finally commenced.  It has been a long time since I hit the road in any real way late last year from memory but once it starts it is the gift that keeps on giving.

Conditional Formatting with Format Cell

• February 26, 2017
• Marcus Small

The article shows how to format an Excel cell with different colours through the format cell feature in Excel.

Excel Dashboards Essential Updates

• January 14, 2017
• Marcus Small

This Excel dashboard displays revenue and expense metrics with some additional KPI data.  

Compare Two Lists on Different Sheets and Output Matches

• December 17, 2016
• Marcus Small

Compare two lists with the Excel VBA scriting dictionary.  Output the results of the matching cells to a third sheet.

Copy/Paste Top n Visible Rows

• November 27, 2016
• Marcus Small

I recently had a question regarding copying data after a filter had been applied.  What if we wanted the top n items from a list to be copied to another place.  The problem to address here is that a filtered list can have cells which are hundreds or thousands of rows apart.

Dependent and Non Dependent Comboboxes

• August 24, 2016
• Marcus Small

Cascading combo boxes have been done online and I had a problem recently which knocked me for 6 as we say here in Australia.  Anyone who deals in cascading combo boxes will know only too well that if you are using a list and the Indirect formula to manage the combo boxes then the process can get very unruly very quickly.  One of the issues with comboboxes is that they are set up to work in sequence.  Combo box 1 feeds into combobox 2 which feeds combo box 3.  The combo boxes need to be filled out in that order.

Advanced Filter a List Automatically

• July 17, 2016
• Marcus Small

I was recently working on an automated procedure to generate data in a list based on drop down (data validation) selection.  The idea is to change items in a drop down (data validation) and have the corresponding data table filter to the specific items chosen in your drop downs.  I have shown a formula based solution for this method (a long time ago) so if a more simplistic solution hits the mark - the link can be found here.

Auto-Highlight the Active Row When a Cell is Selected

• June 20, 2016
• Marcus Small

Something different on theSmallman today - actually something different all together.  Since starting my Excel website and subsequent blog over 3 and a half years ago I have created over 300 pages from financial modelling to dashboard design to unlocking Excel's scripting dictionary.  I have writen every word myself and have drawn extensively on my posts on Ozgrid and Chandoo forums.  Today for the first time someone else is going to do the instructing.  The two handsome gentleman above are Kasper and Mikel.

Water Statistics Infographic in Excel

• June 19, 2016
• Marcus Small

The above infographic is a look at how we use water and what is expected to happen if there is a 2 degrees increase in global temperatures .  Governments are good for changing long term policy for change in our world but if you want to enact real change you have to make it yourself.  If everyone collectively agrees to change - change happens in our world.  Our world will continue to grow in population.  If 7 billion people agreed to enact change as a collective - climate change, world poverty etc, would end very quickly in my opinion.

Excel Create or Remove a Table with VBA

• June 16, 2016
• Marcus Small

There are times in Excel when you may wish to create a table on the fly with the assistance of VBA code.  I was in this position recently and needed to this with VBA.  A file was uploaded into a sheet and the task was to create a table and then use that table for more data manipulation.  The following Excel VBA procedure was what I ended up with.

Jump Directly to a Cell with Formula

• June 15, 2016
• Marcus Small

I was involved in a post to trace the precedents and dependents of a cell.  My answer while accurate was a bit long winded as it turns out (happens more often than I like to admit).  Here is a link to the thread on the Chandoo forum. Chandoo Jump to Cell Thread 

The question was along the lines of how do I jump directly to a cell with formula? I was all about the “click on Formula - Trace Precedents - then click OK and it jumps to the sheet and cell which the formula references.” 

Excel Climate Change Infographic

• June 14, 2016
• Marcus Small

This is an infographic based on our changing climate with statistics from the web showing the current state of affairs, all things climate change.

Excel Infographic Change

• June 13, 2016
• Marcus Small

An infographic showing defence spending in first world countries and other key global metrics in a simple excel infographic,

Male Female Customer Infographic

• June 10, 2016
• Marcus Small

So I am on the infographic trail again.  I have been doing a 3 part series for CPA Australia and have decided to make a few additional Excel infographics just for a bit of fun on the side.  With all of the inforgarphics on the site I have not built complex calculation engines.  I simply put a visual display together which is driven by the contents of Excel cells.  The result is an easy to maintain and update visual report.  With any luck some people might find the results useful in the work they do.

Excel VBA Filter by Icon Sets

• June 9, 2016
• Marcus Small

Excel has been improving the autofiltering capabilities and this single topic forms the topic which I have happened upon the most.   I am probably like a lot of developers who had their eyes opened by the Excel loop through a range when you set criteria and Excel does the isolation for you.  The problem with this method occurs when you need to loop through thousands of rows.  This can slow your procedure considerably.  Using the autofilter with VBA by contrast is very quick and the time difference between a small list and a large list is negligible.  More recently Excel has introduced the ability to filter by icon sets.  The conditional formatting coloured arrows or chart indicators which appear in cell.

Excel VBA Autofilter on Multiple Criteria

• June 7, 2016
• Marcus Small

Anyone who has followed my posts on Ozgrid or Chandoo forum will have noticed how frequently I use the autofilter in favour of any kind of loop.  I have been lead to believe that in some instances that arrays will perform faster than the humble autofilter however for brevity of code and swiftness I really like the simple elegance of the autofilter.  In the article Autofilter on Multiple Conditions I delved into the world of the filer using more than one criteria.

Excel VBA to Remove Data Which Does Not Match

• June 5, 2016
• Marcus Small

In the article highlight column differences I touched on the VBA procedure:

Remove Column Differences

The article was introductory where I did a bit of colour coding of cells which did not match and copied the non matching cells to a fresh sheet.  Well this technique can be used in a multitude of ways.  We can remove all of the rows in a sheet which do not meet criteria as specified in a particular cell. 

For example if we had a listing of sales people we might want to isolate only a single sales person order quantity. 

Excel Copying Variable Columns with Array

• June 4, 2016
• Marcus Small

My tassie friend Valario asked another interesting and engaging question.  The question came from the blog post ‘Read Individual Columns to An Array’.  The design of the code was a little bit static so Valerio’s question was as follows.

Another question just to ruin your night!

Excel VBA Send Files to Zip Drive

• June 1, 2016
• Marcus Small

Zipping up Excel files on the fly can be a most useful activity especially if working with outlook.  You may wish to generate a set of files with Excel VBA then zip those files and send them on to a list of people for review or as part of a monthly reporting procedure.  I have seen plenty of these type of procedures.  The most famous of which is on Ron De Bruin's site.

Ron De Bruin Zipping

The idea behind the concept is to have a file path with files inside it.  The zip procedure runs and sends all of the files to a compressed zip file and saves the file in a designated folder.

Excel Create Child Sheets From Master

• May 31, 2016
• Marcus Small

Recently my friend in Tasmania – Valerio – was kindly helping someone online (the kindness of strangers).  The problem was as follows.  The person had a master list and wanted to create child sheets from this master list.  Sort of a parent to child type exercise where a single list will produce multiple sheets (one to many - see image above).  The problem arises when you have some sheets which have already been created and some new sheets which need to be created.  As such we need to test for the existence of the sheet.  In the following article I explore the VBA code required to test if a sheet exists without the use of a traditional custom function.