Microsoft_Excel_2013_logo.svg

Get a List of Holidays in Excel!

Creating a list of holidays in Excel is fairly straight-forward to accomplish with a nesting of the CHOOSE, WEEKDAY, and DATE functions. Using these three functions, you can build formulas in Excel that will automatically tell you when all of the holidays are for a given year and what day of the week each holiday falls on. Some holidays are pretty easy to determine. For example, Halloween occurs on the same day every year. But for the holidays that move every year (holidays like Thanksgiving, which occurs on the 4th Thursday of November), the solution is a little more complicated. At the end of this article, we’ll provide you with your very own Holiday Template, or you can use the information below to build your own, customized list of holidays.Santa Claus Using Laptop

The first step in building a list of holidays in Excel is in understanding how the CHOOSE, WEEKDAY, and DATE functions work. The DATE function simply returns the date (in Excel date format) from a value you specify. The syntax is DATE=(Year,Month,Day). So, all you need to do is specify the year, month, and day of the date you need. WEEKDAY works on a similar premise. It returns a value between 1 and 7 (1= Sunday, 2= Monday, etc.) based on a date that you specify. So, all you need to do is insert a date into the WEEKDAY formula, and it will tell you the day of the week that particular date falls on.

The key function to creating your list of holidays is the CHOOSE function. The way this function works is that you specify a list of options and then tell Excel which option you’d like to pick. The syntax is =CHOOSE(Option_You_Want,Option1,Option2,Option3,…). The first value in the function will be a positive number. So, if you put a “1” in there, the formula will return Option1. If you put a “2”, it will return Option2.

So, if you wanted to calculate the date that Martin Luther King, Jr. Day falls on in a given year, and your year is provided in cell B2, your formula would look like this:
List of Holidays Formula

This formula will determine what day of the week the first of the month falls on, and then add 14 days (since the holiday never falls on the first or second Tuesday of the month), and then adds additional days based on what day of the week the first of the month is. So, if the first of the month is a Sunday, the formula will add 14 to Jan. 1 (bringing you to Jan. 15) and then add 1 to that to get you from Sunday to the third Monday. The result is Jan. 16. You can use this same formula structure to build out your full list of holidays, especially for holidays that fall on a particular day of the month rather than a particular date.

Get Your Free List of Holidays Now!

Download a free template to create your own list of holidays! And don’t forget to sign up for our free email list, where you’ll receive free Excel tips and tricks directly in your inbox and exclusive discounts for our upcoming training classes.

 

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

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.