Monday, September 18, 2017

Pivot Tables – A Final Look (for now…)

As is apparent in our exploration of Pivot Tables in the last three weeks, they are powerful, versatile, and a quick way to do analysis on the fly. This week, we will wrap up this series with a quick look at some additional ways to garner more utility out of this vital tool.

Changing the Summary Function
Go to any cell within the Values section of your Pivot Table and Right-Click. Then select Summarize Values By and click on the calculation of your choice (Sum, Count, Average, etc).

Changing How Values are Shown
In a similar way as you change the Summary function, Right-Click any value cell. Then select Show Values As and choose the calculation you wish to use (you may be surprised at the extensive list…).

Give Your Pivot Table Some Color
Providing some Design and Color can go a long way to making your Pivot Table easier and more pleasant to use. It can also add a considerable touch of professionalism that is so often lacking. Simply click on any cell within your table and open the Design tab/ribbon. Note that you can choose from a variety of Styles, as well as Banded Rows, Banded Columns, and a surprising assortment of Layout options.

Pivot Tables. Powerful, Versatile, Quick. Give them a try…

Monday, September 11, 2017

Pivot Tables – A Third Look

As most seasoned Excel users know, there are multiple ways to Filter pivot tables. With the advent of Excel 2010, however, you have a new, powerful option to use to filter (or should we say, Slice) your data.

Slicers give you the choice of using labeled buttons you can click to filter your Pivot Table data on the fly. In addition to quick filtering, slicers also indicate the current filters being used, which, of course, makes it easier to understand what is being shown in a filtered Pivot Table report.

How-To Slice
1.   Select your table and then choose PivotTable Tools, Analyze, Insert Slicer
2.   Check the fields you want to filter, then click OK
3.   To Filter: Click items in any order
4.   To Clear a Filter, click the filter graphic

Multiple Pivot Tables
To filter multiple tables with a single slicer, select the slicer and then choose Slicer Tools, Options, Report Connections. To delete a slicer, simply select it and press Delete.

Slicers. Another great cooking tool for your Excel kitchen set!

Tuesday, September 5, 2017

Pivot Tables – A Second Look…

Last week we looked at a general overview of Pivot Tables. This week we will continue our examination of this powerful tool, by exploring a few rudimentary How-Tos:

Show or Hide the Pivot Table Fields Pane
To hide or show the Fields Pane, select any pivot table cell, and choose PivotTable Tools, Analyze, Show: Field List. Simple as that…

Refreshing a Pivot Table
Select any cell within the table and choose: PivotTable Tools, Analyze, Data: Refresh. Bamm, refreshed pivot table!

Add a Pivot Chart
Click any cell within an existing Pivot Table, and choose PivotTable Tools, Analyze, Tools: PivotChart. Then click OK after choosing the Chart Type and Subtype. Finally, it is a good practice to move the chart to another worksheet by clicking in the chart and choosing PivotTable Tools, Design, Move Chart: New Sheet [OK]. Piece of cake!

Knowing some simple techniques for manipulating Pivot Tables and Charts can take this vital tool from being a bit daunting to being a stress-free tool! 

Tuesday, August 29, 2017

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.

Tuesday, August 22, 2017

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.

Wednesday, August 16, 2017

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…

Monday, August 7, 2017

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?...