Tableau Dashboard Trial Version Test

This summer I helped a client automate several Excel sales reports, comparing sales forecasts to actual sales, and last year’s results to this year’s.

It’s a complicated process, pulling numbers from different systems, updating lookup tables, compiling the numbers, and creating reports by product, by customer and by sales rep.

Before we automated the process, it took one person almost a week to create the reports, and despite all that effort, nobody was happy with the results.

  • The sales reps thought there was too much data to wade through – they wanted to focus on their customers and sales. Were sales on target? Which customers need more attention?
  • The managers wanted to see the big picture, and quickly assess how things were going overall. Were all the brands performing as expected? Which products or customers were growing or declining?

Tableau Dashboard

While working on the reports, I noticed people talking about Tableau dashboards in Twitter. I followed their links, and was really impressed by what I found.

With Tableau, we could quickly connect to the data in Excel, and also link to the lookup tables, so all the prices and descriptions would be accurate and instantly updated.

So, I downloaded a trial version of Tableau, and created some reports from the client’s data. It was really easy to get up and running, creating tables and charts, with quick filters that let me focus on a specific brand, customer, or sales rep.

Next month, when there’s a new set of data, the old file can simply be replaced by the new file (with the same name), and the charts and tables will automatically refresh when the Tableau workbook is opened.

Share the Results

After you create reports in Tableau, you can send out a pdf file, or a Tableau workbook, to share the results.

There’s a free Tableau reader, so recipients can open the workbook file, and adjust all the filters, sliders and other controls that you’ve added to the dashboard.

Each person can analyze the data in a way that’s meaningful to them, and drill down to the details, or step back to absorb the bigger picture.

To give you an idea of what’s possible in Tableau, I’ve created a sample workbook, shown below. Dan G. Murray, COO of InterWorks Inc., generously shared the sample sales data that he used for a presentation at the Tableau Customer Conference this summer.

With Dan’s data, I made a few dashboards, and Elissa Fink, VP Marketing at Tableau, published them on the Tableau Public server.

Try It Yourself!

Here’s the first dashboard in the workbook, focused on sales rep results and country totals. Each sales rep can see their overall total, and total and average sales by country.

The map gives a snapshot of sales location, and clearly shows that large sections of the USA aren’t buying the company’s products. You can experiment with the dashboard controls, and change the way the data is presented. You might have to widen your browser window, to see the full dashboard.

  • There’s a date range selector at the top left of the dashboard. Only the past four weeks of sales results are in the dashboard, but you can select a specific number of previous weeks, or other range, such as previous quarter.
  • At the bottom left of the dashboard, in the Salesman Name list, click the highlighter at the top right, to turn on the Highlight Selected Items feature. Then, click on a Sales Rep name in one section of the dashboard, and that name is highlighted in all the sections.

Dashboard on Tableau: Sales Rep Dashboard
_________________

Humour in Excel Twitters RIP

Update: You can now find Excel Twitters on my Excel Theatre Blog.

For more than a year I’ve posted Excel related tweets every week, ranging from the hilarious, to the bizarre, to the somewhat useful.

Twitter Search Problems

Lately though, using the search feature in Twitter pulls up long lists of ads, spam, ads, spam, and more spam. To see for yourself, you can search for Excel Spreadsheet in Twitter. Maybe you’ll have better luck than I’m having.

There’s still the occasional nugget of Excelly goodness in the Search page, but I don’t have the time or patience to wade through all the other stuff.

So, I’ll stick to reading what’s in my own Twitter stream, posted by friends and colleagues.

Anyway, I hope you enjoyed the Excel Twitters while they lasted!

A Few Good Things

More good stuff comes from my email and RSS feeds, like these three items from this week.

Andy Pope released his (free!) RibbonX Visual Designer

Chip Gorman emailed this link to an Excel-based soap opera!

Bob Phillips is reviewing Excel 2007 classic menu add-ins in his blog

__________

New Home for Excel Twitters

Update: You can now find Excel Twitters on my Excel Theatre Blog.

New Home for Excel Twitters

Create a Movie Collection Database in Excel

For a simple database, Excel can do a pretty good job of organizing and reporting your data. This example shows a movie collection database in Excel, but you could set up something similar to keep track of books, sales orders, or almost anything else.

Continue reading “Create a Movie Collection Database in Excel”

Remove Excel Macros Security Warning

Even though an Excel workbook doesn’t contain any macros, sometimes when you open a file, a security warning appears.

Macros Disabled

For example, in the screen shot below, there is a warning above the Excel formula bar:

  • Excel Security Warning – Macros have been disabled
Excel Security Warning - Macros have been disabled
Excel Security Warning – Macros have been disabled

Why Security Warnings Appear

These security warnings can show up if:

  • you recorded a macro in the Excel workbook
  • later, you deleted the Excel macro

A module is automatically created to store a recorded macro, but that module is not automatically deleted, when you delete the recorded macro.

Delete the Empty Module

Follow the steps in the video below, to delete the empty modules in the workbook.

  • Warning: As a precaution, you should make a backup copy of the file, before you remove any code.

Doing that should prevent the security warning from appearing again, the next time you open the workbook.

Note: To see the written steps, go to the Excel Macros – Frequently Asked Questions page on my Contextures site.

______________

Let Users Lock or Unlock an Excel Workbook

Some people like an Excel workbook that’s locked down, so they can’t accidentally mess anything up. They just want to go to the data entry section, put in their data, and get out alive.

Protect or Unlock?

Other people hate Excel workbooks that are protected. Maybe they know a bit more about Excel, and are comfortable making changes.

Or, they’ve been assigned to manage a workbook, and don’t want to bother with worksheet protection, because it slows them down.

Give the Users Control

One of my clients has plants all over the world, and we’ve made a similar data collection workbook for each plant.

On the last sheet of the workbook, I’ve added a drop down list, where the user can select TRUE or FALSE, to lock the worksheets.

select TRUE or FALSE, to lock the worksheets
select TRUE or FALSE, to lock the worksheets
  • If the setting changed to FALSE, a macro runs, to unprotect all the worksheets.
  • If the setting is changed to TRUE, all the sheets are protected.

The TRUE/FALSE option is a quick and easy way for users to control the workbook settings, and seems to be working well.

The Code

There’s code on the worksheet module that runs when the Lock cell’s value is changed. To see the code, right-click on the sheet tab where the drop down list is located, and click View Code.

Here’s the bit of code that checks the Lock cell, and protects or unprotects the sheets. In the sample file, there is the full code, and another example that protects or unprotects with a password.

If Target.Address = wsListsAll.Range(“Lock”).Address Then
For Each ws In ThisWorkbook.Worksheets
If bLock = True Then
ws.Protect
Else
ws.Unprotect
End If
Next ws
End If

Download the Sample File

If you’d like to see all the code, you can download the sample file from the Worksheet Protection Selector page on my Contextures website.

On that page, scroll down to the Download section, and you’ll see a link to the file.

The file contains macros, so you’ll have to enable them to test the code.
_______________________

What Is a Waterfall Chart and Why Would I Need One

Last month your revenue was $40,000 and this month it’s only $30,000? What happened? In Excel, you could print a nice report that shows each revenue stream for last month and this month, so you can compare the amounts.

Waterfall01

You could even create a bar chart to compare the different revenue streams.

Waterfall02

The bar chart lets you see the differences for each stream, but maybe you’d like to see how each revenue stream contributed to the overall change in revenue.

A waterfall chart will let you see the changes that occur between a starting point and an ending point. In Excel, you can create a waterfall chart by building a column chart, and making some changes to it.

Create a New Table

The first step in building a waterfall chart is to create a table that calculates the individual changes, and a running total. In the example below:

  • June and July revenues are at the far right.
  • F3 is the total for June.
  • C9 is the total for July.
  • Column D shows the difference, where amounts have gone down.
  • Column E shows the difference, where amounts have gone up.
  • Column B is the running total, from the June start, to the July end.

Waterfall03

Add a Column Chart

To start the waterfall chart, select the range in the thick border (A1:F10), and insert a clustered column chart.

Waterfall04

Hide the Running Total

To focus on the revenue stream changes, you can hide the series for the running total:

  • Click on a dark blue column, to select the Run Ttl series
  • On the Ribbon’s Format tab, for Shape Fill, select No Fill

To remove the Run Ttl series from the legend:

  • Click on the legend to select it
  • Click on Run Ttl, then press the Delete key

Waterfall05

Format the Waterfall Chart

Next, you can add a bit of formatting to make the column chart look more like a traditional waterfall chart. To widen the columns:

  • Right-click on any column, and click Format Data Series.
  • In the Series Options category, set the Gap Width to 0%.
  • Close the dialog box.

Waterfall06

To lighten the gridlines:

  • Right-click on a gridline, and click Format Gridlines.
  • In the Line Color category, select Solid Line.
  • From the Color drop down list, select a light shade, such as the lightest grey.
  • Close the dialog box.

In this revenue chart, up is good, and down is bad, so you can change those series colors to red and green. If the End series is red, change it to a different color, to avoid any confusion. To change a series colors:

  • Click on a column, to select its series
  • On the Ribbon’s Format tab, for Shape Fill, select the color you want

And here’s the finished waterfall chart. I also added data labels with a custom number format, to show up and down arrows.

Waterfall07
________________

Create Excel Pivot Table from Multiple Sheets

A common pivot table question is “How can I create a pivot table from data that’s on separate sheets in my workbook?

Sometime people have a workbook set up with a separate sheet for each region, or for each salesperson.

Eventually, they want to pull all the data together, and create a summary report in a pivot table, from multiple sheets.

Continue reading “Create Excel Pivot Table from Multiple Sheets”

Number Excel Comments for Printing

If you add comments to an Excel worksheet, you might want to include those comments when printing. There are a couple of built in options for printing comments, but neither is ideal.

We’ll look at those options first, then a numbering system, that’s similar to numbered footnotes.

The Built In Options

In the Page Setup dialog box, on the Sheet tab, there are 3 options for printing the comments:

  • (None)
  • At end of sheet
  • As displayed on sheet

CommentPrintOpt

Print At End of Sheet

If you select At end of sheet, a separate page of comments prints, listing the cell address, commenter name and comment text.

separate page of comments prints
separate page of comments prints

As Displayed on Sheet

If you select As displayed on sheet, the comment that are currently visible on the worksheet will print, exactly as they appear on screen.

That might work if there are a couple of comments that you want to show, and can arrange them over an empty space. Otherwise, you’ll end up with a jumbled mess of comments, covering your data.

Print Comments As Displayed on Sheet
Print Comments As Displayed on Sheet

Add Numbers to Cells With Comments

Instead of using either of the built in options to print comments, you could use a bit of programming to add a tiny number at the top right of each cell that has a comment. Here’s a close up view of the numbered cells.

CommentsNum01

List the Numbered Comments

With another bit of programming, you can create a numbered list of the comments, with other details, such as range name, cell value, cell address and comment text.

CommentPrintList

This list is on a separate worksheet, that you can print when you print the sheet with comments.

Download the Sample File

To download the sample file for Excel 2003 or Excel 2007/2010, go to the Number and List Comments section on the Comments programming page. There’s sample code to add numbers, remove numbers and list the comments, and a zipped sample file that you can download.

The Excel 2003 numbering code didn’t work well in Excel 2007. The numbers didn’t appear in some boxes, and the boxes didn’t line up correctly in the cells. So if you’re using Excel 2007, be sure to download that version’s sample file

Both files contain macros, so you may get a warning when you open them. Enable the macros if you want to run the code.
__________