Halloween and Dressing up your Charts

October 30th, 2014 by datapig No comments »

Tomorrow is Halloween here in America; a time for candy and costumes.

Unfortunately, Halloween falls on a work day. I hate that.

There is always peer pressure to participate in 'Halloween Costume Day'. What is Halloween costume day? It's a day where, in the name of fun, all employees are encouraged to dress up in a costume for the day. This means the people you normally hate are now working around you dressed as robots and witches.


Well if you're feeling pressure to dress up for Halloween, here are three simple costume ideas I pulled off the web. All of these are just the ticket for the lazy passive-aggressive employee in your life.


Identity Thief

Get some "Hello My Name Is" stickers and write a bunch of names on them. Then just stick them to your shirt to become an Identity Thief.


Error 404 Message

This idea has been around for some time. If you're completely lazy, you can take a white T-shirt and become an Error 404 message.


50 Shades of Grey

If you want to be a bit more provocative, get some color samples from your local hardware store and create a 50 Shades of Grey costume.



Using Paste Special Chart Formats

Speaking of dressing up, here's a quick trick that lets you dress up your unformatted charts.

After you've expertly formatted a chart, you probably want to apply that same format to other charts.

Instead of doing all that work again, you can use the Paste Special Formats option on your unformatted charts.


Step 1: Select your formatted chart and press Ctrl+C on your keyboard

Step 2: Select your unformatted chart, click the Paste dropdown on the Home tab, and then select Paste Special.


Step 3: On the Paste Special dialog box, select the Formats option and then click OK.



If all went well, your unformatted chart will be all dressed up and ready to go.

Repeat these steps for all your charts.


Happy Halloween!

Add a DropBox Account to your Office 2013 Save As Screen

October 27th, 2014 by datapig 4 comments »

I use DropBox for many of the things I do: I pass files to clients, I collaborate with co-authors on books, and I store documents I want to keep in the cloud. For the better part of a year, I was managing my DropBox documents via their website. I finally did a little research and found a way to add my DropBox account to my Save As Screen in Office 2013.


If you use DropBox, I highly recommend this nifty trick.


Step 1: Download and Install the DropBox Application

This will create a new DropBox directory (typically C:\Users\YourUserName\DropBox)


.Step 2: Download and Run the DropBox Batch File

DropBox offers a .bat file that will automatically adjust your registry (only for Windows 7 and higher) to add DropBox as cloud option in Office 2013. Download this .bat file to your machine and then double-click it to run. When you run it, you'll see a command prompt asking you for the location of your DropBox directory. Simply type or paste the full path then press enter.


Once you get positive confirmation that the script ran successfully, close the command prompt window.


.Step 3: Add a new Storage Location

At this point you can open any Office 2013 application and click the Add a Place option. From here, simply select the DropBox option. This action adds the DropBox location to all your Office applications.



I wouldn't say it saves me loads of time, but it's definitely nicer to stay in the Office environment even when I'm managing my DropBox account.

Hack into Password Protected VBA Projects

October 8th, 2014 by datapig 19 comments »

A few days ago, I had to pull out one of my old tricks to hack into a password protected VBA module. The file I was working on was protected by a former (evil) employee who, for some reason, did not leave the VBA password.


I'm notoriously lazy, so there is no way I'm retyping code that's already there. I'd rather hack into the workbook.


Today, I'll walk you through the steps I use to crack VBA passwords. I learned this trick a while back from one of my Excel Boot Camp students. I can't remember who. Sorry…whoever you are. Even though I can't remember your name, I value our friendship greatly. » Read more: Hack into Password Protected VBA Projects

5 Rarely Used VBA Tricks

October 1st, 2014 by datapig 12 comments »

Hey folks! I'm back from a long hiatus. I've been down with (of all things) the Shingles. I know…weird. Apparently, if you've had the chicken pox as a child (which I did) there is a chance that the dormant virus causing chicken pox can rear its ugly head and develop into Shingles. It's actually fairly rare in men my age to get Shingles.


So in celebration of my recent recovery from my rare Shingles outbreak, I'll share with you a few rarely used VBA tricks. » Read more: 5 Rarely Used VBA Tricks

Enable Double-Click and Right-Click with the Essbase Add-in

August 27th, 2014 by datapig 2 comments »

Last week, Dick Kusleika and I conducted some training for the good folks at TD Ameritrade. While training, some of the folks found that Excel wasn't responding when double-clicking or right-clicking the mouse. As most of us know, there are plenty of shortcuts and productivity gains to be leveraged in Excel with the mouse. But these folks weren't able to do any of those mouse actions.


It turns out the issue was the Essbase Add-in. » Read more: Enable Double-Click and Right-Click with the Essbase Add-in

The Huey Louie Dewey Error Message Needs an Update

August 15th, 2014 by datapig 1 comment »

Every so often, when working in Access, I get the ever delightful Huey Louie Dewey error message.

Never heard of it? Well, when you try to specify query criteria with commas and no operators or quotes, you get this error message:

The expression you entered contains invalid syntax, or you need to enclose your text data in quotes. You may have entered an invalid comma or omitted quotation marks. For example, if the Default Value property of a text field is ''Huey, Louie, and Dewey,'' it must be enclosed in quotes if you mean it as a literal text string. This avoids the confusion with the expression ''Huey Louie'' And ''Dewey''.


When I saw this error message today, I got to thinking » Read more: The Huey Louie Dewey Error Message Needs an Update

Understanding Weighted Averages

August 12th, 2014 by datapig 5 comments »

One of the first warnings an aspiring analyst hears is, "never take an average of an average". The reason for this warning may not be intuitive, but it's important to understand why it's generally a bad thing to average a bunch of averages. In today's post, I'll attempt to explain the problems with using simple averages and how Weighted Averages can help you avoid inaccurate conclusions about your data.


Where Straight Averages can Go Wrong

Imagine you manage two sales reps (Jim and Tim). These two sales reps have been working for you for three months. Over the last three months, you've collected data on their win rates vs. sales calls. After three months of selling » Read more: Understanding Weighted Averages

Dashboard Tools Add-In Update

August 5th, 2014 by datapig 2 comments »

I've been pretty busy with client work, so no new blog post today. I did, however, want to share some updates I've made to my Dashboard Tools Add-In.


If you don't know yet, the Dashboard Tools Add-in is a utility that helps you: enhance your dashboards with stylized graphics, find viable color schemes, and create dashboard mock ups.


For this update, I added two new tools: a Color Capture tool and a Customize Percentages tool.


The Color Capture Tool

The new Color Capture tool is based on the eye dropper tool developed by » Read more: Dashboard Tools Add-In Update

Quantifying Subjective Text with an Excel-Based Sentiment Analysis Tool

July 24th, 2014 by datapig 8 comments »

Sentiment Analysis is a growing field in the world of BI and Data Science. With social media serving up millions of data points by way of comments, tweets, and status updates, it's becoming increasingly important to be able to take all that subjective text and turn it into quantitative data that can be integrated with other metrics. That is essentially what Sentiment Analysis is - a term that describes the exercise of quantifying subjective text by analyzing certain keywords deemed to be either positively or negatively charged.


How Sentiment Analysis Works

In its simplest form, a Sentiment Analysis algorithm can be nothing more than determining if a text string has more positive keywords than negative keywords. Text with more positive words is classified as positive content, while » Read more: Quantifying Subjective Text with an Excel-Based Sentiment Analysis Tool

Generating Normally Distributed Random Numbers – Sample Data that Makes Sense

July 16th, 2014 by datapig 9 comments »

Like many of you, I'm often asked to develop mockups (working demos of dashboards and reports).

In my zeal to make my mockup as realistic as possible, I spend a good bit of time generating sample data.


For example, if I'm mocking up an HR dashboard, I'll dedicate some brain power to generating salary data. Now in order to make salary data realistic, there has to be a believable mix of high salary and low salary records. I can't just use the RAND and RANDBETWEEN functions Excel provides. These functions generate random data in a "Uniform Distribution". That is to say, every number has an equal chance of occurring with an equal frequency.

. » Read more: Generating Normally Distributed Random Numbers – Sample Data that Makes Sense