Convert Formula to Relative or Absolute

Convert an Excel range from one reference to relative or absolute referencing.

Read More

Create Number Sequence with VBA Custom Function

An Excel custom function that allows sequental number sequences to be created.

Read More

Countifs Across Sheets

Add a count formula across multiple sheets in Excel.

Read More

Excel Autofill - Fill Down to Bottom of Range

In Excel you may wish to drill down on a range which has a header value not already contained in the rows.  The following article shows a practical example of how to generate a unique row name based on the heading in a specific place.  The procedure is in VBA.

Read More

Social Media Dashboard in Excel

This is a social media dashboard created in Excel.  It is free to download and has all of the graphs and source formula in the file.

Read More

Range Areas in Excel with VBA

Trap a range area with VBA and loop to next range area.  This little used Excel VBA technique is awesome and a real win when you learn the mechanics of it.

Read More
Tags , , ,

Revenue and Expense Infographic Construction Industry

This is an Excel infographic covering he construction industry.  It displays revenue and expenses with some relevant pictures and charts.  You should be able to modify it to your industry.

Read More

More Dashboard Updates

This is a post about updates to the Excel Dashboard section of the smallman site.

Read More

Conditional Formatting with Format Cell

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

Read More

Excel Dashboards Essential Updates

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

Read More

Compare Two Lists on Different Sheets and Output Matches

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

Read More

Advanced Filter a List Automatically

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.

Read More

Auto-Highlight the Active Row When a Cell is Selected

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.

Read More

Water Statistics Infographic in Excel

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.

Read More

Excel Create or Remove a Table with VBA

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.

Read More

Jump Directly to a Cell with Formula

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.” 

Read More

Excel Infographic Change

I was reading some statistics on the global condition and decided to create an Excel infographic on the topic.  It is not all bad.  Global poverty has declined in recent years.  The change is heartening and means a better life for millions of people.  Defence spending in first world countries is a problem though.  I believe in peace and looking after your people first.  Spending close to half of your tax revenue on military activities is a questionable use of funds.  Tax evasion or is that avoidance is a problem also.  The big end of town probably have never paid their way.

Read More

Male Female Customer Infographic

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.

Read More

Excel VBA Filter by Icon Sets

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.

Read More