A snappy fix for layout problems in Excel

Have you ever tried copying parts of one workbook to another and been restricted by column widths?  Or maybe you’re almost done with a report layout only to find that the last table you need to add has 4 columns, where there is only room for 3?  Today we’re going to show you how to use Excel’s Camera tool to get around any Excel column width limitations to achieve your dashboard goals!  Here we have an Excel heat Map on a separate sheet in our workbook.

It has been inserted into the dashboard below where the first thing to notice is the workbook’s  variable column widths, in particular columns J and K.  If we had just inserted our heat map as it was, the column widths in our dashboard would determine the width size of the heatmap.    Instead we used Excel’s camera tool to insert our heatmap sized at exactly what we wanted, regardless of the destination sheet’s column widths.

 

We follow these simple steps:

  • select  the heat map in the source sheet
  • click the Camera Tool icon
  •  navigate to the destination sheet
  • click and insert exactly where you want

The Excel Camera Tool is also a great way creating dynamic screenshots of particular groups of data.  The Camera Tool takes a picture of a selected area, and you can then paste that picture wherever you want it. It updates automatically, and because it is a picture rather than a set of links to the original cells, any formatting or data change in the source is automatically reflected in the picture.

The heat map chart source figures have been updated to show Europe’s higher sales – as you can see Europe now has the greater sales:

 

The dashboard heat map has updated automatically to reflect this value change.

 If you can’t see the Camera Tool on your Excel menu you can easily attach it to your Quick Access Toolbar by performing the following steps:

  • Click the File Tab
  • Click Options
  • Choose the Quick Access Toolbar Option
  • In the ‘Choose Command From’ dropdown, select Commands not in Ribbon
  • Find the Camera Tool from the alphabetical list of commands and add it to the Quick Access Toolbar.

2009 Excel Dashboard Competition Winners

Thanks to everyone who entered this years competition, again the standard was very high, and it’s always great to see the product being used so effectively. The entrants were extremely varied in both their style and subject matter, and made for a difficult decision. However I’m pleased to be able to announce the winners:

1) Ajay V Singh – Operations Dashboard for a Debt Collections Company.

The target audience are the CXO level execs of the business, aiming to provide a view of all the nerve points of the organization in a single unified interface that is portable and yet comprehensive.

The dashboard layout is dense but uncluttered and well thought through. Colours are well balanced, and allow the reds to draw the reader’s attention as intended.

Ajay’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

Collections Dashboard Screenshot

 

 

 

 

 

 

 

 

 

 

 

 

 

2) John Munoz – Insights into Unemployment in the United States.

Using data from the bureau of Labor statistics, the dashboard gives a deep glimpse into the unemployment situation in the US. A large volume of disparate and tabular information is brought together in a single concise view, which aids understanding and adds real insight. The trends and demographic splits come through very well, and make for easy comparison.

John’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

unemployment_dashboard_munoz

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3) Lisa Cunningham – Anti-Social Behaviour Dashboard

The dashboard is produced by the Research and Information Team at Leicestershire County Council as part of a suite of dashboards produced for the Crime and Disorder Reduction Partnerships. It is available to the public through the local web portal, which makes readability, and also the contact information provided vital. The dashboard aims to provide an at a glance view of the level and trend of ASB, and does an excellent job.

Lisa’s background summary of the dashboard, with larger screen shots, will be available on our web site in the coming week.

ASBDashboard

Excel Dashboard Competition – deadline extended

We have decided to extend the entry deadline through the holiday period, to 28th August.

As a reminder, the competition is for real world solutions (no sample data set), and judging criteria include:

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

See the competition page for more detail.

-Thanks to all of you who have already entered, the quality has again been good, and will doubtless lead to an interesting debate when it comes to choosing the winners. As we’ve extended the deadline if there are any additional tweaks you’d like to incorporate you can of course send revised versions.

Excel Dashboard Competition: Bank Dashboard

This blog post is the first in a series of blog post that features the winners of the 2008 Excel dashboard competition.

“A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.”

Stephen Few, Information Dashboard Design (2006)

The dashboards were judged on the clarity and effectiveness of their design, particularly

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

Furthermore we honored the technical aspects of the dashboard, did it use effective (Excel) techniques for

  • The Dashboard layout
  • Data management, pulling data from a database or data warehouses
  • Data logic and calculation : YTD figures, variances, etc….
  • Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook

Today we will review the winning entry, Wades Stokes Bank Dashboard:

image


Continue reading “Excel Dashboard Competition: Bank Dashboard”

The Dashboard Squint Test

Before we go and review the 2008 Excel Dashboard Competition Winners I have to make you familiar with  the Dashboard Squint Test.

Software usability experts and web designer use a quite effective way to assess the organization of a web page or a user interface, the so called Squint Test. You squint your eyes and make an assessment on the overall layout, of elements that stand out, the visual balance and other characteristics of an effective user interface.

SquintTestScreen0001

 
This test can be easily extended and applied to dashboards. Squint your eyes and assess the overall layout.

SquintTestScreen0002


Continue reading “The Dashboard Squint Test”

Excel Dashboard and Visualization Boot Camp

Here the announcement of the Excel Dashboard Boot Camp hosted by the two experts Jon Peltier and Mike Alexander:

Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together to bring you our first annual Excel Dashboard and Visualization Boot Camp!

Continue reading “Excel Dashboard and Visualization Boot Camp”

2008 Excel Dashboard Competition Winners

After much deliberation and debate, we are pleased to announce the winners of the 2008 Excel Dashboard Competition. We were impressed by many of the entries, and thanks to all of you who entered. It’s good to see MicroCharts being put to effective use, and adding value in such a variety of business scenarios and sectors. We had entrants from a broad range of industries including Banking, Insurance, Healthcare, Manufacturing, Oil and Gas and Pharmaceuticals.

The winners are:

1) Wade Stokes – Bank Dashboard

 

 

 

 

 

 

 

 

 

 

Displaying many disparate Banking Key Performance Indicators, and designed as the basis for the Management review of business performance, it truly achieves More Information per Pixel.

2) Jim Uden – Outpatient Surgery Center Dashboard

 

 

 

 

 

 

 

 

Developed for Meridian Surgical Partners, as a one page snapshot for the review and presentation of partnership level business operations and trends.  Jim also includes probably the best associated description of dashboard content and the thought processes involved which we’ve seen.

3) Hitesh Patel – Pharmaceutical Sales Dashboard

 

 

 

 

 

 

 

 

 

Developed by Hitesh Patel and Mike Askew of Data Intelligence, for Bristol Myers Squibb. A key report for the Regional Sales Managers, containing the information required to run the business in terms of cash, growth, share, and competitive performance.

Congratulations to all 3 of our winners. Have a look at our competition page for screenshots and some background on the winning entries. Over the next few weeks we’ll be analyzing the entries in more depth at http://blog.xlcubed.com. We’ll overview each, cover some of the techniques used, and hopefully suggest some further improvements.

Extended Deadline of Excel Dashboard Competition

We will be extending the deadline of our Excel Dashboard Competition by two weeks to 15 June. So you still have a chance of winning the iPhone or the InfoVis workshop.

FlowingData came up with the nice idea of building a visual display or the hundreds of data sets available in the U.S. Census 2008 Statistical Abstract.

I loaded one of the sheets (Patents and Trademarks.XLS) into Excel, enriched
it with MicroCharts, added a detail chart and published it to the Web.

 

 

More Information per Pixel

This blog is about most widely used BI tool in the world, Microsoft Excel!

Our mission is to connect business users to data such that an average Excel user can build his budgeting application, enterprise dashboard or data warehouse reporting with Excel. Such a solution can span from a simple Excel reporting that pulls data from a sheet database using lookup functions to a full fledged enterprise dashboard that sources data from an OLAP cube & can be browsed interactively in the web.

When it comes to dashboard visualization, we are not of the whistles, bells and gauges school of visualization, we advocate best practice in data visualization and effective dashboard charts like sparklines and bullet graphs.

If somebody tells you that you have to leave Excel, buy an expensive dashboard product, invest a lot in IT tools, IT administration, learn SQL and MDX to build an effective dashboard, don’t believe them.

Subscribe to the blog (email or RSS) and we’ll show you how to make Excel fly.