Import Website Data to Excel

• October 15, 2015
• Marcus Small

I was recently asked to repair a webquery which had gone bad.  The company controlling the website had changed the layout of the content.  I decided to extract the data from the website into excel using VBA and a call on the HTML tables inside of the website itself.  Obviously this technique works best where there are dedicated tables on a website.  It does not discriminate as all data from the page is imported but with some smart manipulation you can inject some VBA to extract the relevant tables into a fresh worksheet.

Convert Vertical Range to Horizontal with VBA

• September 12, 2015
• Marcus Small

I was recently asked by a my friend Joe to help him with an Excel problem.  He wanted to convert data in a vertical list into a horizontal list. This sort of thing is one of those things which is usually done with a pivot table.  What if your data is a unique ID and clients which can be attached to the same unique id.

Filter Source Data in Excel with Slicer

• September 10, 2015
• Marcus Small

I was recently answering a post on Ozgrid about filtering a list using a slicer.  The post was very similar to a blog post of mine with a slight twist.  The poster wanted the original list filtered based on the selection of the slicer and if no slicer item was selected then the filter was to be taken off the dataset.  I found the problem interesting because in order to solve the problem you had to know how many slicer items were in the list in the first place.

Excel VBA Colour Function

• September 8, 2015
• Marcus Small

This is an alternative to conditional formatting.  The following will detect the colours Red, Yellow and Green in a particular cell.  It will add the name of the colour – good for those colour blind individuals.  It is a custom function so needs to used in conjunction with a formula or in a VBA procedure.

How to Share Un share an Excel Workbook

• September 6, 2015
• Marcus Small

Recently I was assisting someone on a forum and the workbook they had supplied had been saved as a shared workbook.  I have not used shared workbooks and was at a bit of a loss regarding how I might un share the document.  I needed to un share it in order to make changes to the structure of the workbook – add lines/columns.  These are the steps to share or un share a workbook.

VBA Open File Dialog

• August 22, 2015
• Marcus Small

During a procedure you may wish your user to choose a file to open.  This can slow the process of running code down but if you have a moving target this will be essential in getting the right data imported or manipulated via VBA.  I have not had to do this very often but a client asked if they could choose a file mid procedure and the following is what I came up with.

Adding Mulitple Criteria in a Single Column

• August 9, 2015
• Marcus Small

Creating a sum where the criteria is in the same column has usually been about creating a sumif or sumifs and adding multiple iterations together.  This turns formula which intuitively should be short into long, difficult to read formulations.  With the intelligent use of Sumproduct you can use one formula to capture multiple criteria in the same column.  Here is an example.

Infographics in Excel (part2)

• July 16, 2015
• Marcus Small

The introduction of infographics in Excel to the site has grown faster than I anticipated.  The above image is just a sample of some of the infographics on the main part of the site.  It has been a bit of a learning experience as I have picked up tips and tricks from the extensive online resource.  I will continue to grow the infographics section until I run out of room which seems to be about 25 items in the menu.  Not too far till I get there so will just add them to my blog if I ever reach that number.

Group Like Data with VBA

• July 9, 2015
• Marcus Small

Grouping information in Excel is a way of hiding data, while telling the user there is data hidden.  In my opinion it is a preferable way to "hide" information as you are aware that there is data grouped.  Hidden rows in the conventional sense are often hard to detect.  You may find out by accident after you have copied information that there were rows hidden.  It is often a surprise.

Copy Multiple Sheets with VBA

• July 5, 2015
• Marcus Small

I was asked during a webinar recently how you send multiple worksheets to a new workbook in a batch.  I was pretty sure this information would be on my site but a quick search of my site did not reveal any joy.

I put together a simple file which sends an output sheet and two source data sheets to a directory saves it then starts the process again after changing a unique identifier.  The process is a little more in-depth than sending just one sheet but there is not a great deal more code involved.

Isolate Sub Groups in Excel

• June 1, 2015
• Marcus Small

There are times where you may wish to report sub groups within departments.  It can be difficult when the sub groups are varying lengths.  Here is a solution to that problem.  The formula will test for the first item in the sub group and return a result, then test for a second sub group and if found return a result.

The formula is as follows.

=IFERROR((OFFSET($B$7,MATCH($A$32,$A$8:$A$18,0)+

IF(COUNTIF($A$8:$A$18,$A$32)>(ROW(B32)-ROW(B$32)),(ROW(B32)-ROW(B$32)),NA()),0)),"")

Infographics in Excel

• May 13, 2015
• Marcus Small

Infographics in Excel is not a new concept but these sort of graphical representations are very powerful and convey a striking message.  Infographics are usually not as dynamic as a dashboard, they tell a striking story.  This is not to say that the source data which populates the infographic can't be dynamic in nature. 

My website is about to explode with Infographics as I publish a swath of interactive Excel Infographics for people to plug and play with.

Social Media Dashboard in Excel

• May 1, 2015
• Marcus Small

A social media Dashboard. It outlines social media activity, visits to pages, likes, retweets. The dashboard focuses mainly on Pinterest, Facebook, Twitter and Google +.

Incident Tracking Dashboard

• April 27, 2015
• Marcus Small

The dashboard tracks incidents by year and by cost. It is only a small dashboard but has stacks of functionality.

Evaluate and Filter Part 2

• April 26, 2015
• Marcus Small

This post is a follow up from the blog post earlier in the month on Filtering without a filter.  It is an alternative to the autofilter method I have outlined extensively.  The post focuses on filtering out more than one criteria and putting the results in a fresh sheet.  To do so we use the evaluate method.  This is relatively complex, however if we break down what needs to change we can significantly simplify the process.

Excel Dashboard by Department and Region

• April 25, 2015
• Marcus Small

Updating Excel dashboards, thesmallman.com

Move Sheets Based on a List with VBA

• April 12, 2015
• Marcus Small

I recently had a problem, I had a summary sheet which was calculating all of the data between a beginning sheet and an end sheet.  However, the sheets in-between the begin and end tabs were changing based on a selection from a list.  What I wanted was the flexibility to move the sheets in and out based on what had been selected in this list.  The summary will be updating once the procedure runs and the end result needs to be flexible.

Once again we will need VBA to produce a result. 

Evaluate and Filter in Harmony

• April 4, 2015
• Marcus Small

Performing a check on things like the sheet name with the Evaluate method avoids a looping construct. This tutorial show you how it is done.

Bubble Chart in Excel

• March 26, 2015
• Marcus Small

Creating Bubble charts in Excel is reasonably straight forward.  They are very similar to a line chart without the line and with a bit more pizazas.  You will find quite a few Bubble Charts onthesmallman.com particularly in the dashboard section.

The following article will take you through how to make one.

Format Chart Numbers as Thousands or Millions

• March 23, 2015
• Marcus Small

Recently I have been creating some comparison charts (prior year agains projections) and usually the numbers in my tables have been in a format which would suit.  In this recent project though the data was in large whole numbers and I wanted to format this into a chart so it appeared as millions.  In order to get from whole numbers to millions I had to work with the number format in what was a slightly unintuitive way and I hope by sharing this that I save you some time in the process.