Auto-Highlight the Active Row When a Cell is Selected

June 19, 2016 0

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

June 18, 2016 0

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

June 15, 2016 0

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

June 14, 2016 0

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 Climate Change Infographic

June 13, 2016 0

There is a stack of information online about climate change.  Excel is a
wonderful tool to display and change this sort of data.  I have made an
infographic based on our changing climate with statistics from the web
showing the current state of affairs, all things climate change.  This
Excel inforgraphic displays people’s views on climate change and the
glaring reality which we face as a result of our actions.  Who would have
thought that digging up or siphoning lots of carbon and releasing it back
into the atmosphere would not have an affect on our planet’s weather. Read More

Excel Infographic Change

June 12, 2016 0

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 in Excel

June 9, 2016 0

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

June 8, 2016 0

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

1 2