It’s October 17th, which means it’s Spreadsheet Day! Today is the day we all celebrate Excel and take a moment to think about how Excel has made our lives better.
.
Spreadsheets are used by lots of people in many different ways. The best thing about spreadsheets is that you don’t even have to be smart to use them.
» Read more: Happy Spreadsheet Day
One of my 12 fans (Lester) emailed to remind me that I have a blog. I almost forgot. So with a good kick in the pants, I’m back to blogging.
.
While I was away, Microsoft awarded me with the coveted Microsoft Excel MVP award for the fourth time. So I guess I better start earning that distinction here. Here we go.
.
Today, I’ll show you a nifty trick that allows you to actually see the AutoFilter criteria you have applied on an Excel table.
.
This is actually a pretty common request. When you’ve got an AutoFilter applied, it would often be nice to see how the table is actually AutoFiltered. Alas, Excel’s only accommodation is to give you a generic count of records on the status bar.
.
.
I’ve come up with a piece of code that will give you more details in the status bar. Like this:
.
.
Here is how you can implement this:
I’m back from the Power Analyst Bootcamp in Washington DC – and of course, it was a success. 37 of the finest analysts attended the 2-day event, where we shared our passion for Excel, deli meats, and spicy tricks and tips. In the end, everyone left with a bag full of new techniques that will make them better analysts.
.
While I was gone, there seems to have been a flurry of activity and questions in reference to hacking into a protected workbook. A couple of years ago, I posted a slick technique you can use to hack into a protected 2007 worksheet. Apparently, hacking in a protected workbook is also a highly desired talent. So today, I’ll walk through the steps to hack into a protected workbook.
.
Excel 2007 and 2010 files are essentially zipped packages that contain XML files. This means that if you take an xlsx file and change the extension to zip, you’ll be able to see all the xml documents that make up your Excel file. Not only that – you can change the content and properties of an Excel 2007 file simply by manipulating the XML documents that make it up.
» Read more: Hack into a protected Excel 2007 or 2010 Workbook
I firmly belive that when you create the next great application in your organization, you do yourself a major injustice by sending it out with a crap name like “The Data Inputer” or “Turd Churner 6000″.
.
It’s important to brand the application you’ve worked so hard to develop. In all likelihood, you’ll want your application to become an ongoing fixture in the organizatoin. Given that, it needs an identity that accurately places focus on its purpose and place in the daily operations of your company.
.
Here are a couple of rules I use when naming a new utility, application, or dashboard.
.
1. Stay away from lazy alphabetisms. “The CPT Tool” may be easy to say, but that name just relegates your work to the pool of countless other utilities in the the alphabet soup of tools the company has.
.
2. Stay away from nonsensical acronyms. Sure, it’s cute to force your newly created inventory reporting application into a name like “F.I.S.T. (Field Inventory Srategy Tool)”. But it sounds like the evil spy network in a Marvel comic book. Acronyms in general are not bad when they’re done right. The acronym should end up capturing the spirit of your tool. Something like “EPiCenter (Enterprise Productivity Information Center)” sounds like what it is – the central focal point for an organization.
.
3. Don’t give your application a corny name like “CASPER” , “OSCAR” or “JOHNSON”. As much as it makes sense to you, other people won’t get the inside joke. It just sounds goofy. Besides, do you really want users calling to tell you that your JOHNSON has bugs?
.
4. Use words or terms that have some relevence to the tool you’re creating. Some of the names I’ve used in the past are: PlanningExpress, PricePoint, ResourceIQ, BudgetExpress, ProfitCenter. I bet you could look at any one of these names and get a pretty good idea what the associated application does.
.
5. Don’t be afraid to come up with a completely unique name. Most software applications have catchy and unique names that are fairly easy to remember. For example: Xcelsius, Zilliant, Antivia, GMaps. Nothing says you can’t give your application a Web 2.0 kind of name – something like Centizio!
.
.
So what are some of the names you’ve come up for your applications?
.
Oh… and if you’re struggling to find a catchy name, check out this nifty name generator.
It’s the Thursday before a three day weekend (we have the Labor Day holiday on Monday). This means that most of us are already heading for the barn. So today, I’ll take it easy and share with you a new Canadian band I’ve been listening to.
.
They call themselves Rural Alberta Advantage. I would describe their music as Alternative-Folk-Canadian-Country. In any case, the star of the band is their drummer Paul Banwatt. For my money, this guy makes the band. Every time I listen to one of their songs, I focus in on the drumming.
» Read more: Drummers and Sticky Notes
About a week ago, Bill Jelen sent me an email with a challenge to figure out the nifty trick he shows in this YouTube Video. Inspired by the video, I decided to use the trick in a simple dashboard model.
.
What I ended up with is this cool technique that allows me to replace my old Form Controls with Pivot Slicers. As I select each slicer, my dashboard responds as it would would if I were using a set of Form Controls. Keep in mind that the charts and other components in my dashboard are in no way connected to a pivot table.
» Read more: Use Pivot Slicers as Form Controls
I’ll be busy today, releasing a new dashboard for user testing.
.
Why spend hours of your own time finding bugs, when you can let users do it for you!
I’ll be back tomorrow with a new trick!
» Read more: User Testing
A few days ago, I posted a macro that will automatically set the series colors in a chart to match their source cell’s colors.
.
After reading my post, Chris Umphlett got inspired to create a macro that automatically sets the data points in a chart to the same color as source cells.
.
Here’s how it works.
» Read more: Color Pie Chart Slices to Match their Source Cells
Have you always dreamed of getting into Stanford’s School of Engineering and Computer Sciences, but was too much of a loser to even try?
Well, here’s your chance! Apparently, in an experiment in distributed education, Stanford will be offering their “Introduction to Databases” course FREE to students worldwide.
» Read more: Getting into Stanford University
