Spreadsheet Day 2010 — Top 5 Excel Tips

Remember, Sunday October 17th is Spreadsheet Day, so you’d better start planning your celebrations. You could start the day with a big bowl of Chex cereal — each bite looks like a little spreadsheet. For dessert at the end of the day, have some pie, or bars, while you dream about charts.

As my contribution to Spreadsheet Day 2010, I’ll list the top 5 Excel tips that I read on Excel blogs over the past year. These were Excel articles that solved problems I’d encountered, or got me thinking about new techniques to try.

You can read these articles on Sunday, while you’re celebrating Spreadsheet Day. Maybe your kids would enjoy them as a bedtime story!

There were many other Excel articles too, but I only had room (and energy) to list five. You can help out by listing your favourite in the comments.

Copy PivotTable Style Formatting

When PivotTable Styles were introduced in Excel 2007, it seemed that we couldn’t copy the Style’s formatting, when pasting a pivot table as values.

Fortunately, John Walkenbach found a workaround for the problem, and shared the solution with the Excel world. We’re eternally grateful.

You can read the steps in his article: Unlinking a Pivot Table From Its Source Data. (Update: No longer available)

Vertical Bullet Graphs

Jon Peltier always has some new and awe-inspiring chart trick to show us. In How to Make Vertical Bullet Graphs in Excel, he details the steps for creating these charts that take very little room, but are packed with information.

Jon plans to post instructions for a Horizontal version too, so subscribe to his RSS feed, if you haven’t already.

Create Your Own Add-Ins

Dick Kusleika tells us how he uses Excel to solve problems on the job. His blog posts inspire lots of discussion, and his post on moving from the Personal.xls workbook to personal add-ins was one of my favourites.

Dick’s sample code was great, and there are many suggestions in the comments too, so be sure to read those.

Format Pivot Table Numbers Like Source Data

Along with delicious bacon recipes, Mike Alexander posts tasty Excel and Access tips on his Bacon Bits Blog. I liked his code for copying the number formats from the source data, and applying it to the pivot table data.

You can read the details here: Auto Format PivotTables to Match Source Data. (Update: No longer available)

Moving Forward with PowerPivot

If you’ve upgraded to Excel 2010, you’ve probably experimented with Microsoft’s free PowerPivot add-in. None of my clients have upgraded yet, so I enjoyed reading Ken Puls’ article on his experiments with PowerPivot.

He describes the good and the bad, and his enthusiasm is contagious. Read Ken’s story here: Quantum shifts with PowerPivot.

Your Top Excel Tips

What were your favourite Excel blog articles from the past 12 months? What did you like about them?

Did they inspire you to try new things, or help you save time?

Thanks for sharing!
_________