On This Site...

Home

I'll use this page to provide links to my Excel blog, articles I've written, as well as tools, routines, and procedures I've developed. Feel free to use the tools and code in your own work, but please remember where you found it!

Webcasts

· Analyzing Data Dynamically Using Excel 2007 PivotTables

· Creating Effective Conditional Formats Using Excel 2007

· Tips and Tricks to Manage Data Effectively by Using Excel Tables

· Create Attractive Charts and Graphics Using Excel 2007

Webcast Follow-Up

· Thanks for attending my September 27, 2007 webcast on analyzing data using PivotTables in Excel 2007. As promised, here is an Excel 2007 sample file you can use to re-create the exercises demonstrated during the ‘cast. If you use Excel 97-2003, you can use this file.

· Thanks for attending my February 22, 2007 webcast on using tables in Excel 2007. During the presentation, several astute viewers noted that the table style I created and applied didn’t change the header row’s font color. I checked my workbook and a blank workbook, and determined that the offhanded font color change I made earlier did override the table style’s formatting. I apologize for the error, but I’m glad I discovered the behavior. I’ll post a URL for the webcast recording as soon as it’s available.

Articles

· My article on a neat way to create a cash flow worksheet in Excel that doesn’t break when you delete a row.

· My Experts' Blog contributions on O'Reilly Media's Annoyances Central web site. Those contributions are original; you'll find reprints of some of my fixes from Excel Annoyances in the Daily Fix blog.

· My article Top Ten Excel Annoyances on WindowsDevCenter.com. This article has been reprinted in Coastal Computer News Magazine Online and the Rochester Computer Society Inc. August 2005 newsletter.

· A reader from the Netherlands wrote to ask about a possible error in the syntax of an INDEX formula. It turned out to be a difference in how one renders decimal values in English and other languages.

· Here's an article that shows you how to use conditional formats to simulate stoplights for business intelligence dashboards in Excel.

· If you use Excel 2003, you should definitely take advantage of data lists. Here's a quick tip on how to add a Total Row to your list and change its summary operation.

· The first in a series: creating business intelligence dashboards using Xcelsius, part 1.

· Scenarios help business presentations run smoothly, but if you close a workbook with a scenario applied, you'll lose your original worksheet data. Read this article to learn how to create scenarios and back up your data within your workbook.

Workbooks and Code

· A workbook for tracking and commenting on microbrew beers from California, Oregon, Washington, Texas, and Alaska.

· A routine that cuts off a cell's contents after a user-specified substring. This routine can be used to find the URL a visitor views without keeping any login or other information included in the URL (e.g., the URL http://www.techsoc.com/index.htm?userid=1033 could be truncated after the .htm substring, leaving just http://www.techsoc.com/index.htm)

· And here's a similar routine. Instead of truncating a string, it keeps the characters after the substring you specify (e.g., you would keep everything to the right of ? in the URL http://www.techsoc.com/index.htm?userid=1033, leaving just userid=1033)

· Here are the same two routines as functions, which you can call from a formula in your worksheet. The functions, CutAfter and CutBefore, expect two arguments: the address of the cell that contains the full string, and the address of the cell that contains the string you want to search for. To use the functions in a workbook formula, press Alt-F11 to display the Visual Basic Editor, choose File | Import File to display the Import File dialog box, click the file you want to import, and then click Open.

· If you play 7-card stud online, you can use this Excel workbook to track all cards played during a hand.