Pivot Tables – A First Look

This week, we will begin a brief series of posts regarding Pivot Tables. As I mentioned in my blog post seven years ago, much has been written on Pivot Tables, and much has also been misunderstood about this highly practical, but not perfect tool.

Good Things about Pivot Tables
Every analyst or manager should have at least moderate skills at using pivot tables. You can use pivot tables to summarize, analyze, and explore what-ifs in your data. What is particularly beneficial about Pivot Tables is they are very powerful, lightning fast, and easy to use. As if that wasn’t enough, if you change your mind on your layout, it takes only seconds to modify the view of your data/information.

A Few Quirks…
Although some analysts live and breathe Pivot tables, they are not all things for all situations. Though powerful, they have some odd quirks, such as resizing your columns when you change an entry and, (although this is easy do so…), often need to be rebuilt if your data significantly changes.

A Word regarding Aesthetics
Let’s face it, pivot tables are not going to win any beauty contests! Whereas, you can apply one of the stock formatting schemes that haven’t changed in many years, they are still rather, well, homely. This, of course, may not be or key importance to you if you are just doing some “quick and dirty” analysis, but it may not be something you want to show the board of directors.

Some Final Thoughts
Though not an ideal tool for every circumstance, pivot tables can often save you many hours of analysis time, and they truly are easy to use. If you have never experimented with Pivot Tables, give them a try. I can guarantee that you will amaze yourself with how simple it is to manipulate your data.

Automating Macros

Continuing with the subject of using macros in your Excel workbooks, we are going to look at Executing Macros Automatically this week.

There are times when you may wish to have a recorded macro perform automatically whenever your workbook is opened (or closed). Let’s say, for instance, that you have a Customer Service Report workbook that you routinely access to update how the department is progressing. If you know that you are going to want a particular macro to run each time you open the workbook, you can automate the process with a very simple piece of VBA code.

All that is needed to have your workbook update whenever it is opened is to name your macro, Auto_Open (or Auto_Close if you want it to run when the workbook is closed…). It really is as simple as that!

But, what if occasionally you do not want to have the macro execute? Merely hold down when opening or closing your workbook to suppress the operation.

Automating Macros: Easily accomplished with a simple renaming of your macro.

Recording Macros

Ah, Macros… The wonderfully mysterious tools of glorious geekdom! As is the case with many otherwise meaningful and useful words in business, “Macros” has for some become a Buzzword (e.g. “Well, can’t you just build a macro to do that?” or “I hear macros can be very useful in spreadsheets. We probably should use those...”)

Everyday users and clueless managers occasionally seem to think that using a Macro is the Silver Bullet for solving their Excel issues. In fact, macros can be extremely beneficial, but they are more typically created and used by employing the special skillset of VBA programmers. On many levels this is true.

When it comes to using Recorded Macros, however, the entry level is much more accessible. A Recorded Macro can, in fact, be an ideal way of lessening your repetitious work and simplifying your routine chores.

Rather than recreating the wheel each time you do a report, a recorded macro can store your mouse clicks and keystrokes while you work, and then let you to play them back in future revisions of your workbook. You can save your recordings, and when you run the macro, it will play the commands in the same order that you recorded them. It can be an amazing timesaver (and do away with repetitious drudgery…).

For example, let’s say you track the performance of the Customer Service Reps in your company. This may be a repetitive weekly task that can be easily automated. Here's how to Record a Macro for this type of situation:

1.   Access your report workbook and click the start of the cells you are going to update.
2.   Point to the Developer tab, and then click Record Macro.
3.  In the Record Macro dialog box, enter a Name that applies to your operation. For ease of operation later on, assign a custom Shortcut Key (this will be your Magic Button to replay the macro…).
4.   Now perform the calculations, formatting, moving, etc that applies to the repetitive and monotonous update.
5.   Finish recording the macro by clicking the Stop Recording button.

To Run your Macro, simply press your Custom Shortcut Keys or click on the Replay Button that you may have created.

Presto! Instant update! Your newly created Macro has just done all the work that may have taken you a considerable amount of time to complete. Try this out – It may even inspire you to learn more about the VBA programming that you can use to conquer the business world…

Conditional Formatting Rule Manager

Conditional Formatting is, as many of us know, an excellent way to visualize trends, spot abnormal values, provide quick analysis, and much more. Using the Rules Manager tool, you can access a first-class way to create and control Conditional Formatting.

The ability to "Create and Control" from one resource is the key advantage in applying this time-saver.

To apply new formatting using the Rules Manager, do the following:

1.   Select the range or individual cells you wish to format

2.   On your ribbon, go to Home – Conditional Formatting – Manage Rules

3.   Once in Manage Rules, click on New Rule, and choose:

a.   Format all cells based on their values

b.   Format only cells that contain

c.    Format only top or bottom ranked values

d.   Format only values above or below average

e.   Format only unique or duplicate values

f.    Use a formula to determine which cells to format

4.   Click OK twice

The Rules Manager is a convenient One-Stop Resource for your Conditional Formatting needs. As with anything in Excel, if you can occasionally eliminate a few steps, it can add up to significant time-saving overall.

The Rules Manager – Who knew?...