Category Archives: How-To

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

Mental Math Tricks to Help Save You Time

Mental MathIf it’s been a while since you graduated elementary school, chances are you haven’t practiced your multiplication tables in a long time. Smarterer’s Basic Math Test is the fifth most popular test on our site, providing proof that people love (or need!) to brush up on their computational capabilities. There’s a reason schools teach these basic math skills so early on–it’s because they’re so essential for simple tasks in every day life and can be applied in a multitude of situations.

A lot of us have let these mental math skills deteriorate because we have calculators on our desks, computers, and smartphones. You never know when you’ll be caught in a pinch without one, and who wants to be the guy that has to pull out a calculator when their boss asks them to perform a simple calculation that they could easily perform in their head?

Mental math doesn’t have to be difficult. In fact, there are a number of useful little tricks and shortcuts that make it easy to perform computations without the aid of a calculator that can help save you time in real world situations. Read on to learn them.

Quickly Find Percentages

Percentages crop up all the time in a variety of situations, but can be tricky to calculate without the aid of a calculator. Use these simple rules of thumb to quickly find percentages:

  • To find out 15% of a number, divide it by 10 and the add half of it.
  • To find out the 20% of a number, divide it by 10 and multiply the result by two.
  • To find out the 5% of a number, divide it by 10 and then divide it by two.

Tipping

There are few things better than going out to a restaurant and catching up with friends over drinks and a meal. That is, until the bill comes. If you always find yourself pulling out your cell phone and use your tipping app, you can save yourself a few steps by doing it in your head. Follow this simple rule of thumb: When tipping the standard 20% on a bill, round to the nearest dollar, multiply that number by 2, and then move the decimal one place to the left. For example:

Mental Math

Discounts

Have you ever been in a store and seen a sign indicating an item is 40% off? Sure, that’s a great deal, but do you really know how much money you’ll actually have to pay after the discount? There’s an easy trick you can use to find out. Simply subtract the discount from 100% and then multiply the starting price by that number to find the discounted price. See below:

Mental Math

Sales Tax

Almost every state in the US imposes a sales tax on most items that consumers buy. While the tax rate varies from state to state, from as low as 2.9% to as high as 11%, you can use the rules of thumb for finding percentages in this case as well. Note that in states where quarters and halves of percents are used, as is the case in Massachusetts, with a 6.25% sales tax, many retailers round up to the nearest whole percent (in this case 7%). This means that for every dollar, Massachusetts will take $0.07 for a tax, so multiply the total by 7 and move the decimal over one place.

Are you a math whiz? Put some weight behind that title by taking the Smarterer Basic Math test!

Basic Math

The Personal Finance Toolkit: 6 Free Tools to Help Balance Your Books

Keeping track of personal finances One of the surest signs of growing up is having to pay bills and balance a check book. To some, this hallmark trait of adulthood embodies the dismal reality of financial responsibility, but to others, it can signify the long awaited and satisfying feeling of financial independence. Regardless of how you look at it, there’s no denying that staying up to date with your finances is not a simple task, and that’s why there is a number of tools out there to help make it easy to stay organized.

Software like Quickbooks and TurboTax can put a dent in your wallet, but there are a number of free programs and websites that serve a similar purpose. We’ve rounded up a comprehensive list of the best personal finance tools out there for a variety of different tasks and situations. Whether you’re a freelancer writing invoices, or you just want to keep a personal budget, having these programs in your toolkit can save you valuable time and prevent frustration down the road.

Templates

Personal Finance Templates

One of the simplest ways to utilize the software you already have is to use templates for spreadsheet software like Excel, Numbers, or Google Spreadsheets. Google Docs has a searchable set of free money management templates ready for you to use right away. From personal budgeting to invoice tracking, and everything in between, there’s a template for you.

Mint helps you manage your personal finances.Personal Budgeting

Mint.com is the one-stop source for personal budget management. The site allows users to graph spending by category, track savings goals, follow investments and identify possible opportunities for savings. The user interface is highly visual and user-friendly, deftly organizing multiple bank accounts, investments and other banking categories.

Money Management

For an alternative to Quicken, turn to AceMoney Lite. The software enables users to manage budgets, track multi-currency finances, analyze spending habits, make transfers between accounts, and do on-line banking, all without any prior accounting knowledge. For another alternative, check out HomeBank, the free personal accounting software built for everyone. It doesn’t come with all the extra bells and whistles, like bill payment or access to bank accounts, but it makes an excellent reporting software.

Income Tax Filing

Free Personal Finance SoftwareFor a super simple and free way to file your federal income tax return electronically, use TurboTax’s free online tax return service. The online service allows you to file 1040EZ and simple federal tax returns, asks questions in plain English and puts your answers on the right forms, searches for every tax credit you deserve. Come tax season, this service will become your best friend.

Small Business Accounting

Personal and Small Business Finance Software Small businesses are always strapped for cash, and sometimes don’t even have a budget that allows for software to track its finances. GnuCash provides these businesses with a solution. The extensive feature list includes double-entry accounting, stock/bond/mutual fund accounts, small-business accounting, reports, graphs, QIF/OFX/HBCI import, transaction matching, scheduled transactions, and financial calculations.

Are you a budding accountant? Prove your skills in the Smarterer Bookkeeping test!

Bookkeeping

The Designer’s Free Toolkit: 6 Free Stand-Ins for Essential Design Software

Free design programsGreat designers are proficient in a variety of software – photo editors, vector graphic creators, 3D design programs, and everything in between. If you work in the field of graphic design, you are most likely intimately familiar with Adobe InDesign and its creative suite, Adobe CS6.

You might also be familiar with CS6′s price: $1300.

For those of us looking for an entry point into the field, a simplified tool set for personal projects / school reports, or a quick stand-in for your everyday work environment while on the road / a friend’s computer, there are plenty of free alternatives to conquer the task at hand. They’re no substitute for the real thing, but all are plenty powerful enough to get the job done.

Update 1/18/13: Adobe recently released CS2 for free, introducing a worthy contender to the other options on this list. Hit the link to download early versions of Photoshop, Illustrator, etc. for free.

Image Editing

For an alternative to Photoshop, turn to GIMP, the GNU Image Manipulation Program. GIMP is an extremely powerful tool, offering all the same features that Photoshop does, at zero cost to you. Great for simple image editing like removing red-eye or optimizing colors, but also stocked with more advanced tools to rival Photoshop’s rich feature set.

Image Organization and Hosting

Once you’ve perfected all your pictures using GIMP, you’ll want to organize them into albums and share them with your friends. Look no further than Picasa, Google’s image hosting solution. Picasa allows you to easily upload photos right from your desktop, and then add tags, apply effects, and distribute them into photo albums.

Vector Graphics

For a vector graphics alternative to Adobe Illustrator, check out Inkscape. The program runs on the open-source SVG (scalable vector image) standard, which is supported by the most recent versions of most standard web browsers. While it doesn’t have any animation capabilities, Inkscape is a viable option for creating 2D vector graphics. And as a bonus, Inkscape’s blog has tutorials to walk the beginner/intermediate user through the basics of a variety of vector projects.

Screenshots and Screencasts

Have you ever needed to demo something on your computer screen to a remote collaborator? With free calls and video chats, Skype has long been a standard for digital communication – but it is the lesser-used screenshare functionality that makes it a collaborative workhorse. Broadcasting your project over a Skype call is one of the easiest ways (free or otherwise) to coordinate a collaborative project over the web.

If you need to record or add notation to turn your screencast into a full-on presentation, the free version of Jing may add the functionality you need. Jing allows you to add timed notes and record 5-minute screencasts to walk through project elements with your team.

3D Design

For creating, modifying, and sharing 3D models, Trimble SketchUp is your program. The free version provides a simple, yet robust alternative to expensive 3D design programs. SketchUp is a Google-supported program, with integration that allows you to drop your created models into Google Earth. They also provide training in two forms: through video tutorials and an in-program tutorial database which allows you to follow hands-on guides at your own pace.

Desktop Publishing

Scribus is an alternative desktop publishing program to Adobe InDesign. It allows you to create beautiful publications and supports professional publishing features, such as color separations, CMYK and Spot Color support, ICC color management, and versatile PDF creation. This software arguably outperforms Microsoft Publisher and other low-tier retail products, and does so with concern for everything from conception to final printed product.

Clip Art

For free clipart, Open Clip Art Library is your one-stop shop. According to it’s website, it is “the largest collaborative community that creates, shares and remixes clipart. All clipart is released to the public domain and may be used in any project for free with no restrictions.” Together with Scribus, which is governed by the GPL, you can ensure your projects don’t run into any legal hiccups.

Are you a designer? Test your chops and show off your aptitude with our graphic design tests on Smarterer:

InDesign SketchUp Design Principles