Quick Excel Tips

Most of these quick excel tips apply to nearly any version of Excel.

However, because of the changes to the user interface in newer versions of Excel, explanations that say how to get to something via a menu (such as how to get to print and page setup options) may be different for different versions

Data Entry / Editing

  1. [Essential] Learn to use the combination of CTRL, ALT, and SHIFT along with the Arrow Keys to navigate and select text.
  2. [Essential] Press F2 to edit the currently selected cell (will place the cursor at the end of the text/formula)
  3. [Essential] Insert a Line Break inside a cell by pressing Alt+Enter. On a Mac: Ctrl+Option+Return
  4. [Essential] Press ESC while you are editing the text or formula in a cell to exit the cell and cancel any changes that you may have made.
  5. [Essential] Learn and use other important keyboard shortcuts. See this article for a list of my favorites and to download a 1-page reference sheet.
  6. [Very Handy] Use Ctrl+Enter to quickly fill a range of cells with a value or formula after entering a value in one of the cells.
  7. [Very Handy] Use Ctrl+d to copy the cell/row immediately above the selected cell/row. Copies formatting also. Use Ctrl+r to copy the cell immediately to the left.
  8. [Very Handy] To quickly fill a range with 4,5,6,7… enter the 4 in the first cell and then hold down CTRL as you drag the fill handle. Watch How to Use AutoFill in Excel
  9. [Handy] To quickly fill a range with a pattern such as 10,20,30,40… enter the 10 and the 20 then select both and drag the fill handle.
  10. [Handy] Shortcut to Copy the Current Row and Insert the Copied Row above it: SHIFT+SPACE then CTRL+c then CTRL+SHIFT++.
  11. [Handy] Use the keyboard shortcut combination F2 then CTRL+SHIFT+HOME to quickly select an entire formula within a cell.
  12. [Handy] Press Ctrl+; to quickly enter today’s date in a cell.
  13. [Handy] Press Ctrl+Colon (Ctrl+Shift+;) to quickly enter the current time in a cell.
  14. [Handy] Fast Fill/Copy: Double-click the fill handle (the little “square”) of a selected cell to fill (copy) down the same number of rows as the adjacent column.
  15. [Handy] To split data in a cell into multiple columns, go to Data > Text to Columns. Handy for lists of names, comma-delimited data, etc.
  16. [Handy] Right-Click and Drag on the border of a selected cell or range to open a hidden menu that lets you paste values. (Learned that on ExcelCampus.com)
  17. [Handy] Press F2 prior to using arrow keys when editing a formula in a dialog box such as a conditional formatting rule (to avoid inserting references).
  18. [Tricky] A menu of fill options will show up after using the Right mouse button to drag a Fill Handle (instead of the Left mouse button)
  19. [Tricky] After pressing Enter in Excel, you will typically move down one cell. Go to File > Options > Advanced to move in a direction other than down. Or, select a horizontal range of cells first and see what happens when you press Enter.
  20. [Tricky] To enter a value as a fraction like 3/4 without having it autoformatted as a date, include a leading zero and a space (0 3/4)
  21. [Info] If you see “######” in a cell, it means the column is too narrow to display the value.
  22. [Info] Inserting a new row will copy the formatting from the previous row (as well as sparklines and maybe other stuff) unless you first copy a blank row from elsewhere in your spreadsheet and insert the copied row instead. Undo automatic formatting after inserting a row by selecting “Clear Formatting” from the paintbrush icon that appears next to the row.

Printing Tips

  1. [Essential] Choose a specific range of cells to print by setting the Print Area … more info + video demo
  2. [Essential] Fit a worksheet to one-page wide by setting the Print Scaling and leaving the number of pages tall blank … more info + video demo
  3. [Handy] Print a chart object (such as a timeline) across multiple pages by selecting the cells surrounding the chart rather than the chart object itself.
  4. [Handy] Switch to Page Break Preview via the View menu to change the position of the page breaks in your worksheet.
  5. [Handy] Have a data table spanning multiple pages? Print the header rows on all pages by going to Page Layout > Page Setup > Sheet tab > Rows to Repeat at Top
  6. [Handy] Print a square grid in Excel: Ctrl+a, Arial 10pt font, Column width = 1.71, Row Height = 12.75 …download grid templates
  7. [Handy] Excel can do headers and footers just like Word, including auto page numbering and dates. Go to Page Layout > Page Setup to set them up.
  8. [Handy] If a portion of worksheet is not printing, you may need to redefine the print area via Page Layout > Print Area.
  9. [Tricky] Update the print settings on multiple worksheets at the same time by selecting multiple tabs (hold down Ctrl as you click on the tabs) and go to Page Layout > Page Setup.
  10. [Advanced] A Print Area is actually a Named Range, so you can create a Dynamic Print Area using the OFFSET function (see the article “Dynamic Named Ranges in Excel“).

Options and Customization

  1. [Essential] Get rid of the grid! In newer versions of Excel, go to the View tab and uncheck Gridlines. In Excel 2003, go to Tools > Options > View tab and uncheck Gridlines.
  2. [Handy] To change the default number of sheets in a new workbook, go to File > Options > General and change the “Include this many sheets” setting
  3. [Cool] To change the background color of a worksheet tab, right-click on the tab and select Tab Color.
  4. [Tricky] Tired of seeing the green triangles in Excel? You can turn off specific rules/warnings via File > Options > Formulas (or Error Checking in older versions).
  5. [Tricky] Before keying data into a table, highlight the range of cells to edit. Then use the enter key to quickly move through just the highlighted region.

Navigation and Selection

  1. [Very Handy!] Use Ctrl+F to search a worksheet for a value. If you have a range selected, it will search only that range. Handy for replacing values as well.
  2. [Very Handy!] Use Ctrl+Arrow to jump between edges of occupied ranges. Add the Shift button to highlight cells as you go.
  3. [Very Handy!] Use Ctrl+G > Special to do things like Select all cells with comments, Select all cells containing formulas, etc.
  4. [Very Handy] Use SHIFT+Space to select the current Row and CTRL+Space to select the current Column. Remember which is which by noting that both “Ctrl” and “Column” start with the letter “C”.
  5. [Handy] Navigate to and Select a named range by pressing Ctrl+G to list the named ranges. Click on the name you want to select and press OK. Useful for selecting the current Print Area.
  6. [Handy] Move between tabs (worksheets) in Excel using Ctrl+PgUp and Ctrl+PgDn. In Google Sheets, use Ctrl+Shift+PgUp and Ctrl+Shift+PgDn.
  7. [Handy] Move to a specific worksheet tab by right-clicking on the arrows to the left of the tabs and selecting from the list.
  8. [Handy] Ctrl+Home takes you to cell A1. On a MacBook it’s Fn+Cmd+LeftArrow
  9. [Advanced] Create a bookmark by naming a cell. Select a cell to mark as a bookmark location and enter a name such as “bm_1” in the Name Box (or by going to Formulas > Define Name). Create a hyperlink in a cell or select a shape object to turn it into a button by pressing Ctrl+k. Select “Place in This Document” and then find and select the bookmark under Defined Names.
  10. [Info] In a protected worksheet, press Tab to navigate between input cells.

General

  1. [Essential] Right-click on everything! Contextual menus pop up with options that are unique to the thing you clicked on. When there is no right mouse button … Phone: Tap-and-hold (long press); Trackpad: Two-finger click; Mac: Ctrl+click.
  2. [Essential] Use Split screen and Freeze Panes (via the View menu) so that you can keep a portion of your worksheet visible as you scroll and edit another part of your worksheet.
  3. [Very Handy] For a quick sum or count of selected cells, look at the bottom of the Excel window in the status bar (see an example).
  4. [Handy] Make a copy of a worksheet by holding Ctrl as you click and drag the worksheet tab you want to copy.
  5. Turn on/off automatic recalculation via Formulas > Calculation Options. (In Excel 2003: Tools > Options > Calculation). To manually recalculate press F9, Shift+F9, or Ctrl+Alt+F9.
  6. Press Ctrl+Alt+F9 to manually force a full recalculation of your workbook. This may be needed when using functions like XIRR().
  7. Pressing F9 will cause volatile functions like RAND() to recalculate. This can create interesting chart effects (see an example)
  8. A great way to learn new techniques in Excel is by dissecting templates. Remember to check for named ranges and conditional formatting rules.
  9. [Advanced] Use the built-in Macro Recorder to record simple sequences of actions and then look at the resulting VBA code (this is a great way to learn VBA).
  10. [Advanced] If you work with large tables of data and you don’t know what Pivot tables are, find out! They are powerful.
  11. [Info] Dates in Excel are stored as serial numbers starting from 1 = 1/1/1900. The value for Jan 1, 2009 is 39814.
  12. [Info] Times in Excel are stored as decimal values representing a fraction of a day. For example 0.5 is 12:00 PM (noon) and 0.25 is 6:00 AM.

Quck Excel Tips : Formulas

  1. [Essential] Use F4 to toggle a reference between A1, $A$1, A$1 and $A1 while editing a formula (When NOT editing a formula, F4 is an alternative to Ctrl+Y which is “repeat” or “redo”)
  2. [Important] Excel performs Negation before Exponentiation, so watch out for errors in formulas like =–x^2 or =5/(–x^2). Use =-(x^2) if you want to perform the exponentiation first.
  3. [Handy] Do you use Names for cells and regions? Press F3 when entering formulas to pull up a list of defined names and drop one in the formula.
  4. [Handy]You can quickly name cells and ranges by typing a name in the reference box directly to the left of the formula bar. You can’t name something n1, n2, or n3 because those are cell references. Use n_1, n_2, n_3 instead.
  5. [Handy] Join text from multiple cells using the “&” operator: =A1&” “&B1. This works well for combining first and last names.
  6. [Handy] Tired of toggling between sheets and workbooks to see how inputs affect outputs? Use excel’s Watch Window to keep an eye on specific cells.
  7. [Handy] Having a hard time with a complex calculation or finding a bug? Try using the Audit Formula feature to quickly highlight interdependencies.
  8. [Handy] Use COUNTIF() to count the number of data points between two values …see article
  9. [Cool] Use Named Ranges if you want your formulas to use natural language or variable names rather than cell references (e.g. =m*x+b)
  10. [Tricky] Use OFFSET(ref,-1,0) to refer to the cell immediately above. Useful for a running balance that lets you more easily insert and delete rows.
  11. [Tricky] Enter a Line Break within a cell using a formula: =”abc”&CHAR(10)&”def” (then set the Wrap Text property)
  12. [Tricky] Start entering a formula and after typing a function name like =INDEX press CTRL+SHIFT+a and see what happens (Excel will add argument placeholders to your function).
  13. [Advanced] There is no SUMPRODUCTIF function, but you can conditionally sum products using =SUMPRODUCT(–(cond_range=”x”),range1,range2)).
  14. [Advanced] Need to round to a specific number of significant figures? Try this formula …see article
  15. To copy a formula without changing references, you can first convert it to text by adding an apostrophe before the equal sign: ‘=
  16. [Advanced] Use a Dynamic Named Range to reference a list that expands or contracts based on the amount of data in it …see how
  17. [Advanced] Arrays: When using an Array Formula, you press Ctrl+Shift+Enter instead of just Enter after entering or editing the formula. Identify an array formula by checking the formula bar – you’ll see curly braces around the formula like this: {=theformula}
  18. [Advanced] Arrays: Array constants (arrays that are “hard-coded” into formulas) are enclosed in braces and use commas to separate columns and semi-colons to separate rows like this 2(row)x3(column) array: {1,1,1;2,2,2}
  19. [Advanced] Arrays: Check out sample array formulas for doing things like multiple linear regression and creating sequential number arrays …see article
  20. [Example] Calculate the Day of the Year (1-366) in #Excel for a given date using =theDate-DATE(YEAR(theDate),1,0). Return the Date value for a given Year and Day of the Year (1-366) using =DATE(theYear,1,dayOfYear)

Formatting

  1. [Essential] Press Ctrl+1 (that’s a “one” not an “L”) to open the Format Cells dialog window for easy access to all the cell formatting options.
  2. [Very Handy] When Excel automatically formats a number as a date, you can change it back to a number using the shortcut CTRL+SHIFT+~. The tilde (~) is usually in the upper left of your keyboard.
  3. [Handy] Press Ctrl+E to use Flash Fill for quickly reformatting lists. Watch How to Use Flash Fill on our YouTube channel.
  4. [Handy] Make multiple columns the same width by selecting each of the columns and then changing the width of just one of them. This applies to rows as well.
  5. [Handy] Double-click on the column or row sizing handle (the line between the column letters or row numbers) to auto-size the column or row to the longest entry (some exceptions with wrapped cells).
  6. [Handy] Undo automatic formatting after inserting a row by selecting “Clear Formatting” from the paintbrush icon that appears next to the row
  7. [Info] If you see weird or magical stuff going on with formatting, it may be due to conditional formatting. Or, it could be that Excel is applying formatting automatically (which it does sometimes because it thinks it’s so smart).
  8. [Handy] Trying to get a title centered across a table? Use the Center Across Selection format rather than one large merged cell.
  9. [Handy] Apply text formats to part of a cell by highlighting that portion in the formula bar before applying the formatting. (This is how you can do stuff like displaying H2O, where the 2 is a subscript)
  10. [Handy] Use the “Shrink to Fit” cell format option to avoid showing ##### in a cell containing a date.
  11. [Cool] Conditional Formatting: Highlight odd numbered rows with this formula: =MOD(ROW(),2)=1
  12. [Advanced] Create a Custom Number Format to display values using special formats
  13. Custom Number Formats – The custom date format [h]:mm can be used to display times that are greater than 24 hours, like 42:36.
  14. Custom Number Formats – Display numbers as fractions using the format code # ??/100 to display 5.2 as 5 20/100 and ?/2 to display 5.2 as 10/2 (note the automatic rounding)
  15. Custom Number Formats – Display feet and inches as 8 3/12 using the format code # ??/12 (rounded to the nearest inch)
  16. Custom Number Formats – Display temperature with the degrees symbol using the format code: #.##”°”
  17. Custom Number Formats – Display “kg” units (or other labels) within a cell without causing the value to convert to text using a format code like #.## “kg”
  18. Custom Number Formats – Display a number with leading zeros using a format code like 00000 to display 345 as 00345
  19. Custom Number Formats – Add a carriage return within a custom number format by pressing Ctrl+j
  20. Custom Number Formats – Display 23576 as 23.6K using the format code 0.0,”K”
  21. Custom Number Formats – Display 23,576,000 as 23.6M using the format code 0.0,,”M”

Special Features

  1. [Cool] Create a Drop-Down List within a Cell using Data Validation. The list can be on a different worksheet …see how
  2. [Handy] Named Ranges as Bookmarks: You can create a bookmark within a large spreadsheet by naming a cell. Then use CTRL+G to quickly navigate to that cell. You can create a hyperlink to navigate to a bookmark (press CTRL+K and click on Bookmark).
  3. [Handy] Autoshapes as Navigation Buttons: You can use an autoshape to create a button and then apply a hyperlink to that button to link to a bookmark, another worksheet, or even an external web page.
  4. [Handy] Named Constants: You can create a Name for a constant or formula without actually referencing a range. Go to Insert > Name > Define (Excel 2003) or Formulas > Name Manager (Excel 2010).
  5. [Handy] What-If Scenarios: If you have a mathematical model, consider using Scenarios to store and analyze different what-if scenarios. (go to Tools > Scenarios in Excel 2003 or Data > What-If Analysis > Scenario Manager in Excel 2010)
  6. [Handy] Grouping and Outlining: You can use the Data > Group and Outline feature in Excel to expand and contract groups of rows and columns …see an example.
  7. [Handy] Insert a ✔ by pressing +. to open the Windows 10 Emoji Panel or by going to Insert > Symbol and changing the font to “Segoe UI Symbol”. Watch How to Insert a Check Mark in Excel on youtube.
  8. [Tricky] Add an AutoCorrect Exception to prevent “MPa” from changing to “Mpa” (Tools > AutoCorrect Options in Excel 2003 or File > Options > Proofing in Excel 2010)
  9. [Tricky] Insert special symbols like °, ², ∂ and µ quickly by adding custom Autocorrect entries. Example: replace (^2) with ² …see article … Unicode Characters
  10. [Handy] Goal Seek: Set an output (calculated) cell to a specific value by changing an input cell automatically using Excel’s Goal Seek feature. …see an example
  11. [Tricky] Remove Duplicates from a list by selecting the cells then go to Data > Filter > Advanced Filter, and check Unique Records Only.
  12. [Advanced] Show the Developer tab in Excel 2016: Go to File > Options > Customize Ribbon and select the “Developer” tab option
  13. [Advanced] Solver Add-In: In Excel 2010+, enable the Solver Add-In by going to Developer > Excel Add-Ins. Solver will show up in the Data ribbon. In Excel 2003 go to Tools > Add-Ins, the Solver will show up under Tools. …See Excel Solver examples
  14. [Advanced] Customizable Drop-Down Lists: Use data validation lists that reference named ranges to create lists that you can customize easily …see article
  15. [Tricky] Named Ranges: If you set the Zoom to 39% or less, Named Ranges consisting of 2 or more adjacent cells will be shown outlined.
  16. [Tricky] Pictures in Comments: You can display a picture as the background in a comment (Format Comment > Colors and Lines > Fill – Color > Fill Effects > Picture)

Charts and Graphics, quick excel tips

  1. [Handy] – Your ability to compare areas isn’t as good as your ability to compare lengths, so use bar graphs instead of pie graphs if possible.
  2. [Handy] – To align objects to the corners of the grid in Excel, hold down the ALT key as you draw, move, or resize objects.
  3. [Handy] – If you have a hard time selecting a series or object in an Excel chart, try using the drop-down box in the Chart toolbar.
  4. [Handy] – Link text in chart titles, axis labels, data labels, and text boxes to a cell by entering =A1 in the formula bar after selecting the object.
  5. [Cool] – Create a timeline using an X-Y chart, data labels, and vertical error bars.
  6. [Cool] – Create a Dot Plot or Dot Chart in Excel using the REPT() formula …see how
  7. Jon Peltier: Jon’s awesome collection of Excel Charts and Tutorials
  8. Charley Kyd: How to Create Bullet Graphs in Excel to Replace Gauges
  9. John Walkenbach: How to handle missing data in a line chart using =NA()
  10. John Walkenbach: Chart Templates

International Stuff

  1. [Essential] Change the Currency symbol from $ to £ or something else by opening the Format Cells window (Ctrl+1) and going to the Number tab. …see video demo
  2. [Handy] In Excel 2010 you can use WORKDAY.INTL and NETWORKDAYS.INTL for date calculations that exclude specific days of the week (instead of Saturday and Sunday).
  3. [Important] Whether you should enter a day as m/d/yy or d/m/yy depends on your computer’s system settings.
  4. [Handy] Use the built-in “*3/14/2001″ date format or the “*1:30:55 PM” time format to display dates based on your computer’s system setting. The “*” is what identifies the number formats that use your system’s settings.

Spreadsheet Compatibility Tips

  1. [Info] Avoid Vertical Oriented Text … Vertical text is not compatible with Google Docs or Excel Web App
  2. [Info] OpenOffice Compatibility: When using the INDEX function in Excel, use the correct syntax INDEX(array,row,column). Don’t use the shortcut INDEX(array,column).
  3. [Info] OpenOffice Compatibility: In Excel arrays, make sure that you are using “;” for row separators and “,” for column separators
  4. [Info] OpenOffice Compatibility: In Excel use cell references rather than named ranges for the linked cells in form field controls

Hidden Features

  1. The EVALUATE function: Trick for evaluating text as a formula …see article
  2. Use the DATEDIF function to find the number of years, months, or days between two dates. See “Calculate Age in Excel” for examples.

More Excel Tips and Articles by Vertex42

Vertex 42 Review

Vertex 42 Review:

Professional Spreadsheet Templates That Work

August 26, 2021 

If you are like me, and almost 80% of the people that need to use excel, and every time you open excel you are clueless and stuck whenever you attempt to use it?  You don’t even use 10% of its capabilities, but the most important thing is that if there is a spreadsheet you need, for hundreds of different topics, chances are vertex42 already has it available for you.

Maybe you have been planning on tracking your finances or managing your small start-up business. Maybe you need a Gantt chart, a Calendar, or a financial calculator, well guess what, they have them and they are FREE

You realize that the pen and paper combo just doesn’t seem to work anymore, and sticky notes are just annoying clutter in your workspace. You want to use excel, but you have no idea how to use all of the features, and you don’t have the time or just can’t learn how to do them, so What do you do?

Well just stick around, you will be glad you did.

Introducing Vertex42?

Vertex42 is a website where you can find hundreds of newbie-friendly spreadsheet templates designed for a variety of uses.

It was founded in 2003 by brothers Jon Wittwer, a mechanical engineer, and Jim Wittwer, a graphic designer.

Dr. Jon Wittwer was incredibly obsessed with Excel while he was still pursuing his Ph.D. in mechanical engineering. He was known on the campus as the excel guy (how cute!) One day, he decided to dive into online business and put up the website.

His brother, Jim Wittwer, is a graphic designer by profession and helped mainly by designing the website for the excel guy. He is known to be fond of spreadsheets as well.

Together, they merged their love for organization and Excel and have come up with Vertex42.

The Wittwer brothers have been making life easier for excel newbies by providing free spreadsheet templates for managing time, personal finances, education, and careers.

Can you use Vertex42?

Yes, if you have a computer with excel or google pages and you need a spreadsheet, then you must check them out, again they have spreadsheets for uses you haven’t even dream about, and again they are very easy to use, they have done the hard work for you. Almost anyone from all stages of life in all kinds of the industry will most likely find a spreadsheet template for their personal use.

Students

Classes are overloaded, home works and projects are piling up, and stress is shooting through the roof.

What could be the best way to manage all these than to just go ahead and download a Homework Planner? Perhaps Class Time Sheet?

Teachers-Managers

Teachers and managers can easily download templates for managing attendance, lesson planning, etc. which can assist them in organizing an otherwise chaotic scene.

Small Business Owners

Managing a business can become really stressful, especially if it is a one-man team. But it doesn’t have to be that way!

Templates from the site include an invoice, timesheets & payroll, financial statements, budgets, calculators, project management, inventory, data analysis, schedules, general planner, etc.

 All of which are helpful tools in running a successful business, right from start-up!

Home use

With helpful spreadsheets which they can download from the site, you can easily keep track of important things. From the simplest grocery list to the most complicated home mortgage, find everything home and family-related spreadsheet!

 Also find an entire section for health charts and logs, which includes food logs and meal planners;

perfect for health-conscious parents who want the best for their children.

Vertex42 Main Advantages

1. User-Friendly Website

Of course, the website itself is as organized as its goals. You can find a couple of categories on the tabs such as Excel templates, Calendars, Calculators, Gantt Chart, Word Templates, Education, Apps, and Blog.

These tabs have drop-down menus that you can access by hovering over them. You will find more options under each one that will direct you to collections of downloadable spreadsheets same time and increase your productivity! 

2. Updated Printable Calendars

Under the Calendars tab, you will find calendars with different designs that are also theme-enabled, so you can freely adjust them to your preferences once you have downloaded them.

There are yearly and monthly calendars available as well, with portrait and landscape options. You can even find ink-friendly calendars! ink is expensive!, you dont have to spend your entire ink cartridge to print one of those

Aside from the ones mentioned above, there are also weekly and daily planners up for grabs. Both planner styles are available for the current year and a perpetual ones.

3. Gantt Chart Templates

Developed by Henry Gantt, the Gantt Chart is a tool designed for project management in the early 1900s. It includes organized categories of tasks and project deliverables that are accompanied by bar charts to show the start and end times for each specified task.

4. Template Support and FAQ

On this page, we can find a quick tutorial on how to use Excel and how to go about using it. There are also links to other pages provided, with an in-depth explanation of certain topics including the basics. It includes outward links to how to open and save files, how to enter and edit data, how to format cells and text, etc.

There is also a section on this page that answers questions about specific templates found on the site. Say goodbye to your Excel confusion, as you find this page full of information. And if you haven’t found the answer you’re looking for, they have a Contact Us page…

5. Contact Us Page & Consulting Requests

If you aren’t convinced yet that these guys take Excel seriously, you might want to check out their Contact Us page. There, you can find their social media handles, plus the founder’s e-mail address where you can reach out to him personally. He replies within one business day if not sooner as the page says, as long as it is a legitimate non-spam e-mail. Don’t be wasting the Excel guy’s time!

And if that’s not enough, you can actually contact a Vertex42 Approved Consultant if you are on a more complex project that needs excel assistance. This site really is something.

6. Excel Tutorials for Beginners

Aside from the quick tutorial found on the Support page, there is also a whole page educating students, particularly middle and high school students, on how to properly and effectively use Excel.

The page includes video instructions with fun narrations, downloadable transcripts for each video, and also the very basic terminologies in the world of Excel. This is a very helpful skill to have as a student, and it is openly and freely available to access anytime!

Why trying to invent the wheel regarding spreadsheets

Now that you have learned all that Vertex42 can do for you, go and head on to their website. 

Find the spreadsheet template that you so badly needed, download it, and get organized! Be the most productive you could ever be. I am sure you will be coming for more, and more spreadsheets as you understand how amazing this site is, and how much time and headache will save you.

The templates are free for download, so you don’t have to buy anything if you just need small and simple templates. Don’t waste your time Googling things about Excel. Stop looking for reviews someplace else.

Stop! you know that there is a spreadsheet for that!

Ok, first a small introduction, assuming you dont know what an excel spreadsheet is :

An excel spreadsheet template is a setup configuration that allows a user to feed data to predetermined cells. Usually, you can enter the results into the cells and use formulas pre-coded into the program to calculate and analyze your data. For this reason, many businesses use excel spreadsheets to keep records of their company and their employees.

What can you use spreadsheets for?

Ever since its creation in 1985, Excel has grown into an international workplace tool. And in business, you can do just about anything with it. Besides, it is a powerful tool that allows companies to: analyze data, schedule tasks, budget, and connect with clients, among others.

To understand the uses of these excel spreadsheets, you need to understand the different template categories.

  1. Spreadshet template for Business

Excel spreadsheet templates play a massive role in business. This is because, whether large or small, all companies have to deal with a great deal of information. Therefore, the best way to organize this data is using a program that allows for managing and analyzing said data. Whether it’s a monthly expense spreadsheet or a spreadsheet template for expenses, you will need an effective way to keep track of your business dealings.

Other uses of spreadsheet templates in businesses include the creation of questionnaires, data entry, storage, and accounts and calculations. Scheduling and administrative tasks are also organized, thanks to excel spreadsheet templates.

Also, the tool allows you to prepare marketing tasks, payroll management, and reports with ease. Examples of such templates include the small business petty cash log, weekly time record of small business, and profit and loss statement templates.

  1. Spreadsheet Template for Personal use

You can also use excel spreadsheet templates for personal reasons. A google spreadsheet workout template is an excellent example of such. Also, if you want to keep track of your bills and personal finances, a spreadsheet template for accounts and personal finances comes in handy.

There are countless ways in which you can use a spreadsheet template for personal reasons. Whether you plan to work out, prepare for your schedule and personal business, keep track of your expenses and savings, manage a project you took on, or plan meals, then you can use excel templates for any of the above reasons.

Examples of such templates include the personal monthly budget, weekly chore schedule, and loan calculator.

  1. Planners and trackers

Working without a plan rarely achieves the wanted results. Businesses understand this, and that is why they invest in human resource officers and shift supervisors. Even the meals served are planned beforehand. To keep a clean and clear record of all these events, you need a tool that takes up the role of planners and trackers in your organization.

Excel spreadsheet templates like the meal plan template, google sheets, and the attendance sheet template allow the people in charge to record what you do and when you do it.

Examples include the sales invoice tracker, the loan amortization schedule, and the Gantt project planner.

  1. Lists

People find a way to come up with countless lists in their lifetime. And businesses are no different. You need to keep a list of your expenses, salary payouts, clients, suppliers, events, donations, and so forth. Relying on human memory to remember all that is quite an overshoot.

That is why spreadsheets like the goodwill donation spreadsheet template 2020 exist. Therefore, you can keep track of your event logs with ease.

Such lists include weekly attendance reports, inventory lists, bill-paying checklists, and a service price list.

  1. Spreadsheet Template for Budgeting

Budgeting is an essential aspect of businesses. Microsoft understands this and has created spreadsheet templates for bills, and budgeting, aiming to help business owners keep track of their money with ease.

Some budgeting spreadsheet templates include the monthly company budget, a general ledger, and profit and loss statements.

Other spreadsheet templates you can access from Excel include charts and calendars.

of course, we are falling short, there are literally hundreds of different templates, and as our title suggest, we are sure that there is a spreadsheet template for that, so before you start breaking your head, and losing time check out this amazing website, they have everything you might need regarding spreadsheet templates, and yes they have ios and google versions too… check them out here

GPA Calculator

GPA Calculator

Calculate and Track your High School or College GPA Using this Free GPA Calculator for Excel® and Google Sheets
Have you ever wondered, “How much will a B instead of an A affect my GPA?” or something similar? You may execute that type of what-if scenario with the Vertex42TM GPA Calculator spreadsheet, as well as keep track of your grades and cumulative GPA.
You can use Google Sheets or download the GPA Calculator for Excel or OpenOffice. For instructions on how to calculate your GPA, see the section below.

The Vertex42™ GPA Calculator lets you track your grades from semester to semester, calculating both a semester GPA and your overall cumulative GPA.

You can edit the Grade Scale to define the points associated with letter grades, according to your specific school policy.

Then, when you enter the grades into the spreadsheet, the points are automatically totaled and weighted based on the number of credit hours for each course.

 

Using the GPA Calculator

The GPA Calculator spreadsheet should be pretty intuitive, and some instructions are included at the top of the worksheet.

Adding Semesters: In the Course History worksheet, you can include additional semesters by copying the set of rows for one of the semesters and inserting/pasting the set of rows at the bottom. The formulas in the spreadsheet are designed specifically for making this process simple.

Inserting Rows: If you need to insert rows to add more classes for a particular semester, then make sure to insert new rows above the last row or below the first row in the semester block. This will help ensure that the references in the summation formulas stretch to include the new row(s). You will also need to copy the formula for the Points when you add the new rows.

How to Calculate GPA

GPA stands for Grade Point Average, but most of the time, to calculate GPA requires using a weighted average. For example, if you earned an A in a 3-credit course and a B in a 2-credit course, your GPA is not simply the average of an A and B. Instead, the grades are weighted based on the number of credit hours in the course. That can seem complicated, but it’s not really that bad.

Below are the steps you can use to calculate your GPA if you happen to be caught without a computer.

Step 1 – Convert Letter Grades to Points

Letter
Grade
Points
A+ 4.3
A 4
A- 3.7
B+ 3.3
B 3
B- 2.7
C+ 2.3
C 2
C- 1.7
D+ 1.3
D 1
D- 0.7
F 0

To the right is a typical 4.0 GPA Scale, showing the grade points and the letter grade equivalent. The first step in calculating your GPA is to convert the letter grades to points.

Step 2 – Multiply the Points by the Number of Credit Hours

If I got an A- in a 3-credit class, the points for that class would be 3.7 times 3 credits equals 11.1 points. (This is where the weighting comes into play)

Step 3 – Add the Total Points then Divide by the Total Credit Hours

To calculate your GPA, add up the total grade points and the total credit hours and then divide the total grade points by the total credit hours. If you include only the points and hours for a single semester, this will calculate your GPA for the semester. If you include all the points and hours for every semester, this will calculate your overall cumulative GPA.

The figure below shows an example of a GPA calculation for a semester in college.

How to Calculate GPA - Example

Retaking a Course

Many programs only allow a certain number of failing or D grades, or you may need to have a certain GPA to qualify. Sometimes you have no choice but to retake a class, but it can also be one of the fastest ways to increase your overall GPA. The new version of the GPA calculator lets you estimate what effect retaking a class can have on your GPA. It assumes that a retaken course replaces your previous grade.

For example, let’s say that you’ve attempted 50 credit hours and earned a total of 150 points, so your GPA is currently a 3.00. Maybe for some reason you’d failed one of your 3-credit classes. If you were to retake that class and get an A, your new GPA might be a 3.24 (depending on the grading scale). That is a huge jump for just one class! If you didn’t retake that class, it would take 5 more A’s in 3-credit classes to reach a GPA of 3.23.

Time Zone Converter and World Meeting Planner

Time Zone Converter and World Meeting Planner

This meeting planner spreadsheet is designed to assist you in scheduling conference calls and meetings with people from all over the world that are in different time zones.

There are numerous online time zone converter programs available, so why not use Excel?

Especially if you work with specific time zones on a regular basis.

This spreadsheet allows you to create a list of up to seven different destinations (including your own).

After that, you enter their UTC Offsets and select the proper DST Rule for each. After you’ve done that, you can enter your meeting time and compare it to the Time Comparison Table to see if it’ll work.

world meeting planner and time zone converter

How to Use the Meeting Planner

1. Enter the Meeting Date and Time

Important: The first column in the list of locations should be YOUR location. The Start date and time that you enter for the meeting should correspond to this first location.

You don’t enter the end time for the meeting. Instead, the end time is calculated after you enter the Duration (in hours).

To Display the Current Date and TimeEnter the formula =TODAY() in the start date field and =NOW()-TODAY() in the start time field. Then, every time the spreadsheet recalculates, such as after press F9, the dates and clocks will update to show the current time. Pretty awesome!

2. Enter Labels for the Locations

The labels are just labels, so it doesn’t matter what you call each location. You could enter a person’s name if you wanted to.

3. Enter the Standard UTC Offset

The “Standard” UTC Offset is the offset for the location in mid-winter. In other words, “Standard” time refers to when the location is NOT on Daylight Saving Time. You can look up the Standard UTC Offset for your locations using the link to the wikipedia article included in the spreadsheet.

4. Choose a Daylight Saving Time Rule

The complexity of time zone conversion comes from Daylight Saving Time (DST) rules, which are different throughout the world. There are even different rules for locations within the main time zone, such as Arizona.

Each rule defines the dates that DST begins and ends, and if a location is ON DST, then it is assumed that 1 hour is added to the UTC offset. There may be exceptions to that in reality, but they are rare, so I stuck with this assumption for now.

I intentionally tried to keep the spreadsheet as simple as possible, so I didn’t account for the time that DST starts (typically 2:00 am), and the rules only work for a single year (which is why I have a note about the planner not working for meetings spanning multiple years).

The DST rules do not have standard names, so you will need to identify the rule for your locations by looking up information on Wikipedia and choosing the correct rule based on the information and tables in the DST Rules worksheet. I named the rules using some common locations, but you can change the names if you want to.

Using the Time Comparison Table

The table at the bottom of the worksheet helps you see the availability times for people in different locations. You can enter their normal work hours above the table. The cells shaded red are the times that they are NOT available. The lighter shade of red shows when they “might” be available and these areas are currently set as 1 hour before work and 4 hours after work (you can change that).

The Time Comparison Table is rounded to the nearest hour, so even though it highlights your meeting time, note that the shaded time is rounded to the hour.

Why the Analog Clocks?

Analog Clock in Excel
An analog clock in Excel

I included analog clocks mainly because I figured out a way to make the clocks with Excel charts and thought it would be fun to create something like a “World Clock” with Excel. But I think that the Time Comparison Table is more useful for planning meetings.

If you are curious about how to create clocks like this with Excel, see the Clocks worksheet. The clocks use an XY chart with the center of the clock at (0,0). All the elements of the clocks are created using different series and labels and line styles.

Adding More Locations

If you want to add more locations, you’ll probably want to remove the clocks because while it is easy to copy and insert a new column, it isn’t easy to create a new clock.

Compound Interest Calculator for Excel

Compound Interest Calculator for Excel

compound interest calculator    

 

 

Download Here

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Description

This spreadsheet was designed as an educational tool – to help show how compound interest works for both savings and loans. The table is based on the payment frequency and shows the amount of interest added each period. The graph compares the total (cumulative) principal and payments to the balance over time.

One of the worksheets in this file is nearly identical to the online calculator above, and was used to help verify the calculations.

 

Compound Interest Formula

Basic Compound Interest Formula

The basic compound interest formula for calculating a future value is F = P*(1+rate)^nper where

  • F = the future accumulated value
  • P = the principal (starting) amount
  • rate = the interest rate per compounding period
  • nper = the total number of compounding periods

Formula for Compounding Yearly, Monthly, Weekly

Compound Interest Formula for Annual Rate

The formula is often written as F = P*(1+r/n)^(n*t) with the following variables definitions:

  • P = the principal amount (the initial savings or the starting loan amount)
  • r = the nominal annual interest rate in decimal form. (e.g. 5% means r=0.05)
  • n = the number of compound periods per year (e.g. for monthly, n=12)
  • t = the time in years

This is the same as the basic formula where rate = r/n and nper = n*t. Although the math can handle a decimal value for nper, it should usually be a whole number. For example, with monthly compounding for a total of 18 months, n=12 and t=1.5 resulting in nper=12*1.5=18.

Formula for Daily Compounding

Daily Compound Interest Formula

For daily compounding, the value for n (number of compound periods per year) is typically 365 and you use total number of days in place of n*t like this: F = P*(1+r/365)^days. For day count conventions other than n=365, see the wikipedia article.

How to Calculate Compound Interest in Excel

In Excel and Google Sheets, you can use the FV function to calculate a future value using the compound interest formula. The following three examples show how the FV function is related to the basic compound interest formula.

F = P*(1+rate)^nper
F = -FV(rate,nper,,P)
F = FV(rate,nper,,-P)

Example 1: What is the future value of an initial investment of $5,000 that earns 5% compounded annually for 10 years? Answer: F = 5000*(1+0.05)^10 = 8144.47. The Excel formula would be F = -FV(0.05,10,,5000) or F = FV(5%,10,,-5000). In Excel, when you add a percent sign after a number, the number is divided by 100.

Example 2: What is the future value of an initial investment of $5,000 that earns 5% compounded monthly for 10 years? Answer: F = 5000*(1+0.05/12)^(12*10) = 8235.05. The Excel formula would be F = -FV(0.05/12,12*10,,5000) or F = FV(5%/12,12*10,,-5000).

NOTEFor savings calculations, the FV function in Excel can be a bit confusing because if you enter the present value as a positive number, you need to negate the final result. That is because with annuity functions like FV and PV, Excel assumes that cash you pay out, such as your initial savings and deposits to savings, is represented as negative numbers.

The syntax for the FV function in Excel is FV(rate,nper,pmt,[pv],[type]). Excel solves for FV using the following equation (for rate≠0):

Relationship between PV, FV, RATE, NPER in Excel
Fig 1: Formula relating pv, rate, nper, pmt, fv in Excel.

When pmt=0, fv=-pv*(1+rate)^nper, so the variable P used in the standard compound interest formula relates to the Excel formula as P=-pv. Likewise, the variable A (defined below as positive for deposits to savings) relates to pmt as A=-pmt.

Compound Interest Formula for a Series of Payments

Compound Interest Formula for a Series of Payments

For both loans and savings, we typically want to include a series of payments or deposits in our calculation, such as depositing 100 each month for 3 years. The formula for the future value of a uniform series of deposits or payments is F=A(((1+rate)^nper-1)/rate) where

  • A = the payment amount, added to the principal at the end of each period
  • rate = the rate per payment period
  • nper = the number of payments

When the payment period matches the compound period, rate=r/n and nper=n*t.

This formula can be derived from the compound interest formula, based on the fact that the total future value is the sum of each individual payment compounded over the time remaining. If you are interested in the derivation, see Reference [2] at the bottom of this page.

In Excel and Google Sheets, we can use the FV function again. The formulas below show how the FV function relates to the standard formula. Note that for now we aren’t including a principal amount.

F = A*(((1+rate)^nper-1)/rate)
F = -FV(rate,nper,A)
F = FV(rate,nper,-A)

Example 3: If I deposit $1000 at the end of each year, and my investment earns 4% annually, what is the future value at the end of 5 years? Answer: =1000*(((1+0.04)^5-1)/0.04) = 5416.32. The Excel formula would be F = -FV(0.04,5,1000) or F = FV(4%,5,-1000).

NOTEThese formulas assume that the deposits (payments) are made at the end of each compound period. According to Figure 1, this means that type=0 (the default for the FV function). If I wanted to deposit $1000 at the beginning of each year for 5 years, the FV function in Excel allows me to calculate the result as =FV(4%,5,-1000,,1) where type=1. Just remember that the type argument has to do with the timing of the deposits (A), not the principal (P).

Future Value for both Principal and a Series of Payments

When you start with a non-zero principal amount and you make a series of payments, the resulting formula is just the sum of the two formulas described above. The FV function lets you include both the payment amount and the principal as follows:

F = P*(1+rate)^nper + A*(((1+rate)^nper-1)/rate)
F = -FV(rate,nper,A,P)
F = FV(rate,nper,-A,-P)

Example 4: If my starting savings is $4000 and I earn a whopping 6% compounded annually and I deposit $200 at the end of each year, what is the future value at the end of 5 years? Answer: =4000*(1+0.06)^5 + 200*(((1+0.06)^5-1)/0.06) = 6480.32. The Excel formula would be F = -FV(0.06,5,200,4000).

The table below shows how the calculations work each compound period. The table starts with an initial principal of P0=4000. The next rows shows that at the end of the first year, the interest is calculated a i1=rate*P0. The new principal is P1=P0+i1+A. This process continues until the end of year 5, where P5=6480.32 (the same value we calculated with the compound interest formula).

Table Showing Compound Interest Savings Example

Formula for Rate per Payment Period (when Compound Period ≠ Payment Period)

Formula for Rate Per Payment Period

The calculator at the top of the page allows you to choose a compound frequency that is different from the payment frequency. The Rate Per Payment Period is calculated using the formula rate = ((1+r/n)^(n/p))-1 and the total number of periods is nper = p*t where

  • r = the nominal annual interest rate in decimal form
  • n = the number of compound periods per year
  • p = the number of payment periods per year
  • t = the time in years

These values for rate and nper can then be used in the compound interest formulas mentioned above.

A common example where this formula is needed is for a savings account where the interest is compounded daily but deposits are only made monthly. To approximate what the bank is doing, you can use n=365 (Compound Frequency = Daily) and p=12 (Payment Frequency = Monthly).

Another real-world example is the Canadian mortgage where the compounding is semi-annual (2 times per year) and the payments are monthly (12 per year).

 

Compound Interest Formula for Loans

Traditional amortized loans use the same formulas as those defined above for savings, except that the loan amount is represented as a negative value for the starting principal, P. Payment amounts (A) are still positive values.

How to Calculate the Loan Payment

For fully amortized loans, you typically need to calculate the payment amount (A) that will make the Future Value zero (F=0) after a specific number of years. The formula for the payment amount is found by solving for A using the formula from Figure 1. In Excel, you can use the PMT function. Note that if the loan is for $3000, P=-3000).

AF=0 = (-P*(1+rate)^nper) / (((1+rate)^nper - 1)/rate)
AF=0 = PMT(rate,nper,P))

Example 5: If I apply for a loan for $3000 with a rate of 6% compounded annually, what is my payment if I want to pay it off completely in 5 years? Answer: A = (-(-3000)*(1+0.06)^5) / (((1+0.06)^5-1)/0.06) = 712.1892. The Excel formula is A = PMT(0.06,5,-3000).

The table below uses the exact same equations as the savings example, except that the principal is P0=-3000 and the payment, A, was calculated so that the future value is zero after year.

Table Showing Compound Interest Loan Example

Am I paying “Interest on Interest” if my loan payments are on time?

That is an interesting debate. Here are the two competing arguments.

Argument #1Yes. The table in Example 5 clearly shows that the new principal is calculated by adding the interest and the payment to the previous principal. The formulas are exactly the same as the savings example, except that you are starting with a negative principal. The formulas show that interest IS added to the principal, which satisfies the definition of compound interest, and that explains why you can use the compound interest formulas in traditional loan calculations.

Argument #2No. For an amortized loan, the payment isn’t actually 100% principal. Instead, you must first pay the amount of interest that you are charged, and the rest of your payment is applied to the principal. (This is how almost all amortized loans are worded). You are paying the interest first, so no interest is added to the principal. Therefore, you are not paying interest on interest if your payments are enough to completely pay the interest charged each period.

I was in the camp of Argument #2 for many years, and it wasn’t until creating these compound interest calculators that I realized Argument #2 is just legal jargon – a way to claim you aren’t paying interest on interest. Saying that the payment is only partially principal because the interest is first subtracted from the payment means that the formula for the new principal, PN, would just be written with parentheses as PN=PN-1+(iN+A). The parentheses tell us to first add the interest (a negative value in this case) to the loan payment. Then, the result is added to the principal. Does that actually change the final value? Of course not.

Argument #2 then says “Yeah, but your payment is enough to completely pay the amount of interest charged, so no interest is actually added to the principal. Therefore, you aren’t paying interest on interest.”

Argument #1 replies with “Yeah, but without affecting the result, the math allows me to consider my payment to be applied to the original principal, with the interest added afterward. Thus, I AM paying interest on interest.”

Argument #2 would then say “Our definition of the loan payment means that you are forced to add the amounts in parentheses first, so we are allowed to say we aren’t adding interest to the principal.”

Which argument is correct? Or, could they both be correct? Ultimately, the fact is that the compound interest formulas calculate the same result for Example 5, regardless.

What does this mean for the borrower?

Ultimately, the takeaway is that part of your regular loan payment is being used to pay off the interest. To take advantage of the compound interest formula, the borrower should make additional principal-only payments. Learn how debt payoff is similar to an investment.

NOTE Even a so-called Simple Interest Loan requires the payments to be applied first to the accrued interest before they can be applied to the principal. So, even though the daily interest accrual is based on simple interest, the amortization is still based on the compound interest formula.

References

Disclaimer: This information on this page is for educational purposes only. We do not guarantee the results or the applicability to your unique financial situation. You should seek the advice of qualified professionals regarding financial decisions.

Printable Periodic Table of Elements

Printable Periodic Table of Elements

Download and print a Periodic Table that includes names, atomic masses, charges, and other properties.

A good periodic table is an essential component of any chemist’s or future chemist’s reference materials.

After searching for a useful printable periodic table, I discovered that most were very basic and only included a few properties.

So we set out to make a periodic table in Excel that included as many important chemical and physical properties as we could fit on a single piece of 8 1/2 x 11″ paper.

  • The Atomic Number,
  • Atomic Mass,
  • Symbol
  •  Name,
  • Electronegativity,
  • Density,
  • Ionization energy,
  • Boiling point,
  • Melting point,
  • Electron Configuration,
  • Oxidation States,
  • Ground State Level,
  • and Atomic Radius

are all included in the Periodic Table of the Elements below.

If you usually work with a different set of properties, you can download the spreadsheet and replace the numbers with your own.

periodic table of the elements

periodic table of the elements

 

This spreadsheet was used to create the printable periodic table above and also contains a worksheet listing the data for various chemical properties.

If you’d like to create your own personal custom periodic table, you can download this Excel file and add your own sets of properties.

Data: The spreadsheet also includes a worksheet that lists the property data for the elements. You can use this data table for creating trend charts or use lookup functions to use the data within calculations.

Tip: You can create your own mini periodic table poster by printing on 11×17 paper.

5/28/11: Update (Version 1.1) – Added indicator for radioactive elements, color-coded melting and boiling point data, added the crystal structure (in place of the van der Waals radius), and marked the atomic radius as either the metallic radius (m) or covalent radius (v).
9/7/16: Updated the Excel and PDF files to correctly classify Radon as a gas.

 

Click to DOWNLOAD the Table HERE 

 

Calculate the Break Even Point

Calculating the Break Even Point
Download a Microsoft® Excel® Break-Even Point Calculator –

A Break-Even Analysis is used by a startup business to determine whether it is financially viable to produce and sell a new product or pursue a new venture.

This analysis is a common tool in a well-thought-out business plan.

The break-even point formulas are relatively simple, but estimating projected sales, determining the right sale price, and calculating fixed and variable costs can be difficult.

While the business owner is still responsible for these tasks, our Break-Even Calculator can assist you in running and reporting the analysis.

In addition to the spreadsheet, this page explains the formulas used in break-even analysis. If you are more worried about your budget than your time, you can use the formulas and explanation below the template to create your own spreadsheet from scratch.

BUY NOW
via ClickBank.net

Only $8.95
Required: Excel 2003 or later

60-DAY MONEY-BACK GUARANTEE

GauranteeTry it out! If you don’t think it was worth the cost, I will refund your purchase.

LicensePrivate Use
(not for distribution or resale)

“No installation, no macros – just a simple spreadsheet” – by 

 

 

 

 

 

 

Description
The Break-Even Point is typically defined as either the number of units sold or the dollar amount of sales required to cover your costs.

It can also be defined as either (1) the point at which an investment begins to generate a positive return or (2) the point at which total costs equal total revenue.

A break-even analysis can also be used to calculate the Payback Period, or the time it takes to break even.

Our Break-Even Analysis Calculator is a straightforward spreadsheet with three separate worksheets for calculating either (1) Break-Even Units, (2) Break-Even Price, or (3) Payback Period.

All of these scenarios are simply different ways of manipulating the basic breakeven equation, which is discussed in detail below.

 

In addition to the Break-Even Point, the worksheets calculate the number of units or prices required to achieve a target Net Income Before Taxes (NIBT).

Solving Break-Even Analysis Problems

The formula used to calculate a breakeven point (BEP) is based on the linear Cost-Volume-Profit (CVP) Model[1] which is a practical tool for simplified calculations and short-term projections. See reference [1] for more information about this model, and especially the discussion about the assumptions. All the different types of break-even analyses are based on the following basic equation:

Break-Even Equation

Total Costs = Total Revenue
TC = TR
TFC + TVC = P × X
TFC + (V × X) = P × X

The variables and definitions used in the break-even equation are listed below.

  • P = Selling Price per unit
  • V = Variable Cost per unit.
  • X = Number of Units Produced and Sold
  • TR = Total Revenue = P * X
  • TC = Total Costs = TFC + TVC
  • TFC = Total Fixed Costs
  • TVC = Total Variable Costs = V * X
  • P-V = Contribution Margin per unit (CM)
  • CMR = Contribution Margin Ratio = (P – V) / P

Payback Period

The Payback Period is the time it will take to break even on your investment. In break-even analyses in which are are solving for the break-even price or number of sales, the payback period is defined ahead of time. Depending on rate of change in your market, this may be a few months or a few years. Or, if you are just starting a business, your bank may want to see evidence that you will start making a profit after 18 months, or some other period.

Sales Price

One of the assumptions of the linear CVP model is that the Sales Price per unit (P) remains constant. So, the total revenue (TR) is just the price (P) multiplied by number of units sold (X). However, prices typically decrease with increasing demand, so be aware that the linear CVP model is a simplification.

Variable Costs

Variable costs include the production, direct labor, materials, and other expenses which depend on the number of units produced and sold.

On financial statements, like an income statementCost of Goods Sold (COGS) is a variable cost.

Some variable costs may be percentage-based (like commissions) while others may be dollar-based (like material costs).

Example: If you are selling software online, the payment processing service might charge $1 plus 7.5% of the sale price. If the sale price was $14.00, then the Variable Cost per unit (V) would be 1+(0.075*P) = $2.05. In the break-even calculator, you can split the cost between the percentage-based and dollar-based categories.

Direct Labor: Let’s say that every 100 sales require 8 hours of technical support over the life of the products.

If the labor cost (including payroll taxes) is $20/hr, then the Variable Cost per unit (V) would be V = (8*$20)/100 = $1.60 per unit.

The linear CVP model assumes that the Variable Cost per Unit (V) is constant over the specified period.

You should be aware that this is a simplification. For example, when labor is involved in the production, productivity can have a significant effect (see ref [1]).

Fixed Costs

Fixed costs are those which are assumed to be constant during the specified payback period and which do not depend on the number of units produced.

Advertising, insurance, real estate taxes, rent, accounting fees, and supplies would all be examples of fixed costs.

Fixed costs also include salaries and payroll taxes for non-direct labor such as administrative assistants and managers, or in other words, the payroll is not included as variable costs.

In reality, increasing production may also increase the expenses that are listed as “fixed costs” because they increase as the business grows and hires new employees.

After you run the break-even analysis, and especially if you use the CVP model to calculate sales required to reach a target profit, you should revisit your cost analysis to ensure that the costs match the level of production and sales required to reach your goals.

Formula to Calculate the Break-Even Point

You can find the basic breakeven point formula all over the place, and the formula that is most often given is for calculating the “Break Even Units”, or the number of units that you’ll have to sell to cover costs.

Actually, there are many ways to define the break-even point. You may want to solve for the total dollar sales to break even, what price you’ll have to charge to break even. You may also want to calculate how long it will take you to break even, which is officially called the payback period.

Break-Even Units

The following formula is for calculating the number of units (X) you will have to sell over the specified period of time.

X = TFC / ( P – V )
X = TFC / CM

If you want to solve for the number of units required to reach a targeted Net Income Before Taxes (NIBT), then substitute (TFC+NIBT) for TFC in the above equation.

Break-Even Sales

The break-even sales amount (S) is just the total revenue (TR) at the break-even point, which can be calculated as S = X × P. The following formula, derived from TR = X × P is another way to calculate the break-even sales amount.

S = TFC / ( 1 – V / P )
S = TFC / CMR

The value (1 – V / P) is known as the Contribution Margin Ratio (CMR), which is basically just the percentage of revenue earned for each unit sale after subtracting out the variable costs:

CMR = 1 – V / P = (P – V) / P

Break-Even Price

To solve for the price, you can use the Goal Seek tool in Excel to set X to a certain value by changing the price.

The formula for solving for the break-even price requires you to break down the variable costs into dollar-based and percentage-based costs:

  • V = Vd + (Vp × P) = Variable Costs per unit
  • Vd = Total Dollar-Based costs per unit
  • Vp × P = Total Percentage-Based costs per unit

The following formula is used to solve for the sale price (P) required to break-even if you produce and sell X units during the specified payback period.

P = ( 1/(1-Vp) ) × ( Vd + (TFC / X) )

If you want to solve for the price required to reach a targeted net income before taxes (NIBT), then substitute (TFC + NIBT) for TFC in the above equation.

Payback Period

For very simple sales scenarios, the CPV model can be used to solve for the Payback Period, or the number of months required to break even. Like the other formulas, we start with TR = TC. Both the revenue and the costs may depend on time so we have to define a few new terms.

To calculate the payback period, the number of units sold (X) is specified as a number of units per month. The fixed costs are broken down further into Start-up Costs (SC) and Recurring Fixed Costs (RC). Start-Up Costs are the costs required to develop the product, or create the very first product. Recurring Fixed Costs are those which are paid monthly or annually but which are not directly tied to the number of units sold, like web-hosting fees, monthly advertising expenses, insurance premiums, etc.

  • t = Payback Period in months
  • TFC = SC + (RC × t)
  • TVC = V × x × t
  • SC = Total Start-up Costs
  • RC = Recurring Costs per month
  • x = Number of units sold per month = X / t
Payback Period (t) = SC / ( P×x – V×x – RC )
Break-Even Sales (S) = P × x × t

Example: The selling price for an iPhone application is P=$1.99 and I expect to sell x=450 units per month. The development cost of the application is SC=$7,500 and my recurring monthly fees for advertising and web hosting come to RC=$65.00/month. I am charged a commission of Vp=30% to sell the app from iTunes. Result: The break-even spreadsheet calculates the payback period to be 13.35 months, which I’d round up to 14 months (because fractional recurring costs don’t make sense in this case).

Important: This calculation should only be used as a rough estimate. It does not take into account the time value of money, risk, interest, financing, opportunity costs, etc. The financial formulas NPV and IRR are usually better for calculating the return on an investment.

action item templates

action item templates

Whether you are sitting in a meeting with a laptop or tablet or sitting at home in an online meeting, a spreadsheet provides a simple way to record ideas and action items. Especially for online meetings, a Google Sheet can provide an excellent way to work on ideas and review action items. Try an action item template on this page to get started right away.

Action Items Templates

for Excel

Screenshot of the Action Items Template in Excel

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2010 or later

LicensePrivate Use (not for distribution or resale)

Description

Use this template in your meetings to record and track your action items or use it like a simple checklist. It includes a checkbox column that uses a conditional formatting rule to cross out actions when they are done.

Check out the articles How to Use Conditional Formatting and Add Cool Features to Your To Do Lists in Excel to learn more about these techniques.

Simple Action Item Tracker

for Google Sheets and Excel

Action Item Tracker Template

DOWNLOAD

⤓ GOOGLE SHEETS
⤓ EXCEL (.XLSX)For: Excel 2010 or later

LicensePrivate Use (not for distribution or resale)

Description

This spreadsheet provides a simple way to track action items assigned to different members of your team or organization. It was designed originally as a collaborative tool in Google Sheets.

Members of the team can create a bookmark to the spreadsheet and it can be edited during meetings.

The Google Sheets version uses the in-cell checkbox feature. The Excel version uses a drop-down to select the checkmark. Watch how to Insert a Check Mark in Excel (on youtube)!

Action List Template with Ranking

for Excel

Screenshot of the Action List Template in Excel

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2010 or later & Excel for iPad/iPhone⤓ GOOGLE SHEETS

LicensePrivate Use (not for distribution or resale)

Description

This spreadsheet helps you list and rank action items or ideas by Value and Difficulty.

This is a very useful alternative to the traditional method of picking a Priority level for a task.

To learn more about this type of ranking, see my PICK Chart page. This template uses a modified version of the PICK method that I have called the “JAM” method, an acronym for Just-Do-It, Abandon or Maybe.

The JAM ranking is similar to the PICK ranking except that it uses a 3×3 matrix instead of 2×2 – allowing you to choose a MEDIUM value and difficulty. The numeric rank ends up being a number between 2-6 and a custom conditional formatting icon set is used to display the symbols.

JAM Chart for Ranking Ideas and Tasks

Update 3/4/2019: The Google Sheets version uses a Color Scale for the JAM Ranking because icon sets are not compatible yet (thanks to Caroline for that suggestion).

Family Tree Template

Family Tree Template

Whether you need to make a family tree for a school project or you want to get serious about researching your family lines, the first step for any genealogy project is to start with a family tree.

Vertex42’s Free Family Tree Template (also known as a horizontal family tree pedigree chart) will help you quickly organize your family information.

Choose from a few different formats and styles. If you wish to simply record information by hand, then download one of the printable blank family tree charts.

Continue reading below the download block for additional tips on creating a family tree as well as links to other genealogy resources.

Download a Free Family Tree Template or a Printable Blank Family Tree Chart!

Printable Blank Family Tree Charts

These blank family tree charts will help you to hand-record your family history – great for class handouts. These can be used to create a family tree diagram for a simple 4-generation family tree project, or you can cross-reference multiple family tree sheets for larger projects. If you want to save your work, we’d strongly recommend that you use our Free Family Tree Template or some other family tree software (see some of the resources below).

4 Generation Family Tree Pedigree Chart:

 Portrait (.pdf) | Landscape (.pdf)
Blank Family Tree Chart
(Portrait)

Blank Family Tree Chart
(Landscape)

Family Tree Template with Photos

for Excel, Word, and Google Sheets

Family Tree Template with Photos

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2010 or later⤓ WORD (.DOCX)For: Word 2010 or later⤓ GOOGLE SHEETS

LicensePersonal Use

Designed by: Jon Wittwer and Jim Wittwer

Description: This family tree chart template for Excel and Word allows you to easily insert photos from your computer. The tree looks great with photos added. In case it’s not obvious, you start with yourself at the base of the tree, enter your parents in the two spaces in the upper part of the trunk, and work up the branches from there to list grandparents and great-grandparents.

6-Generation Family Tree Template

for Excel and Google Sheets

6-Generation Family Tree

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2010 or later⤓ GOOGLE SHEETS

LicensePersonal Use

Designed by: Jon Wittwer and Jim Wittwer

Description: For my own research, I wanted to see more than just 4 generations on a single page. Almost all other 6-generation family tree templates that I’ve seen make the space for the names way too small and waste a lot of space on the left side of the page. This is the new design we came up with and it works great for showing 6 generations.

Free Family Tree Template

for Excel, OpenOffice, and Google Sheets

Free Family Tree Template

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2007 or later & Excel for iOS⤓ GOOGLE SHEETS

OTHER VERSIONS

Excel 2003 (.xls)OpenOffice (.ods)Word 2007+ (.docx)

LicensePersonal Use (not for distribution or resale)

Description


Landscape
 | Portrait

These Excel-based Family Tree Templates are great for creating multi-generational family tree pedigree charts. The Excel workbook contains 2 different styles and orientations. See the screenshots on the right. It also includes the blank family tree charts used to create the PDF’s listed above and the example Kennedy Family Tree shown below.

“No installation, no macros – just a simple spreadsheet” – by Jon Wittwer and Brent Weight

related blog articles
10 Resources to Help You Find Your Ancestors

Family Tree Chart Example

This is an example of a four-generation family tree based on John F. Kennedy and family, created using the family tree template.
Kennedy Family Tree: Example created using the Free Family Tree Template
(Click to Enlarge).

Tips to Make a Family Tree

  • Start with what you know – you and your living relatives
  • Ask your relatives for help using their memories and family documents such as Bibles, birth and marriage certificates, journals and military records.
  • Review other websites to see what others have discovered about your family lines. Be careful as some of the family trees on line are not accurate. Check their sources.
  • Focus on primary sources such as copies of birth, census, death and marriage records. Many of these are available on line for free or a nominal fee.
  • Be patient – it can take a long time to uncover your genealogy.
  • Consider CAPITALIZING the surname – this will help it stand out when reviewing your records
  • List dates as 1 Jan 1890 to avoid confusion over formats such as dd/mm/yy or mm/dd/yy
  • List locations or places as City/Township, County, State, Country. For example: Houston, Harris County, Texas, USA

Reimburse form

Reimburse Form Expense Templates

A reimbursement form is used by most businesses and non-profit organizations.

A new employer will soon realize they need a way to reimburse employees for general business expenses like office supplies, mileage, software, training fees, etc.

For travel, you can use the Travel Expense Form or Mileage Tracker, but for these other expenses, a general reimbursement form will suffice.

All you need is a simple spreadsheet for this type of form, so our Employee Expense Reimbursement Form below is just the thing. I have also added a new printable PTA Reimbursement form.

Employee Expense Reimbursement Form

for Excel, OpenOffice, and Google Sheets

Employee Expense Reimbursement Form

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2007 or later & Excel for iPad⤓ GOOGLE SHEETS

OTHER VERSIONS

Excel 2003 (.xls)OpenOffice (.ods)

LicensePrivate Use (not for distribution or resale)

“No installation, no macros – just a simple spreadsheet” – by Jon Wittwer

Description

This reimbursement form was designed to allow employees to request reimbursement for general business expenses.

For travel-related expense reimbursement, use the Travel Expense Report. If you routinely use a vehicle for business purposes, download our Mileage Tracking Log.

Reimbursement Form with Receipts

for Excel

Reimbursement Form with Receipts

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2010 or later & Excel for iPad⤓ GOOGLE SHEETS

LicensePrivate Use (not for distribution or resale)

DESCRIPTION

Including digital receipts with reimbursement, forms are becoming increasingly popular. Attaching electronic scans or photos of your receipts along with your form via email is an option. But, with this template, you can insert images of your receipts below the form. Then you can print the form with the included receipts as a single PDF file.

New for Google Sheets: Google Sheets allows you to insert images into cells, so this could be done with receipts. However, GS currently doesn’t have a great way to easily view larger versions of the images within cells. This template uses a separate worksheet to allow viewing the larger versions of the images.

How to Reimburse Employees for Business-Related Expenses

For Employers

Customize the template and then give a copy of the form to your employees when they need to submit a request for expense reimbursement. Make sure they know to attach a copy of their receipts. Remember to customize the list of items in the Categories worksheet and keep the mileage rate note up-to-date (see the references below for the current rates).

It would probably be good to write up a short document that you can give your employees as a guide for what types of expenses you will reimburse and any other policies that you want to put into place (such as requiring an employee to get prior approval for any purchase over $XX).

Processing a Payment: For accounting purposes (assuming you are using an “accountable plan” – see IRS Publication 463), I find it simpler to write a separate check than to include the reimbursement in a payroll check. Employees may appreciate being reimbursed as soon as possible, instead of waiting for the normal paycheck. If you write a check, make sure to write Expense Reimbursement in the Memo field or otherwise indicate that the check is a reimbursement rather than a normal paycheck. See IRS Publication 463 for detailed rules.

For Employees

For each expense, choose an appropriate category. If you have questions about how to use the form, ask your employer. Don’t forget to attach copies of your receipts.

Important: Keep a copy of your receipts and your reimbursement request form for your own records!

Business Meals: For business meals to be tax-deductible, there must be a clear business purpose, along with a receipt. You might include the purpose for the meal in the Description if you are listing many items on a single form.

PTA Reimbursement Form

for Excel and Google Sheets

PTA Reimbursement Form Template

DOWNLOAD

⤓ EXCEL (.XLSX)For: Excel 2007 or later & Excel for iPad⤓ GOOGLE SHEETS

LicensePrivate Use (not for distribution or resale)

DESCRIPTION

My wife was PTA treasurer this year (2018-2019), so we based this form on how our school’s PTA handles reimbursements. You can use this form to create a printable reimbursement form for your PTA, PTO, or similar organization.

blank printable calendars

Blank printable calendars

Need a truly blank calendar – a calendar page without any numbers or month names? If so, you found the right page. You can download a number of different free printable blank calendars from this page in a variety of colors and formats.

You can also download a blank calendar template for Microsoft Excel.

If you want a blank calendar that includes the days and months of the year, check out our printable calendars, or try our other calendar templates.

Blank Calendar Template

Blank Calendar Template for Excel

⤓ DOWNLOADVersion: Excel 2007 or later

TEMPLATE DETAILS

LicensePrivate Use
(not for distribution or resale)

“No installation, no macros – just a simple spreadsheet” – by Jon Wittwer

Description

This theme-enabled blank monthly calendar template allows you to change the color and the fonts used in the calendar by simply selecting a new theme in Excel. Just go to Page Layout > Colors or Fonts.

Printable Blank Calendars

The following printable blank calendars are available as PDF downloads and may be used to print as many paper copies as you need, free of charge, provided that the copyright notice is not removed. You may not make the file downloadable from a public server or website, but feel free to link to this page or even to a specific PDF file, because these blank calendars are never out of date.

Blank Calendar with Days of the Week – Landscape

Printable Blank Calendar

This design includes the days of the week but allows you to write in the month name. It is available in both Sunday and Monday-first formats.

 SUNDAY FIRST

Blue | Black | Green | Purple | Red | Gray

 MONDAY FIRST

Blue | Black | Green | Purple | Red | Gray

Blank Calendar with Days of the Week – Portrait

Free Blank Printable Calendar

Similar to the previous landscape design, except that this one is in Portrait orientation.

 SUNDAY FIRST

Blue | Black | Green | Purple | Red | Gray

 MONDAY FIRST

Blue | Black | Green | Purple | Red | Gray

Printable Blank Calendar Grid

Blank Calendar Grid

Prints only the outline for the days of the month, so you can stamp, color, or write in your own days as you see fit.

 PORTRAIT ORIENTATION

Download Calendar Grid

 LANDSCAPE ORIENTATION

Download Calendar Grid

Blank Monthly Calendars

The following free blank calendar designs include 12 pages with a separate month on each page and are available in a variety of colors. The calendars have both the month name and the days of the week listed at the top in either the standard Sunday-Saturday or Monday-Sunday formats.

12-Month Blank Calendars – Landscape

Free Blank Monthly Calendar

This screenshot shows the Monday-Sunday format. In both formats, the days with the light background shading are Sunday and Saturday.

 SUNDAY-SATURDAY

Blue | Black | Green | Purple | Red | Gray

 MONDAY-SUNDAY

Blue | Black | Green | Purple | Red | Gray

12-Month Blank Calendars – Portrait

Blank Monthly Calendar - Portrait

These 12-month blank calendars are the same as the previous design except for page orientation. The screenshot shows the red version.

 SUNDAY-SATURDAY

Blue | Black | Green | Purple | Red | Gray

 MONDAY-SUNDAY

Blue | Black | Green | Purple | Red | Gray

New Blank Calendar Designs

The following designs were created by Vertex42 in January of 2017 after a flurry of intense creative brainstorming after asking ourselves “How can we take a boring calendar grid and do something interesting with it and still have it be functional?” These are available as PDF downloads and are designed for printing and filling in the month name, year, and days by hand.

Printable Blank Calendar – MINIMUS

Printable Blank Calendar - Minimus Design

As its name implies, this design was an attempt to create a calendar grid using very little text, lines or shading.

 Download (Black)

 Download (Blue)

Printable Blank Calendar – BOLD

Blank Calendar - Bold Design

This design uses a combination of borders and bold background shading to subtly emphasize typical workdays (Monday-Friday) instead of the more common approach of shading the weekends.

 Download (Blue)

 Download (Gray)

Printable Blank Calendar – REVERSE

Blank Calendar with Reverse Borders and Shading

This is a fun design where we basically reversed the borders and shading. If you can spare the ink, this is a pretty cool-looking calendar.

 Download (Blue)

 Download (Gray)

Printable Blank Calendar – INSET

Blank Calendar with Gray Background and Inset Grid

This design came about accidentally as I was trying to create something with some shading. It kind of reminds me of the old Access database form fields.

 Download the PDF

Printable Blank Calendar – FLOAT

Printable Blank Calendar with a Floating Grid

The grid for this calendar was created using a bunch of shape objects in Excel. Shading was added to the objects to make the elements of the calendar look like they are floating above the paper.

 Download (Black)

 Download (Blue)

Printable Blank Calendar – BLUEPRINT

Blank Calendar with a Boxed Grid

The name for this design came from the fact that it somewhat resembles a blueprint (for a storage room or dormitory or something like that). Perhaps “Boxy” might be another way to describe it.

 Download the PDF

Printable Blank Calendar – THICK

Blank Calendar with a Thick Outer Grid

Not a very flashy title for this design, but THICK refers to the border style.

 Download (Black)

 Download (Blue)

Printable Blank Calendar – SLANT

Blank Calendar with a slant Outer Grid

You don’t often see a triangle shape used in a calendar grid, but if you are looking for something a little different, this one is cool.

 Download (Black)

 Download (Blue)

If you want to find other blank calendars in PDF format, do a Google search with “filetype:pdf” included in the search phrase, such as “blank calendar filetype:pdf” or use the Advanced Search feature to limit your search to PDF files.