Posts Tagged ‘Visualization’

« Older Entries

Formatting Access Queries to the Thousands Place

October 24th, 2011

In an Email last week, Christina asks:

“Do you know of an easy way to show Access query results in thousands?
Thanks DataPig.You are the like that black bird on Angry Birds – the bomb.”

Ok….so I added that last sentence. But Christina did ask a pretty good question.

.

Here’s how you can format Access query fields so that they show as thousands.

.

You first start with a working query. As you can see, this query shows revenue by market with an indication of how much of that revenue comes from Internet Sales. You’ll notice that the revenue columns inundate you with lots of numbers. That is to say, the revenue columns are difficult to read. Showing these columns in thousands would help readability.
» More: Formatting Access Queries to the Thousands Place

Leave your comment »

Posted in Access Queries, Access Tips and Tricks

Tags:

Use Pivot Slicers as Form Controls

August 30th, 2011

About a week ago, Bill Jelen sent me an email with a challenge to figure out the nifty trick he shows in this YouTube Video. Inspired by the video, I decided to use the trick in a simple dashboard model.

.

What I ended up with is this cool technique that allows me to replace my old Form Controls with Pivot Slicers. As I select each slicer, my dashboard responds as it would would if I were using a set of Form Controls. Keep in mind that the charts and other components in my dashboard are in no way connected to a pivot table.
» More: Use Pivot Slicers as Form Controls

3 Comments »

Posted in Excel Charts, Pivot Tables, Visualizations

Tags:

Color Pie Chart Slices to Match their Source Cells

August 23rd, 2011

A few days ago, I posted a macro that will automatically set the series colors in a chart to match their source cell’s colors.

.

After reading my post, Chris Umphlett got inspired to create a macro that automatically sets the data points in a chart to the same color as source cells.

.

Here’s how it works.
» More: Color Pie Chart Slices to Match their Source Cells

5 Comments »

Posted in Excel Charts, Visualizations

Tags:

Automatically Label First and Last Chart Points

August 16th, 2011

One of the best practices of dashboard building is to avoid overwhelming your customers with too much data at one time – especially in a chart, where they can lose sight of the primary message if focusing on inconsequential data. One of the common ways dashboard designers help focus the message of a chart is to limit the data labels to only the key points.

.

Once I have only two data labels, my eyes immediately take in the message of this chart (that we went from 11 to 53).
» More: Automatically Label First and Last Chart Points

6 Comments »

Posted in Excel Charts, Excel VBA, Visualizations

Tags:

Automatically Set Chart Series Colors to Match Source Cell Colors

August 10th, 2011

While working on a dashboarding project here, I accidently developed a charting macro that will automatically set the series colors in a chart to match their source cell’s colors.

.

I’m not sure how useful this actually could be, but it’s charming enough to share with my 12 fans.

.

Let’s say I created this chart – and I want to apply specific coloring to each series without having to painstakingly change all the color options in each series.

» More: Automatically Set Chart Series Colors to Match Source Cell Colors

15 Comments »

Posted in Excel Charts, Excel VBA, Visualizations

Tags:

Quickly Change Chart Series Positions

August 8th, 2011

Well, the summer is over for many of us. I’m back to my regular blogging schedule (whatever that means).

.

For my first post back, I’d like to show you a trick I learned from charting guru, Jon Peltier.

.

Ever needed to switch the position of a chart series (color and all)?

In this example, I want to show the series in red first. I’ve already created my chart, so I just want an easy way to switch the positions of the columns.
» More: Quickly Change Chart Series Positions

4 Comments »

Posted in Excel Charts, Visualizations

Tags:

Enhance Excel 2010 Dashboards with Camera Tool and Picture Effects

May 16th, 2011

For those you who have Excel 2010, you get the benefit of a new feature called Picture effects. With picture effects, you can take an ordinary picture and wrap them in fancy borders, colors, and other such visualizations.

.

So I thought, why not use these effects in my dashboards. What you see here are charts that I’ve captured with the Camera Tool then applied Picture Effects.
» More: Enhance Excel 2010 Dashboards with Camera Tool and Picture Effects

6 Comments »

Posted in Excel Tips and Tricks, Visualizations

Tags:

Creating a PowePoint Deck in Excel

March 7th, 2011

How many times have you built a PowerPoint presentation with Excel data. We’ve all done it at some point – some of us do it on a weekly basis. The typical scenario is the weekly or monthly management review. You copy or link Excel tables and charts to a PowerPoint deck, and then refresh when it’s time to produce another exciting version of “Make the Data Look Good”.

.

In this post, I’d like to show you a technique that takes the concept of using Excel data in PowerPoint to the extreme. The basic idea is that you can build a workbook in such a way that it mimics a PowerPoint presentation. That is, the workbook is the presentation itself and each worksheet becomes a slide in the presentation.

.

Once you do that, you can easily convert that workbook into an actual PowerPoint presentation using a bit of VBA automation.

.

I’ve created a sample workbook so you can get an idea of how this works.
Download the Workbook

. » More: Creating a PowePoint Deck in Excel

13 Comments »

Posted in Excel VBA, Visualizations

Tags:

Adding Check Boxes without Form Controls

February 9th, 2011

Need to add a bunch of check boxes to your spreadsheet without messing with CheckBox Form Controls?
It’s easy with a bit of VBA code!
.

» More: Adding Check Boxes without Form Controls

16 Comments »

Posted in Excel VBA, Visualizations

Tags:

Using Flash Progress Indicators

February 3rd, 2011

Besides being an Excel and Access guru, I moonlight as an Xcelsius expert. Because Xcelsius is so closely tied to Excel, I’ve always seen Xcelsius as an extension of the Excel BI stack. But that’s a conversation for another time.

.

A while ago, Ryan Goodman created and posted a few Flash progress indicators on his Xcelsius blog. These are Flash .swf files designed to be used as loaders for Xclesius dashboards.

Â

.

I recently had an opportunity to work on a project where I could implement these loaders into an Excel dashboard as progress indicators – a lazy man’s progress bar if you will.
» More: Using Flash Progress Indicators

4 Comments »

Posted in Access VBA, Excel VBA, Visualizations

Tags: