Regular readers know that English is not my mother language , but are kind enough to forgive me for my many mistakes.

I am always willing to learn and today, while researching for an upcoming post, I came across an expression  I never heard before: “spiffy charts”. I felt in love with “spiffy charts” the moment I read it. And I read it straight from the horse’s mouth (I mean Microsoft).

If you don’t know how to make a chart and are keen to preserve that blissful ignorance, I highly recommend Microsoft Office Online Training, specially the module Create a professional looking chart (regular readers also know how I love “professional-looking charts“). You’ll learn how to “customize your charts to make them more attractive, memorable, and effective”. This means useless charts.

So, let’s see how to turn a humdrum (this is a new word, too…) chart into a spiffy one. First, to declutter your chart remove grid lines:

As you know, grid lines are useless, specially if you don’t care about the data. I would remove the gray background and the border around the legend. And I’d give the chart a more descriptive title to tell the users what they are seeing, but that’s my personal taste.

Then you should remove the y-axis and add labels to each column, further “decluttering” the chart. At this point the readers start sighing for a nicely designed chart table.

Want to give your chart a little more “flair” and make them more “professional-looking”? Just add a gradient fill and a subtle shadow:

Now comes the spiffy part. Imagine that you have a 3D column chart with two series, and one obscures the other. What do you do? No, you can’t remove the stupid 3D effect (remember: you want to make t spiffy chart, not a humdrum one). Well, all you have to do is to change the order of the series:

Much better now, don’t you think? They accept that 3D charts “can be more attractive, but sometimes more difficult to read accurately” (surprise, surprise!). Apparently that’s a detail in the grand scheme of things. You are excused from making accurate charts if you are making professional-looking ones.

So, what else can you do to improve your chart? Ah, yes: the y-axis in a humdrum column chart always starts at zero. We don’t want that, do we?

Now you know how to make inaccurate, professional-looking, spiffy-with-a-flair marmalade charts. Please go straight to the kitchen, make some real marmalade and forget all you’ve learned about data visualization in the Microsoft Office Online Training.

(This is not a real Microsoft Office Training site, is it? It must be some kind of spoof site, and I fell for that trick. Right? Right?)

Sigh…

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Spiffy Charts

Related posts:

1. Spending Time Deciphering Time Utilization Charts
2. 14 Misconceptions About Charts and Graphs
3. Why can’t bloggers-about-blogging do basic charts?

Let’s face it: you can’t prove you have a good chart. Unless you can convince your employer to deploy at least two different formats/layouts and are able to compare results, you can say “this is a good chart” but that’s an act of faith, not an act of science.

It’s True Because It Rhymes

Information visualization experts like to evaluate a chart based on its compliance to some more or less accepted standards (Tufte’s data-ink ratio, for example). That’s like saying “it must be true because it rhymes”: the truth is defined by the language itself, not by the real world. Now, please close the curtains of our ivory tower…

I know, it’s not easy to assess the efficiency and effectiveness of good displays. They look natural and obvious, undeserving of praise and, probably, boring and uninspiring. Compare these charts:

This is a true story: users wanted to evaluate sales territories, one at a time. Color-coding each bubble (Example A) was pointless, while Example B provided context without distractions. Guess what chart they would choose if they were allowed to… (happy ending: they reluctantly accepted Example B). (A word of advice: if you are looking for a promotion, a kindergarten chart variety always outperforms a “serious” chart.)

If your chart is doing a good job at helping people, no one will actually be aware of the chart’s role at making sense of the data. That’s why it is so hard to find good examples of data visualization using standard charts. If people actually like them, they like them because of their usability and/or interactive features.

When Stephen Few asks the readers “true stories about the benefits of data visualization” that’s almost an admission of impotence.  He should have hundreds if not thousands of good examples to share with us, right? Well, I know there are many examples out there, but I can give you none, sorry. Is data visualization some kind of astrology? I know it works. Why? Because I have faith. (On second thought, he is not asking for good data visualization examples. It really doesn’t matter if you use Tableau or Xcelsius, and that’s a relief.)

Opening the Pandora Box

Ultimately, what makes a good chart is how it resonates with your audience. Assuming that your are not unethically distorting the data, a chart that forces people to act is better than another one that only makes people aware of the subject.

If a single chart can save the world, it will not be a Few’s or Tufte’s 100% compliant chart. It will be a glossy Xcelsius pie chart.

(Wow, that’s depressing…)

If you read this blog that’s a clear sign of intelligence and sophistication . Unfortunately, you are not representative of the typical data visualization user and/or producer. The real world loves pie charts and doesn’t understand scatter plots.

Here is my Pandora box: give the audience what it expects and understands, even if that hurts your data visualization soul (OK, give it 90% of what it expects and use the remaining 10% to educate it.)

Cultural Relativism? Not So Fast.

Please don’t misrepresent these arguments. I’m not saying that all charts are born equal. There is a reference point and some misconceptions should be avoided A chart that maximizes insights, removes clutter, uses color wisely and clearly shows the patterns hidden in vast amounts of data, that’s probably a good chart and that’s what you should aim for. And yes, you should avoid pie charts.

If you present some sophisticated charts to your unsophisticated audience you’ll lose it. Relax. Draw a line but don’t forget the candies. You can take a horse to the water, but you can’t make him drink, unless you give him some sugar cubes…

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Is Data Visualization Useful? You’ll Have to Prove It.

Related posts:

1. Data Visualization: Who Needs a Reality Check?
2. Data Visualization Personas
3. Sub-Prime Charts: Should Data Visualization Be Boring?

If you want to add animation to your charts that’s a clear sign that you have too much free time. Go out and play with the kids instead.

Yes, animation is a powerful attention-grabber, even more powerful than a glossy 3D pie chart in Crystal Xcelsius. And yes, it can actually be helpful (from time to time). But.

A good example of animation in data visualization is the famous Hans Rosling’s TED presentation, where a long-term pattern is clearly seen (at min 4:00):

This works well because the trend is easily identifiable and you don’t care much about the details. It would be much more difficult to make sense of this data if there were multiple trends and short-term variability. (More on the use of animated charts.)

After watching this presentation people often ask me: “Wow! Can we do that in Excel?” Wrong question. The right question would be “Wow! Can our data do that?”.

You can always create a simple animation in Excel, but it’s hard for a non-programmer to get a smooth transition effect. Here is an example from my dynamic Excel dashboard:

Although you can see a pattern emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be used).

Animation is better used if there is a pattern to be discovered, but you need something more: the ability to interact with the data. You must be able to stop, go back, get the details. While you can create a simple animation effect with a for/next loop in VBA, interaction with the chart is much easier using a Google spreadsheet with a motion chart:

(This is just an image. Click here to play with the chart.)

Dynamic Charts in Excel

In Excel, instead of creating a VBA routine, consider using a scroll bar linked to the value you want to change (year, for example):

Using a scroll bar adds some level of interaction because you can scroll back and forth, pause and examine the details for a specific year. Obviously, you need to change the chart data source dynamically.

Let’s recap how to create a dynamic chart in Excel. You can do it by changing values or by changing the data source itself (using a different range).

Option #1: Copying the Data

The range A1:E11 is our data set and we are comparing regions. Moving the scroll bar at the bottom changes the year. Column F is our data source. You can enter this formula there:

=OFFSET($A$1,ROW()-1,MATCH($A$13,$B$1:$E$1,0),1,1)

ROW() gets the current row and MATCH() returns the position of value 2002 (A13) in the range B1:E1. So, the formula in cell F2 reads something like this: start at A1, go down one row, go right three columns and get the data in that cell (range of width 1 and height 1). When the user selects a different year the data is copied to column F.

Option #2: Using a Dynamic Named Range

The second option is to create a dynamic named range. Create a named range and, instead of entering a fixed range, enter this formula:

=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$A$13,Sheet1!$B$1:$E$1,0),10,1)

As you can see, it is very similar to the one above, but now the number of rows down is fixed (1) and it returns a range of 10×1 cells (because we have 10 regions, but if the number of regions varies you may use COUNTA(A:A)-1 to count the number of regions, excluding header – and don’t forget to move the year to a different cell…).

When you verify your range this is what you get:

You just need to use this range in your chart (when entering the range you must add the workbook name: “=Book1!SourceData”). You’ll also need a range for the category axis labels:

=OFFSET(Sheet1!$A$1,1,0,10,1)

You can download the spreadsheet here.

Take Aways

If you want to animate your charts make sure you do it because it adds value, not because you want to show off your skills. You can do it in Excel but it looks much better in Rosling’s Trendalyzer or in the Google spreadsheet gadget.

To create animations your data source must change dynamically, and that requires some work (and skills). I advise you to shift your focus from animation to interaction and use all that work to design a better user experience. Do you prefer a “wow!” (animation) or a “wow, thank you!” (interaction that actually helps the user)?

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Animation, Interaction and Dynamic Excel Charts

Related posts:

1. Animation and demographic information visualization
2. How to create a dashboard in Excel

You need a better color palette for your Excel charts, but you are a mere mortal and your artistic skills are less than stellar. Hell, you can’t even choose the right tie for a suit! So, what do you do? (hint: watch the video below)

Maybe we could ask Edward Tufte for advice. In Envisioning Information, he writes:

What palette of colors should we choose to represent and illuminate information? A grand strategy is to use colors found in nature (…). Nature’s colors are familiar and coherent, possessing a widely accepted harmony to the human eye (…). A palette of nature’s colors helps suppress production of garish and content-empty chartjunk.

Better said than done, right? Well, let me tell you a secret: it’s easier than it seems.

How to create a new Excel color palette

I’m going to show you how to create a palette of colors found in nature ( you can create a palette of colors not found in nature, if that suits you better). Here are the steps:

1. Select a photo you like. If you happen to live in a big city, finding nature can be challenging. Google for “[your location]+park” and you may get lucky. Go there, take some photos and download them to your computer. If that looks like a lot of work, you can try Flickr. Search for “autumn colors” and you’ll get some useful results. I’ve picked the one on the right;
2. Upload it to an online palette generator. There are many, so choose whichever you like. I like Genopal because it is clean (no ads) and extracts exactly the number of colors we need (eight).
3. Convert color codes to RGB. Copy the color codes to Excel and use the HEX2DEC() function to get the RGB values.
4. Create the Excel palette. In Excel 2003, replace the existing colors with the new ones. In Excel 2007 create a new color theme.

This is it. You can try it in your next chart.

A new Excel palette: the making of

If you need the details, here is a step by step tutorial:

Is this good enough?

As you can see, the results are heavily dependent on the photo you choose, so you should try different photos and test the resulting palette in a chart. If you find it hard to come up with a good color scheme, you can use this method to create a basic color palette that you can tweak to meet your needs.

Other Excel and Chart Color Resources

Jon Peltier wrote two interesting posts about managing the Excel color palette (here and here) and Stephen Few shares some Practical Rules for Using Color in Charts (PDF). Bonavista’s Chart Tamer includes a professionally designed palette.

Photo credit: Xavier Fargas

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Better Color Palettes for Your Excel Charts [Video Tutorial]

Related posts:

1. Video: Royksopp – Remind Me
2. 10 x 10 Tips to improve your (Excel) charts: pie charts
3. 10 x 10 Tips to improve your (Excel) charts: formatting

Templates and defaults are very useful when you are not a subject-matter expert. You don’t have to know much, but if you choose the wrong templates you are on the wrong track.

Cooking is a good example. I don’t know how to cook and, frankly, I don’t want to learn. But my wife is coming late from work, I’m tired of take-away food and we must feed the kids. So I guess I have to add cooking to my daily routine.

Here in Europe, Thermomix (also known as “Bimby”) is a very popular kitchen appliance (specially among men, so I’m told). You just add ingredients, press three buttons (temperature, speed and time) et voilà, diner is ready (sort of). Follow the recipe  and what you get what you expect. That’s good enough for me.

This is a gadget most people love or hate. A friend of mine hates it and tried to persuade me that I should learn how to cook using the traditional pots and pans. Only ill-informed people buys it, he says.

Yes, but he’s single. No kids, no blog, loves to cook. How can he possibly understand my motives? I just want to get this thing done with minimal fuss, no random results, small learning curve. Some good templates, that’s all I want.

I had to buy one. I just did.

OK, but what about making better charts?

Apparently, most people love making charts as much as I love cooking. Even if they need them on a daily basis, there are so many things fighting for their attention that the need for a better data visualization can easily be overlooked and pushed to the bottom of their list.

Junk food, junk charts. Some times it’s great fun to use them. And it’s very easy to take them for real food, real charts. But they are unhealthy, and if you use them often you and your business will pay a price, sooner or later.

It’s not about food or charts, it’s about business and personal health (or wellness). You don’t have to know the details, you don’t have to know how to use the tools. But you have to have some kind of framework to guide you. Fat is bad, sugar is bad, 3D effects are bad. Then you need to find some templates that implement that framework and tools that use them by default (if you make charts in Excel, using its defaults is not an option).

We played with the machine over the weekend and I’ve very pleased with the results. We eat better than we usually do and saved money and time. Not bad. Now, do yourself a favor and find your own charting machine. If you’ll excuse me, mine is calling me. Dinner is ready.

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Cooking Better Charts

No related posts.

It’s very easy to use charts to support false arguments, distortions, omissions or outright lies. But you can use words and statistics too. If you want to deceive nothing will stop you.

Simple lies are often easy to spot and not very interesting. More interesting are our biases. Germans call it weltanschauung (“world view”) and without it we wouldn’t be more than boring rational machines. Our biases help us to select the data and interpret it the way it makes sense to us, reinforcing our believes.

Lying with charts, if done properly (!), is more an act of omission (what you hide) than an act of commission (what you show). To better understand the differences, let me give you an example of how data visualization amateurs lie with charts.

In a post titled “charts can be deceiving”, E. D. Kain writes:

I’m not a huge fan of charts because I think they’re usually just used to create illusions and sales pitches.(…) Numbers don’t lie, but how we present them can make all the difference in the world.

Then he goes on and offers an example of how deceiving charts can be. I’ll recreate them for you. Chart A is the original chart, Chart B is his:

Jon writes about the flaws in both charts, so I’m not going to discuss them here.

It’s funny to see how a lack of action (the original chart accepts the Excel default scale) induces an over reaction (an absurd “theoretical” scale). Manipulating the y-axis scale is “How to Lie With Charts 101”.

Yes, charts can be deceiving. Words too. Numbers don’t lie? Bullsh*t. The political discourse is full of “illusions and sales pitches” and carefully selected and biased numbers. Yes, charts can be deceiving. It takes one to know one, I guess.

Deconstructing Lies with Charts

The original chart reveals a clear act of omission: how can you conclude anything relevant if you have no reference to compare the trend to?

So, let’s try to answer this question with the available data: are the wealthiest 1% of households getting a more favorable tax treatment, or not? First, we need some contextual data:

So, the wealthier you are the more taxes you pay; a downward trend is also visible across quintiles (although the highest quintile shows a slight increase over the last two years).

And what happens within the highest quintile?

Well, this is interesting: tax rates increased, but not for the top 1%. But the general rule is kept: the higher the income, the higher the tax rate.

What if we chart, not the tax rate but the change, assuming 1993=100? Again, some contextual data:

Tax rates for the lowest quintile declined sharply. And here is another general rule: the higher the tax rate, the less it changes.

Here is the detail chart for the highest quintile:

Well, it seems that at the very top some rules don’t apply, after all (surprise, surprise). The top 1% households did get a more favorable tax treatment after 1996, when compared to the top 5% and the top 10% households.

Takeaways

The world is never black and white, and your own shade of gray is as unique as your fingerprints. If you want to use charts to support your arguments, please don’t resort to scale tricks and make sure you add enough detail and contextual data.

There is no intrinsic objectivity in a chart, but if you want to support your story you should cover your bases and make sure it’s hard for someone else to come up with a different narrative. This is valid for charts but for words and number too.

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Weltanschauung, Lies and Charts

Related posts:

1. Charts: Monthly Unemployment Rates by State 1976-2009
2. 10 x 10 Tips to improve your (Excel) charts: formatting
3. 10 x 10 tips to improve your (Excel or not) charts: Line charts

If you want to sell better data visualization practices you can’t use the same approach with everyone. Marketers use archetypes and like to create stories around them like if they were real people. Their marketing messages are then tailored for Jane (archetype #1) or Theresa (archetype #2).

Let’s try this. Allow me to introduce you to three of my co-workers.

Co-worker #1: Anna, a Newbie

Anna was asked to create a chart, something that she rarely needs to. After playing with the wrong Excel options, she comes up with a really ugly and inefficient chart. I show her how a simpler one could solve several perceptual issues. She changes her chart but keeps some of the chartjunk (she finds my chart too minimalistic and laments her lack of of graphic design skills).

Anna is now aware of what “data visualization” means – clearly more than creating a few Excel charts. She should work on her data analysis and communication skills and stop worrying about graphic design skills. Corporate culture and peer pressure will push her to the dark side chartjunk side of data visualization. I hope this seed is strong enough to withstand it, but only time can tell.

Co-Worker #2: Peter, a Middle Manager

Peter agrees that some of our processes are terribly inefficient and wants to change them. We could try to improve data visualization, but that doesn’t make much sense if everything else remains the same.

I’ve been sharing some tips with Peter on how to create better charts and he starts to recognize a bad chart when he sees one. Problem is, his previous model (Excel defaults, PowerPoint templates) is shattered, but he is unable to create a new one. He feels lost.

My tips seem to make sense at the lunch table but when he tries to apply them something is missing. Tips have this effect on people: they create an illusion of knowledge but the lack of context renders them almost useless. He needs a crash course on information visualization.

At this level, we are not discussing how to improve a chart. Instead, we must discuss how to add best practices in information visualization to the data management model. Selling this to top managers isn’t always easy. But Peter likes to bang his head on a wall…

Co-Worker #3: Frank, A Professional Chart Maker

Frank creates presentation charts every single day. Ten years ago he was creating exactly the same charts, 3D effects and primary colors. He doesn’t recognize the problem and the audience seems to be ok with this routine. If he needs something a little more complex than 3D pies and bars his manager asks me to help. This could spark his curiosity, but it doesn’t.

I believe Frank will never try to improve his data analysis, management and visualization skills, unless he’s formally ordered to do so. He’s not dumb, he’s just too comfortable in his comfort zone.

It’s Your Turn

I want to add more details about Anna, Peter and Frank and I’d like you to help me. How do you see them? How are you going to sell them our product (better data visualization practices)? Would you like to add your own characters? An imaginary boss, perhaps? The IT guy? Tell us about them!

Photo Credit: Smaku

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Data Visualization Personas

Related posts:

1. Data Visualization: Who Needs a Reality Check?
2. Information Visualization Is a Jigsaw Puzzle
3. Will you Help Me Write a Data Visualization E-Book (and Win a Free Copy)?

You know, I would really love to quit my day job and spend my time learning about information visualization, write about it, help people understand why good information visualization skills are relevant for their business.

Now, I could try to speed up things by selling some crappy affiliate products, fill up the blog with Google ads or use some other “business model” internet marketers love. But that’s definitely not my model. I’m selling my Excel Dashboard Tutorial and writing an information visualization ebook. That’s what I like to do and that’s what I want to be paid for. And I’d like to promote good products (Jon’s Chart Utilities, Bonavista’s Microcharts or Tableau, for example) if I had the time to do it properly. But you’ll never see Google ads in my blog.

The Fart Machine

Displaying Google ads is a dangerous game that I don’t want to play. Here is an example. I was visiting an information visualization blog when I saw, right below the post title, a very visual “Online Fart Machine” ad (no, not the one above). I’m sure the blogger wouldn’t be pleased to see this kind of visuals in his blog, but that’s the risk of running Google ads.

This is what first time readers see when they arrive at your blog. It hurts your message, your credibility and probably your future income.

The Trojan Horse

It’s not the fart machine only. Since you have no or little control over Google ads, you may end up sending contradictory signals to your readers: you write about best practices, but your ads act like a Trojan Horse, advertising the worst possible practices. I see this all the time.

Premium Chartjunk

3D effects, backgrounds and shinny textures are well-known examples of chartjunk. I’ll call them the “regular chartjunk”. There is also the “premium chartjunk”. Every time you add clipart to your charts and presentations (because it’s funny or cute) you’re entering the realm of the premium chartjunk.

How can you avoid premium chartjunk? Remove any clipart and any kitten images and pictures and please, please, make sure you know what your audience defines as “bad taste”.

(And unless there is a very bizarre lateral thinking going on,  I’m pretty sure a fart machine will not speed up your promotion or bring visits to your blog.)

Photo Credit: Chris Devers

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Ads, Chartjunk and the Fart Machine

No related posts.

From time to time, Seth Godin comes to visit our little field of information visualization, and I’m pleased to note that he is learning…

Today’s post, “How to make graphs that work” is remarkably better than “The three laws of great graphs” or “How to make a PowerPoint chart”. Today he warns  us against Excel and PowerPoint defaults and templates, invites us to tell a story, by following some simple rules and braking some other rules. It’s clearly a post on the safer side…

But I do have some remarks. Godin says:

when you show me something exactly like something I’ve seen a hundred times before, what do you expect me to do? Here’s a hint: Zzzzzz.

Right. That’s the problem with defaults and templates. But it’s a problem with all defaults and templates, no matter how good they are. So, what can you do? First choice: be relentlessly creative. Can you do it? Good for you, I can’t. Second choice: make your charts invisible. Show the message, not the chart.

Pie charts are spectacularly overrated. If you want to show me that four out of five dentists prefer Trident and that we need to target the fifth one, show me a picture of 5 dentists, but make one of them stand out. I’ll remember that.

I actually prefer the Presentation Zen style. If you have two slices, you just need a percentage. And be careful with the pictures you choose. In this case, I see a female dentist that doesn’t prefer Trident on Wednesdays…

You can animate, but only if you have a note from your doctor.

You can animate if there is a pattern inside (Hans Rosling, anyone?).

So, what do you think? Is Seth Goding ready for serious information visualization?

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Seth Godin Is Getting It…

Related posts:

1. Seth Godin on Great Graphs: A Very Light Purple Cow
2. Minard, Tufte, Kosslyn and Godin (and Napoleon)
3. 14 Misconceptions About Charts and Graphs

Is Excel a commodity, with no competitive advantage? Only if you want it to. Why? Because a vast majority of Excel users:

• Have the data analysis skills of a toddler (or less);
• Can’t go beyond Excel Chart defaults;
• Functions? They know how to click the SUM() button;
• Don’t know what a dynamic chart is;
• Think pivot tables are too complex;
• VBA? No way!

I believe you can squeeze some competitive advantage from Excel if you avoid some basic mistakes everyone else makes. Here is a very short list (some generic, some chart-specific).

#1. Assuming that Excel Can Do It.

Because a spreadsheet is such a loose environment, people often assume that all things numeric can be done using Excel and that no specific skills are needed. Using Excel as a database tool is an obvious example. If you can’t stop singing Ode to Joy every time you see one million rows in Excel 2007 that’s a clear sign that you are on the wrong track.

#2. Assuming that Excel Can’t Do It.

Most people aren’t aware of how powerful Excel is and use it almost as a pocket calculator. If you routinely have to manage quantitative data, learning a little more Excel always pays off. As an example, you can use it to create complex executive dashboards or, at least, as a dashboard prototyping tool.

#3. Not Having a Go-To Person.

Not everyone needs to be an Excel expert, but having someone that understands the business and proactively tries  to find better ways to perform common (or not so common) tasks should be a requirement.

#4. The Excel Islands.

If you have a data source you can connect your Excel sheet to, do it. Don’t make the mistake of copy / pasting the data into your sheet. That’s a bad practice and it can undermine your organization’s entire information system.

If you connect your spreadsheet to the data source (via ODBC, for example) you are ensuring the integrity of your data and minimizing maintenance costs. If you are creating a new table, use a simple database tool like Access. And if you really, really, need to use Excel, make sure you create a table that can easily be exported and used by a different tool.

#5. The Uncharted Archipelago of Excel Workbooks.

Search for “book*.xls” in your hard drive. How many do you find? Get rid of all those useless files now.

#6. Bypassing IT.

We all have our little fights with IT, but we must understand their role and the reasons behind their actions sometimes make sense… Excel users often lack knowledge to understand data structures, data access, documentation, security (IT 101, basically). If your IT don’t want to conquer the world and actually helps users, you should work hand in hand with them.

#7. Not Using VBA.

I’m sorry to break this to you, but if you spend your time analyzing data and Excel is your primary tool, then you must use VBA. I’m not telling you should be a programmer, far from it. But you should be able to record a macro and make some simple tweaks. There are many repetitive tasks that can easily done using three lines of code.

#8. Excel is the only charting tool.

There are no neutral tools. They force upon you a certain way of doing things that you may not be comfortable with (or you shouldn’t). Excel’s Chart Wizard is one of the stupidest wizards I’ve ever met. If you need to spend time removing defaults there is something wrong with the defaults. Excel charts emphasize:

• Stupid Defaults versus Cleaner Formatting Options
• Static versus Dynamic
• Marketing versus Insights
• Disposable versus Reusable
• One versus Multiple
• Large versus Small
• Island versus Continent
• Presentation versus Discovery

I’ll detail this in a future post but, like PowerPoint, Excel charts do have their own cognitive style. If you want to do things differently you can, but you are choosing a path of endless pain. So, give some users the option to use a different charting tool or, as a bare minimum, use a better wizard.

#9. Forgetting the 3R’s.

Reduce, reuse, recycle. Yes, I’m talking about Excel charts. How many disposable charts are cluttering your hard disk? Go green when creating your charts:

• Charts are typically much larger than needed, so you can safely reduce their size;
• When you create a chart make sure that it can be reused when the data updates;
• A chart for Market A is probably very similar to a chart for Market B. By recycling the chart from market to market you don’t need do create new charts and pollute your hard drive.

#10. Not Making Things To Work Together

Lists like this one are useless, unless you know how the basic system works. Try to see the big picture and it will be much easier to understand where each piece belongs to:

• If a function needs a range address, you will know that another function can provide the address (first step to create dynamic charts);
• If you are repeating the same stupid task, you’ll find a way to automate it;
• etc.

Bottom line: make sure you know what you should use Excel for, find inefficiencies and give power users the option to use specialized tools.

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes' Charts.

Are Excel Charts Hurting Your Business? 10 Mistakes You Should Avoid.

Related posts:

1. Excel Charts: If It Isn’t Broken…
2. Better Charts for Business: When Business Doesn’t Care
3. How to create a dashboard in Excel