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.

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!