Excel Meme Contest Winners

April 17th, 2014 by datapig 2 comments »

Well the Excel Meme contest is at an end and we have three winners. I received over 100 memes – some of them not usable (tsk..tsk..tsk).

Out of the ones that weren’t too naughty or eaten by my email, I’ve picked three winners.

It was a difficult task to pick just three out of the dozens of creative and very funny memes.

But here they are.

.

Winner: Chris Macro

I’ve had this experience of trying to remain patient as someone scrolls through thousands of rows while telling me his spreadsheet is quote “like 2 million something big”.

.

.

Winner: Szilvia Juhasz

The notion that you can continuously click Undo until you actually get to a previous version of Excel.

Come on…that’s funny.

.

.

Winner: Oz du Soleil

The secret to a funny meme (in my opinion) is to make the words match the picture perfectly.

Oz nails it with Jean Luc Picard. Oh…and…please do stop hiding Column A!

.

.

I can’t possibly show all of the memes here, but I’ll show some of the honorable mentions.

But before I do that, let me give a hearty thanks to everyone who sent me their memes. Believe me – I loved them all.

You can download all the Excel memes I’ve received in one big ZIP file.

.

.

Feel free to comment and let us know which ones are your favorites.

Alphabetical order:

.

.

From Anonymous

.

.

From Chris Macro

.

.

From Daniel Rera


.

.

From Debra Dalgleish


.

.

.

From Doug Glancy

.

.

From Edward Lawrence


.

.

.

.

From Ghazanfar Abidi


.


.

.

.

From Ian Johnson

.

.

From Jeff Weir

.

.

From John Walkenbach


.

.

.

From Jon Peltier


.


.

.

.

From Jordan Goldmeier

.

.

From Karim Jahangir

.

.

From Kevin Lehrbass

.

.

From Kyle Laurendine

.

.

From Mary Alexander (Mrs. DataPig)

.

.

.

From Mynda Treacy


.

.

.

From Rebecca Austin

.

.

From Richy Lee

.

.

From Robert Braun

.

.

From Ross McLean


.

.

From Szilvia Juhasz


.


.


.

.

From Vince Chandler


.

.

From Vitalie Eremia



..


.

.

From Wim Gielis


.


.


..

.


.

.

Thanks again for participating and congratulations to Chris, Oz, and Szilvia!

Have a great weekend!

Speaking of the weekend … it’s not too late to register for our Excel Business Intelligence Boot Camp. Registration will stay open through the weekend.

Support for Excel and Access 2003 Has Ended

April 15th, 2014 by datapig 3 comments »

I’m a little late on this, but Microsoft has announced that they will no longer provide support for Office 2003.

Those still using Excel or Access 2003 will no longer get: Assisted support, Online content updates, Software updates, or Security updates.

If you know of anyone who is still using Excel or Access 2003 give them a hug.

Notes on this picture:

  1. Found it online…can’t remember where.
  2. Office 2003 had some really nice feet.
  3. Apparently Office 2003 was a white man (who knew?)

.

On a personal note, I have a cool new book I’d like to sell. Contact me if you want to buy it.

Excel Meme Contest Update

April 10th, 2014 by datapig 3 comments »

The Excel Meme Contest is underway and the memes are rolling in.

There’s some tough competition out there.

Here’s a preview of some of the submissions; just to get your creative juices flowing. Keep them coming folks!

.

From Edward Lawrence

.

.

From Kyle Laurendine

.

.

From Richy Lee

.

.

From Wim Gielis

.

.

From Mynda Treacy

.

.

From Ian Johnson

.

.

From Chris Macro

.

.

From Jon Peltier


.

.

From Jon Peltier

.

.

From John Walkenbach

.

.

From Robert Braun

.

.

From Bill Jelen

.

.

A few more from me; Mike Alexander


.

.


.

.

.

.


.

. Remember….the rules are simple.

  1. Create a meme that has something to do with Excel or Spreadsheets.
  2. Email your meme to me with the subject line: Excel Meme Contest
  3. Submit as many as you’d like.
  4. Try to keep the memes “safe for work” ; in other words family friendly
  5. Submit all memes by April 16th.
  6. I’ll pick three winners with the funniest memes. I’ll share the winning memes here.

Email your memes to mha105 at Yahoo.com

Excel Meme Contest

April 8th, 2014 by datapig 6 comments »

I’ve got some extra books on my shelf that I want to get rid of.

I’ve decided to give them away as prizes in an Excel meme contest.

>

The rules are simple.

  1. Create a meme that has something to do with Excel or Spreadsheets.
  2. Email your meme to me with the subject line: Excel Meme Contest
  3. Submit as many as you’d like.
  4. Try to keep the memes “safe for work” ; in other words family friendly
  5. Submit all memes by April 16th.
  6. I’ll pick three winners with the funniest memes. I’ll share the winning memes here.

Email your memes to mha105 at Yahoo.com

.

Each winner will win these Two Books. That’s right; you can win not one, but two books.


.

Don’t know what an Excel Meme is?

I’ve created a few Excel Memes to get you in the spirit of things.

Feel free to pass these around the office.

.

.

.

.

.

.

Let hilarity begin.

Two More Weeks to Get on the BI Train

April 7th, 2014 by datapig No comments »

.

I’ve got a new Blog post brewing for this week.

But before that, I’d like to point out that the ticker in the upper-right hand corner of this blog says that you have just 2 more weeks to sign up for our Business Intelligence Boot Camp.

.

Don’t miss this chance to let Bill Jelen (Mr. Excel) and yours truly guide you through the mysterious world of Microsoft’s new Business Intelligence tools for Excel.

In this 3-day event, you’ll learn how to:

* Analyze large amounts of data and report those results in a meaningful way
* Get better visibility into data with new Data Mining tools
* Add interactive controls to your dashboards without VBA
* Automate repetitive data cleansing and transformation tasks
* Create eye-catching visualizations and Dashboards
* Create map-based geo-spatial reporting mechanisms
* Access external data sources to expand your message

Click this link for details: Business Intelligence Boot Camp

Chandoo Interviews DataPig

March 27th, 2014 by datapig 3 comments »

Some of you may know that Excel guru Chandoo has launched a new Podcast where he talks about all aspects of Excel.

Last week I sat down with Chandoo for an hour-long interview on Excel Business Intelligence.

We did it via Skype. It was just like being in India, except I didn’t have to drink bottled water.

.

We talked about the new Excel Business Intelligence roadmap, and how Excel Analysts can start down the path of becoming true BI gurus. I don’t want to brag, but the interview is funny and interesting. Between you and me, I carried Chandoo the whole time.
.

We put a secret prize in the podcast. Listen to find out what it is and how to win it. My interview starts about 2:30 into the audio.

Check it out: Play in new window | Download

.

If you like it, be sure to subscribe to the Chandoo.org Podcast on iTunes: Click here to subscribe.

Understanding Standard Deviation

March 26th, 2014 by datapig 4 comments »

Standard Deviation is one of those statistical terms thrown around the corporate world with vague abandon. Many business analysts don’t truly understand the concept of Standard Deviation. If you’re one of those folks, you can stop living the lie. In today’s Pulitzer worthy post, you’ll learn how this underestimated statistical measure can help you better understand the data you’re working with.

.

What Standard Deviation Measures

Imagine you supervise two deli managers who sell bacon. Since you don’t want any bacon to be wasted, it’s important that these two managers hold a steady inventory. In an effort to measure how well they manage inventory, you decide to analyze the boxes of bacon each manager ordered in the last six weeks. Taking the average of the last 6 weeks shows that each manager orders an average of around 32 boxes of bacon per week. On the surface, the averages make it look like they are performing equally.


But if you look closer, you’ll see that one of the managers has weekly orders of 22, 34, 58, 52, 10 and 21 boxes. For this manager, the average may be mathematically correct, but it hides the volatility of his weekly orders. In other words, sometimes the average of a dataset doesn’t do a good job representing the data. This is where Standard Deviation comes in.

.

Standard Deviation gives you a sense of how dispersed (spread out) the data in your sample is from the Mean (Average). Said another way – it lets you know if you can rely on the Mean to give you a meaningful representation of the data.

.

In our example, we use the STDEV function in Excel to give us Standard Deviation along with our Mean.

In the case of the first manager, the Standard Deviation is 2. This tells us that each data point in the sample sits an average distance of 2 statistical data points from the Mean (Average). Is that good? Well, think of it this way – a Standard Deviation of 0 would say every data point is exactly equal to Mean of the sample (32.3 in this case). So a Standard Deviation of 2 is not far off from that, indicating that a majority of data points are positioned extremely close to the Mean. The closer the Standard Deviation is to 0, the more reliable the Mean is. More than that though, Standard Deviation close to 0 tells us that there is very little volatility in the sample. With a Stanadard Deviation of 2, the first manager’s weekly orders are remarkably consistent.

.

In the case of the second manager, the Standard Deviation is 18.9. The average distance each data point is from the Mean is 18.9 statistical data points. That’s a huge spread! The further away a Standard Deviation is from 0, the less accurate the Mean for that sample. In this case, a Standard Deviation of 18.9 alerts us that the Average shown for this manager (32.8 boxes per week) is just not reliable. It also indicates that this manager’s weekly orders are extremely volatile. Of course, with only six data points, you can confirm the volatility with your eyeball.

.

That is basic Standard Deviation in a nutshell. Although it doesn’t get the attention afforded to other statistical measures (Mean, Median, Mode, etc.), Standard Deviation is actually critical to many statistical calculations. An understanding of how Standard Deviation works will pave the way for you to do things like: determine the volatility of a stock, normalize comparisons between datasets, identify outliers, create standardized z-scores, and much more.

.

How Standard Deviation is Calculated

OK, we know what Standard Deviation shows. Let’s now take a look at how it’s actually calculated.

We’ll start with this set of numbers (in black). As you can see, I’ve already used the STDEV function to calculate the Standard Deviation of 21.6 (in orange).

.

Here are the steps Excel took to calculate 21.6 as the Standard Deviation.

Note these steps are purposefully visual to better relay what’s going on. In reality, all this stuff happens instantly behind the scenes.

.

First, Excel calculates the Mean (the Average) for the Sample. In this case, the Mean is 40. It then calculates the difference between each data point and 40. For instance, the difference between 50 and 40 is +10 statistical data points. The difference between 10 and 40 is -30.

.

.

The next thing Excel does is Squares those differences so that all the differences will be a positive number (+10 would become 100; -30 would become +900).

.

.

Excel then adds up all the Squared Differences to get the Total Squared Differences.

 

 

Next, Excel uses the Total Squared Difference to calculate the Sample Variance. This is done by dividing the Total Squared Differences by the count of data points in the sample minus 1. In this example the count of data points is 7, so we divide the Total Squared Differences by 7-1.

.

.

Finally, Excel calculates the square root of the Sample Variance. This square root becomes our Standard Deviation.

.

.

Population Standard Deviation vs Sample Standard Deviation

In statistics, you’ll often hear the terms Population and Sample. These terms refer to the completeness of the data in your possession. The differences between the two are sometimes not all that clear.

.

If you’re using a complete dataset, you’re using a population. An example of a population would be if you were analyzing the time in service for all the sales reps in your company. You would have the data for all the sales reps that exist in your company; a complete population. 

.

If you are using a partial set of data, or a subset of data, you’re dealing with what is called a sample. An example would be if you were analyzing sales data for one quarter of a year. A quarter is merely a subset of an entire year’s data, so in this case, you’re working with a sample.

.

The reason this population and sample designation matters is because the calculation for Standard Deviation changes slightly depending on the nature of the data you’re dealing with. Specifically, the way you calculate the Sample Variance changes. Remember in Step 4 of the calculation steps above? We said that Excel divides the Total Squared Differences by the count of data points in the sample minus 1. Well, this only applies if your data is a sample (a subset of a bigger data pool).

.

If your data is made up of the entire population, the calculation in Step 4 changes to divide the Total Squared Differences by the complete count of data points. In other words, there is no need to subtract 1.

.

This difference in calculation will obviously yield different Standard Deviations.

.

So why the difference in calculation? Well, the difference is not an Excel quirk. It’s an actual statistics tenent called Bessel’s Correction. Bessel’s Correction states that when you use a sample dataset instead of a population, you need to subtract 1 from the count of data points used (written in the statistics world as N-1). This correction accounts for the bias introduced by estimating a Mean using a subset of data instead of using the true population Mean. The reasoning behind the need for this correction is admittedly a bit difficult for us non-math geeks to wrap our brains around. I’ll try to explain it in my simplified understanding.

.

When you use a sample, you’re using a subset of data chosen from the true population. In this case, you won’t have the benefit of using the true population Mean. You will need to estimate a new Mean based on data you grabbed in the sample. This estimated Mean is already biased towards fitting the data chosen in the sample, so you need to exclude that one point (the estimated Mean) from calculating the Sample Variance. This is called losing a degree of freedom. Another way to look at it is this. If I said you could have five variables in an Excel formula, but one of them would have to be used to calculate the Mean, you would have only four variables available to you (5-1). The fact that you have to calculate Mean loses you a degree of freedom.

.

If you use a population set instead of a sample set, you have the benefit of the true population Mean. So you’re not losing a degree of freedom by being forced to introduce an estimated Mean. Thus there is no need to subtract 1.

.

.

Standard Deviation Formulas in Excel

Excel has the ability to handle Standard Deviation calculations for both population and sample datasets. Simply click in any cell and start to enter a =STDEV. You’ll see a tool-tip dropdown that gives you, what seems to be, a ridulous number of Standard Deviation functions.

Here’s a quick rundown of what each function does.

  • STDEV: Calculates Standard Deviation for a sample using Bessel’s Correction (N-1).
  • STDEVP: Calculates Standard Deviation for a population.
  • STDEV.S: Calculates Standard Deviation for a sample using Bessel’s Correction (N-1). This function technically replaces the STDEV function.
  • STDEV.P: Calculates Standard Deviation for a population. This function technically replaces the STDEVP function.
  • STDEVA: Calculates Standard Deviation for a sample using Bessel’s Correction (N-1). Allows for text and TRUE/FALSE values.
  • STDEVPA: Calculates Standard Deviation for a population. Allows for text and TRUE/FALSE values.

.
I can’t imagine any scenario where I would use the STDEVPA and STDEVA functions. I think you can safely ignore those.
.
As far as I can tell, there is no discernible difference between STDEV.S and STDEV. Microsoft says you should move toward the newer STDEV.S function, but you can technically use those two functions interchangably.
.

Likewise, I don’t see any discernible difference between STDEV.P and STDEVP. Although STDEV.P is the newer function, you can use those two functions interchangably.

.

.

Until next week…Happy Mathing!

Three Tips for Making your Pivot Table Formatting Stick

March 11th, 2014 by datapig 4 comments »

Few things have sent me over the edge like pivot table formatting.

You know what I’m talking about.

You spend precious time formatting your pivot table, just to have that formatting disappear when you change anything in the pivot. After several tries at forcing your custom formatting to stick, you make this face…

.

…then you begrudgingly use one of the PivotTable Styles on the Design tab.

.

Well no more. Today, I’ll give you three tips for creating sticky pivot table formatting.

 

Tip Number 1:.

You know that default setting in the PivotTable options (“Preserve cell formatting on update”)? It’s a giant turd. It seems like it should prevent the loss of formatting, but it doesn’t. Leave it checked and forget about it.

.

..

Tip Number 2:.

There is a difference between formatting a pivot table and formatting the cells around it. Don’t just start highlighting cells. You have to specifically select the area of the pivot table you want to format. For example, if you want to format a Subtotal, you’ll have to hover your mouse over the edge of the Subtotal until your cursor turns into a black arrow. When it does, click the mouse to select that area. After the area is selected, you can format away.

.

.

Tip Number 3:
Clear all filters BEFORE formatting your pivot table.
.

Here is a pivot table I formatted with a Region filter set to Southeast. Everything is lovely.

.

The minute I select another filter criteria (Southwest in this case), my formatting disappears. It seems my pivot table somehow associates formatting with the filter state it was in when that formatting was applied. In this example, I formatted the pivot table while it was filtered to Southeast. So my formatting will only show up when I select the Southeast region. Any deviation from that filter state, removes the formatting. This means if I change the filter or remove the Filter field altogether, my formatting is blown away.

.

 

To avoid this loss of formatting, you can simply clear all filtering before you format. This means ALL filtering (Filter pages, dropdowns, text filters, etc…).

.

Slicers seem to be an exception

Slicers seem to be an exception to the filtering rule. You can have a slicer in place and filtered; your pivot table formatting will not disappear. It looks as though, pivot tables don’t associated filtering from slicers with any kind of formatting state.


.

.

Well, that’s that. Three tips to finally solve that disappearing formatting problem.

Here’s a before and after of someone who used these three tips to formatting her pivot tables.

.

I told you it works.

The Mathematical Reason you Can Never Divide by Zero

February 27th, 2014 by datapig 13 comments »

I was goofing around in PowerPivot when I decided to divide a couple of columns. When reviewing the results I noticed that for some of my rows, PowerPivot gave me a strange value “Infinity”. This is because for these rows, I’m dividing a number by zero.

.

Apparently in PowerPivot, when you divide a number by zero, you get the value Infinity. Infinity means the resulting answer is too large to represent as a conventional floating-point value. This is tantamount to Excel’s #DIV/0! error.

If you somehow divide 0 by 0, PowerPivot will give you the value NaN (not a number) – meaning the answer can’t be mathematically defined.

.

This got me thinking.

We all take for granted that we’ll get an error when we divide by zero; any Excel analysts on the job more than a week knows this. But have you ever stopped to think about the mathematical reason why you can never divide by zero? While the reason is vaguely intuitive, it’s fun to break it down.

.

One way to think about it is to consider what happens when you divide a number by another.

Division is really nothing more than fancy subtraction.

For example, 10 divided by 2 is the same as starting with 10 and continuously subtracting 2 as many times as needed to get to zero. In this case, you would need to continuously subtract 2 five times. So 10/2 = 5.

Now if I tried to do this with 10 divided by 0, I would never get anywhere, because 10-0 is 10 all day long. I’d be sitting here subtracting 0 until my cheap East German calculator and I die (also known as infinity).

10-0 = 10
10-0 = 10
10-0 = 10
10-0 = 10
…… Infinity

.

But wait…it gets more complicated.

.

Consider the smallest non-zero number you can divide a number by.

Well, if I divide 1 by .01, I get 100.

If I divide 1 by .001, I get 1,000.

In fact, the smaller I make my denominator, the bigger my result will be.

No matter whatever candidate smallest number you think of, there is a number smaller than that. AND the closer you get to zero, the bigger the result….until Infinity.

.

Now here’s the wonky bit.

When you start from a negative denominator, the closer you get to zero, the smaller the result until -Infinity.

 

So a number divided by a very small denominator (close to zero) has the potential of being either close to Infinity or Negative Infinity (depending on whether it’s postive or negative). This is of course irrational. For this reason, mathematicians consider any number divided by zero to be Undefined (aka…we don’t know what the hell to do with this).

.

This is the reason why, Excel, PowerPivot, Access, SQL Server, and every other computational system freaks out when you try to divide by zero.

.

Luckily, there are ways to get around divide by zero. In standard Excel, you can write a simple if statement to check for a zero denominator:

IF(B1=0,0,A1/B1)

.

In PowerPivot, we get a nifty DAX function called DIVIDE. The DIVIDE function essentially handles the divide by error for you:

DIVIDE([Numerator],[Denominator],0)

.

Check out Rob Collie’s post to learn more about the DIVIDE function.

.

So now you know.

 

Waffle Charts with Conditional Formatting

February 27th, 2014 by datapig 3 comments »

For those who don’t know, a waffle chart is an interesting visualization that helps display progress toward goal. It’s basically a square divided into a 10×10 grid; each grid box representing 1% toward a goal of 100% percent. The number of grid boxes that are colored or shaded is determined by the associated metric. This kind of chart is a relatively effective option when you want to add an interesting visualization to your dashboard without distorting the data or taking up too much dashboard real estate.

.

A few months ago, I showed you how to build waffle charts using actual Excel chart objects. Today, I’ll show you a much simplified technique using little more than conditional formatting. With this technique, you can even get fancy and add more than one color, representing progress toward an intermediate goal. For example, the yellow boxes shown in this waffle chart represent actual performance, while the blue boxes represent where the metric should be at this time (quarterly goal, weekly goal, whatever).

.

Let’s walk through the steps:

 

Step 1: Set up the component pieces

Create a metric cell (in this example, B5) which will capture the actual performance.

Then set up an optional intermediate target cell (B9) if you want to add an extra layer of coloring to represent a quarterly or monthly intermediate target.

Finally, create a 10×10 grid of percentages that range from 1% to 100%.

 

Step 2: Create the Intermediate Target Grid Boxes

If you choose to include and intermediate target, it’s important to color those first.

Highlight your 10X10 grid and select Home>Conditional Formatting>New Rule.

Create a rule that colors each cell in your 10X10 grid if the Cell Value is less than or equal to the value shown in your intermediate target cell (B9 in this example). Click the OK button to confirm the conditional format. Be sure to apply the same color format for both the fill and the font. This will ensure the percentage values in your 10X10 grid are hidden.

 

Step 3: Create the Actual Performance Grid Boxes

With your 10×10 grid still highlighted, Home>Conditional Formatting>New Rule.

Create a rule that colors each cell in your 10X10 grid if the Cell Value is less than or equal to the value shown in the metric cell (B5 in this example). Click the OK button to confirm the conditional format. Again, be sure to apply the same color format for both the fill and the font. You will want to choose a different color that that which you selected for the target cell.

 

Step 4: Clean up the Formatting

Highlight all the cells in your 10×10 grid and apply a default gray color to the cells and font. Also apply a white border to all the cells.

At this point, your grid should look similar to the one shown here. When you change the metric or target percentages, your grid should automatically adjust colors to reflect the data change.

 

Step 5: Create a Linked Picture

Copy all the cells in your 10×10 grid, and then click the Paste dropdown arrow on the Home tab. Select the Linked Picture icon.

.

Excel will paste a picture of your grid to the worksheet. Resize the picture to the shape and size you need. The linked picture will automatically reflect the state of your actual grid. You can copy and paste this linked picture to your actual dashboard interface.

 

Step 6: Add a Dynamic Label

To add a label to the waffle chart, click on the Insert tab in the Ribbon, select the Text Box icon, and then click on your worksheet to create an empty text box. While the text box is selected, go up to the formula bar, type the equal sign (=), and then click the cell that contains your metric cell.

.

Overlay the text box on top of your waffle chart.

.

The reward for your efforts is an attractive graphic that helps your audience visualize performance against a goal.