Tuesday, July 25, 2017

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!

Tuesday, July 18, 2017

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!

Tuesday, July 11, 2017


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

Wednesday, July 5, 2017

Add Leading Zeroes with REPT

REPT is one of those functions in Excel that most users find obscure and of little use. I have to admit that I was one of those users until I started investigating this strange little function with a keener eye to what it can accomplish.

As illustrated in the following, REPT simply returns a specified text string a stated number of times: =REPT(“Text”, Number of times)

So, what is an example of one of the many ways that REPT can help us solve a curious little problem in Excel.

As you probably know, Excel removes any leading zeroes in a number. There are, however, a number of instances where these leading zeroes are needed.

For instance, let’s assume you want to convert some raw numbers to System Codes for your company? Let’s also assume that the System Codes have 8 characters and start with a zero if less than 8 characters. In the illustrated table below, you can start with the following function in B2, and drag it down to B8: =CONCATENATE(REPT(0,8-LEN(A2)),A2)

Since “8-LEN(A2)” calculates the number of times to repeat zero, then the number of times the leading zeros are repeated is 8 minus the length of the numbers. The CONCATENATE function then simply joins the repeated zeros with the number.

Nice! Just one simple way to use the often-overlooked and slightly mysterious REPT function!

Monday, June 26, 2017

VBA – Why/Why Not?

For those special individuals with proper skills, there is a massive amount of Hidden Power lurking in the depths of Excel. VBA – Visual Basic for Applications is the programming language of Excel with which you can create the sometimes famous, sometimes infamous Macros. There are, after all, good reasons to use VBA, and some good reasons not to use it.

Why to Use VBA
·       By implementing User Forms, you can make your Excel worksheets very easy to manipulate for nearly any user.
·       It allows you to conduct more sophisticated tasks and permanently automate them, potentially saving a ton of your valuable time.
·       With good coding, you can produce creations that can solve business conundrums that are difficult to approach with built-in Excel tools.
·       VBA is truly amazing, and once you build your skills, it will transform you to unquestioned Guru status!

Why to Not Use VBA
·       First, a scary one: There is No Undo button (yes, I thought that might get your interest…).
·       Testing and the inevitability of debugging your code can take a lot of time.
·       If you find it difficult to maintain clear notes in your code, it may cause dreadful problems in the future (rebuilding your work is not cool…).
·       Most importantly, most users do not have VBA skills, and probably do not know anyone who does. If at some point they do not have you for a reference, your beautiful creation may turn out to be useless.

VBA. A fabulous tool or a disaster waiting to happen. You will need to make your own decision…

Tuesday, June 20, 2017

Transcend VLOOKUP!

You want to look up information in a table or database, and you wonder, “Which is better, VLOOKUP or the combination of INDEX and MATCH”?
Without a doubt, VLOOKUP is the most frequently used function in this instance, primarily because it is the more familiar formula for most users, but also because most Excel users simply aren’t aware of the ease and benefits of the INDEX/MATCH combination.

The major drawback of VLOOKUP, of course, is it requires a static reference in the form of the first column. (Who needs that?!?!) INDEX/MATCH on the other hand, is more Flexible, allowing you use whichever column you choose for your reference. Rock on!

For example, look at the illustration of using the INDEX/MATCH combo below:

Let’s say you want to create a Code Identifier in cell F2.  MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:
=MATCH("Tampa", $A$2:$A$8,0)

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula:


Combining the INDEX and MATCH functions is where the Magic comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

=INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0)) 

Using the MATCH and INDEX functions together is truly a Powerful and Versatile way of extracting data. Transcend VLOOKUP! There are better tools in town!

Tuesday, June 13, 2017

Double-Click Magic Tricks

All of us are familiar with the use of Double-Click shortcuts that save you time. Since some can be overlooked or forgotten about over the years, it is occasionally worthwhile to review some of these useful pieces of “Excel Magic”.

1. Perfectly Adjust Column Widths
This is an oldie, but still a goodie. Simply select Multiple Columns and Double-Click on the separators. Works equally well for adjusting row heights.

2. Collapse Ribbon to Get More Space
One of my favorites. Merely Double-Click on ribbon Menu Names and Poof – More Excel real estate!

3. Lock Format Painter
This one surprises a lot of users. Save a ton of time by Double-Clicking on the Format Painter icon, making it Reusable. (Who knew?!)

4. Jump to Last Row / Column in Table
Another old favorite: Just select a cell, and Double-Click on the cell-border in the direction you want to go. Bamm! You’re there!

5.  Double-Click in the Corner to Create a Split (2010 & 2013)
Not for the Excel 365 users, but valuable for the 2010-2013 crowd. You can very quickly insert Splits, (highly useful when you want to see multiple areas at one time), by clicking on either the Little Bar Shape next to Horizontal Scroll-Bar near bottom right corner of the Excel window or directly above the Vertical Scroll-Bar. Once done, you can drag the bars wherever you wish.

Double-Click Tricks can save you time and make you Look Good in the process!  (And Looking Good is always, well, good...)