template Archives - Page 5 of 7

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.

Task Manager Template: Stay on top of Your Deadlines

January 27, 2019

by howtoexcel with One Comment accounting calendar template

Whether you’re managing multiple deadlines, have a lot of tasks, or just have an odd schedule, this task manager template can help you manage all of that. Visually you can see what’s coming up and what still needs to be done. It’s a good way to organize and manage all your responsibilities.

How the Task Manager Template works

I’ve tried apps to track deadlines and tasks and none of them have ever done what I wanted them to do. And since I spend a lot of time in Excel, I thought I’d try to make a task manager template that can be a one-stop shop for managing all of that.

The task manager has four main tabs to it that I’ll go over in detail:

Calendar
Recurring Deadlines
Tasks
Team Calendar

Calendar

In its simplest form, you can use this tab to generate a calendar for whichever month and year you want just by changing the cell values. The calendar will highlight the current day as well as any holidays that you have specified.

It’s pretty simple, but once you start adding tasks and deadlines, it’ll look a whole lot different. I’ll refer back to the calendar as I go as it’ll change as I make updates to the other tabs.

Recurring Deadlines

There are multiple sections in this tab and it really acts as a setup tab, but the deadlines are definitely key.

In the left-hand-side of the page is the month-end schedule. Now, if you’re not an accountant and don’t have to deal with month end, you can probably skip this. However, for accountants that deal with a close process and have deadlines, you can change the month-end date.

For example, I’m going to change the month-end date for January 2019 to February 12:

If I go back to the Calendar tab, even though I’ve selected January, the calendar will continue until February 12:

You can enter the month-end values for each month so that way each month will cut off where you want it to. If you don’t need a custom end to the month, then the default values will suffice and you they will just end when the month does.

On the right side of the page there is a section for holidays. This is where you can put the non-working days in your part of the world. What you could also do is put any vacation days or time off that you have planned.

I’ve left a description of the holiday next to it but this is not necessary.

Now, if I go to the middle section of the tab, that’s where I see the recurring deadlines. There are five key fields here: description, occurrence, type of occurrence, day, and next date.

In the description field you’ll want to put in the name of the deadline. If you’ve got multiple deadlines on the same day you’ll want to combine them into one as they will only take up one line on the calendar anyway.

The occurrence and type of occurrence fields will go hand in hand. For example, if your deadline is the first business day of the month, then you’ll put a 1 in the occurrence field and Business Day in the type of occurrence.

If your deadline is the first Monday of the month, then enter a 1 in occurrence, Day of Week in the type of occurrence, and enter Monday in the day field (this is the only time you’ll need to use this field).

If your deadline is always the 1st calendar day of the month, then enter 1 for occurrence and Calendar Day in the type of occurrence field.

There’s an option to just enter a fixed date as well. If you always have a deadline on January 30th of every year, then enter the date in the occurrence field and Fixed Date on the occurrence type. This is helpful if you’ve got property taxes or annual deadlines that you can easily forget about. Of course, you’ll need to remember to update your deadlines.

Lastly, there is an occurrence type for Business Days Before Period End. Before your month end (whether custom or calendar), you can work backwards to compute your deadlines. Let’s say one business day before your cut off date you have to get a report submitted. In that case, you can enter a 1 for the occurrence and select Business Days Before Period End for the type.

Using the above scenarios, this is what my deadlines look like so far:

The Next Date field automatically gets populated based on what you’ve entered in the three prior fields.

These deadlines now show up on my calendar:

The deadlines are highlighted in red (except where there’s a holiday, as on Jan 1). The description of the deadline also shows in the first line for that particular day as well.

Tasks

On the tasks tab, you’ll be able to see your deadlines, to do list, and tasks. In the first section, you see the deadlines which we’ve already entered thus far:

You can add any notes to this section as well as update the status of the deadline. If you haven’t completed deadlines that are in the past, the Days Until Deadline visual will show a zero. However, if I update the status to ‘Completed’ for all the tasks in the past, then my countdown will update to show many days until my next deadline:

This now gives me an accurate countdown as to how many days away I’m showing until the next deadline.

The To Do List won’t show up on the calendar and is just a way for you to track items you’re working on now. It will also show another countdown and is similar to how the recurring deadlines work in this section:

The last section on this tab is the tasks section.

This section is important since this is what will show up on your calendar. The deadline column will automatically be the first item on the list. And so you have a limit of three tasks that will show up on your calendar (two if there is a deadline there).

For the first three days of the year, I’m going to put the same three tasks:

This is how the calendar now looks:

Because the first two days of the year each had deadlines, only Task 1 and Task 2 made it onto those days. On the third day, all three tasks showed up.

Team Calendar

If you’re working on projects or teams, you might find it helpful to have a glimpse as to when people are off.

On the left side of the page, you fill in the person’s name and the range of dates that they are off. You can enter in multiple ranges for the same person so the names can repeat.

Next to the actual dates, you’ll want to enter each person’s name that you wish to track (which I’d presume is everyone that is on the left side that you have entries for).

The date will automatically start at today and you’ll see the next 90 days of availability. Any holidays and weekends will be highlighted in light blue. Any time off outside of that will be highlighted in dark blue as in the above example.

Download the Task Manager Template

Free Version – Limit of 10 task and recurring deadlines, contains ads and sheets are locked.

Full Version – 30 recurring deadlines and to-do-list items. No ads and no sheets are locked.

Action Items Template – How to Stay Productive

November 18, 2018

by howtoexcel with No Comment template vba

Excel can be a useful tool for tracking items, whether related to a meeting or a project. The action items template allows you to enter multiple fields related to an action item, including responsible person, department, expected completion, and tags to help organize them.

You’ll be able to create new action items, sort them into different department tabs, recall the ones you want when it’s a new meeting, update the items, and archive them when they’re done.

Let’s start from the beginning.

First, fill out all the fields relating to the action items from your meeting.

The tags field will help when you recall meeting items in case you only want ones related to sales, a project, or some other criteria. Tags can be separated any way you want – comma, space, or any other separator.

Then, click Save New Items, which will put them into every relevant tab.

By default, I have the sales and marketing tabs setup, but if you need more departments simply copy those tabs. If a tab doesn’t exist for the department, then you’ll get an error and it won’t be able to populate those tabs.

However, you don’t need to have a department for each action item and can simply assign a generic one.

The sales tab now shows the action item:

If a comment is left blank, then it will simply say ‘no update’ was made. In the comment field, it will always show the date of the meeting.

Now, say you want to make a new meeting and want to populate the action items. Click on the New Meeting button. This will give you the opportunity to enter any tags:

You can enter up to three different tags in your criteria. This is where if you have a specific type of meeting you can use the tags to help identify which items you want to populate in your meeting list. Of course, this assumes you entered the tag in the action item to begin with. If you do want to include everything, leave the tags blank and just click Done

When you pull up a new meeting it will only recall the most recent comment. Any items that show the completion at 100% will not populate the meeting items.

Any comments you enter now in the Latest Update field will add to the existing comments.

If you have items that are completed and don’t want to see them on the individual department tabs, you can click on the Archive Items button and that will move the items into the Archive tab.

Download the Action Items Template

Download link

Like this template? Give us a like on Facebook and be sure to check out our other templates here

Protect Your Sensitive Excel Data with This Template and Add-in

May 4, 2018

by admin with No Comment add-ins template

One of the challenges sometimes with sending your spreadsheet to someone, whether it’s to review or to make changes to it, is that it contains sensitive information.

So I’ve created a template and add-in that will help you accomplish that.

You can download the template here or if you prefer, the add-in is available here.
Disclaimer: I do not offer any guarantees or promises that this will work perfectly and it’s the responsibility of the user to ensure and confirm that all data is adequately protected as the spreadsheet still requires manual steps from the user.

The template will look like a regular spreadsheet, but with one main difference. On the far right end of the Home tab, you’ll see this button:

If you select the data you want to encrypt, whether it’s a few cells or an entire column, then clicking this button will mask your data. Here’s how it works.

In my spreadsheet, in column A, I have a list of customer names that I want to protect.

If I highlight the range and click on the Encrypt Selection button, what will happen is it will mask the data and create a new tab showing me which customer is mapped to which mask:

After all, masking the data is great, but if you don’t know what the data means, it’s not very helpful. The macro creates a tab called “Mapping Hidden Cells” and it’ll give you a popup warning you to delete this tab before you send the data to anyone. The purpose of this tab is to give you the table, and you can either make note of it or save it somewhere. Either way, you’ll want to delete this afterward, otherwise it would make masking the data pointless.

If I return to my main tab I notice that the data is now masked:

The customer names are now nowhere to be found. At this time I’d like to point out that the masking will only happen to the cells that you selected. If for example I had customer data in a different area and didn’t select it, that data would not have been masked.

This is why it is crucial for the user to review the data themselves and validate that the sensitive information has in fact been correctly masked, and that the mapping tab has been deleted.

Cash Flow Forecast Template

June 14, 2017

by admin with No Comment cashflow forecasting template vbafree

Download Template

This template allows you to monitor and forecast out cash flows for a specified number of days. The current date defaults to today’s date but you can override it manually but if you do the formula will be gone.

You can also change the number of days you want to look in advance. For instance, you may only want to look at the cash you expect to have available for the next 7 days, 14, or however long you want.

First you will want to populate the current balance for each of the accounts. Right now they are hard-coded cells but you can certainly add formulas to populate this. The input section is on the second page of the Summary tab (scroll to the right if you do not see it on your screen)

The cells above in yellow are ones you can edit. The ones in grey are formulas and need to remain the same as they are used in the chart.

There are three main sections in the chart:
– Funds Available
– Upcoming Transactions
– Outstanding Checks

Funds Available is simply a formula to show what cash on hand is expected at the end of the forecasted days. It looks at the current bank balance, deducts upcoming transactions, as well as the current outstanding checks. A positive number indicates the account will have cash remaining at the end of the period. A negative amount indicates that not enough cash is in the account to accommodate all the upcoming expenses and checks to be cashed.

Upcoming Transactions are populated from the Recurring Transactions tab.

You can specify if a recurring transaction recurs monthly or annually. Based on this, along with today’s date, it will calculate the next occurrence of the transaction.

Further down on the Summary tab you can see a breakdown of the largest upcoming expenses on the left-hand side for all the banks and bank-specific transactions on the right-hand side. The yellow cell indicated below can be toggled to another bank and you will see transactions just for that bank. Both of these tables will only show expenses that fall within the date range you specified (e.g. if you specify only the next 7 days, it will only show expenses up until that date).

summary of upcoming and recurring transactions

The Outstanding Checks are fueled by the individual bank tabs. Each tab allows you to list any checks you have outstanding along with their amounts. Note that if you change any of the bank names on the input section you will also have to rename the tab. If the tab name does not match the bank name, the checks outstanding will not populate.

Mortgage Calculator – Calculate House Price, Monthly Payment, and Gain or Loss

May 31, 2017

by admin with No Comment template

Download template

This mortgage calculator will allow you to accomplish three different things:

1) Determine the housing price based on a desired monthly payment, interest rate, and terms.
2) Determine the monthly payment based on the price of a house, interest rate, terms, and down payment
3) Calculate the mortgage balance remaining and calculate a gain or loss depending on the selling price you enter.

To select which calculation you are after selecting the option from the drop-down under the ‘Calculate’ header:

All of the inputs are in yellow cells and can all be changed. Please note that since some of the same fields are used in the different calculators, if you input figures in one cell and change the calculator you will notice the fields won’t reset to 0 but will carry the figures you entered earlier. So you will need to clear or change the amounts in those fields when that happens.

Calculate an Affordable Housing Price

I’ll start with the first example, the Housing Price. In this instance, you want to determine the house price you can afford based on the annual interest rate, term, and how much you want to pay per month. Once you enter your inputs it will tell you the house price that you can afford and if you need additional funds for your downpayment – based on your desired downpayment %

In the above example, I wanted to know what price I can afford if I wanted to pay $2,000 a month at a 4% annual interest rate for 30 years. The result was $523,653.10. And since I entered my desired downpayment % as well as how much I had available, it lets me know any amounts I am short for the downpayment. For a house costing $523,653.10, a 20% downpayment would be $104,730.62. Since I put that I have $100,000 available, it tells me that I need an additional $4,730.62 to meet the 20% downpayment.

Calculate the Monthly Mortgage Payment

Next, I will switch over to calculate my monthly payment. In this situation, I specify the house price I want to buy, the amount of the down payment, interest rate, and years

In this example I set my house price to $500,000; downpayment to $50,000; term again 30 years and interest rate also still 4%. This calculates my monthly payment to be $2,148.37.

Calculate the Gain or Loss on a Sale Price

In my last example, Gain/Loss on Sale has the most variables since it takes into account the price, down payment, monthly payment, the start of the mortgage, selling date, price, and estimated costs. This would be if you wanted to gauge whether you might be looking at a loss or a profit based on the variables entered.

If you’ve been entering these amounts in the previous calculators you will notice some values have carried over, so always be careful to clear the fields first or at least double check the inputs or you may get a result you did not expect.

mortgage calculator gain or loss on sale

For my inputs here I have entered similar values to my earlier examples and now I am calculating whether I will have a gain on the sale if I sell it for $550,000. After commission costs of 20,500 plus closing costs of 11,000 (550,000 x 2%), plus the mortgage balance that remains of 404,966.58, that will leave me with proceeds from the sale of $113,533.42. The gain or loss in this template looks at whether I am walking away with more money than my original downpayment or less. Since my downpayment was $104,730.62, it is a gain on the sale since I am taking more than what I originally put into the house.

These calculators should only be used for estimating purposes and shouldn’t be intended to calculate with 100% accuracy any tax liabilities or other costs. Housing rules vary widely from one region to another so it would be very difficult to factor in every variable. Even mortgage penalties among banks vary in calculation so for the sake of simplicity those variables are taken out of the equation, however, you can estimate a closing cost % and if you want to be conservative you can adjust this % to help account for these variables as you see fit.

Amortization Schedule and Summary Template

May 14, 2017

by admin with One Comment template vbafree

Download Template

This template allows you to track multiple amortization / depreciation schedules and summarize them all in one tab. Above is a completed schedule with the inputs (highlighted in grey at the top) filled in. As you can see the last payment will also take care of any balloon payment required. I have made 10 different tabs but you can copy additional ones or delete ones you do not need. Each schedule accommodates up to 1,000 payments by default and assumes monthly payment intervals however this can be adjusted.

In the sample file I have three different amortization schedules. If I go to the Summary tab I see the following break:

The tab names must match to what is written in the Tab Name field otherwise they will not pull correctly. If you update the Current Month field (just enter a date value, do not enter text even though it looks like text) the formulas will update and show you what your balance currently is, how much interest has been paid to date, and how many payments are still remaining. The benefit of this template is if you are managing many different amortization schedules you can get a snapshot of all of them in one tab.

accounts receivable template visual basic excel

Accounts Receivable Statement – Template Using VBA

May 3, 2017

by admin with No Comment accounts receivable template vba

Download Template

This is an updated version of a prior post that generated a statement without VBA, but the limitation there was you had to save the pdf yourself. This updated version allows you to do the following:

– Save the current statement to PDF
– Cycle through all your customers and save all their statements to PDF
– Generate an email (but not send) to the customer with the attached statement.

It is important to note the customer name on the invoice data needs to match the name on the customer data tab otherwise the invoices will not pull on the statement correctly.

The template works in exactly the same way as the prior version – invoice data and customer data needs to be downloaded from your accounting software. You can customize your statement and include any images you like so that it will look consistent for every statement that you send out.

This template is setup to accommodate up to 150 invoices.