Everyone loves a good mystery! And now you can create a few in your Excel workbooks. I’ve been updating some Excel files that are used for data entry. Some tabs have a long series of questions, and some questions have two or more subsequent questions.
Favourite Free Excel Add-Ins
Excel is certainly packed with features, but I use a few free add-ins that make Excel even better.
[Update] I’ve created a list of all the free Excel add-ins that were suggested in the comments. Thanks for sharing your favourites!
Name Manager
by Jan Karel Pieterse of JKP Application Development Services
If you use names in Excel, you need this tool. You can quickly find names with errors and delete them, or track names that link to another workbook.
There are many more features, so download Name Manager, and see what it can help you with.
Excel Utilities
by Rob Bovey of AppsPro
Rob’s add-in has handy tools for working with named ranges, worksheets and selections.
You can quickly protect and unprotect all the sheets in a workbook, remove unused styles, and my favourite – centre across a selection, without merging cells.
There are many more features, and you can read the full description on Rob’s page for Excel Utilities.
Pivot Power
Since I work with pivot tables so often, I created my own pivot table add-in, that you can download.
The commands that I use most often are Clear Old Data, and Sum All Data.
Your Favourite Free Excel Add-Ins
What are your favourite free add-ins for Excel? Any of the ones that I’ve listed? Have you created some of your own?
_________________
Find Answers to Excel Questions
Where do you go when you’ve got an Excel question?
Survey: Where to Find Excel Answers
A while ago, I recommended the Excel newsgroups as a great place to ask questions and get one (or several) solutions. Is that what you use, or something different?
Let’s try a quick survey – it’s my first attempt, so fingers crossed that it goes smoothly! If you answer “Other”, you can add details in the comments, if you’d like.
UPDATE: The poll has closed.
Where I Find Answers
For quick questions, I usually use Google, either a general search, or a newsgroup search.
Next, if nothing turns up in Google, the Microsoft Knowledgebase is my next option, in most cases.
Occasionally, if really stumped, I’ll email a colleague for help.
Excel Books
I have a good collection of Excel books that I use too.
Some are excellent reference books, with a good index, and markers on the pages that I turn to frequently.
Others are more for inspiration, and learning new things, than for research.
Your Excel Books
There’s a list of Excel books on my website, divided into categories. If you use any of those, I’d love to know what you think of them.
Are there any Excel books you’d recommend, that are missing from the list?
_________________
Highlight Items in 2 Excel Lists-Conditional Formatting
If there are any typos in this blog post, blame my maple syrup related injury. Sunday morning, I tried to clean a few drips from the syrup jug, and ended up with a puncture wound.
Those crystallized bits are razor sharp, so don’t put your delicate typing fingers anywhere near them. Maybe it’s just a Canadian hazard!
List of Excel Files
Bravely carrying on, I was updating some files, and keeping track of the updates in Excel.
On a sheet named All_Files, I have a list of all the files, and the number of downloads for each file.
On the Files_Updated sheet, I have a list of files that have been updated.
Mark Duplicate Entries With Conditional Formatting
When looking at the full list of files, I’d like a quick way to identify the files that have been updated.
The actual list is pretty long, and my faulty memory only works for the first few updates. After that, I can’t really remember which ones have been done.
See the written steps, and an Excel video, in the sections below
Use Formula to Count Duplicates?
I could add a new column, with a COUNTIF formula to count the number of times each file appears in the Update list.
Instead, I’ll use conditional formatting to colour the rows for files that have been updated.
Highlight Duplicate Items
Just like data validation, conditional formatting complains if you try to refer to cells on another worksheet.
So, I’ll name the range on the Files_Updated sheet, and refer to the named range.
For some reason, Excel is okay with references to named ranges on another sheet.
Name the Range
To name the range:
- On the Files_Updated, select column A
- Click in the Name box, and type a one word name for the range – UpdateA in this example.
- Press the Enter key, to complete the naming.
Add the Conditional Formatting
Next, add the conditional formatting to the list of all files.
- On the All_Files sheet, select the cells that contain the file names and download quantities.
- On the Ribbon, click the Home tab
Click Conditional Formatting, then click New Rule.
Create Conditional Formatting Rule
- In the New Formatting Rule dialog box, click Use a formula to determine which cells to format
- In the formula box, enter a COUNTIF formula, referring to the named range on the Updates sheet, and to the active cell on the All_Files sheet. Use an absolute reference to the column, $A. In this example, the formula is:
=COUNTIF(UpdateA,$A2)
- Click the Format button, and select the formatting you want for the highlighting.
- Click OK, twice, to close the dialog boxes.
The rows for the files that have been updated are now highlighted.
You can quickly see which files are done, and concentrate on the files that still need to be updated.
Watch the Video
To see the steps in action, you can watch the following short video.
____________________
Summer Giveaway for Excel Nerds Winners
Thanks for participating! You’re an awesome bunch of Excel nerds. The entries in the “You might be an Excel nerd…” giveaway were very entertaining, and read more like my resume than a list of contest entries.
Yep, I do some of those things, and I’ll bet you do too. 😉
Thanks again to the generous prize donors:
- Jan Karel Pieterse, of JKP Application Development Services
- Ken Puls, of The Ken Puls Blog
- Jon Peltier, of Peltier Technical Services
- Apress publishers
The Draw
As promised, I did a random draw of prizes and entries. But, in true Excel nerd fashion, I didn’t just drop the names into a hat. No, that might have only taken 5 minutes or so. Instead, I wrote a macro to do the draws for me.
Before the draw, I cleaned up the list of entries, removing the people who said they weren’t participating, and deleting any extra entries per person. I kept the comment numbers with the names, and created a numbered list of prizes, based on the order they appeared in the original giveaway post.
I set up a list to record the draw number, prize number and entry number, then ran the macro. There was even a drum roll at the start of the draw, but it was disappointingly quiet. Oh well, at least everything else went smoothly.
You’d probably do it differently, and maybe without the drum roll, but if you’d like to see the macro that I used, you can download the Draw Macro Sample file. Now you’ll be able to organize your own giveaways!
And the Winners Are…
After the numbers had been selected, I used a couple VLOOKUP formulas to pull the prize names and winner names from the original lists of numbered prizes and entries. Congratulations to all the winners! Here’s the list:
Collect Your Prize
I’ll send an email to all the winners on July 29, 2009, with instructions for claiming your prize. Please reply by 5 PM EDT on Monday August 10, 2009, or your prize will be put back into the vault for the next giveaway.
Note: Some of the prize donors will be away from their offices in August, so there may be a delay in mailing.
Update: I’ve sent out the emails, using the email address that you used in your comment. If you haven’t received your email, please let me know at ddalgleish @ contextures.com
______________
Prevent Excel Dependent Drop Down List Problems
You know how to create a drop down list in Excel, by using the Data Validation feature.
Sometimes you get extra fancy, and make a drop down list that depends on the selection made in another list, using dependent data validation.
Dependent Drop-Down List Problem
Dependent data validation works well, but there’s a loophole.
In the screen shot below, I can change the category to Fruit, even though a vegetable, Cabbage, is selected as the Item in that row.
With this loophole, you could end up with some strange data on your worksheet!
Lock the First List
Excel users are very resourceful, and can find ways around almost every safeguard that you set up.
However, you can slow them down a bit, by making the first list dependent on the second.
They’ll have to clear the Item selection before they can change the Category.
Currently, the Category cells have a data validation list that’s based on the named range, Produce.
Change Data Validation Formula
You can change the formula in the Source box, so it only uses the Produce range if the Item cell is empty. In the screen shot below, cell B2 is active, and the Data Validation Source formula is:
=IF(C2=””,Produce,B2)
Cannot Change Category
If cell C2 is empty, the Produce list will show in the drop down list.
If an Item was selected in cell C2, then the drop down list will show contents of cell B2.
Highlight Any Inconsistencies
It’s still not foolproof. Users could clear the Category cell, then enter any value. Or they could cut and paste from another cell.
As an extra precaution, you could add conditional formatting in column C, to turn the cells yellow if the selected Item is not in the selected Category.
With cell C2 active, the conditional formatting formula is:
=COUNTIF(INDIRECT(B2),C2)=0
The bright yellow colour will alert users if there’s an inconsistency in the selections, and make it easier for you to spot any problems.
Highlighted Items
For example, in the following screen shot, Cabbage and Rutabaga are highlighted in yellow, because the Category has been changed to Fruit.
Those items don’t appear in the range named Fruit, so the COUNTIF formula returns a zero.
[Update] Video: Block Dependent Drop Down Changes
This short video shows another way to block people from changing the first drop down list, if there’s a selection in the second drop down.
There are written steps, and a sample file on the Advanced Dependent Drop Down Lists page, on my Contextures site.
_________________
Summer Giveaway For Excel Nerds
It’s time for an Excel giveaway! Just describe one of the telltale signs of Excel nerdery (is that a word?), and you could be the proud owner of a shiny new Excel book, e-book or utility.
To enter, write an original (and suitable for work!) comment below, completing the sentence, “You might be an Excel nerd if…”
For example, you might be an Excel nerd if:
- you’ve written 3 books on pivot tables.
- long-lost friends and relatives call you, not for money, but for Excel help.
- you understand Dave’s “accept labels” comment, that inspired this giveaway.
The submission deadline is 12:00 noon (EDT) on Tuesday, July 28, 2009. Winners will be announced here on July 29th.
Update: Congratulations to all the Excel Nerds winners, listed in the July 29th blog post.
The Goodies
Thanks to the generosity of some Excel authors and developers, and my publisher, I have a nice collection of books and utilities to give away.
JKP Application Development Services
From Jan Karel Pieterse, of JKP Application Development Services:
- one copy of Professional Excel Development
- one copy of Professional Excel Development, 2nd Edition
- one copy of Excel 2007 VBA Programming For Dummies
- one USB key (1 GB) with a set of tools like Name Manager and a free RefTreeAnalyser license
(4 copies of this prize are available)
The Ken Puls Blog
From Ken Puls, of The Ken Puls Blog:
- one copy of RibbonX: Customizing the Office 2007 Ribbon
Peltier Technical Services
From Jon Peltier, of Peltier Technical Services:
- one copy of a PTS Charting Utility – winner can select one from the following:
Apress Publishers
From my publisher, Apress, four prizes – e-book versions of Excel books:
- Pro Excel 2007 VBA, by Jim DeMarco
- Excel 2007: Beyond the Manual, by Helen Dixon
- Pro Excel Financial Modeling: Building Models for Technology Startups, by Tom Y. Sawyer
- Pro Office 2007 Development with VSTO, by Ty Anderson
(e-books)
Contextures
And finally, from Contextures – three prizes
- Beginning Pivot Tables in Excel 2007
- Excel 2007 PivotTables Recipes
- Excel Pivot Tables Recipe Book
The Rules
- To enter, just write an original (and suitable for work!) comment below, completing the sentence, “You might be an Excel nerd if…”
- The comment must be submitted before the deadline of 12:00 noon (EDT) on Tuesday, July 28, 2009
- One entry per person – any additional entries will be deleted from the draw
- A random draw will select each prize and its winner. No substitution of prizes.
- Winners will be notified by email, so please provide a valid email address. This will not be publicly visible, but may be shared with the contest sponsors, so they can contact prize winners to arrange delivery.
- Physical prizes will be shipped, postage paid, but taxes or other charges (if any) will be the responsibility of the recipient.
______________
Slowly Installing Office 2010 Technical Preview
My head hurts today, and unfortunately it’s not from a weekend of wild carousing. No, it’s from a weekend of installing software. I’m sure you’ve experienced similar pain, and you might be a bit sympathetic.
Office 2010 Technical Preview
As you might know, the technical preview for Excel 2010 started last week, so I downloaded it, and was eager to take a peak at it.
Even though it was going onto my old laptop, I wanted to create a virtual machine first, and install Excel 2010 in that, away from all the other software.
Install a Virtual Machine (VM)
I’d never created a VM before, but how hard could it be? The first thing I did was find a site that gave step by step instructions on installing and setting up a VM.
That went well, and the next task was to install an operating system in the VM. Of course, all I could find were some Vista disks, and I wanted to install XP Pro.
My MSDN subscription came to the rescue, where I downloaded XP Pro, and installed it on the VM, which took a loooong time!
Install Microsoft Office 2010
The XP Pro installation went smoothly though, and eventually I was ready to load Office 2010.
That installation got off to a bad start, when it whined about a missing file, and wouldn’t begin the Office 2010 installation.
After a frustrating search, I finally found the missing file on the Microsoft site, and installed it.
Work Day Is Over
Finally, with all the pieces in place, the 2010 installation was relatively quick, and trouble free.
By then, the day was almost over, and my enthusiasm had waned considerably, so there wasn’t much time to explore the new features.
That exploration will have to wait until next weekend, I guess.
Other Installations
During the slow periods, while things were being installed, I decided to load Expression Studio onto my desktop computer.
That would give me a chance to work with it, and see if I like Expression Web better than my old version of Dreamweaver.
It took quite a while to install too, with four programs, and multiple product keys.
Browser Update Too
To top things off, I also upgraded to Firefox 3.51, then had to find new versions of some add-ons that I like to use.
Anyway, that’s all behind me now, and everything seems to be working well.
Excel Functions Word Search
Until my head recovers, I’d appreciate it if you use your inside voice, and work quietly at your desk.
For your amusement, here’s an Excel Functions word search puzzle. If anyone asks, just tell them you’re working on some complex Excel functions.
To create it, I uploaded a list of Excel 2007 worksheet functions to the Armored Penguin site, and the puzzle was generated in a few seconds.
You can download the Excel file with the word search, and answer key sheet.
Thanks! We’ll resume our regular stomping about on Wednesday.
_____________________
Copy Excel Code to a Different Workbook
Last week one of my clients asked for a bit of help, so I sent a workbook with some sample code to tackle the problem. It was just what he needed, so my client wanted to copy the code from the sample file, into his own workbook.
Unfortunately, he wasn’t sure how to do that, and asked for a bit more help.
A quick way to copy code is to drag a module from one workbook to another, in the Visual Basic Editor. In this example, the code is in VBACodeCopy.xls and will be copied to MyForm.xlsm.
- Open both workbooks
- On your keyboard, hold the Alt key, and press the F11 key, to open the Visual Basic Editor
- In the Project Explorer window, find both workbooks.
- In the workbook with the code, click the + sign to view the list of Modules
- Click on the module that you want to copy, and drag it over the project where you’d like the copy placed.
- Release the mouse button, and a copy of the module will appear in the workbook. It will automatically create a Modules folder, if necessary.
Copy and Paste Excel Code
If you only want some of the code on a module sheet, or to copy code from a worksheet or workbook module, you can copy and paste the Excel code.
Watch the Video
To see the steps for copying an entire module, you can watch this short video.
________________
14 Basic Skills For Excel Users
If you use Excel at work, or list it on your résumé, what tasks should you be able to do? Are there basic skills for Excel users that office workers should be able to perform with little or no help?