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.

A Self Colouring Waterfall Chart

• March 13, 2015
• Marcus Small

The waterfall chart has been around a while and whenever using them the charts have always frustrated me because they were coloured manually.  I decided to have a crack at the problem to produce a chart with 3 different colours, red for when there is a downward trend, green for when there is an upward trend and no colour to help emphasise the movement.

I have done some work in the past and these are the waterfall models I have linked to the Smallman.com

Showing Trends with a Chart

• March 5, 2015
• Marcus Small

Adding a bit of pizazz to charts can be difficult at times.  I tend to stick to what I know works when comparing say budget to actual.  What is nice is to add some trending data to the conversation.  I think it adds value from a visual perspective.  It is nothing the user cannot see when actual is against budget or forecast.  It just adds an extra element to the chart.  I prefer to add trend data as a Line chart rather than use Excel’s inbuilt trending tool.  There is more flexibility in the formatting of a Line chart.

Filter Data in VBA without an Excel Filter

• March 4, 2015
• Marcus Small

In Excel VBA it is possible to apply a filter directly to items contained within an array.  This method, little known, is a way of filtering without the more traditional auto filter.  I have not conducted any tests but as the filter is performed in memory I assume the process happens more quickly than a regular Excel auto filter.  This would make a nice Excel case study.

Excel VBA to Sort an Array

• February 23, 2015
• Marcus Small

I was recently researching the sorting of an array.  I sat in my reading chair and read through John Walkenback’s excellent Power Programming tome on the subject and did some research online when I came across the brilliant .Net feature System Collections ArrayList.  What a find it was as this feature allows you to sort an array and send that array right back into Excel.