How to Create New Categories of Data

This happens all the time; you get a set of data and you realize that whatever category is there, is wrong.  If it’s regions, than there should be 5 regions instead of 8.  If it’s product categories, there’s supposed to be a different grouping.  And of course there are 10,000 rows of data that need changing.

To watch the video showing how to re-categorize the data quickly and easily, click here.

Posted in Best Practices, Excel, Video | Tagged , , , , , , , , , , | Leave a comment

How CBS’s “Criminal Minds” can make you a better Business Intelligence Professional

Watching TV isn’t being lazy… it’s research.

How do you find the person you need to talk to?  What do you talk about with people you don’t meet very often?  How do you remember details that make a difference in getting meetings?  I learned three important techniques by watching Criminal Minds.

Want to be a better BIP? Watch Criminal Minds

1. Keep files on people.

“Office of Unfettered Omniscience – how may I help you, O fortunate one?” (Penelope)

Keep a file on the people you connect with. It is amazingly helpful to remember someone’s drink or food preferences, for example. If a year goes by and you haven’t connected with them, it show that extra bit of caring to suggest going out for a caramel latte instead of saying “let’s get coffee.”

We respond to others that remember details.  Those details tell us the other person was listening to us when we were talking.  Mentioning those details can make a huge impression and helps your requests stand out.  Dentists and doctors do this.  They write down some facts about you in your file, and then when they meet with you they have something personal to talk about with you.  They do it because it really works.

2. Make notes during and after meetings.

“I don’t believe that intelligence can be accurately quantified, but I do have an IQ of 187, an eidetic memory, can read 20,000 words per minute…  Yes, I’m a genius.” (Reid)

I try to write notes after any meeting.  Jotting down preferences and facts (like four older siblings, drink preference, etc).  It does take a few minutes. Others may have moved on to other meetings, or gotten in their car and left the appointment.  But by staying and writing a few notes, you will have saved some critical facts and information that may have a lasting effect.

3. Track down all leads.

“ You’ve got a problem. Deadbolt’s the number one password crack-resistant software out there. You’re gonna have to get inside this guy’s head to get the password.” (Garcia)

“I thought I was calling the office of Supreme Genius.” (Morgan)

When someone mentions a person that may be able to help you or a person that could use your help… follow up on that lead.  I once followed four leads in a row.  I called one person, and we spoke for 10 minutes.  I asked them if there was someone else I should reach out to.  They gave me a name.  I found a number, called them, and we spoke for 10 minutes.  I did the same process over and, after four leads, I had a meeting scheduled with the last person. What was the conclusion? The last person offered me a job before I had even filled out an application.

If I would have stopped at the third lead, I wouldn’t have gotten the job.   Were the conversations with those first three contacts interesting? Not really.  It was more of an exercise in “finding the right person.”  But following up on all the leads produced a happy ending in about the length of an episode of “Criminal Minds.”

Photo Credit: Zap2it

Posted in Best Practices, Communication | Tagged , , , , | Leave a comment

Checklist for Going on Vacation

picture of laptop at beach

Checklist for Going on Vacation

I had a week vacation coming and wasn’t sure “exactly” what week I was going to take. In the rush to get out of the office I didn’t cover everything I should have.  That following Monday, I missed a meeting and had my boss ask me about deadlines.  I and even had some colleagues from Scotland call me while I was making breakfast for my kids.  I realized that I should have had this checklist:

Checklist for Vacations
1.       Know what meetings you need to re-schedule
2.       Know what meetings you can cancel
3.       Turn on your out of office e-mail assistant
4.       Notify people who need to know you’re leaving
5.       Forward your phone to your cell phone

1. Re-Schedule Meetings
Look through your meetings and look at who is attending.  If there are major projects or major “players” attending the meeting, you should reschedule the meeting.  You don’t have to offer an explanation.  “I’m on vacation this week” is not necessary.  You can just say, “Due to schedule conflicts, I’m moving this meeting.”  Or just leave it at “Rescheduling.”  It’s up to you how much you want your personal life involved at work.

2. Cancel Meetings
It’s going to be crazy enough when you get back.  You don’t have to reschedule every meeting.  Some can be deleted.  Realize the importance of your time and priorities when you get back.  Think to yourself, “What absolutely has to get done when I get back?”  If it doesn’t pass that test, then cancel the meeting.

3. Out Of Office E-mail Assistant
This is a checklist.  We all know to do this, but we forget.  So here are a few items to include in your out of office.

  • When you will be back
  • Whom to contact in your absence
  • What to do in an emergency

4. Notify People
Even if you have your out of office e-mail set, you still need to contact a list of people who need to know you’re leaving.  Do it in one e-mail or make the e-mail up and send it to each person separately.  Either way, you have to notify some people.  Here’s a brief list that you can customize:

  • You boss will need the reminder
  • Anyone you listed in your out of office e-mail
  • Major stakeholders of projects you’re working on
  • Anyone depending on a deadline you’re going to miss

5. Forward Your Cell phone
This is more of a philosophical choice for you.  But most companies have a tech support group that can set up this option for you.  Any call that goes to your work phone can be forwarded to your cell phone.  Getting a voice mail box of 50 items when you get back is enough to make you just ignore them all.  If the calls go to your phone you have the choice ignore or take them.  Usually these calls will be short and you’ll save yourself a bunch of useless work when you get back.  I say “useless” because people will call you, leave a message, and then send you an e-mail realize you’re on vacation and then figure it out themselves.  This leaves you with a pile of follow up items that were all canceled.  Take the call, tell them you’re on vacation and everyone moves on.

If you have more items that need to be added, please share in the comments.

Photo credit: Spree2010

Posted in Best Practices | 10 Comments

OLAP – The Business Intelligence Podcast [BIP]

(File size: 21Mbs, Show Length: 23 minutes)

Awesome theme show music by: Matt West – “Let’s Move”

Show Notes:

Found Our Purpose at TED
TED video by Simon Sinek: How great leaders inspire action.  How you present information to reach different parts of the brain first.  What is the purpose of

OLAP (Online Analytical Processing

What is the definition of OLAP? What OLAP does for a business and the mindset of OLAP and how that can unclutter your job.

Count Colored Rows

Figuring out how many rows are colored on an Excel spreadsheet can be unbelievably frustrating.  This video shows you how to do it and save you hours of aggravation.  @Pilchy was ready to put a shotgun to his head because of this problem… I guess you could say we saved his life.

Questions or comments for this, or the next show? Is there a reporting emergency?  Or have you always wondered if there was a better way of doing something?  Share with the group in the comments below!

Posted in Best Practices, Excel, Podcast, Presentations, Purpose | Tagged , , , , , , , , , , , | Leave a comment

Business Intelligence Professionals Are Like Batman

I made some slides for a sales summit and received a lot of positive feedback. In fact, the sales manager owes me lunch. But something happened at the meeting that broke an unspoken rule…. they gave me credit on stage.

From the Shadows…
Our work is best done in the shadows. We help a business use and depend on numbers so that they don’t have to make decisions based simply on a “gut feeling.”  We make the information accessible and digestible.  Making a manager shine or saving the company money can be very powerful, but getting open credit is much like having a board member of Wayne Incorporated telling the shareholders that Bruce Wayne is Batman.

At the last moment…
Giving vital information to a manager instead of publishing it yourself creates a safety net.  Batman doesn’t go public with information.   Batman brings information to Commissioner Gordon who then decides what to do with the information.  Commission Gordon never says, “Batman said we should do….”  He makes the decision and responds.  Having another person validate and respond in context of the situation creates a safety net that can save you at the last moment.

The manager may have more information or can connect the information you provide to some other situation in the company.  This will help validate your work and keeps those in charge in the position of knowing the best way to handle the situation.

Once when I published a report showing the ROI on a promotion, the results were not what I expected.  The manager quickly defended the promotion and began to cast doubt on the research.  The next time I did an analysis, I went to the manager with my findings first.  I asked him to put it in context with the other activities of the business, and the result was that he “owned” the findings and presented them.  FYI… in both situations the ROI was negative.

In this example, my safety net was there because I asked the manager to take my information and present it.  That allowed him to check it with the rest of the activities of the business.  If there had been a problem with the information or a flaw in the logic based on some business changes, my safety net would have caught me at the last moment.

Suddenly out of nowhere…

When you’ve earned credibility, managers will respond when you bring up something “from out of nowhere.”  When Batman tells Commissioner Gordon to get a warrant or check someone out, it carries clout. This is because Batman has a track record of delivering the right information at the right time.  This credibility allows him the option to ask for special assistance from Commissioner Gordon.  If the paper boy tells Commissioner Gordon the same information, he may take it or leave it.

If you are short on work, a public announcement may drum up some new customers. But this will mean that you will be doing work that you didn’t ask for. You want to work on things that you are passionate about. You want to focus on things that make a huge benefit to the company. Batman doesn’t give out speeding tickets. He’s looking and working on the big issues. If he were putting tickets on parked cars, he wouldn’t be keeping Gotham safe.

My hope is that those who heard my name didn’t write it down.  If there’s no written documentation, I at least have plausible deniability.

Photo Credit: imdb

Posted in Best Practices, Communication, Presentations | Tagged , , , , , , , , , | 2 Comments

How to Count or Sort Rows by Color

@Pilchy had a reporting emergency and called for help on Twitter. @getreport help has responded with this training video.

Click here to get the Visual Basic code e-mailed to you.

If you’ve had a spreadsheet with many rows colored and other rows left uncolored, then you realize that there is no easy button to sort or count rows that have color. This can be a very time consuming process, unless you use Visual Basic. Here’s one solution that we’ve come up with.

To get a copy of the VB code used in the video, Sign up here

Posted in Best Practices, Excel, Video | Tagged , , , , | 2 Comments

Arbitrage – The Business Intelligence Podcast [BIP]

(File size: 15 Mbs, Show Length: 16 minutes)

Awesome theme show music by: Matt West – “Let’s Move”

Show Notes:
I am amazed at how people will think something is impossible at work.  Everything is do-able when you break it down to the simple steps.


  • Definitions and background
  • Taking advantage of a difference in price in two different markets
  • Striking a combination of matching deals that capitalize upon the imbalance between them
  • A situation where your risk is nothing, but your benefit is something

Death by PowerPoint
It is amazing how bad some Microsoft PowerPoint presentations can be.  Here’s an example of a great speech done with terrible PowerPoint slides.

I’ve put together a set of videos to help show you exactly how to take data and make good charts out of it.  Good charts make great PowerPoint slides.

Chart Challenge 1: Look for a pattern

Chart Challenge 2: Choose chart type

Chart Challenge 3: Remove Clutter

Questions or comments for this, or the next show?

Posted in Uncategorized | Leave a comment

The Art of Charting in Excel

In this 3rd video (3 of 3), we show you the magic. Learning how to reduce clutter and make that chart stand out is really an art. A good chart can land the client, separate you from other vendors, or make your message to management extremely persuasive. Your last resort in PowerPoint should be to put a bunch of numbers on a slide. But often it’s the first thing people think to do… we know they need help.

We are wrapping up this series. There are some of these simple rules and steps when creating charts provided in an early posts. But a VIDEO showing you how to create good Microsoft Excel charts will show you EXACTLY how to do it. Here is the final part 3 of a 3 part series on how to create compelling charts in Excel. Enjoy!

If this video doesn’t show up, you can go to the permanent page which has the video loaded in both Windows Media file and Quicktime. Click here

What other steps should be included? Share with the community in the comments.

Posted in Best Practices, Communication, Excel, Presentations, Video | Tagged , , , , , , , | Leave a comment

Confounded new tool, Whippersnappers, and why the new Excel is great!

Picture of an old person getting instructionsChange is hard.  Real change is really hard.  You don’t hear that said very often.  Realizing this truth helps you understand why people you know have a hard time changing.  We’ve written before about how we should tier our users and part of the reasoning for this is that the vast majority of people don’t want things to change.  When you get right down to it, people spend over half of their energy just trying to keep things the same.

Now there is “the new” Excel.

When this tool came to a local client’s office, we were amazed at how many people didn’t want to upgrade.  The Finance department “unionized” and said they wouldn’t upgrade until all the bugs were taken care of.  A few Business Intelligence professionals were considering it, and the majority of the sales department still didn’t know what Excel was.

We took a more systematic approach.  We had one laptop with the old version and the desktop computer upgraded with the new tool.  This way we could see the differences, and still use the old one if there was a problem.  There was not a problem, just a learning curve.  After we transitioned to the new tool we found 2 or 3 things that we loved about the changes, and when people ask, we demonstrate them.  Here are our favorite new features:

1.       The “Remove Duplicates” button.  This is located on the “Data” Ribbon to the right side of the ribbon.  It will take the data you highlight and remove any duplicated values leaving you a unique set of records.  Super handy for getting a clean customer or parts list.

2.       The “Cell Styles” button.  This one allows you to format cells with one click.  After you’ve generated a great set of information and you want to highlight the totals or spruce up the headers, this button makes it shockingly easy.  The button is located on the “Home” Ribbon and once you click it, it shows 50 different options for formatting.

3.       This isn’t very simple to find, but there’s a “Rosetta Stone” for moving from 2003.  If you use this link, the new format shows up.  Find your favorite function and then the web page will show you where to get to it in the new version.   Click here.

We’ve found that the new tool benefits outweigh the problems with Pivot Tables or file extensions.

Do you have a favorite feature in that “confounded new tool?”   Share with the other whippersnappers in the community via the comments.

Photo Credit: kio

Posted in Best Practices, Communication, Excel, Purpose | Tagged , , , , , , , , , | 2 Comments

Cut Data Reconciliations by 90%

Picture of two sets of data to reconcile

Cut Data Reconciliation by 90 percent

You get a new data file and the numbers are close, but slightly different from the other file you already had.  Suddenly, you’ve got two different sets of data.  Now you have to spend two hours matching things up.

Okay, let’s NOT do this. Instead let’s use three steps and make this project a ten minute exercise.  Then we can bank that extra time and use some of it towards that side project you’ve been wanting to do.

Step 1: Find the criteria

Ask the person who sent you the data to include the criteria they used to run the report. Ninety percent of your effort will be trying to find this information by looking in the data, but if you ask up front you can save yourself a lot of time.  Many times the issue is simply that each set of data is using different date ranges (fiscal vs. calendar, or up through yesterday vs. including today, etc.)

Step 2: Find the primary data items

Create a concatenation of the “key” fields in each report. So if you’re looking at customer, region, and part, then join those three fields together into one field. Do this for both reports.  Then sort the data by this field.  You can use this list as a unique set of data and that alone may be all you need.  Here’s a video on how to get the unique records in Microsoft Office Excel 2003 (Tip: Office Excel 2007 has a one button solution for this – click on the “Data” Ribbon and select “Remove Duplicates”.)

Step 3: Find the subtotals of the primary data

Create a subtotal for each set of data by the concatenated field. Use this subtotal on each set of data.  This usually reduces the checking from thousands of rows to less than one hundred. If not, you may want to adjust your concatenation to have fewer fields. Once you’ve done this for both sets of data, the differences jump out at you and you can look at the records within that concatenated “key” to isolate your differences.

What other steps should be included?  Share with the community in the comments.

Photo credit: ajturner

Posted in Best Practices, Communication, Excel | Tagged , , , , , , , | 12 Comments