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!

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…

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:

=INDEX($A$2:$A$6,4)

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!

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

Spinners are Tops!

Okay, okay, I know that the title of this week’s blog is corny, but Hey, Spinners are nonetheless Very Cool!

Spinners are Form Controls which you can place into an Excel Worksheet to give you Interactive Functionality with your data. By adding a spinner button control to a worksheet and linking it to a cell, you can return a numeric value from the position of the control, and go on to use that value in conjunction with other functions (e.g. Offset, Index, etc) to return values from lists.

What is so Cool about Spinner buttons is that they provide an intuitive approach to controlling the data you enter into your spreadsheets, and make you look Brilliant while doing it (that is, after all, the most important thing…). 

To find where they are located, (in Excel versions 2010 or later), do the following: 

1. Click on File in the upper-left and select Options 
2. Choose Customize Ribbon and then put a checkmark next to Developer

3. Click Okay 

After you have completed the above, the Developer ribbon will be available on your toolbar. Along with a great many exciting resources (you can spend days exploring them), the Developer ribbon supplies all the Form Controls. Hover your mouse over the selections until you see Spinner button, give it a click, and draw the button on your worksheet. 

Now is where the Fun begins! Right-click on the spinner, and then click Format Control. In the initial test of this cool control, enter the following in the boxes on the Control tab dialogue box:

1. Current value: 1 
2. Minimum value: 1 
3. Maximum value: 30 
4. Incremental value 1 
5. Cell link: $B$2 (Note: You can choose any value…) 

Now when you click the Spinner control, cell B2 is be updated according to the numbers you choose. You can then use that number to do Wondrous Things (or at least pretty cool stuff…) in Excel!

Use Spinner Buttons in your Excel worksheets to make it look like someone spent hours of programming time designing them. In just 5 minutes you can make amazing additions to your worksheets that are just too good to miss. If you have never tried the Form Controls, give them a Spin! It may open a new world for you…

The New IFS Function

Once in a while, Microsoft comes up with a new feature in Excel that thoroughly captures my interest. As most Office 365 users know, there are new features and functions being regularly added to this latest version of Excel. Not all of them strike the fancy (as the British may say…) of a large group, but there are exceptions.

One such marvelous feature (IMHO) is the new IFS function. If you are like many Excel users, you have had experience with Nested IF Functions.  These handy functions offer a wonderfully powerful way to extract information from your data subject to multiple conditions. 

Regrettably, however, Nested IF Functions tend to be rather, well, Clunky. Thankfully, the new IFS function simplifies the nested practice, as it allows you to specify a series of conditions in a single Streamlined Function!

Instead of being tasked with stringing multiple IF functions into a mega-function, (which will likely boggle the minds of others who might inherit your work), you can achieve the same results in a much more elegant format.

Let’s use the classic exam grading system of assignments for an illustration. Assume that you have a student’s numerical grade in Cell A1.  Using our cool new function, we can construct a formula in Cell B1 as follows:

=IFS(A1>=90, “A”, A1>=80, “B”, A1>= 70, “C”, A1>=60, “D”, A1>=0, "More studying is recommended…”)

If the student’s score in A1 is 84, then the clear result in B1 will be “B”.  This marvelous enhancement to Excel that can save you a good deal of time, and make your formulas easier understood by others as well as yourself. If you are an Office 365 user, try it sometime. I’m confident that you will find this a very worthy addition.

Concatenation and You

As I have previously mentioned, Concatenation is one of my favorite time savers. Teaching classes as large as 200 students, I have frequent use for sending emails to large groups. 

Not surprisingly, I always make a spreadsheet of my students’ names (this could also work for you company employee, of course). When it comes to sending emails to the group, I have found that Concatenation is the perfect solution!

Assume that you have a list of students with the First Names in Column A and Last Names in Column B. With concatenation, you can easily combine them into an Email-Friendly column of names in a “Last Name, First Name” format. The secret to Concatenation is using Ampersands and cell references combined with quoted text or punctuation. Here’s how to do it:

Assuming your table starts in cell A1, put the following formula in C1 (Note: There must be a space after the comma in quotation marks):

=B1&", "&A1

This elementary formula combines the contents of B1 (last name) with a comma, space, and contents of A1 (first name). Select C1, place your cursor over the “handle” in the lower-right corner of the cell, and give a quick double-click. This will populate your Column C Email List down as far as you have data in Columns A and B.

Then it is a simple matter of copying the contents of Column C, and pasting into Outlook. Bamm! You have just saved you time that you can use doing something useful (like Golf…)!