Author Archives: Brian Lynn

Excel Tips for an Easier Life, Part 5: VBA Macros

To date, our community has answered nearly 2 million questions in the Smarterer Excel test, making it the most popular test on the site. Excel is an essential and powerful tool that nearly every professional finds themselves needing at some point. In our previous posts, we helped you out with Excel shortcuts, formulas, and keystroke macros. This week, we will introduce you to another type of macro – one that has a steeper learning curve for beginners, but can save you precious hours once you become proficient at it.

Automate Your Way

Are you tired of tasks that are repetitive and time-consuming? While keystroke macros can automate many tasks, they don’t always work as expected. Macros are essentially a set of instructions written in a programming language called Visual Basic (aka VBA), for Excel to interpret and perform specific actions. As a result, not all sequences of actions are interpreted by the program correctly.

By knowing how to program in VBA, you will have full control of the tool and be able to ensure that your macros work 100% correctly.

Simple Case Study

Suppose you’re a marketer using Excel to clean up a large email list. Every week, your boss needs you to break the list into segments in different worksheets. Instead of looking for the break points for each segment, cutting & pasting into the separate worksheets, you can achieve this with a few lines of simple VBA code! It’s well worth the effort if it is a recurring task.

Enabling VBA Macros

1. To use Macros, you must save your workbook into a new file. Choose “Save as”, and select “Excel Macro-Enabled Workbook (*.xlsm) for the file type.

2. Click the Microsoft Office Button, and then click “Access Options.”

3. Click “Trust Center”, then click “Trust Center Settings” on the right, and then click “Macro Settings.”

4. Select “Enable all macros”.

5. Create two new worksheets, one with name “Sheet1” and the other “Sheet2”.

Programming VBA Macros

1. On your keyboard press “ALT” together with “F11″. The Visual Basic Editor will open. To go back into Excel, press “ALT + F11″ again.

2. Before you can start writing any code, you need to insert a module. Per the screenshot below, click on the Ribbon of the editor and select “Module” from the dropdown menu.

Defining the Subroutine

1. The most common type of program in VBA is called a subroutine. You will start writing a subroutine by typing the word sub at the top of the module you created earlier.

2. The next thing to do is type in a name for your subroutine. We’ll call it “copy_range()” in this case. Remember not to use spaces and try to avoid punctuation characters in the name.

Segmenting the List

Suppose the email list has 10 entries, and you only need to break the list into halves, each on a new and different worksheet. (For the sake of this tutorial, we’ll keep the example extremely simple. Obviously macros are meant for very large data sets).

Inside the subroutine, write the following code:

1. Range(“A1:C5″).Select

Explanation: Range() is a method for you to specify which cell you want Excel to pay attention to. In this case, we want to copy the first half of the list into a new sheet. Hence, we define the range to be from cell A1 to C5. To select the range, the .Select part of this line of code performs the action, equivalent to below:

2. Selection.Copy

Once the range is selected by the first line of code, the .Copy part performs the “copy” action, putting the range on the clipboard to allow you to paste it into other cells/worksheets.

3. Sheets(“Sheet1″).Select

To paste the first half of the mailing list into worksheet called “Sheet1”, we need to select the worksheet first. Again, .Select performs this action for you.

4. ActiveSheet.Paste

Once “Sheet1” is selected, it becomes the active sheet (just like the name indicates). Similar to the .Copy action in step #2, .Paste performs the action of pasting the data into “Sheet1”.

That’s it! The code for the entire subroutine should look exactly like below. If so, you’ve just programmed your first VBA macro!

Now hit ALT+F11 to go back to Excel. Click “Developer” on the ribbon, and select “Macros”. You’ll see the method called “copy_range” in the menu. Select it, hit “Run” and watch the magic.

We hope that these tips will save you hours, so you can come back to Smarterer learn other subjects in the mean time! As usual, Bill Jelen, the renowned “Mr. Excel” and Microsoft MVP, offers videos showing many tricks on programming programming VBA Macros.

Stay tuned for Part 6 in our Excel series, Loops in VBA Macros — and let us know what other Excel skills you’d like to see here!

Excel

Excel Tips for an Easier Life, Part 4: Keystroke Macros

To date, our community has answered nearly 2 million questions in the Smarterer Excel test.  Excel’s just one of those skills that almost everyone finds themselves needing at some point, and this series is here to help you improve and simplify your life with what can be a confusing program if you don’t know the tricks of the trade.  In previous weeks we helped you out with shortcuts and formulas – Macros are another basic technique that will make your life a thousand times easier.  Read on to find out how!

The Power of Automation

Are you tired of tasks that are repetitive and time-consuming? Such tasks are almost a given in entry level roles across many professions today. Thanks to the power of Excel, many of these tasks can be automated easily, and the entire process can be in your control. For part 4 of our Excel series, we will show you the magic of Keystroke Macros, so you can turn “mundane” into “fast lane”!

Case Study

Suppose you’re a financial analyst using Excel to track expenses. Every week, your manager wants the overdue amounts formatted so that they’re very easy to see (e.g. bold, red, gray cell borders). To format one cell, you have to go to “Format Cells”, then click the “Font” tab and select “Color”. For the borders, click the “Borders” tab, select “Outline” and your desired colors on the lower left. That took nearly 10 clicks.

As you can see, if you have thousands of entries in random order, formatting each cell individually will take you several hours to complete the task (along with extreme boredom). Thankfully, you can record all these actions into a Macro, and apply them to any other cells with a click of a button.

Enabling the Magic

1. To use Macros, you must save your workbook into a new file. Choose “Save as”, and select “Excel Macro-Enabled Workbook (*.xlsm)” for the file type.

2. Click the Microsoft Office Button, and then click “Access Options”.

3. Click “Trust Center”, then click “Trust Center Settings” on the right, and then click “Macro Settings”.

 

4. Select “Enable all macros”.

Once Macros are enabled, you should see a “Developer” tab on the top right of the ribbon.

Steps to Heaven

1. In the workbook, click one of the cells you’re going to format.

2. Click the “Developer” tab, and then click “Record Macro”.

3. In the Record Macro dialog box, type a name for the macro in the Macro name box. Macro names must start with a letter and can include letters, numbers, and underscore characters, but can’t include spaces.

4. Specify a keyboard shortcut (aka keystroke) so you can invoke the Macro easily later on. Be sure to avoid using a keystroke that is already used by common Excel operations, such as Ctrl+c etc. We recommend Ctrl+j, Ctrl+k and Ctrl+m, since their default actions are not commonly used. You can cross reference your shortcuts with this list.

5. When you click OK, you’re ready to record. Until you stop the recording, every Excel command and keystroke will be recorded in the macro, in the order in which they are entered.

6. Now format the cell the way your manager instructed (e.g. bold, red, gray cell borders).

7. Once you’re done with all your desired actions, click “Stop Recording”.

Now you can go down the list and simply hit “Ctrl+j” to apply the format!

We hope that these tips save you a few hours and give you time to do something more productive, like learning another new skill! For more, check out Bill Jelen, the renowned “Mr. Excel” and Microsoft MVP, who offers videos showing many tricks on recording Macros.

Follow us @Smarterer to stay tuned for Part 5, Programming Basic VBA Macros — and let us know what other Excel skills you’d like to see here!

Excel

Excel Tips for an Easier Life, Part #3: Text Manipulation

Welcome to the 3rd part of our series on Excel tips! As proposed from our previous posts on Excel Shortcuts and Formulas, we want to help you become proficient in Excel quickly by cutting through the clutter and drill down on the most important techniques.

We saw this need from our community, ever since they have answered more than 1,000,000 questions in the Smarterer Excel test.

Introducing Text Manipulation:

In an entry level job, there are many cases where you need to analyze a large set of unstructured data you copied from a website. Often, it requires much work to decipher when you paste them into Excel. This is where text manipulation formulas can help tremendously.

The Situation:

Suppose you are creating a spreadsheet for your school library to keep track of which student borrowed a book. You copied a list of students’ names and their student ids from the school’s website. The data looks like this after you paste into excel:

Let’s say your goal is to have the first column containing the student ids, and the second column containing the first name only.

If there are thousands of students, it would be too time consuming for you to clean up each cell individually. Luckily, this will be a piece of cake using Excel:

1. First, you extract the student id. Let’s suppose that all id numbers have 7 digits. In this case, you can simply use:

Left(A1, 7)

This formula will extract the first 7 characters starting from the left. Add this to your second column as shown below.

2. Now that you have the id isolated, you will be extracting the student’s first name using this formula:

Mid(cell, starting position, number of characters)

This formula will extract, within the specified cell, a number of characters counting from the starting position. Since all student ids have 7 digits, you know that every student’s first name starts at position 8.

3. But wait…if each student’s name has a different number of characters, how would you know the amount of characters to extract? By looking at the pattern, you can see that the first space in the cell always separates the first and last name of the student. Therefore, you can find the position of the first space using:

Find(“ ”, A1, 1)

This formula will look for the first space character, within cell A1, starting from the beginning position, which is 1. If a match is found, the formula returns the position number. Otherwise, it returns “false”. Here, the result is 13.

 

4. If we subtract the 13 by 8 (position of the first name), we get 5, the number of characters in between the two position! In other words, to get the “number of characters” for the formula in step #2, we do Find(“ ”, A1, 1) – 8.

5. To complete the formula from step #2, we plug in our components:

Mid(A1, 8, Find(“ ”, A1, 1) – 8)

Notice that it is possible to put a formula inside another formula. This is one of the areas where Excel becomes truly powerful, where you can have almost endless combinations to do just what you need.

6. Once you have the formulas working on the first row, select them (shift + left arrow). Then, simply drag the black button on the bottom right downward to extract data from each row!

7. To keep your spreadsheet clean, you may like to delete the first column (if you don’t want to keep the raw data).

Since the formulas are still referencing the raw data, however, deleting the data will cause problems. So you would have to disconnect the formulas from the data, while keeping the results you generated.

To do this, simply select the formulas, click “Copy”, go to the “Home” ribbon, select “Paste”, and in the drop down menu select “Paste Special”.

Alternatively, you can use the keyboard shortcut we wrote about before: Ctrl+c, Alt -> e -> s -> v, Enter

Once this is complete, the formulas transform into pure values. Feel free to delete the first column.

 

Using text manipulation in this fashion, you’ve just got a taste of Excel’s power in automating a task. But it doesn’t stop here. Excel offers an incredibly powerful feature called Macros, and we’d love to cover this topic over the next few weeks.

As usual, Bill Jelen, the renowned “Mr. Excel” and Microsoft MVP, offers videos showing many tricks on text manipulation.

Follow us on Twitter @smarterer to stay tuned for our Part #4, Save a Life-time with Keystroke Macros.

Tips for an Easier Life in Excel, Part #2: Formulas

As a continuation from last week’s post, Essential Tips to Make your Life Easier in Excel, we are excited to share tips for basic Excel formulas!

To reiterate our proposal, we will help you with a series of great tips on Excel in the next several weeks. Since our community has answered more than 1,000,000 questions in the Smarterer Excel test, we want to help you by cutting through the clutter and drill down on the most important techniques in Excel.

For part 3 of this series, here are of the most frequently used formulas that anyone should learn:

Basic Excel Formulas:

Excel is a prerequisite for most professions today: accounting, finance, sales etc. For almost any job, you will have a bunch of data points to analyze. Here are basic formulas you won’t escape from:

  • Sum(range): Adds the numerical value of all cells within a range. Text is ignored.
  • Average(range): Obtains the average of all cells within a range.
  • Median(range): Obtains the middle value all cells within a range.
  • Round(range): Round the value to the specified decimal place.
  • Min(range): Obtains the lowest value of all cells within a range.
  • Max(range): Obtains the highest value of all cells within a range.
  • Count(range, value): Counts the number of cells that contain numbers that are not in a contiguous row or column.
  • Countif(range, criteria): Counts the occurrence of a value within a range.

Fancy & Powerful Excel Formulas:

When you have a large dataset with thousands of categories and data points, how do you go about your project? Thanks to Excel, many analyses today much easier to execute comparing to the old days, saving thousands of hours of mundane, manual labor. Here, we recommend 3 formulas that you must master to get to the next level:

Sumif(range, criteria, sum-range):
Suppose you are a real estate agent and wants to know your commission based on different value of properties that you sell:

For the total sum of the commissions for property values over 160,000, use this formula =SUMIF(A2:A5,”>160000″,B2:B5). Explanation: The A2:A5 defines the range. “>160000” is the criteria that Excel should search for in column A, and B2:B5 is the range/column where Excel looks for the values to sum up.

 

Vlookup(value to lookup, range, number of column, exact match):
Suppose you are a scientist and you have the following data:


In this example, your goal is to find the Temperature when the Density is 1.09.

Explanation: Using VLOOKUP(1.09,A2:C10,3,FALSE), Excel first searchs the Density column (within range A2:C10) and finds the row that matches 1.09. It then finds the corresponding value in the Temperature within the same row, by counting the number of columns from left to right (e.g. Viscosity column would be 2, and Temperature column would be 3). The “FALSE” property at the end specifies that you would like an exact match for 1.09 in column A.

If(criteria, do this, else do this):
For many beginners, if statements look intimidating at first. But once you get used to the syntax, it is actually very straightforward. For example, you’re a financial analyst with a budget of $100. For this year’s budget, you have $50 in cell A1. To generate a simple report for management, you want cell B1 to display the words “Within budget”:

To do this, you simply input into cell B1 this formula: IF(A1<100,”Within budget”,”Over budget”). Explanation: the formula first looks at cell A1 and see if the value is less than 100. If it is, then it will display the words “Within budget” (note that in order to display text, you must wrap the value in quotation marks). Otherwise, if cell A1 is greater than $100, cell B1 should display “Over budget”.

As you might expect, you can combine various formulas to perform very sophisticated analysis in Excel. But before jumping the gun, it’s important to achieve mastery of individual formulas first. In Part #3 next week, we will show you how to combine these techniques to propel your productivity.

If you want to learn more, Bill Jelen—the renowned “Mr. Excel” and Microsoft MVP—offers a video showing many tricks on basic formulas.

Follow us on Twitter @smarterer to stay tuned for Part #3: Complex Techniques with Formulas.

In the meantime, why not test your current knowledge of Excel with our test?

Excel