All articles with 'Personal Macro Workbook' Tag

As part of a my ongoing consulting gig, I often run painfully long queries on SQL Server to fetch data. This data obviously ends up in Excel for further analysis. Now, some of these queries return NULL values in several columns (did I tell you that the queries have a gazillion left joins on them, oh yeah, they do). Although technically NULL is nothing, when you import this data to Excel, we get the text value NULL in the cells. And I don’t need these NULL values messing up all the calculations and pivots.

Of course, we can go ahead and use the isnull() SQL function to deal with them at the query level. But since the queries have 100s of columns and used by various teams for different purposes, changing them causes a lot of pain. So I did what any sensible Excel user would do. Just kill those NULLs mercilessly once they are in Excel.

Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.

1. Right click on any value field
2. Go to Value field settings
3. Click on “Number Format” button
4. Choose Currency format
5. Close the boxes, one after another

Unless you get paid per click, you wont be happy with all those clicks.

Wouldn’t it be cool to just click once and apply most common format to your pivot fields?

Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 24th session of Chandoo.org podcast, let’s customize Excel so we become productive.

What is in this session?

Each of us use Excel in our own way. And yet, we all end up using the same Excel. That’s not fair. Shouldn’t the Excel of an accountant be different from Excel of a teacher?

In this podcast, lets understand some of the powerful & useful ways to customize Excel so that we can do our work better. Tune in only if you are serious about productivity.

You can get Excel Customization Handbook free. Listen to the podcast for instructions.

In this podcast, you will learn,

• Announcements
• Why customize Excel?
• Customization options:
  • Excel Options
  • Quick Access Toolbar
  • Excel Ribbon
  • File menu / back stage view
  • Themes, styles & templates
  • Personal Macros
• Closing thoughts & Bonus give away instructions

Yesterday, I talked about how you don’t have to know how to code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste. As discussed then, I ‘volunteered’ KV under pain of exposure to empty the contents of his secret satchel onto the virtual table, so that […]

You know, I‘m constantly amazed at the depth of knowledge and wisdom that comes up in the comments of this site. For instance, the other day, undercover Excel secret agent KV came up a great list of interview questions in response to Chandoo’s great article What are best Excel interview questions?

I particularly liked this one:
Do you have a Personal Macro Workbook setup ? If yes, what are the most common macros you use in your daily work?

Secret Agent KV obviously has some purpose-built gadgets that he employs to kill inefficiencies. And he obviously also knows the two top-secret reasons to keep code in a Personal Macro Workbook:

1. To keep it safe from international spy rings.
2. So you can whip it out whenever you are cornered by any evil-looking spreadsheet.

While that first point is important, that second is the clincher: code stored in a personal workbook can be unleashed with a simple judo-chop to the throat.

Today, lets understand how to setup your personal macro workbook and open a secret passage-way to Excel awesomeness.