template Archives - Page 5 of 8

Time Off Tracker Template

March 20, 2020

by howtoexcel with One Comment accounting calendar template vacation

If you need to track employee sick days and vacation, then the time off tracker template can help you. This template can also help you decide whether to approve or deny time-off requests as well. Below, I’ll go over the main features of the template and how it works.

Get the Full Version

Setting up the time off tracker

On the Summary tab, you’ll start with a list of all the employees you want to track. This will include their hourly wage, start date, their beginning balance for their vacation days, as well as their vacation rate. The time off tracker template will also track sick days as well.

You can enter the current year and the cutoff date if you want to see up to a certain point in time. The annual hours and vacation rate percentage will impact the annual vacation day accrual. Annual hours you’ll probably want to set to either 2,000 (50 weeks x 40 hours) or 2,080 (52 weeks x 40 hours).

For sick days, there is a section off to the right on the summary page where you can enter the number of sick days people are entitled to annually. You can also specify a maximum number of people that can be off at any one time. This is related to approving requests which I’ll cover further down.

You’ll also see a section for holidays and blackout days for when you don’t want people taking time off. These lists can be as long as you like.

Entering and requesting time off

Whether you want to check if a person can book time off during a certain period or if you want to actually book it, you’ll do this through the Request.Form tab. Here, you can select the employee, the type of request (vacation or sick), and how long they will be off for. This template assumes employees do not work weekends. If the request includes a weekend, it will automatically account for that. Here’s a sample request for someone looking to take Jan. 24 – Jan. 31 off.

The available days, hours, days requested and hours requested will all automatically fill in once you enter all your selections. You’ll notice the days requested equals six, which isn’t counting Saturday and Sunday. It also assumes these are full days off and hence multiplies the days off by an eight-hour workday to get to 48 hours requested off.

If you want to see if this request complies with your policy (blackout rules, maximum people off) you can click on the Check Availability button. Then you will see the following summary:

The lights come up green for having sufficient time available and there being enough coverage. But it comes up red because the request includes a blackout day. Over on the right-hand side, you’ll see the person’s most recent time-off requests. It will also show any people who are off during this time.

You can still proceed and click the Post Time-Off Request button either way. It will post the information into the Timeoff tab:

If I double-click on any of the red boxes to the right, it will delete an entry. There is no data that needs to be entered on this tab, this is simply for record-keeping. The Timeoff tab is used to populate other areas of the spreadsheet.

If I were to go back and try and book another entry on Jan. 31 for a different employee, it will now tell me that someone is off during this time:

Note: you won’t be prevented from posting vacation if a person already has vacation booked for the same date. But if you make a mistake you can clear the duplicate entry from the Timeoff tab. You can also visually see who is off during a given period on the Calendar tab:

Blackout days are highlighted in black, vacation is dark blue, weekends are light blue and sick days are in yellow. In the above example it shows the one employee who took a vacation day during the blackout period. The color code for vacation overrides the blackout formatting. From this, you can also see that two employees were off on Jan. 31.

Entering in partial-day requests

If an employee is taking a half-day or only a certain amount of hours off rather than a full day, you can select the Partial Day option from the drop-down in the Request.Form tab. A new field will appear, allowing you to enter the hours per day:

In this example, this employee is requesting 5 hours off on both Jan. 30 and Jan. 31. The employee is requesting a total of 10 hours off, or 1.25 days. If you need to do a partial request, you can’t mix and match with full-day requests, you’ll need to do them separately. Even if the hours are different, multiple requests will be needed.

Whether an employee takes a full or a partial day off, it’ll look the same on the Calendar tab.

Adjusting for rate changes

One of the more complex calculations this time off tracker template factors in is if an employee moves to new pay rate or if their vacation rate changes during the year. This is where the Ratechanges tab comes into play. You select the employee, the date that the change is effective as well as the date it ends – this can be left blank if there’s no further rate change. The purpose of the end date is if there are multiple changes for an employee during the year.

Then, you enter the new hourly wage and vacation rate. Enter both numbers. For instance, if an employee now accrues 6% vacation rather than 4%, you’ll want to enter the new vacation rate but you’ll also want to enter in their hourly wage as well, even if it remains the same. This is important so that the formula calculates correctly.

This calculation will spit out a change in daily accrual as well as a total adjustment for the year based on the cut-off date. This adjustment will populate on the Summary tab under the Vacation Days Adj (Rate Changes) field.

As with any complex calculations, always be sure to double-check these numbers against your own. Especially when it comes to multiple rate changes a year, it’s important to ensure the data is entered correctly and that the correct number of days are being accrued. Although I’ve tested the spreadsheet, it’s impossible to factor in every possible situation and so I cannot guarantee 100% accuracy in all situations.

If you’d like to test out the template, you can download the free version here which is limited to tracking five employees. The full version has no limits and there are no ads and the code is fully unlocked:

Get the Full Version

If you liked this post on the time off tracker template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Bank Reconciliation Template With Side-by-Side Matching and Automatic Matches

February 23, 2020

by howtoexcel with One Comment accounting bank reconciliation macros template

UPDATED VERSION: Check out the new 2023 version of this bank reconciliation template

This bank reconciliation template is an update from an earlier file that was made three years ago. It offers many of the same features with some notable improvements, and I’ll go over both in this post. I’ll start off by highlighting the key features and how it can help improve the bank reconciliation process for you.

Matching transactions is easier than ever in this new bank reconciliation template

In the earlier version, the bank reconciliation template looked at the total of transactions for a day or that matched criteria and so it didn’t make match individual items. It can be a mixed bag since some people prefer one way of matching (e.g. multiple cash transactions on a book entry matching up to one large bank deposit amount versus having one deposit for each entry). This template tries to make both methods a bit easier.

The auto-matching feature takes care of the latter approach where each line is effectively given a unique id that will be used to match against other transactions. This is ideal for one-to-one matches where you don’t want to look at just the totals.

If you’ve used the earlier version of this file, you’ll know that in this template, you can set up categories and keys associated with them. For example, if a check transaction shows up on your accounting system as CK#1234 you can create a rule in this template to say anything with CK in the description is categorized as a check and that the numbers that follow the number sign form the key, or the unique identifier. You can create these rules in the Setup tab.

Here are a couple of examples as to how this looks:

setting up categories and keys on the bank reconciliation template

The Category is just the name of the category and the Identifier is what Excel will be looking for in the item description to see if it falls into that category or not. For checks, I’ve used the use numbers after identifier to say that what comes after CK# is what should be the key or the criteria that the template will be looking for when auto-matching. If there is no criteria, you can leave this blank and it’ll simply look at the amount and the category. However, this can be less accurate depending on if you have duplicates and similar data in your bank and book downloads.

You can also cap the length of the key, which is what I did in the above example, setting the Length of Key to 4. What this does is say that only the first four numbers will be pulled after the identifier. You can leave this blank and everything will be included. There is also a section for Gap if you don’t want it to immediately start pulling numbers after it finds the key. For instance, if I used CK as the identifier rather than CK#, then I’d want to set the Gap field to 1, to ensure that it skips over the next character, which in this case would be the # sign. But if you want to immediately pull data after the identifier, you can leave the Gap blank.

Alternatively, you can also just use the date as your key but that will not be very precise. The template and auto-matching will only be as strong as the rules that you put into place.

Manually matching transaction is easy, too

Even if you can’t auto-match all your transactions, I’ve tried to make this template as easy as possible to bulk match transactions as well. While the auto-matching is designed to help one-to-one matches, it’s also possible to match multiple transactions to one. This can be done using the Reconciler, which can be accessed via the Ribbon:

Ribbon buttons on the bank reconciliation file.

In the previous version, these buttons were within the file. Now, they’re on the home tab within the Ribbon, making it easy to access from anywhere in the file. Select a transaction from either the Book or Bank tabs and click the Reconciler button and you’ll have an interface where you can easily match transactions side-by-side:

In the previous version, side-by-side matching was not possible in the Reconciler and this allows you to easily do your matching within this interface. If I select the first transaction, which is a wire transfer, it will show me all the possible wire transfers I can match it to:

However, the ability to match the transactions won’t appear until I have the credits and debits matching an equal amount on both sides, to prevent running into a situation where I’ve matched an unequal amount:

By default, there will be no warning to pop-up when you’re matching transactions. However, if you prefer there to be one, you can change this in the Setup tab where there’s an option to toggle the confirmation from ‘No’ to ‘Yes.’

With it set to off, you can continue going through and matching transactions to ensure that you don’t have to click boxes before moving on to the next item to match. As it’s set up, you can match multiple items to one amount. However, you can’t match multiple-to-multiple and if you want to match multiple to one, then select the one transaction that will be matched to multiple items when launching the Reconciler. In the above screenshot, any transactions on the left-hand-side can be matched to multiple transactions on the right-hand-side, but not vice versa.

However, there is a SWAP button at the top of the form, which is also new, which can allow you to easily switch between the two views.

On both the Book and Bank tabs, there is a column for Manual Override and if you want to match an item manually you just need to enter a value in here. And that’s what the Reconciler does when you’re matching transactions. This is also where the next key feature comes into play: auditing and correcting your matches.

Audit trail from the Reconciler makes it easy to see which transactions are matched to one another

In the transaction that I matched above, it posted this in the Manual Override section:

You’ll notice that it says Previous BOOK Row 6. What this tells me is that the transaction was reconciled on the Previous OS Items tab, which includes transactions carried over from the previous period. It also tells me the row it was on and that it came from the book side. If the entry were to say Current, then it would be from the current transactions and that it would just be from the Book tab rather than the Previous OS Items tab.

If I wanted to undo this match, I could just press delete and clear the data in the Manual Override column. However, if you do this, be sure to clear off the other entry or entries related to it. Otherwise, you can be out of balance if you only cleared out one side of what was matched.

Reconciling the month

Once you’re done with your reconciliation and you want to see a list of your outstanding items, you can click on the Reconcile Month button in the Ribbon. This will spit out the outstanding items and group them by category. This process is similar to how the older version of the file worked.

When you’re finished and ready to start a new month or period, you can click on the New Month button which will clear the Book and Bank tabs and move any outstanding items over to the Previous OS Items tab. You’ll now be able to start a new month or period.

You can also use the Clear Data tab if you just want to remove everything and start completely from scratch

Testing out the file

If you want to give this file a try, please download the bank reconciliation template for free here. You can test out all the functions. There is a limit of just 25 transactions on the Bank and Book tabs. If you want the full version of the product, including with the code unlocked, please visit the product page here.

If you liked this post on the new bank reconciliation template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

This Macro Converts Your QuickBooks Export Into a Table-Friendly Format

December 22, 2019

by howtoexcel with No Comment accounting add-ins formatting macros template vba

QuickBooks does a good job when it comes to recording sales and doing day-to-day accounting tasks. You may be content with the reports that come out of QuickBooks, too. But if you’re looking for some more in-depth analysis to do of your own or to make your own reports, you’re likely going to want to move that data into Excel. And, unfortunately, the QuickBooks export into Excel can be less than optimal.

With many spaces, subtotals, and a non-tabular format, it’s not a very practical output to use in Excel. If you want to run a pivot table and do some serious analysis in Excel, you first have to clean up the data before being able to use it, and that can be a very tedious and tiresome process.

For example, this is what your QuickBooks report might look like when you’re pulling a simple summary of your customer sales:

There are a lot of things that need to be adjusted for this report to be useable in Excel, including getting rid of the blank spaces and ensuring that the customer information is repeated in the first column, as opposed to just in the first line and in the last line’s total. From afar, it’s a bit of a painful process to have to go in and clean this up. And while it’s not impossible, it’s not going to be quick, either.

That’s where a macro can help you make the task much quicker and it will save you a lot of time if you have to go through these steps often. Click the button on the ribbon and your data will convert into a more table-friendly format! Here’s how it works:

Using the macro to fix the QuickBooks export

Before running the macro, you’ll need to specify the columns where your customer names and dates are:

Then, run the Covert Data button:

Downloading the file

The free version of the QuickBooks macro will allow you to run the conversion if it doesn’t go past 100 rows. However, if you decide to purchase the full version please ensure that the macro and file works as expected. There’s no guarantee the QuickBooks export hasn’t changed or won’t change in the future. If there are changes that need to be made to the macro, please feel free to contact me so that I can make the necessary adjustments. Whether you prefer the add-In or the actual Excel spreadsheet itself, both versions are available both here and in the paid version as well.

Here is the download link for the add-in as well as the Excel file. For the paid versions, please visit the product page.

If you have another program or software that you’d like a similar add-in for, I can help with that as well.

If you liked this post, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Excel 12 Month Calendar Template

November 10, 2019

by howtoexcel with No Comment accounting calendar template

There are many Excel calendars online but with this template, you can customize it to suit your needs and use the calendar for more than just one year.

At a minimum, you can use the template to help generate a 12-month calendar for a specific year. However, it can do a whole lot more than that. If you want to use it for a custom fiscal year, you can use a different start date. You can also use custom accounting periods based on weeks, whether it’s the 4-4-5, 4-5-4, or 5-4-4. It can help make a template that will fit all of those needs.

And on top of that, you can also highlight holidays and deadlines on the calendar to make it easy to keep track of all your important dates.

How the template works

There are two tabs on the calendar template: calendar and setup. The only area to enter data is on the setup tab, and that’s where you will select the details relating to how you want your calendar to display.

variables to setup in the excel 12-month calendar template

The first item that you’ll want to enter is the start of the fiscal year. If you just want a regular calendar, then you can leave this as January 1. However, you’ll want to enter the year that you want the calendar for as well. This is the only cell where you’ll specify both the year for the calendar and the first day of the calendar as well.

On the following field, you can toggle between whether your week starts on Sunday or Monday. This will just impact how the calendar looks and will have no effect on anything else in the template.

If you just need a regular 12-month calendar, that’s all you need. If, however, you’re looking for a more customized format that accommodates a varying week schedule, then you will want to enter an ‘X’ in the Custom Quarterly Format field. If you do not enter an ‘X’ here, then the following values will be ignored and won’t have a bearing on the calendar.

The next three fields relate to how many weeks each period has. If you have fiscal periods that follow the 4-4-5 pattern where the third month of each quarter is a 5-week month, then you would enter ‘4’ for period 1 and period 2, and ‘5’ for period 3.

Those are all the variables that will relate to the structure of the calendar itself. You can go a bit further, however, and enter in holidays and other important dates as well.

Entering holidays and other dates

In column G you can enter the holidays or non-working days that you observe. Any dates in this column will be highlighted in yellow on the calendar. Columns H and I serve the same purpose, and that’s to highlight any important dates or deadlines. In column H, the dates will be highlighted in red while the ones in column I will be in green.

12-month calendar with holidays and important dates highlighted

There’s no restriction on what you can enter in these columns and these deadlines could be annual or monthly. You could even use various date functions to help create recurring deadlines here as well.

You can enter dates in for the entire column so they don’t even need to be for the current year. This is why you can reuse this template for future years, as you can just adjust the starting date for the year and update the holidays, and the template will automatically update based on what you have entered.

Download the 12-month excel calendar template

This template contains no macros and is free to use. You can download it here. It does have some ads and the calendar tab is locked to ensure nothing isn’t overwritten by accident. There is also an ad-free version available here that is completely unlocked. As always, I encourage you to always try the free version first to ensure it works how you expect it to and that it’s what you’re looking for.

Looking for a full-month calendar template?

This template is for a 12-month snapshot but if you’re looking for one that focuses on an individual month and that can allow you to enter tasks and deadlines, be sure to check out this template. It will give you even more options for managing your monthly tasks and deadlines.

And if you’re looking for a personal calendar that will help you manage different goals at once, then this goal tracker template could be what you’re looking for. But if you’re looking for something completely different, feel free to contact me and make a suggestion. I am always looking for new ideas and templates to add to the site.

If you liked this 12 Month Calendar Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Free TFSA Contribution Tracker Spreadsheet for Canadians (2025 Update)

October 31, 2019

by howtoexcel with No Comment accounting investing template

What is a tax-free savings account (TFSA)?

A tax-free savings account (TFSA) is a very useful tool for Canadian investors to shield investment gains and dividend income from taxes. However, it can be challenging to keep track of the rules and just how much you’re able to contribute and what your TFSA limit is for the year.

How much can I contribute to my TFSA in 2025?

For 2025, the annual contribution limit for the Tax-Free Savings Account (TFSA) is $7,000. This amount is set by the federal government and can increase over time due to indexation for inflation. If you were at least 18 years old in 2009 (the year the TFSA was introduced) and have never contributed, your total available contribution room as of 2025 would be $102,500. That total includes all annual limits from 2009 through 2025. Your personal contribution room may be lower if you’ve contributed in previous years, or higher if you made withdrawals in earlier years, since withdrawals get added back to your room at the start of the following calendar year.

It’s important to note that unused contribution room carries forward indefinitely, so you don’t lose it if you don’t max out your TFSA each year. This flexibility makes the TFSA one of the most powerful investment vehicles for Canadians, since it allows you to plan contributions around your cash flow without worrying about missing out on available room. To confirm your personal TFSA limit, you should always check your CRA “My Account,” as financial institutions don’t track your overall contribution room across multiple accounts.

Do TFSA withdrawals increase my contribution room?

Yes, TFSA withdrawals do increase your contribution room — but not immediately. Any amount you withdraw from your TFSA is added back to your contribution room starting on January 1 of the following year. For example, if you withdraw $5,000 in 2025, your contribution room for 2026 will increase by $5,000 on top of your regular annual limit. This feature makes the TFSA highly flexible, since it allows you to use the account both for long-term investing and for shorter-term goals where you may need access to your funds.

The key detail to remember is that you cannot re-contribute the withdrawn amount in the same calendar year unless you already have unused room available. Re-contributing too early would result in an over-contribution, which is subject to a penalty tax of 1% per month on the excess. To avoid this, it’s best to track your contributions and withdrawals carefully — or use a TFSA tracker — so you know exactly when that room becomes available again. This rule is one of the most common sources of confusion among Canadians, so understanding the timing is essential to getting the most out of your TFSA.

What happens if I over-contribute to my TFSA?

While it may seem simple to track your balance, there’s one issue that can cause headaches for TFSA holders, and that’s when it comes to withdrawing funds. One of the advantages of a TFSA is that since the funds that are contributed are after-tax, you don’t incur any penalties for taking money out. Unlike with an RRSP, you don’t have to worry about a withholding tax. With a TFSA, you can freely move money in and out of your accounts as you need it.

The caveat, however, is that when you withdraw funds, the contribution room isn’t replenished until the beginning of the next calendar year. And so if your TFSA had been maxed out on July 1^st and you had withdrawn $10,000, then that will free up contribution room –- but it won’t be until January 1^st. Any withdrawals that are made, regardless of the time of the year, won’t free up space until the beginning of the next calendar year.

That’s where much of the complexity comes into play when it comes to TFSAs. While contributions will reduce your available contribution room immediately, withdrawals won’t make that room available until next year. That lag can create many problems for TFSA holders. That lag can give people the misleading impressing that they have contribution room since they recently took money out, and that’s where overcontributing can happen very easily.

Suppose your TFSA is maxed out (2025 cumulative balance is $102,000) and you pull all the funds out today and they re-contributed them immediately after. In this scenario, you’ve now overcontributed by the entire balance -– meaning you’ll get a 1% penalty on that entire amount, which would amount to $1,020. And that’s just for one month. Leave that overcontribution in your TFSA and those penalties will pile up quickly.

While that may not be a common scenario that will take place, it’s an extreme example that helps to demonstrate just how costly it can be to make a very simple mistake. That’s why simply tracking the balance and looking at contributions and withdrawals is not enough, TFSA holders need to factor in the lag that happens with withdrawals. It’s a small but important detail that can make a big difference in determining how much contribution room you have available.

What this template will help you do

The purpose of this template is to help you keep track of both the contributions you make to your TFSA as well as the withdrawals so that you know what your limit is in a given year. If you’ve got multiple TFSAs and they aren’t all at one financial institution, keeping track of all your transactions can be a challenge. That’s where a spreadsheet can come in very handy; having all your information all in one place can make it much easier to stay on top of your TFSAs.

By logging your transactions each time you make a withdrawal or contribution from one of your TFSAs, you can have a complete picture of your balance at any given time. There’s no limit to the number of transactions you can enter in the template, and this can be used for a running total — forever. And with no macros and a simple, easy-to-use interface, the goal of this template is to make the process as painless as possible.

Use this template to track your TFSA limit

The template itself is very simple to use. There’s an area to enter any TFSA transactions, and while you can start from your first year of eligibility, if you know the contribution room you have as of the start of a specific year, you can start from there.

In the header, there is a section where you can specify your contribution room and the year. If for example you’re starting 2025 and you know the contribution room you have is $50,000, this is how you’d enter it:

Entering the contribution room and year for the TFSA tracking template.

Upon doing so, the template updates to show the starting room as per that year.

TFSA tracking template showing contribution room by year.

You can, however, start the calculations from your first year of eligibility by selecting from the drop-down list in cell R2:

Selecting the first year of eligibility for a TFSA from a drop-down list.

Next, you can specify any contributions and withdrawals from the TFSA transactions table. If you have multiple TFSA accounts, you can enter the specific one in the account tab. The template will total all of the transactions, making it easy for you to stay on top of your total contributions and withdrawals, regardless of which account they originated from. You can keep adding to the list of transactions as you need to and the table will continue expanding.

TFSA template listing transactions and showing the possible types of transactions to choose from.

All the amounts are to be entered as positive. The type of transaction will ensure that the transaction is put into the correct column and update the balance calculation correctly.

TFSA tracking template with transactions listed.

What if I go over my TFSA limit?

If the value in ending room field goes negative and indicates that you have overcontributed to your TFSA, the amounts will be highlighted in red (see below). That being said, just because it hasn’t highlighted in red doesn’t mean you’re safe. You should use this as a guide and not an absolute indicator of whether you’re okay or not.

TFSA template showing balances and which has highlighted an overcontribution.

Planning makes perfect

Even if you haven’t made any transactions during the year, what you can do is to enter transactions you expect, or plan to make. Especially if you’re expecting to withdraw funds, you’ll want to budget for that in this template to ensure that it won’t cause a problem for you. Doing some planning beforehand can help prevent problems down the road, and save some costly surprises.

Checking your data

One of the most important things that you can do is to verify that your data is correct.

When in doubt, your best bet is to confirm with the CRA. If you have My Account setup for access online, what you can do is access your balance as of the beginning of the year. While it won’t have all the contributions and withdrawals that you have made since the start of the year, you will have information on your available room as of January 1. This will at least give you a number that you can use as a starting point and then after factoring in your transactions, you can determine what your up-to-date balance is.

Downloading the file

As always, if you want to go ahead and try the file out just keep in mind there are no guarantees that come with it and that when in doubt, you should always verify your information with the CRA especially when it comes to determining how much room you still have available.

Here’s the link to download the TFSA template

The template will not factor in penalties and ultimately the accuracy of it will depend on how up to date your recordkeeping is.

The TFSA Contribution Tracker Spreadsheet is completely free of charge with no limitations. If you like it, please give this site a like on Facebook and also be sure to check out many other templates that are available for download. You can also follow us on Twitter and YouTube.

Invoice Generator Template

September 28, 2019

by howtoexcel with No Comment accounting accounts receivable business template

If you need to make an invoice and don’t want to spend money on some overpriced software, you can do so easily in Excel with this template. Not only can you customize it to how you want it to look and feel and produce a professional-looking invoice, but you can also track items, set prices based on customers and even have taxes calculated based on location codes.

Ultimately, it’s up to you how complex or simple you want it to be. Here’s how the template works:

The invoice itself

Whether you want to add a logo, change the colors or add some information to the headers, you can have a lot of control over how your invoice looks. The key thing to remember here, however, is not to add or remove any columns or rows. If you need extra space, stretch out the rows or columns, but don’t add any new ones.

And if you need to move the invoice date and number fields, be sure to move them, not delete them or copy and paste. They’re named ranges and so it is important that they remain intact for the code to still work.

Setting up the invoice template

Before you get started and using the template, what you’ll want to do is to set up some items, customers, locations and rates. Unless you really want to start from scratch every time, which I wouldn’t recommend.

First up, start with the Locations tab. If you’re only selling to one part of the world, then just set up a generic location but you can add as many different ones as you need. This is key for ensuring that the correct tax amount is being calculated per customer.

Next up, go to the Customer tab where you’ll have a list of your different customers, including their addresses and location codes. It’s important to add the location codes first because on the customer tabs the locations are drop-down selections that are derived from the locations tab, this ensures that you only select from a location code that has already been created. This is important to ensure that you aren’t mapping to a location that hasn’t been set up, otherwise, you’ll get an error.

Set up all the customers you need. Then next, move over to the Rate tab. Here, you’ll want to set up your customers from columns F onward. On this tab, you will also create your different items and differentiate between products and services. You can create as many items as you want. There is a rate field (column D) when you specify your default rate.

In the columns for your different customers, you can specify a special rate per customer. For example, in the above example, ITEM-1 has a default price of $50. However, if Company A is selected as a customer, a rate of $25 will be applied. If Company B or C is selected, the default rate of $50 will apply since no special pricing has been made available for those customers.

Putting it all together – creating an invoice using the template

With all that set up, now you can go back to the invoice tab to create your own invoice.

First, start with selecting your customer under the Bill To section. Enter the invoice date and invoice number.

Then, it’s a matter of selecting the items in column D, the date in column E and Quantity in column F (if it’s a service item, the quantity won’t matter, a value of 1 will be assumed). The remaining fields should auto-populate. You can edit everything that is in yellow. Anything not in yellow means that it you should NOT modify it (Note: the actual template will not show this highlighting):

Once everything is good to go, you can click on the Generate Invoice button.

This will do two things:

Create a PDF of the invoice you just created and save it to the location specified, and
It will also add the invoice to the Invoice.List tab. This creates a ledger for you to track all the invoices that have been created. If an invoice number is already on there, it will not allow you to create a duplicate. It’s important that you do not delete the invoice number field if you’re changing the template around.

The Invoice.List tab will log all the relevant data from the invoice. This includes the number, date, when it was saved and by who, which folder, and even individual item sales.

Disclaimer

The goal of this template is to allow you to generate invoices as accurately as possible. It also helps you track all your invoices. However, it’s by no means a perfect solution as you could conceivably alter the data in the Invoice.List tab after the fact. What I’d recommend is password protecting the file or hiding the tab if it will be used by multiple users.

So if you choose to use this file, it’s just important to keep that in mind. By adding too many controls and preventing people from deleting or correcting items, it may end up being too much of a hassle. This template isn’t a substitute for accounting software and it’s intended to create and track invoices.

Please note it’s up to you to ensure that your invoice is accurate and correct. You should always double-check an invoice before sending it out.

Download

The invoice template is free to use although there is a limitation of 3 items per invoice. It will also have an ad in the ribbon. The full version is available here. It will remove the limitations, advertising, and the code for the VBA will be unlocked as well.

If you like this Blank Invoice Template, please give the site a like on Facebook. Also be sure to check out our templates section. You can also follow us on Twitter and YouTube.

Monthly Expenses Template: Analyze Your Spending Quickly and Easily

August 16, 2019

by howtoexcel with No Comment personal finance template

A great way to start finding opportunities to save money is by simply looking at your monthly expenses. It can be as easy as downloading your recent transaction data from your bank or credit card issuer. While there are apps that can do that for you, by downloading it you have more control over the information and you also don’t have to give access to a third-party application. With the monthly expenses template, you can easily analyze your expenses in seconds.

The template has three tabs: DATA, CATEGORIES and SUMMARY. Let’s start with the most critical step, and that’s downloading your data and getting it into the template.

1. Getting Your Expenses In the Right Format

The most important part of the process is just getting the data correct from the start. On the DATA tab, there are four main headers (highlighted in blue) that you’ll want to populate information for: Transaction Date, Posting Date, Description, and Amount. You don’t need to use Posting Date since the monthly expenses template only uses the Transaction Date, but I included it since I found that many downloads included both dates.

The Description is a key field since it includes the Doing Business As (DBA) name, and this is crucial to ensuring that the right vendor name is extracted. Normally you’ll see the vendor name somewhere in the description. Have a look at this post as an example of how to extract the DBA name. It may not work 100% of the time as it ultimately depends on how the merchant sets up their DBA name, but generally, it should give you a pretty accurate result. There may also be instances where you have multiple vendors that are the same, and this again will likely be due to inconsistency in how one location inputs its DBA name versus another. In my sample data set, here’s how two purchases from Amazon can show up differently:

vendor description dba name monthly expenses template

Without changing the actual description, we’d end up with two different vendors. This isn’t ideal, but it also shouldn’t be all that common. It’s likely a result of one purchase being made for a different type of product or service on Amazon and a different DBA name being used in that instance. What you could do is create a category for similar-named vendors to help at least group them at the category level (see step 2 for that).

Lastly, there is the amount field. Whether this is positive or negative doesn’t really matter as long as your expenses are consistent.

Once all your data is in, the columns in red will autofill as they contain formulas. If they don’t, you may need to copy the formulas down but they should be automatic as long as the data is in the table directly below the most recent row. At this point, you can go setup categories for your vendors if you want to slice the data a bit further, or you can go straight to updating the report (step 3)

2. Setting Up Categories to Track Your Monthly Expenses (Optional)

Quick note: on the CATEGORIES tab, you’ll see an option to change the month-day-year format into day-month-year. This is only necessary if your date is downloaded in text (e.g. doesn’t read as a date) and it’s in day-month-year format (month-day-year format is the default). However, if you’re just downloading data for the last week or month, this may be irrelevant for you if you’re not looking to analyze expenses over a longer period of time.

In this tab, you’ll be able to set up any categories that you want to track. However, you don’t have to add any categories and the analysis will simply look at the expenses at the vendor level.

To set up a category, all you need to do is enter the vendor name and next to it the category that you want to map the vendor to. This will ensure that on the DATA tab, the category will automatically be pulled in for a particular vendor. However, it’s important to note that the matches need to be exact, otherwise this won’t work. Even an extra space in the vendor name won’t result in the formula pulling the correct category.

3. Updating the Reports

The SUMMARY tab creates a dashboard for all your expenses. The only step you need to do here is to go to refresh all the tables. To do this, on the ribbon, select the Data tab and click on Refresh All. This will ensure all the pivot tables are updated.

From there, it’s just a matter of how you want to filter or view your data. You can select the slicers if you want to narrow in on a certain month or category.

There will be two columns that you can look at: one will be for the total spend, and the other for the % of the total. If you have set up categories, you can narrow in to see which vendors made up a given category and the percentage will show their overall share.

You can download the monthly expenses template here. It is completely free and unlocked so you can make any changes you’d like to it. If you find this or other templates on this site useful, please give us a like on our Facebook page.

Monthly Goal Tracker Template in Excel

August 1, 2019

by howtoexcel with One Comment calendar conditional formatting template

Whether you’re trying to lose weight or wanting to learn a new skill, sometimes it can be challenging to stay on top of those goals. One way to you can make that easier with this goal tracker template. It’ll allow you to keep a record of how you’ve done versus your goals and show you the progress you’ve been making. Just being able to see that progress can sometimes help encourage you to keep working at it, and this template will do just that.

The spreadsheet has two tabs: the calendar where you can select the month and year, and the data entry tab where you’ll enter your progress. Let’s start with the data entry since that is going to be where you make any changes and will serve as a starting point.

Setting Up Your Goals and Entering the Data

On the DATA.ENTRY tab, the first three rows are dedicated to the name of your goal, the frequency of it, and your desired target. The goal name is straightforward and doesn’t impact anything but what you’ll refer to it going forward. This can be changed at any point.

The second row is the frequency, and here you can enter either Weekly, Monthy or Daily. They’ll track your totals accordingly and which frequency you select will determine how the calendar will highlight your progress. As soon as you hit a goal that’s set to monthly, the spreadsheet will highlight the entire month in green to indicate that the goal has been met:

If it’s a weekly target, then only the week the goal was met will be highlighted:

Daily targets will only be highlighted one day at a time.

The third row on the data entry tab relates to the actual goal number itself. In my example, I’ve put 500 for the weekly amount of calories burned and 100 studying hours in a month. These are the metrics that will be tracked. They will reset each period as well so if 400 calories were burned in a week, that week won’t highlight in green. But if on the following week 500 are burned, then it will highlight that week.

For daily goals, no goal amount needs to be entered. You’ve either met the goal or not for that day, and avoiding numerical totals here will allow you just to mark an ‘X’ on whether you were on track for that day. This can be useful if it relates to a task that might not have a numerical value. It’ll also allow you to create a chain of X’s like the method made popular by Jerry Seinfeld.

goal tracker template excel daily x jerry seinfeld

This way, you’re not limited to trying to assign a number to every goal. If you do have a numerical goal for every day, you can simply convert that into a weekly or monthly total. The only impact there will be when the cells will be highlighted.

There’s no limit to the number of goals that you can have so you can have a mix of monthly, weekly and daily goals to suit your needs.

For the actual data entry itself, I’ve pre-populated a date of July 28 but you can certainly change that to a different start date. The data entry tab will auto-populate the next 365 days for you. However, you can certainly extend beyond that as well.

Once you’ve got your start date, you can start entering in your data. Just look for the date and goal name (top row) to make sure you’re entering it in the correct cell. For daily goals, simply mark an ‘X’ to indicate they were met. For weekly and monthly targets, enter a number.

The Goal Tracker Calendar

Once you’ve got your goals set up and your data entered, you can switch over to the GOAL.CALENDAR tab. Here, you can change the month and year that you want to look at, and select the goal as well, from the yellow drop-down directly above the date.

Once you’ve selected the goal, it’ll highlight whatever progress you’ve made. For daily goals, it’ll show any X’s, which will automatically be highlighted in green. For other frequencies, you’ll see the actual numbers themselves so you’ll see how you’ve done on each day.

You’ll notice that the calendar for August won’t start on August 1 but on the first day of that week. The purpose of this is to track those weekly goals as otherwise part of the week will be cut off and could appear like a goal wasn’t reached.

You can download the template here.

If you like this Monthly Goal Tracker Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Mileage Log Template – Track Your Travel

April 27, 2019

by howtoexcel with No Comment template vbafree

If you do a lot of driving and you need to expense it for tax purposes, then you know you need a good log to keep track of it. That’s what the mileage log template will help you do. Along with tracking all your trips, it will let you do the following:

Track both kilometers and miles and convert back to a base unit. So if you travel outside of the country and don’t want to do the conversions yourself, you can enter them as either kilometers or miles and the spreadsheet will convert it into your base unit.
Allow you to categorize statuses based on whether you are going to be reimbursed by your company or not.
Track both personal and business travel so you have a complete picture of all your mileage
A summary tab to help you see your mileage by month

How the template works

In the Log tab, simply enter the details from columns A through to J. There are drop-down options for units, type, and status. These drop downs can be changed from the Setup tab. If you need to add more lines to the table, look for the next empty row and in column A enter a date. The table will automatically expand.

The template currently is set to fit onto one page in landscape, but you can adjust the columns as you need. If you do not need to see the status and would just prefer the template goes until column I, then you can delete column J and all the conditional formatting will go away along with it.

Summary of the mileage log

On the Summary tab, you’ll see a breakdown by month of all the different statuses and km/miles traveled. This makes it easy for you to see how much mileage you still have to claim versus how much has been reimbursed for. If you delete the status column then you’ll of course not see this information and simply have the totals.

Customizing the mileage log template in the setup tab

On the Setup tab, you can make changes to the travel type. For instance, you could put a vehicle description in addition to whether it is personal or business. There’s no limit to the number of options you can have in this field.

In the status section, in the description field I’ve indicated what color a status will be highlighted in. If you want to change the name of the status, column C is where you can rename it.

In column G, you specify whether you want your base units to be in KM or miles. This will be used to convert the mileage and determining whether a calculation is needed. If you select miles as your base unit and on the log you put in KM for the units, then it will do a conversion back to miles on the log.

Download options

Feel free to test out the mileage log template by downloading the trial version here. The limitations of the trial version are that the setup tab is not available and it also has an advertisement.

If you’ve tried the trial and would like full version, please visit the product page here.

Compare Worksheets in Excel With This Template

February 24, 2019

by howtoexcel with No Comment template

If you’re looking for a way to compare worksheets in excel and find the differences between them, then this is the template for you. It will quickly highlight any changes between the worksheets and also note the differences. With just a push of a button and it being stored in the Ribbon, the macro is quick and easy to run.

How the Template Works

The steps involved in this template are fairly straight forward and to compare worksheets in excel becomes very easy. Simply open up this file and copy the sheets in that you want to compare.

Then, on the Ribbon, select the Compare Sheets button.

You will then be prompted to select which worksheets that you want to compare.

It’s important to note that when doing the comparison it isn’t looking at formatting and simply looking at the actual values.

Once you’ve selected the tabs you want to compare, click on the Compare button and the macro will run and will now compare worksheets. If you want to compare thousands of rows then this process will take a while and you’ll need to be a bit patient, since the macro will look cell by cell.

Before running the Compare Worksheets template

Important: If you’re not sure how big your data set is, use the CTRL + END shortcut, this will take you to the end of your data.

This is a good double check before you run the macro since you’ll get an idea of just how many rows and columns it’s going to look at. Sometimes the range is a lot bigger than expect since you might have many empty rows and columns if you copied data in before and never shrunk down the range.

But if you’re good to go, then run the macro and it’ll compare the worksheets.

What the output looks like

If there are no differences between the files, you’ll get a message box telling you that. Either way, the macro will end up creating a new sheet where it will plot all those differences. In my data sample, I put in a series of random A’s across the sheet:

As you can see, it’ll identify the individual cell as well as the value there, compared to the related cell in the other sheet and what value is there. It will also create a hyperlink so that you can go straight to that cell so that you can review it in case you want to dig a bit deeper.

If you go onto the individual sheets that you compared, any differences from the other tab will be highlighted in yellow and bolded as well. This is just another way to tell you the cell is different than what’s on the other tab.

worksheet comparison highlighted bolded — Sheet 1

And that’s all there is to it. You can re-run the macro for any sheets that are within the file, although you’ll probably want to delete the one that gets created to highlight any differences once you’re done with it.

Download the Compare Worksheets Template

Free Version: Limited to first 20 rows. VBA code is locked.

Full Version: No limitations and code is fully unlocked.