Create a Return on Investment Calculator in Excel

If you have a stock portfolio (or simply have a list of companies you like to follow), Excel makes it easy to create a return on investment calculator and to pull in all kinds of useful stock tracking information from the internet. In this article, you’ll learn how to create a return on investment calculator in Excel and, you’ll also receive a free template download at the end. Once you know how to create your own return on investment calculator, you can tweak the template to fit your individual financial needs. Note that this template will only work in Excel 2013 and later versions.

Creating a Return on Investment Calculator

The basic structure of this template is built around the WEBSERVICE function, which makes creating a macro to download the data from the internet (as was necessary in older versions of Excel) unnecessary. To build your return on investment calculator, you’ll use the WEBSERVICE function to call the Yahoo Finance API, which will pull the appropriate stock data into your spreadsheet. The formula looks like this:

=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&[Ticker Reference]&"&f=[Yahoo Tag]"))

return on investment calculatorIt’s usually easier to just set the Ticker Reference to a particular cell (such as A2). This allows you to quickly change (or copy) the formula without having to edit the formula itself. The Yahoo Tag refers to an alphanumeric tag code that tells the formula which data elements to download. You can find a list here.

So, if you wanted to download the last trade price of Coca-Cola, you would place the Ticker Reference (KO) in cell A2, and use the Tag Code “l1”. Your formula would look like this:

=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&A2&"&f=l1"))

When you input the formula, Excel will download the last trade price of Coca-Cola and input it into the cell that contains the formula. You would simply repeat this process for however many metrics you want to track. To create your return on investment calculator, you’ll simply need the purchase price and the last trade price to determine your return.

Get Your Free Template!

To see what this looks like in practice, download a free copy of our Return on Investment Calculator. The calculator is currently set up to track 5 different stocks. Simply replace the data in the yellow with the information from your own portfolio and hit the Refresh button to download the latest data. The template also includes several charts that allow you to track the performance of your portfolio visually.

You can also sign up for our free email newsletter, where you’ll receive free Excel tips and tricks right in your inbox! You’ll also receive exclusive discounts on our upcoming training classes. Sign up here!

Microsoft_Excel_2013_logo.svg

How to Enable the Excel Developer Tab

In order to unlock much of Excel’s advanced functionality, you must first enable the Excel Developer Tab. Once you open the Excel Developer Tab on the Ribbon, you will have access to all of the macro and coding options. Opening this tab is the first step in learning how to code in Excel.

To get started, open the File Tab on the Ribbon and select “Options”.

excel developer tab options

This will open the Excel Options Dialogue Box. From here, navigate down to the “Customize Ribbon” option.

excel developer tab options

Your screen should look like this:

excel developer tab ribbon

This screen provides you with access to all of the different components of the Ribbon. The scroll box on the left contains a list of all of the commands in Excel (things like cut, paste, copy, etc.). The scroll box on the right allows you to modify the contents of the Ribbon. If you know how to customize the Excel Ribbon, the next step is easy. If you don’t, simply check the box next to Developer in the right-hand scroll box.

excel developer tab ribbon2

Then click “OK”. If you’ve followed the above steps, you should have enabled the Excel Developer Tab. It should appear on the far right-hand side of the Ribbon, as in the screenshot below.

excel developer tab final

As you can see, once you’ve enabled the Excel Developer Tab, you open up access to many of the options that allow you to create and edit macros, write code in VBA, and add interactive form controls (such as buttons, scroll boxes, etc.) to your spreadsheets. Opening up the Excel Developer Tab is the first step in learning how to use these more advanced controls.

Finding the Excel Developer Tab is Only the First Step!

Continue to advance your Excel knowledge by signing up for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and be the first to receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

5 Advanced Excel Skills You May Not Use (But You Should)

There are many advanced Excel skills that can make your day-to-day work in Excel much easier. Some of these skills you may have already heard of and may use on a daily basis. However, there are likely several you have never heard of or were never quite sure how to use. The following list of 5 advanced Excel skills are some of the more under-appreciated functions in Excel. However, once you implement them into your daily workflow, you will find that they make many complicated activities much easier.

The Format Painteradvanced Excel skills format painter

The Format Painter is one of the most useful advanced Excel skills that you should have in your toolbox. It allows you to quickly copy and paste formatting from one or more cells to another area of your spreadsheet. Due to its being unfortunately crammed all the way on the left-hand side of the Ribbon, the Format Painter is easily overlooked. However, once you start using it, you will wonder how you ever formatted spreadsheets without it!

Templates

Templates are invaluable time-savers, especially if you need to recreate similar reports/spreadsheets on a regular basis. All you have to do is format and design the template once and get everything functioning the way you need it to. Then you can simply copy the template every time you need to run the report in the future. So, there’s no need to re-invent the wheel!

Named Ranges

Named Ranges are one of the most useful advanced Excel skills that you can learn. In much the same way that templates can save you time in setting up and designing workbook layouts, Named Ranges are a great way to save time when writing long/complicated formulas. You can use a Named Range to refer to a particular range or group of cells using a regular alpha-numeric name (rather than the actual cell references). Not only do Named Ranges allow you to quickly refer to the same cells across multiple formulas, they also allow you to keep your spreadsheets more organized through the ability to give them actual names. Knowing how to use Named Ranges is one of many key advanced Excel skills that you should be familiar with.

advanced Excel skills sheet protectionSheet Protection

File security is very important when you are sharing your Excel files with other people. Even if the data in your spreadsheet isn’t “sensitive” per se, properly deploying file protection can help prevent unintended changes to your file. Knowing all of the ins and outs of sheet protection is one of the most valuable advanced Excel skills you can learn if you are going to be sharing your Excel files or allowing other people to make changes to them.

Page Break Preview

If you need to do any printing in Excel, the Page Break Preview is a quick and easy way to format and adjust the layout of your spreadsheet. Rather than wasting time (and paper) printing out a spreadsheet multiple times and tinkering with the layout to get it to fit, simply use the Page Break Preview to see how your spreadsheet will print before you ever send it to a printer.

Add More Advanced Excel Skills to Your Toolbox

Sign up for our free email newsletter and, you’ll learn even more advanced tips and tricks! You’ll receive free Excel tips and tricks directly in your inbox and be among the first to receive exclusive discounts to our training classes.

Microsoft_Excel_2013_logo.svg

Automatically Set Conditional Formatting Based on Another Cell

The ability to automatically adjust conditional formatting based on another cell is a valuable skill to learn because it will allow you to take advantage of the full power of conditional formatting. So, if you have a situation where you want to highlight an entire row (or column) of data based on the value of a cell (or cells) within that row, you can use conditional formatting to accomplish this. This makes it easy to quickly identify rows that contain blank cells or whatever value you specify. Once you know how to adjust the conditional formatting based on another cell, you can make your formatting more dynamic, more flexible, and more powerful.

Automatically Set Conditional Formatting Based on Another Cell

To start, simply highlight the rows you would like to apply the conditional formatting to.

Conditional Formatting Based on Another Cell Step 1

Then go into the Home Tab, select “Conditional Formatting”, and then select “New Rule”. You’ll get a screen that looks like this:

Conditional Formatting Based on Another Cell Step 2

In the box enter the following forumla:
=$D2="Yes"

Note that you need to include the dollar sign before the cell reference. This ensures that as Excel evaluates each row, it only evaluates the values contained in Column D. If you wanted to set the conditional formatting based on another cell (the cells in Column C, for example), you would change the cell reference in the formula to $C2. Once you’ve written your formula, set the cell background color to green. This formula will color the entire row green if the employee is bonus eligible. Your entries should look like this:

Conditional Formatting Based on Another Cell Step 3

When you click “OK”, Excel automatically colors the entire first two rows green because they are deemed bonus eligible.

Conditional Formatting Based on Another Cell Step 4

This is a very simple example of how to automatically set conditional formatting based on another cell. You can make the reference formula much more complicated if you want to evaluate several different columns or even set the color based on whether each employee’s score is above or below a certain level. The variations on this technique are almost endless and limited only by your imagination.

Don’t Stop Here

Take your education to the next level! Sign up for our free email newsletter, and you’ll receive free Excel tips and tricks directly in your inbox. You’ll also be the first to receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

Goal Seek: Excel’s Crystal Ball

Goal Seek is one of Excel’s most useful tools for creating predictive models. You can use Goal Seek to help optimize something as simple as a mortgage payment or incorporate it into a larger, more complicated data analysis. So, if you know what you want your mortgage payment to be and, you want to determine how much money you should put down (or what interest rate you need to obtain), Goal Seek makes this quick and easy! It allows you to target different variables within your data model and use the results to determine the best inputs. It allows you to quickly change the values of variables within your model so that you can easily see what the effects are on the final result.

Getting Started With Goal Seek

In order for Goal Seek to be effective, it is important to have the data in your spreadsheet laid out properly. You need to establish one “main” formula cell that contains the primary formula that you want to change. You will then need to have one or more feeder cells which feed into your primary formula. In the example below, the main formula is the Monthly Payment formula (bolded in Cell C1). Cells B2-B5 contain the four variables that make up the Monthly Payment Formula (=PMT(B4/12,B3,-B5).
Goal Seek Data Setup

From here, simply navigate to the Data Tab on the Ribbon, click the “What-If Analysis” button, and then select “Goal Seek”. You’ll then get a screen that looks like this:
Goal Seek Screen

Setting Up the Simulation

The “Set Cell” value should be set to the cell that contains your main formula (in this case, C1). The second field “To Value”, should be set to whatever number you want your main formula to produce. So, for example, if we wanted to lower the Monthly Payment result to $700 (from its current $859.35), you would enter “700” in this field. The last field, “By Changing Cell”, is the cell that Goal Seek will change in order to produce the desired result in the main formula (in our example, $700). So, if you wanted to lower your Monthly Payment by increasing your Down Payment, you would enter “B2” in this field.

Your screen should look like this:
Goal Seek Screen Final

Click “OK” to run the simulation. You’ll get a results screen that looks like this:
Goal Seek Result Screen

As you can see, Excel has adjusted the amount of the Down Payment to $53,377 (from its prior $20,000) in order to produce the desired monthly payment of $700. Click “OK” to save the results, or click “Cancel” to discard the changes. You can run this simulation for any of the other variables (such as Term and Interest Rate) to determine how long you would need to extend the payments or how low the interest rate would need to go in order to obtain the $700 monthly payment. Goal Seek is very flexible and a good way to quickly determine how much a variable needs to change in order to obtain the desired final result.

Learn About Excel’s Other Data Tools

Besides Goal Seek, there are several other Data Tools that you can use to simulate different data scenarios. You can learn all about them by signing up for our free email newsletter. You’ll get valuable Excel tips and tricks directly in your inbox and, you’ll also receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

3 Reason Why You Should NOT Share an Excel Workbook

As a follow-up to our last post on why you should Share an Excel workbook, this post will cover some of the main reasons why you should not Share an Excel workbook. Sharing is a very valuable function in Excel, but in some situations, it is better to simply turn the Sharing off. While Sharing does open up many opportunities for collaboration with your team, it also restricts many key functions in Excel.

1. You Need to Insert an Object

One of the shortcomings of the Sharing function is that it disables many critical functions. You’ll find that once you share an Excel workbook, most of the options on the Insert Tab are disabled. So, this means that you can’t do things such as insert shapes, pictures, or even hyperlinks. In effect, when you Share an Excel workbook, your options are limited mostly to simply entering data into the spreadsheet. If you need to insert an object in a Shared workbook, you will first need to disable Sharing, insert your object, and then re-enable Sharing.

2. You Need to Create a Pivot Table

Creating Pivot Tables (or Charts) is also disabled when you Share an Excel workbook. You will still be able to view and manipulate Pivot Tables that you created before you enabled Sharing. However, you won’t be able to create any new ones. So, before you Share an Excel workbook, you need to make sure that you don’t need to create any additional Pivot Tables.Why not to share an Excel Workbook

3. You Need to Import Data

Another function that is disabled when you Share an Excel workbook is the suite of Data Import options. Once the file is Shared, you won’t be able to pull data into Excel directly from text files, databases, the Internet, etc. You will still be able to use the Copy/Paste functions, however, depending on the type of data that you’re working with, this may create more problems than it solves. So, if you have a large volume of data that you need to pull into a spreadsheet, import the data before you enable Sharing.

When You Share an Excel Workbook, We’ll Share Our Knowledge!

Now that you know the pros and cons of Sharing an Excel workbook, you can continue to expand your Excel knowledge by signing up for our free email newsletter! You’ll get free Excel tips and tricks directly in your inbox and also receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

3 Reasons Why You Should Share an Excel Workbook

Knowing how to properly Share an Excel workbook is a valuable skill, particularly in the modern office environment. However, it is also important to know when to Share an Excel workbook. Many people know how to share Excel files, but many do not know the best way to take advantage of all the Sharing functions. Once you know how and why to Share an Excel workbook, you will be able to take advantage of the flexibility that Sharing offers.

1. Collaborate on a Group Project

The most common reason to Share an Excel workbook is to enable collaboration on a group project. Sharing allows multiple people to go into the file at the same time to make and save changes. So, with Sharing enabled, your project team can all work in the workbook at the same time without having to wait for each other to save and exit the workbook (avoiding that dreaded “Read-Only” status). When you Share an Excel workbook, your team can work faster and more efficiently.

2. Create a Data Tracker With Multiple Editors and Viewers

You can also easily create a multi-user data tracker with Sharing. If you need to create some sort of running log or tracker where multiple people need to get into the file and log changes, Sharing an Excel workbook makes this quick and easy. So, if you have a couple customer service reps who need to log new sales orders as they come in, you can simply setup your log, click “Share”, and then your reps can each easily log their own orders in the tracker without having to stop and ask the other people to get out of the file. Sharing also enables each of the reps (and their managers) to see the other reps’ orders in real-time, so they can easily redistribute the volume if one of them becomes overwhelmed. Sharing makes creating a log or other real-time data tracker fast and easy.Share an Excel Workbook

3. Avoid the Confusion of Read-Only

Sharing an Excel workbook also enables you to avoid the confusion associated with the “Read-Only” status. So, if you want other people to be able to view the contents of your Excel workbook and would like to avoid a cascade of pop-ups every time someone new opens the file, you can simply Share it. This will allow your users to go straight to the content of your file without having to click through multiple pop-ups warning them that other people are in the file. You can then secure the content of your workbook with the appropriate permissions.

Share an Excel Workbook and Extend Your Knowledge

Sharing an Excel workbook is a great way to take advantage of the flexibility of Excel and make the program more responsive to your team’s needs. But learning why you should Share an Excel workbook is just the first step. Sign up for our free newsletter to learn even more valuable Excel skills. Plus, you’ll also be the first to receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

How to Use the Excel IF Function Like Batman

If you know how to use the Excel IF Function, you can easily write formulas that do such things as determine whether to offer someone a special discount, flag a series of test scores as Pass/Fail, or even override #N/A or #DIV/0 error messages. Once you’ve learned the basics of how to create a simple conditional statement using the Excel IF Function, you can also create logic trees by nesting the formulas. The IF Function is one of the simplest and easiest functions to learn, but is also one of the most flexible and powerful.

How the Excel IF Function Works

The Excel IF Function is actually fairly straight-forward and only consists of three arguments:

=IF(Conditional-Statement,Value-If-True,Value-If-False)

First, you create a conditional statement. This would be something to the effect of “If A2 is greater than 3″. Excel will then evaluate that statement as either TRUE or FALSE. If the statement is TRUE (e.g. if A2 = 5, then A2>3 will evaluate as TRUE), the function will return the second argument (Value-If-True) as the formula result. If the statement evaluates as FALSE (e.g. A2 = 1), then the function will return the third argument (Value-If-False) as the formula result. So, for the formula, =IF(A2>3,”A”,”B”), where A2 = 5, the formula will return “B”. If A2 = 1, the formula will return “A”.

Taking Your Excel IF Function to the Next Level

The real power of the Excel IF Function lies in its flexibility. In the example above, we used “A” and “B” as our Value-If-True and Value-If-False values. However, you can use nearly anything for those arguments (just be sure to put text in quotes). Instead of using “A” and “B”, we could have used “Over” and “Under” or “Seattle” and “Washington, DC” or simply used a number.

We can also use functions instead of text or number values. For example, we could say

=IF(A2>3,SUM(A2:A5),SUM(A6:A10))

Excel IF Function

=IF(A2=”Joker”,”I am the Knight”,”Bruce Wayne”)

So, in this situation, if A2 = 5, then the function will add all of the values in cells A2 through A5 together and report the sum. On the other hand, if A2 = 1, then it will add all of the values in cells A6 through A10 and report the sum. We can do this with nearly any function (this is called “nesting”). We can even use other IF functions as our arguments, as in

=IF(A2>3,IF(A=5,"A","C"),"B")

So, in this situation, if A2 = 5, then the formula will evaluate the first function (A2>3) as TRUE, which will cause it to evaluate the second argument (the IF function) as TRUE, which results in “A”. If A2 = 1, then the first function evaluates as FALSE, which directs Excel to skip over the second IF function to the Value-If-False argument, which is “B”.

Don’t Stop Here! Learn Even More!

While nesting functions may seem overwhelming at first, it doesn’t have to be. With additional study and practice, you’ll soon be creating complicated logic trees and evaluating complex conditional statements.

To learn more about formula nesting, sign up for our free newsletter. When you sign up, you’ll get free Excel tips and tricks directly in your inbox. You’ll also be the first to receive exclusive discounts on our training classes.

2000px-Microsoft_Office_2013_logo.svg

How to Customize the Excel Ribbon

At first glance, the Excel Ribbon can be quite intimidating. If you’re not exactly sure where a command or function is located, trying to find it in the Ribbon can be difficult. The default settings provide for at least 8 different tabs, dozens of different command groups, and over 100 separate commands. If you don’t know how to organize the Excel Ribbon, attempting to use it can be frustrating. Luckily, customizing the Ribbon to your own unique needs is fairly easy. Once you know how to create your own tabs and command groups, you can redesign the Excel Ribbon in a way that best suits your needs.

Making the Excel Ribbon Your Own

To get started, go to the File Tab and scroll down to “Options”.

Excel Ribbon Options

In the Options dialogue box that opens, click the Customize Ribbon link.
Customize Excel Ribbon

On the left-hand side of the window is a list of all the commands available in Excel (which you can sort using the drop down box at the top of the list). On the right-hand side is a list of what’s currently showing on the Excel Ribbon. Click the plus sign next to one of the tab names to open up the groups in that tab, and then click the plus sign next to one of the command group names to open up the list of commands in that group (as in the screenshot below).

Customize Excel Ribbon Window

You can add and remove groups and commands by using the two buttons in the center of the window. You can also create your own tabs and groups on the Excel Ribbon using the buttons below the list on the right-hand side of the window. To create a new Tab, simply click “New Tab”. A new tab will then be inserted in the Tab list.

Customize Excel Ribbon New Tab

Right-click the new tab in the list and click “Rename”. You can now call your new tab whatever you want (I named mine “My Tab”). You can do the same thing with the new group that was inserted in your new tab (I named my new group “My Group”). If you want to add another group to your tab, simply select the tab you want to add the group to and, click “New Group”.

Once you have your custom tabs and groups established, you can begin adding commands to your custom Excel Ribbon. Keep in mind that commands can only be added to groups (not tabs). To get started, simply find the command (or commands) you want to add in the list on the left, highlight them, and then highlight the group on the right that you want to add them to. Then click “Add”.

So, if you wanted to add the Copy command to your new My Group group, select “Copy”, then select “My Group”, then click “Add”.

Customize Excel Ribbon Add Command

Your Excel Ribbon should now look something like this:

Customize Excel Ribbon final

That’s all there is to it! You can now add/remove commands, groups, and tabs and customize the Excel Ribbon in any way you please.

Learn to Harness the Power of Excel

Learn even more useful customization tricks by signing up for our free newsletter! You’ll get free Excel tips and tricks directly in your inbox and also receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

What is an Excel Macro?

Just what is an Excel macro? How can you use an Excel macro in your everyday work? Do you need to know anything about coding to write macros? Macros are flexible, dynamic, and relatively easy to learn how to use. Once you learn how to write and use macros, you will be able to simplify everyday tasks and make the performance of your daily duties more efficient.

So, What is an Excel Macro?

A macro is simply a program (or piece of code) that lives and operates within Excel. Excel macros are written in a coding language called VBA (Visual Basic for Applications) and can be used in any number of applications. Some macros are very simple and consist of only a few lines of code. Others are more complicated, with the code running for hundreds of lines. Macros are a way of extending the use and power of Excel through coding.

Excel Macro Example

How Can You Use Excel Macros?

You can use macros in any situation where it is possible to automate a task. So, if you need to hide/unhide a series of rows/columns/sheets you can write a macro to do that. You can also write a macro to automatically adjust formatting at the click of a button, or even send an email (with attachment) to a designated recipient. You can even make one macro run another macro. The possibilities are limited only by your imagination and your familiarity with VBA.

How Do You Get Started Writing Excel Macros?

Luckily, you don’t need to know any code in order to get started writing macros. You can simply use the built-in Macro Recorder. All you need to do is turn the recorder on and Excel will covert whatever actions you perform into code. When you’re done, just turn the recorder off. That’s all it takes to get started writing Excel macros. As you improve your skills, you can rely less on the recorder and create more complicated code. However, the Macro Recorder is a good way to get started.

Get Started Writing Macros Now!

Sign up for our free newsletter and to learn all about macros and more! You’ll get the latest Excel tips and tricks and receive exclusive discounts for our training classes.