Okay, it’s a day late, but here’s an Excel Advent calendar, for you to use during the month of December – for the first 24 days.
Drill Into Data With PowerPivot
Have you tried Microsoft PowerPivot for Excel 2010 (formerly Gemini)? It’s a powerful data analysis add-in for Excel, and is part of the Office 2010 Beta.
If you haven’t downloaded the Beta, you can test PowerPivot in the hands-on Virtual Lab.
That’s where I tested PowerPivot last weekend, and hit a few snags, but was impressed by what PowerPivot can do.
Testing PowerPivot
On Friday, a surprise package arrived in my mailbox – a set of power tools! It was a promotion for last week’s release of PowerPivot, and the power tools had clever labels, like this one on the flashlight.
Did the power tools influence my decision to try PowerPivot?
Of course! Testing PowerPivot was already on my To Do list, and the power tools inspired me to move it to the top.
Will the gift influence my testing? Nope. I’ll still tell you exactly what I think.
The PowerPivot Add-In
I had trouble with the virtual lab on my desktop computer, and couldn’t get the ActiveX control installed.
Next, I tried on my laptop, which is newer, and everything went smoothly there. Both machines are Windows XP, and I used Internet Explorer 8 as the browser.
Start the Virtual Lab
Once the virtual lab was running, it was easy to get started, and work with PowerPivot in Excel.
The PowerPivot add-in creates a new tab on the Excel Ribbon.
Launch PowerPivot
Click PowerPivot Window, to launch the add-in, and open the PowerPivot client window. From there, you can connect to data from a variety of sources.
I’d normally connect to Access data, but in this example I used the SQL Server connection.
Select a Table in Data Source
Next, select a table from the data source, and PowerPivot can automatically select related tables. You can also filter the selected data, before importing it.
In the virtual lab, I connected to a Sales table that had almost 4 million records, and it took just a couple of minutes to import.
The Imported Data
In the PowerPivot client window, each table is on a separate tab.
You can change the tab names, and add calculated fields in the tables.
The formula bar looks just like Excel’s, and the field names appear automatically when you start typing.
Create a Pivot Table and Pivot Chart
You can create a pivot table and pivot chart from the data, using the PowerPivot Task Pane (called the Gemini Task Pane in the virtual lab).
The pivot table and pivot chart weren’t connected though – adding fields to one, didn’t affect the other.
I’m not sure if that was a bug in the virtual lab, or a Beta feature that will be fixed later.
Add Slicers
You can also add horizontal and vertical Slicers to the pivot table and pivot chart, to filter the data that’s displayed.
Try PowerPivot Yourself
This was just a quick overview of the PowerPivot test in the PowerPivot virtual lab. If you don’t have the Office 2010 Beta installed, I’d recommend this as a great way to see what PowerPivot can do.
It took me about an hour to go through the 3 modules, while making notes and taking screenshots.
Read the PowerPivot Instructions
There’s a button to download a PDF file with the instructions, but that didn’t work, so I copied the instructions and pasted them into Word.
It was easier to read the instructions in Word, where I could increase the Zoom level. Also, the instructions disappeared at one point, and I would have had to start over, if I hadn’t made a copy.
The virtual machine hung on me a couple of times, and I don’t see a way to start anywhere except the beginning.
Restarting was annoying, but it was pretty quick to go through the steps the second time.
_______________
For more information on PowerPivot, see the PowerPivot Team blog.
For more information on Excel Pivot Tables and Excel Pivot Charts, see the Pivot Table FAQs on my Contextures website.
___________________
Excel VBA Edit Your Recorded Macro
Last week was Recruit a New VBA Programmer Week on the Daily Dose of Excel blog, so we pitched in by recording an Excel macro, and running it.
I’ve updated that article, by adding a video, so you can see all the gory details, step-by-step.
This week we’ll look at the Excel VBA code that the Macro Recorder created, and make a minor change, to edit your recorded macro.
Excel Time Formula Examples
Yes, Excel can help you have a good TIME. It’s also useful if you’re looking for a DATE, or the perfect MATCH. (Insert your own bad puns here.)
Today we’ll focus on the TIME function, because one of my clients recently wanted some help with that.
What Time Is It?
There are a couple of keyboards shortcuts for entering the date or time.
- To enter the current date on a worksheet, press the Ctrl key, and tap the ; key.
- To enter the current time, press Ctrl and Shift, and tap the ; key
If you want the date or time to update when you open the workbook, you can use a formula instead.
- To enter the date with a formula, type: =TODAY()
- To enter the date and time with a formula, type: =NOW()
Is It Too Late?
My client’s workbook had a cell with a process start time.
To help avoid errors, he wanted to check if the current time was later than the start time.
Worksheet Time Calculations
The formula in cell C6 compares C4 to C2. I
- f the time in C4 is greater than the time in C2, the result is TRUE.
- Because C4 is empty in this screen shot, the result is FALSE.
Excel NOW Function
If we use the NOW function in C4, it includes the date, as well as the current time.
As a result, even though the time in C4 is 8:17 AM, its value is much higher than the 9:00 AM time in C2.
I’ve added temporary formulas in column D, so you can see the numeric value for each cell in column C, in the screen shot below.
Formula to Remove Date Value
To calculate the current time value in C4, we can change the formula, to remove the date value.
The TRUNC function removes the fractional part of a number, so we’ll subtract TRUNC(NOW()) from the NOW function.
=NOW()-TRUNC(NOW())
Translated to English, the formula is: (DATE and TIME) minus DATE
The revised formula leaves the time value only in C4, and now the value in D4 is lower than the value in D2.
The formula in C6 now shows the correct result of FALSE.
More Date and Time Info
For more examples of Excel Date and Time functions, go to the Excel Date and Time page on my Contextures site.
___________
Print Selected Items on Excel Invoice
You have an Excel order form with a long list of products to select. After a customer enters quantities for the products they want to order, you want to print an invoice for the selected items.
Select the Products
Here’s the OrderForm sheet, where quantities have been entered for four of the products, in column E.
Formula Adds Numbers
Formulas in column B check the quantity column (E).
If the quantity is filled in, then the next available number is calculated.
- =IF(E6>0,MAX($B$5:B5)+1,””)
Print the Order
On the Invoice sheet, formulas pull the data from the Order form. For example, this formula shows the name of the first product that was ordered:
- =IF(MAX(OrderForm!$B:$B)<$B12,””,
INDEX(OrderForm!$C$6:$C$12, MATCH($B12,OrderForm!$B$6:$B$12,0)))
Print the Order Form
Here’s the Order Form sheet, ready to be printed.
- The numbered rows from the Products sheet have been added to the Order form.
- Products without a quantity, such as Sweater, don’t appear.
The Sample File
To download the sample file, please visit the Excel Sample Files page, on my Contextures website.
In the Functions section, look for FN0027 – Print Marked Invoice Items.
___________________________
Lean Mean VBA Machine
This is Recruit a New VBA Programmer Week, according to Dick Kusleika, so we’d better get moving. I haven’t recruited anyone yet, have you?
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.
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.
_____________
Numbered Excel Sheets Cause Macro Problems
Naming Excel sheets – how hard can it be? Last month, we looked at sheet names, and saw the problems that were caused by using an apostrophe. This week, I found another sheet name problem.
Macro Creates Sales Rep Sheets
Someone, let’s call him Mr. X, contacted me about one of my sample worksheets — Create New Sheets from Filtered List.
The file has a macro that creates individual sheets for each sales rep, filtering data from a master sheet, like the one shown below.
Second Time Around
Mr. X said he could run the macro once, then got an error the next time he tried to run it. His email also included those dreaded words, “I made some changes.”
Unfortunately, there was no clue as to what those changes were, but at least he attached the problem file.
Instead of Rep names, he wanted to created a worksheet for each customer ID.
Sheets with Number Names
Sure enough, the macro created all the sheets, without problems, the first time it ran.
Then, as promised, I got an error the second time that it ran.
And mysteriously, the Data sheet had been cleared.
The Numbers Game
What happened? Well, the code runs though the list of Customer ID numbers, and if a sheet doesn’t exist for that number, it creates one.
If the sheet does exist, it clears the sheet, then adds the new data.
When the code got to customer ID 1, instead of clearing the “1” sheet, it cleared the first sheet, Data, which has an index number of 1.
To fix the problem, I changed the c.Value reference in the code to CStr(c.Value)).
Now, instead of looking for the sheet with an index number of 1, it looked for a sheet with a name of “1”.
Watch the Numbers
If you use numbers as sheet names, such as a sheet for each day of the month, be careful how you refer to those sheets in your code.
You don’t want to end up like Mr. X, with blank sheets, and error messages!
______________
Sort Multiple Columns in Excel
Without family, where would Excel blog ideas come from?
My daughter called today, and asked how to sort more than 3 columns in Excel. She uses Excel 2003, and the Sort dialog box only allows three column choices.
There’s no box to choose the fourth column – Name.
Sort By Multiple Columns
Obviously, she didn’t check my website, where she would have found the instructions to sort by four columns in Excel.
You can sort in groups of 3, working up from the least important to the most important.
So, I told her to sort the fourth column (Name), then the first three (Service, City, Rating).
Sorting in Excel 2007
If you like to sort lots of columns, you’ll be happier in Excel 2007. There, you can sort up to 64 columns at one time.
If my daughter had Excel 2007, she could choose all four columns, and sort them in one step.
In the Sort dialog box, click the Add Level button, to include another column in the sort specifications.
Automating the Sort
If you turn on the macro recorder while sorting in Excel 2007, you can create a macro to sort the table.
Then, run that macro later, when you need to sort the table again, using the same settings.
Macro Error in Excel 2003
However, if you’re sending the workbook to someone who uses Excel 2003, they’ll get an error message if they try to run your sort code.
Just like families, sometimes the different generations don’t communicate too well. 😉 Because the sort feature changed so much in Excel 2007, the older versions don’t recognize some of the new properties.
Sort Macro for All Excel Versions
If you plan to use the workbook in both versions of Excel, record the sort macro in Excel 2003. This will also run without problems in Excel 2007.
Here’s the code from this example, with the Name in column D being sorted first.
_______________
Advanced Filter Macro Problem in Excel
I love advanced filters, but this week they caused me some serious grief.
Advanced filters in Excel are quick and powerful, and I use lots of them.
They’re a great way to pull a list of unique items from a table, or send specific data from one sheet to another.
Advanced Filters
People sometimes have trouble with an advanced filter, usually because the headings are incorrect, or there are no headings.
The advanced filter criteria can be a bit tricky too, if you go beyond the basics.
I know about those problems, and am careful to avoid them.
A Simple Filter
This week, as part of a larger procedure, I had an advanced filter, similar to the one shown below.
Filter for Unique Products
The code was designed to pull a list of unique products from column G, and put it in column K.
In this example, all 7 products in column G are unique, so they all should have been filtered to column K.
However, after I ran the filter, there was nothing in column K, except the heading!
Leftovers Clog the Filter
I checked, and the headings were an exact match, so that wasn’t the problem.
I checked the code carefully, and everything looked okay. Similar code had run hundreds of times, without a hiccup.
To troubleshoot, I tried to run the advanced filter manually, and in the screen shot below, you can see what appeared in the Advanced Filter dialog box when it opened.
Hmmm. There, in the Criteria Range box, was the range from a previous advanced filter.
Clear Criteria Range Setting
I cleared out the Criteria Range setting, and the filter ran without problems.
As expected, all 7 products showed up in column K.
Change Advanced Filter Macro Code
To prevent the macro problem from happening again in the future, I revised the macro code.
Even though the advanced filter didn’t use a criteria range, I added one to the code, with the setting as an empty string.
Good Housekeeping Prevents Clogging
With that change, the code ran perfectly, even if a previous filter had a criteria range.
To prevent your own advanced filter headaches, add that empty criteria range to your code, if you’re not using a criteria range.
It will clear out the setting, in case a previous advanced filter used a criteria range.
I’ll certainly do that from now on!
__________________