Cyber Monday Sale Pivot Table Tool

It’s Cyber Monday, and we’re all supposed to spend the day shopping online, so I’m having a sale on my Pivot Table add-in – PivotPower Premium.

You can get it today for 30% off, and the sale ends at midnight (Eastern time) tonight, Dec. 2nd. You don’t need to enter a discount code – the priced will be reduced automatically when you add it to the shopping cart.

See the Key Features

You can see three of the time-saving features in this short video.

  • save and apply your favourite pivot table settings
  • clear old items from a pivot table
  • change all the data fields to the Sum function.

Time-Saving Features

The add-in is easy to install, and it creates a new tab on the Excel Ribbon. You can see some of the other tools in the screen shots below. For example:

  • Click to set Currency or Number format
  • Print a list of pivot tables or cache information
  • Turn Autofit on or off




Get the Discount

To see all the details, and buy the add-in at 30% off, please visit this page on my Contextures website: – PivotPower Premium.

The sale lasts until midnight December 2nd, 2013, Eastern Standard time. No discount code is required – the price will be automatically reduced in the shopping cart.


Excel Tools Add-in Black Friday Sale

It’s Black Friday in the USA – the busiest shopping day of the year. This event has spread to Canada too, so I’m having a sale on my latest Excel add-in – Contextures Excel Tools. You can get it today for 50% off, with the discount code CTXBF13. The sale ends at midnight (Eastern time) tonight.

See the Key Features

You can see three of the time-saving features in this short video.

  • Click to make a backup of the active workbook
  • Protect or unprotect all sheets, with or without a password
  • Create a menu sheet, with a hyperlink to each worksheet in the file

Time-Saving Features

The add-in creates a new tab on the Excel Ribbon, and you can see some of the other tools in the screen shots below. For example:

  • Create a Concatenate formula that combines all the selected cell values
  • Sort the sheets in alphabetical order
  • Fill blank cells with the values from above

Get the Discount

To see all the details, and buy the add-in at 50% off, please visit this page on my Contextures website: Excel Tools add-in.

Use the discount code: CTXBF13

The code is good until midnight tonight, Eastern Standard time – November 29, 2013.


Pocket Price Waterfall Chart in Excel

I'm working on some pricing reports for a client, and one of the requests was for a Pocket Price Waterfall chart. I hadn't made one of those before, and fortunately the client sent me a sketch of the chart that they wanted. It looked something like this.
The good news was that it looked similar to other waterfall charts that I've made. However, instead of two highlighted columns, to show start and finish, it had several highlighted columns.

The Pocket Price

The term "pocket price" was new to me, and some Googling led me to an article published by McKinsey & Company in 2003 – The Power of Pricing. They coined the term "Pocket Price" to describe the amount that you actually get from a customer, after the hidden costs and off-invoice discounts that might exist. The article shows a waterfall chart, similar to the one that my client wanted. Maybe that's where they got the idea!

Add the Extra Columns

To experiment with adding more highlighted columns, I started with the cash flow waterfall chart from a previous blog post, and added rows for the Quarterly totals. In the previous version, there was only one number in the End column, and I needed a number in each Quarter row, where I wanted a highlighted column.
I created a new formula in the End column, cells C4:C14, to check the Net Cash Flow column (G). If that cell is empty, show the sum of the previous amounts.
The Base column is the reverse, showing the sum if the cell in column G is NOT empty.

The Revised Waterfall Chart

Here is the revised waterfall chart, with a highlighted column for each quarterly total, ending in September.
Then, I used the same formulas with some faked Pocket Price data, to create the Pocket Price waterfall chart. There aren't any green columns, because all of the values are negative amounts, bringing down the price.
And now I'm ready to create the real Pocket Price waterfall chart, whenever we get to that stage in the project.

Download the Sample File

To see the waterfall charts and the worksheet formulas, you can download the Pocket Price Waterfall Chart sample file. The file is in Excel 2007 format, and zipped. There are no macros in the file.

Waterfall Chart Utility and Video

If you need to make waterfall charts, take a look at Jon Peltier's time-saving Excel Chart Utility. While working on this chart, I discovered that Jon's add-in has this multi-highlighted column feature built in. It also handles data that crosses the X Axis, with totals in the negative amounts.
To see how I used Jon's add-in to quickly create a sample Pocket Price waterfall chart, please watch this Excel video tutorial.


Excel Power Utility Pack Sale Phase 2

80percentoffjwalk Tomorrow, Tuesday June 7th, John Walkenbach is having another deep discount sale on his Excel add-in – Power Utility Pack (PUP).
Remember, Father's Day is this month, so this could be the perfect gift for the dad who has everything. Or give your kids the link, and let them buy it for you!

24 Hours Only

The sale price is in effect for only 24 hours, starting at 12 noon, Eastern Time, so act quickly if you're interested. Even at its full price of $40, the PUP is a great investment, but during the sale it's over 80% off – only $7.77! The older version, for Excel 2003 and earlier, is $6.66.
While you're shopping, you can also buy the password to the PUP's VBA source code, for only $20. That's its regular price, but definitely worth buying. It's a rare opportunity to see under the hood of a professional Excel add-in, and could save you many hours of programming, if you're trying to do something similar.

PUP Features

For your small investment of $7.77, you'll get these features in the Power Utility Pack:

  • more than 70 general purpose Excel utilities
  • 53 worksheet functions
  • 6 non-serious amusements, for when you need a break from number-crunching.

You can read about all the PUP features, but don't spend too much time reading – remember, the sale is only 24 hours!

Fuzzy Lookup Add-in for Excel 2010

clean data If you work with data in Excel, you know what a mess it can be. I help my customers clean up data that they've imported from another computer system, or from reports received from another department or group.
Those files can be filled with spelling mistakes, strange abbreviations, extra spaces or missing punctuation. It can take you hours, or days, to match items between lists, and eliminate any duplicates. Your favourite Excel functions, like VLOOKUP or MATCH, can do some of the work, but fail in other areas.
Shaking your fist, or cursing at the data, doesn't help, as I'm sure you know. Fortunately, there is a new Excel add-in, from Microsoft's BI Labs, that looks promising.

Fuzzy Lookup Add-in for Excel 2010

The new add-in is named Fuzzy Lookup Add-in for Excel 2010, and it helps you find best matches, for items that aren't an exact match. The project description says:

It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables. The matching is robust to a wide variety of errors including spelling mistakes, abbreviations, synonyms, and added or missing data.

That sounds useful, so I downloaded the add-in, and installed it for testing.

Set Up the Fuzzy Lookup

When you open Excel 2010, after installing the Fuzzy Lookup add-in, you'll see a Fuzzy Lookup tab on the Ribbon, and it contains the Fuzzy Lookup command.
Follow these steps to set up the Fuzzy Lookup:

  1. The add-in works with formatted tables, so create one or more Excel tables in your workbook, if you haven't done so already.  The add-in download includes a sample file named Portfolio, which contains Excel tables.
  2. On the Ribbon, click the Fuzzy Lookup command, to open the Fuzzy Lookup pane.
  3. Select the left and right tables for the comparison (to identify matches in a single table, set the left and right tables to be the same)
  4. Columns with the same heading will be automatically joined. You can leave that join, or delete, and create new joins for other fields.
  5. For the Output columns, select the columns in each table that you want in the output table. Include the FuzzyLookup.Similarity column, to see how close the matches are.
  6. Leave the number of matches at the default of 1, or increase it to see more matches for each item in the left table.


Run the Fuzzy Lookup

After you set up the Fuzzy Lookup, follow these steps to run it:
Select a cell in an empty part of the workbook. The results  will start in the active cell, so make sure there is enough room, below and to the right of the selected cell.
Click the Go button, to output the results.

Configure the Fuzzy Lookup

You can change the configurations in the Fuzzy Lookup add-in, and create your own.

Test It Yourself

To do your own testing, you can download Fuzzy Lookup Add-in for Excel 2010. The download page has a couple of videos that you can watch, for an overview of how the add-in works.

Excel Power Utility Pack Sale

PUP Sale Tomorrow, May 3rd, John Walkenbach is having a 2-hour sale on his Excel add-in – Power Utility Pack. Even at the full price of $40, it's a worthwhile investment, but during the sale it's over 80% off – only $7.77! The older version, for Excel 2003 and earlier, is $6.66.
You can also buy the password to the VBA source code, for only $20. That's a bargain too, because it saves you countless hours of writing that code yourself, of wasting time looking for sample code online.

PUP Features

For your small investment of $7.77, you'll get these features in the Power Utility Pack:

  • more than 70 general purpose Excel utilities
  • 53 worksheet functions
  • 6 non-serious amusements, for when you need a break from number-crunching.

You can read about all the PUP features, but don't spend too much time reading – remember, the sale is only 2 hours!

Create a Waterfall Chart in Excel

We have a very famous waterfall here in Canada, and it creates gorgeous photos, like this one from my fall 2008 vacation.
You can create waterfalls in Excel too -- Waterfall Charts. They might not be as spectacular as Niagara Falls, but can be useful for showing how values change. There are details here for creating a simple waterfall chart, and a video that shows the steps.

Net Cash Flow

For example, in a small business, the net cash flow might be a positive number one month, and a negative number the next. In the Waterfall Chart shown below, the red columns represent a negative number, that brings the cumulative cash total down. Green columns are shown for months with a positive cash flow.

  • The starting value for each red column (negative) is at its top, and the cumulative value for that month is the amount at the bottom of the red column.
  • The starting value for each green column (positive) is at its bottom, and the cumulative value for that month is the amount at the top of the green column.
  • The grey columns (Start and End) compare the original and final amounts, after all the monthly values have been included.


Set Up the Excel Data

Excel doesn't have a Waterfall Chart Type, but you can create one by arranging your data in columns, then adding and formatting a stacked column chart.
In the screenshot below, columns have been added after the dates, to create the series for the waterfall chart. The Base column is used as a foundation for the "floating" green and red columns.
The formulas are shown below the table, so you can see how each column is calculated.

Create the Waterfall Chart

After you set up the data, follow these steps to create the chart:

  1. Select cells A1:F17, and insert a Stacked Column chart.
  2. Format the Base series to have no fill and no border, so it's invisible.
  3. Reduce the gap width between the columns
  4. Format the columns with the colours you'd prefer
  5. Remove the Legend.

Download the Waterfall Chart Sample File

On the Contextures website, go to the Create an Excel Waterfall Chart page, and you'll see the formulas used in the waterfall chart data columns. You can also download the sample Excel Waterfall Chart file, to see how it works.

Waterfall Chart Utility

If you need to make more than a couple of waterfall charts, or other custom charts, take a look at Jon Peltier's time-saving Excel Chart Utility. It's very reasonably priced, and will quickly pay for itself, in time saved, aggravation avoided, and possible prevention of hair loss. 😉

Watch the Waterfall Chart Video

To see the steps for setting up your data, and creating an Excel Waterfall Chart, please watch this Excel video tutorial.

Link to Excel Waterfall Chart Utility video

Airport Security Times in Excel Box Plot

image Earlier this month, I had the pleasure of flying out of Chicago's O'Hare airport. I was checking in at the ungodly hour of 6 AM on a Sunday, and hoped that would be a quiet time at the airport. The streets near the downtown hotel were almost empty, so that was encouraging, but the closer the taxi got to the airport, the busier things got. Not a good sign!
There was no line at the Air Canada counter (woohoo!), but there was no counter agent either. He showed up a couple of minutes later, and that part of the process went quickly. (The same guy showed up at the gate later, to take our tickets, and attached the jetway to the airplane when it arrived. I figured he'd fly the plane too, but he didn't!)
Then, it was on to the dreaded security checkpoint, and the inevitable long wait.
One poor gentleman actually passed out in the line, and fortunately recovered quickly, aided by a paramedic, who was also in line. That's another bad sign, if you have to step over people, to get through security.
And what does all this suffering have to do with Excel, you ask? Well, to help you plan your next trip, I found a website that lists the wait times for US airport security checkpoints. From their data, I created an Excel Box Plot (Box and Whisker Chart), for a few of the security checkpoints at O'Hare. You can do something similar for your airport, and choose the best time to fly. Hint: It is not Sunday at 6 AM.

Create Statistics from Your Data

To create a box plot, you'll calculate the the MIN, MEDIAN and MAX for each series. Then, use the PERCENTILE function, to find the lower quartile (Q1) and upper quartile (Q3). You can calculate the AVERAGE too, if you'd like to show that on the chart.
Based on those numbers, you'll calculate the differences, to find the size of each box and whisker.
Then create and format a stacked column chart (boxes), with error bars to show the highs and lows (whiskers).
Unfortunately, I don't remember which checkpoint I went through, but I'll bet that it was Checkpoint 6.

Jon Peltier's Box Plot Utility

If you need to make box and whisker charts, or other custom chart types, the PTS Excel Chart Utility will make it much easier and quicker for you to create them. It adds a new sheet in your workbook, calculates the statistics for your data, then creates and formats a box and whisker chart. It will pay for itself very quickly, in the time you'll save.

Watch the Box Plot Video

To see the steps for creating an Excel Box and Whisker chart, watch this Excel video tutorial. It's recorded in HD, so select that option when viewing it, if you can, for a clearer picture.


Capturx Forms for Excel Review

[Update Nov. 15, 2015  - Adapx, Inc. has ceased operations] I loved the idea of a pen that could magically send my data to Excel, but it didn't work out as well as I'd hoped. 😉 Anyway, here's my review of Capturx Forms for Excel, made by Adapx, Inc.
For the past couple of years, I've worked with a client that has factories around the world. As part of their safety program, employees fill in printed checklists, then send them to the Safety Officer, who enters the data in Excel. As you can imagine, this takes time, and is prone to data entry errors.

An Enticing Offer

Recently, I was asked if I'd like to test a digital pen and Capturx Forms for Excel. With it, I could create and print blank forms in Excel, then use the pen to fill them in. Later, that data could be imported into Excel, with one click of a button.
That sounded good, so I accepted the offer, and the pen and software arrived a few days later. If the product worked well, my client could test it in their plants, and possibly save some time and headaches.
Well, it took a while, but I'm finally done testing. I loved some of the features, but there were frustrations and disappointments along the way.

Stalling on the Installation

Installation was the first obstacle to overcome. Several components had to be installed before the Capturx program, and it took several tries before everything finally worked. I won't bore you with all the details, but I seriously considered putting everything back into the box and returning it.
I'm not sure if there was a problem with my hardware, or conflicting software, or something else. The documentation didn't provide any tips for installation problems, nor did the company website. There's product information online, but no search feature that I could find.

It's Not You, It's Your Printer

Thrilled at finally getting the program to work, I created a simple form, and was ready to test the digital pen. Oops! You need to print the forms on one of the compatible printers. I have a black and white laser printer, not one of the colour postscript laser printer on the list.
When I told the company rep about the problem, she sent some sample forms by courier. I was able to keep testing, without buying a new printer. Whew! Here's one of the sample forms that I used for the test.
Also, while reading the printer information, I noticed that the dot pattern that's printed on the forms "gets used up as you print." When the dot pattern is gone, you need to purchase more from the Adapx website. So, if you use this solution, you'll have some ongoing costs.

The Good News

Once I was up and running, my mood definitely improved. I filled in a couple of forms with the digital pen, then docked the pen in its base. In Excel 2007, a Capturx tab appeared on the Ribbon, and with a single click, I imported the forms' data.
Capturx Forms for Excel
I could compare the imported data to the original writing, and easily make changes where necessary.
From the Master Template sheet, I could create a summary of all the imported forms.
The summary cells are linked to the original sheets, so they'll update if you change the forms' data. However, new sheets aren't added automatically. You have to delete the summary sheet, and create a new one.

A Few More Problems

I hit a few more snags while working with the Capturx Forms for Excel add-in. For example, it was painfully slow to move through the workbook. When I clicked on a different sheet tab, it took several seconds for the sheet to activate.
Excel has crashed a few times while using it, especially when trying the Sort Worksheets feature. When I deleted the Summary sheet, the sort feature worked, but it took more than 10 minutes to complete.
The sample workbook has 95 completed forms in it, which seems like a reasonable number. In a real life situation, you'd probably have many more forms than that. Maybe the features work well in a smaller workbook, but that somewhat defeats the purpose of digital input.

The Final Word on Capturx Forms for Excel

I had high hopes for the digital pen and Capturx Forms for Excel, and some of those dreams came true. The pen worked like a charm, and even with my bad handwriting, there was a good recognition rate for the imported data.
For me, the main drawback is the slow performance. If you're collecting lots of data, how could you manage it with this software? Maybe you could use Excel to import the data, then export it to Access, or another database.
Also, the Help could be better, with a search function on the website. For example, I couldn't figure out how to create a Summary sheet, and nothing in the notes or built-in help provided any clues. After several experiments, I finally got it working. The company is currently offering online training sessions, so that might help with some of the confusing features.
There's potential for a great product, but Capturx Forms for Excel needs some improvement before I can recommend it to my clients.

The Movie

If you'd like to see the digital pen and Capturx Forms for Excel in action (I cut out the inaction bits), you can watch this short video.


How Much Would You Pay For an Excel Utility?

John Walkenbach is having a 2 hour sale of his Power Utility Pak (PUP) – over 80% OFF the regular price. You can get all your Christmas shopping done early, if you act fast! I'm sure that all your family and friends would love an awesome Excel utility.
The PUP sale is Tuesday, November 3, from 11:00 am to 1:00 pm U.S. Eastern Daylight Time (that's 4:00 pm – 6:00 pm GMT).
Go to his Special 2-Hour Sale Order Form to buy either version:

  • PUP v7: $7.77 (normally $40.00)
  • PUP v6: $6.66 (normally $39.95)
  • The complete VBA source code is available for an additional $20.00

If you'd like to see the PUP utility before you buy it, you can download a trial version.

PUP Features

John sent me a complimentary copy of PUP v7 last year, and I reviewed it here. I still highly recommend it, so if you've ever thought about buying it, this is your opportunity.
Even if you use just a few of its many features, you'll get more than your money's worth. Where else can you get a Table of Contents creator, Change Case tool, Dice Game and tons of other features, for under $8?

How Do You Price Software?

If you create an Excel utility and offer it for sale, how do you decide what to charge? Do you check out the competition and price your utility in the same range? Do you crunch a pile of numbers in Excel, and base your price on the results?
To help you with pricing, you can download a free pdf file of the book Don't Just Roll the Dice: Usefully Short Guide to Software Pricing, by Neil Davidson, of Red Gate Software. If you prefer a physical copy of the book, you can buy it on Amazon.
The book explores the economics and psychology of pricing, and the author cites his own experiences (good and bad) with software pricing. It's easy to read, with clear explanations and examples, and ends with a product pricing checklist. It also has this sensible advice: "Practice trumps theory. Try out your pricing and see what happens."