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

Freezing Columns or Rows

When you have a large table or database, and you wish to keep the headings visible, it is an easy matter to Freeze the Panes so you never lose sight of your titles or captions.

This practice is particularly helpful not only to a user who is doing data entry, but also anyone who is reviewing a worksheet. To keep the headings on the screen, simply:

1.   Click the Row Number which corresponds to the row immediately beneath the column headings

2.   Select View – Freeze Panes - Freeze Panes

Conversely, if you have information that you wish to keep visible in your left-most column, simply:

1.   Click the Column Letter which corresponds to the column immediately to the right of the column you with to keep in view

2.   Select View – Freeze Panes - Freeze Panes

That is all there is to it! To restore a frozen worksheet, select any cell on your worksheet and go to: View – Freeze Panes – Unfreeze Panes

You can now scroll your data whenever you wish and still see the headings. Now, that’s Cold…

Quick Analysis

Viable Shortcuts are the domain of true experts. As I have always said, “There is no glory in effort”, and genuine Excel specialists make it all seem so easy.

One terrific new shortcut in included in Excel 2016. The Quick Analysis instrument is remarkably easy to access and use on any applicable table of database. It gives you immediate access to a variety of tools, including:

·       Data Bars

·       Color Scales

·       Icon Sets

·       Greater Than

·       Top 10%

Here is How It Works:

Select the range to analyze and type CTRL-Q. This brings up the Quality Analysis gallery (see illustration below) which gives you access to all the above-mentioned tools. You can even hover over each option to preview the effects.

In the example below, the Data Bars tool was used to quickly add graphical information of the Billing ranges.

The Quick Analysis (CTRL+Q) will be your new friend. Give it a try, and see if that isn’t true. It is a shortcut no expert should be without!

REPT Function + Wingdings

As I have mentioned in the past, the REPT function is an often overlooked and underused tool in Excel. Here is a Fun (and hopefully inspiring…) way to use REPT with Wingdings to create a Customer Service Rating Table/Chart.

Let’s assume you have a call center, and you wish to graphically display the current ratings/results by agent. With reference to the example below, here is what to do:

1.   Create a simple table showing the call center agents in your department, along with their current ratings (1-5 in this case…).

2.   In the “Rating” column, insert the formula, =REPT("(",C4), in the first cell, D4.

3.   Copy the formula down to the last adjacent entry, D12

4.   Format the Rating column to Wingdings

That’s it! With very little effort, you can create a visually interesting rating system for a call center using the highly appropriate icon of a telephone. What Fun!

Forecasting

Forecasting is a vital function in nearly every business. Are your company regional sales going to rise, fall, or remain about the same? Are your office expenses going to track with corporate budgets? Are your losses going to be manageable in the next fiscal year? All such considerations are essential to commercial survival.

Forecasting can, of course, be a bit tricky, (just ask meteorologists…), since you are dealing with Historical Data which doesn’t necessarily correlate with the future. Over a significant amount of time, however, much data is essentially Linear and can, therefore, be used to predict reasonably viable future outcomes. 

Since there is validity in much historical data, Microsoft has created the ingeniously-named, “FORECAST” function as a built-in tool that can calculate linear forecasts. The syntax for this function is as follows:

=FORECAST(ValueToForecast, RangeY, RangeX)

·              ValueToForecast is the point in the future which you need to forecast.

·              RangeY is the list of values which contain the Historical Data to be used as the basis of the forecast, (Sales Figures are classic…).

·              RangeX are the intervals used when recording the Historical Data. Months, for instance (Just Be Sure to express the Months as a Number!)

Stated in Easier-to-Understand English:

=FORECAST(ValueToForecast, RangeY, RangeX)

For Example, let’s say that you wish to Forecast the Sales for the 4th quarter

using the Example below. Here is what you should do:
1. In B11 put:
=FORECAST(A11,$B$2:B10,$A$2:A10)
2. Copy/Drag the above formula to B12:B13 

A Couple of Notes: 
1. Many Excel users find this syntax somewhat Counter-intuitive, so you may wish to spend an extra minute examining the example. 
2. Remember that the underlying assumption for your Forecast is Linear Data. If your data does not fit this model, you will want to explore other potential solutions. 

FORECAST: Limited in its effectiveness, but if used correctly it can be a useful tool in a great many business applications.