Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Archive for the ‘Excel’ Category

Build an Excel Navigation Panel

Image result for NAVIGATIONNavigating within an Excel workbook is simple enough if the workbook contains one or two worksheets and if you are familiar with working with multiple worksheets. However, if the workbook as more than a few worksheet or the user is not comfortable with a multiple sheet environment, navigation can be a little more involved, even a little intimidating.

In this article we will discuss creating and using hyperlinked drawing objects to create an intuitive menu system for an Excel Workbook.

 

SNAGHTML9f2d87e

image

Scenario

The workbook we will be enhancing is used to manage attendance records for employees at each of five locations. Each location is represented by one worksheet for each of three shifts plus one worksheet for the location’s employee list. Unless you are working on a very wide monitor and/or at very high resolution, seeing the tabs for all sheets at the same time will be unlikely:

image

So going directly from Location A to Location D, for example will require at least one extra step in addition to a mouse-click on the desired sheet tab. In some Excel version two additional steps are needed. First you need to right-click in the sheet navigation area, then select the desired sheets. In some versions of Excel, you will only see 15 sheet names when you right-click the navigation area and may have to select “More Sheets” to be able to access all sheets.

SNAGHTML4ac3ffd

 

image

A custom navigation pane will make the process of navigating from one location to any other location in the workbook simpler and more efficient that the stock method of selecting sheet tabs.

‘Own-use’ vs. External User

While the same general techniques apply whether you are designing a navigation panel in a workbook solely for your own use or for another person who will be working with the workbook on a regular basis, designing the panel for someone else to use requires more thoughtful and detailed planning.

Objectives

The navigation should be simple to use, have an attractive look and feel that is consistent throughout the workbook, be visible at all times, and require a minimum number of mouse clicks for the user to switch from one worksheet to another.

Create a Prototype Menu

The more time you put into designing and perfecting a prototype menu, the easier it will be to apply the finished menu to the rest of the workbook. Start by creating an additional sheet which you will use as a sort of drawing canvas for creating the prototype menu. In the sample workbook, this sheet is named ‘Menu Prototype.’

If you used consistent naming principles for the worksheets when  you created the workbook, you can take advantage of Excel features like copy and paste and object grouping to take some of the tedium involved with building your prototype menu

  • Drawing Objects

Almost any drawing object is suitable for individual menu elements. Simple rectangles arguably make the most efficient use of available space.

  • Effects

Excel drawing objects, especially in the 2007 and later versions, offer a wealth of visual effects to set them off from normal worksheet contents.

  • Hyperlinks

Hyperlinks can be applied to most drawing objects. Waiting to apply them until you have fixed the basic properties of the object makes them a little easier to work with.

  • Properties

By default, drawing objects are moved and re-sized with cell. To ensure the menu doesn’t get automatically moved or re-sized, change the relevant property.

NOTE: Screenshots in this article were developed using Excel 2016. If you are using an earlier version of Excel, what you see an your screen may look a little different from these illustrations

Step-by-step, here’s how to build a prototype menu.

  1. Create a simple drawing object such as a rectangle. Use your mouse to drag the crosshair ( c ) to draw the rectangle shape. You can refine the size of the rectangle to what works best for you.SNAGHTML5a89b04

SNAGHTML5a949b3

After you have drawn the shape, click to select it

image

 

and set the height and width of the object on the Drawing Tools Format Ribbon Tab ( f ).

image

2. With the object selected type a caption that will indicate where the button will take the user when they click it. Use the Home Tab’s alignment buttons to align the caption within the button.

SNAGHTML955626f

3. Duplicate the button. You can use copy and paste but it’s faster to use Ctrl D (duplicate). You will need as one copy of the button for each option you want to offer the user. You will need to modify the caption for each button but it is faster to do this after you have made all duplicates you will need.

TIP: In some versions of Excel, if you position the first duplicate where you want it relative to the original, then continue duplicating, additional duplicates will automatically position themselves relative to the previous duplicate.

4. If necessary, drag the buttons roughly to the positions you would like them to be relative to each other. Use the Align commands on the Drawing Tools/Format Ribbon tab to refine their alignment. Select all of the buttons and use the distribute commands to make the layout more symmetrical.

image

image

5. When you have the buttons laid out to your satisfaction, with all the buttons selected use the Group command to combine them into a new custom object.

6. With the new group selected, use shape effects to give your menu panel and buttons a distinct ‘look and feel.’

SNAGHTML98f8946

 

image

7. With the menu group still selected right-click it and select size and properties. In the size and properties pane (or dialogue, depending on your Excel version), set the size property to “Don’t move or size with cells.”

image

 

8. De-select the group and then select each button in turn to assign its hyperlink.

SNAGHTML9a9572b

Choose “Place in This Doucment ( a )," then select the name of the sheet to which you want this button to lead ( b ). Type the name of the cell you want to activate on the selected sheet ( c ). Then click the ScreenTip button and enter the tip you would like to pop up when the user’s mouse hovers over the button.

image

Pay close attention to all the details when you set up your prototype menu. Once you are satisfied that it looks and performs as you want it to, simple copy and paste it to every worksheet in the workbook.

Apply the Menu to all Worksheets

Positioning the menu panel in a consistent location on every worksheet will give your workbook a more professional appearance. Here is a horizontal menu located at the top left or a worksheet.

image

And here is a vertical menu, also located at the top left of a worksheet.

 

image

You can also remove the hyperlink from the button that represents the sheet on which a menu panel is located and ‘grey-out’ the caption for that button.

image

For horizontal menu panels, set the height of Row A to the height of the menu panel. For vertical menu panels, set the width of Column 1 to the width of the menu panel. If you want to prevent then menu panel from scrolling off, use the freeze panes command to lock the top row or first column of each worksheet.

Advantages

  • Intuitive
  • Makes navigation easier
    • Direct access to specified worksheet
    • Direct access to specific location, if needed

Disadvantages

  • Takes time to plan and create
  • Sacrifices some workspace

A hyperlinked menu navigation panel will simplify moving from sheet to sheet in workbooks that have more than a few worksheets.

Build an Excel Navigation Panel

Image result for NAVIGATIONNavigating within an Excel workbook is simple enough if the workbook contains one or two worksheets and if you are familiar with working with multiple worksheets. However, if the workbook as more than a few worksheet or the user is not comfortable with a multiple sheet environment, navigation can be a little more involved, even a little intimidating.

In this article we will discuss creating and using hyperlinked drawing objects to create an intuitive menu system for an Excel Workbook.

 

SNAGHTML9f2d87e

image

Scenario

The workbook we will be enhancing is used to manage attendance records for employees at each of five locations. Each location is represented by one worksheet for each of three shifts plus one worksheet for the location’s employee list. Unless you are working on a very wide monitor and/or at very high resolution, seeing the tabs for all sheets at the same time will be unlikely:

image

So going directly from Location A to Location D, for example will require at least one extra step in addition to a mouse-click on the desired sheet tab. In some Excel version two additional steps are needed. First you need to right-click in the sheet navigation area, then select the desired sheets. In some versions of Excel, you will only see 15 sheet names when you right-click the navigation area and may have to select “More Sheets” to be able to access all sheets.

SNAGHTML4ac3ffd

 

image

A custom navigation pane will make the process of navigating from one location to any other location in the workbook simpler and more efficient that the stock method of selecting sheet tabs.

‘Own-use’ vs. External User

While the same general techniques apply whether you are designing a navigation panel in a workbook solely for your own use or for another person who will be working with the workbook on a regular basis, designing the panel for someone else to use requires more thoughtful and detailed planning.

Objectives

The navigation should be simple to use, have an attractive look and feel that is consistent throughout the workbook, be visible at all times, and require a minimum number of mouse clicks for the user to switch from one worksheet to another.

Create a Prototype Menu

The more time you put into designing and perfecting a prototype menu, the easier it will be to apply the finished menu to the rest of the workbook. Start by creating an additional sheet which you will use as a sort of drawing canvas for creating the prototype menu. In the sample workbook, this sheet is named ‘Menu Prototype.’

If you used consistent naming principles for the worksheets when  you created the workbook, you can take advantage of Excel features like copy and paste and object grouping to take some of the tedium involved with building your prototype menu

  • Drawing Objects

Almost any drawing object is suitable for individual menu elements. Simple rectangles arguably make the most efficient use of available space.

  • Effects

Excel drawing objects, especially in the 2007 and later versions, offer a wealth of visual effects to set them off from normal worksheet contents.

  • Hyperlinks

Hyperlinks can be applied to most drawing objects. Waiting to apply them until you have fixed the basic properties of the object makes them a little easier to work with.

  • Properties

By default, drawing objects are moved and re-sized with cell. To ensure the menu doesn’t get automatically moved or re-sized, change the relevant property.

NOTE: Screenshots in this article were developed using Excel 2016. If you are using an earlier version of Excel, what you see an your screen may look a little different from these illustrations

Step-by-step, here’s how to build a prototype menu.

  1. Create a simple drawing object such as a rectangle. Use your mouse to drag the crosshair ( c ) to draw the rectangle shape. You can refine the size of the rectangle to what works best for you.SNAGHTML5a89b04

SNAGHTML5a949b3

After you have drawn the shape, click to select it

image

 

and set the height and width of the object on the Drawing Tools Format Ribbon Tab ( f ).

image

2. With the object selected type a caption that will indicate where the button will take the user when they click it. Use the Home Tab’s alignment buttons to align the caption within the button.

SNAGHTML955626f

3. Duplicate the button. You can use copy and paste but it’s faster to use Ctrl D (duplicate). You will need as one copy of the button for each option you want to offer the user. You will need to modify the caption for each button but it is faster to do this after you have made all duplicates you will need.

TIP: In some versions of Excel, if you position the first duplicate where you want it relative to the original, then continue duplicating, additional duplicates will automatically position themselves relative to the previous duplicate.

4. If necessary, drag the buttons roughly to the positions you would like them to be relative to each other. Use the Align commands on the Drawing Tools/Format Ribbon tab to refine their alignment. Select all of the buttons and use the distribute commands to make the layout more symmetrical.

image

image

5. When you have the buttons laid out to your satisfaction, with all the buttons selected use the Group command to combine them into a new custom object.

6. With the new group selected, use shape effects to give your menu panel and buttons a distinct ‘look and feel.’

SNAGHTML98f8946

 

image

7. With the menu group still selected right-click it and select size and properties. In the size and properties pane (or dialogue, depending on your Excel version), set the size property to “Don’t move or size with cells.”

image

 

8. De-select the group and then select each button in turn to assign its hyperlink.

SNAGHTML9a9572b

Choose “Place in This Doucment ( a )," then select the name of the sheet to which you want this button to lead ( b ). Type the name of the cell you want to activate on the selected sheet ( c ). Then click the ScreenTip button and enter the tip you would like to pop up when the user’s mouse hovers over the button.

image

Pay close attention to all the details when you set up your prototype menu. Once you are satisfied that it looks and performs as you want it to, simple copy and paste it to every worksheet in the workbook.

Apply the Menu to all Worksheets

Positioning the menu panel in a consistent location on every worksheet will give your workbook a more professional appearance. Here is a horizontal menu located at the top left or a worksheet.

image

And here is a vertical menu, also located at the top left of a worksheet.

 

image

You can also remove the hyperlink from the button that represents the sheet on which a menu panel is located and ‘grey-out’ the caption for that button.

image

For horizontal menu panels, set the height of Row A to the height of the menu panel. For vertical menu panels, set the width of Column 1 to the width of the menu panel. If you want to prevent then menu panel from scrolling off, use the freeze panes command to lock the top row or first column of each worksheet.

Advantages

  • Intuitive
  • Makes navigation easier
    • Direct access to specified worksheet
    • Direct access to specific location, if needed

Disadvantages

  • Takes time to plan and create
  • Sacrifices some workspace

A hyperlinked menu navigation panel will simplify moving from sheet to sheet in workbooks that have more than a few worksheets.

Excel 2016 A Small Surprise with Autofill

Autofill has long been one of my favourite Excel productivity features. It is the fastest way I know to copy cell contents to multiple cells. If you have an existing column of data and want to one or more formulas to adjacent columns, double-clicking the fill handle adds a whole new layer of productivity.

This video illustrates this tip and the small surprise that Excel 2016 introduced that affects this feature. By default, autofill reproduces a series; in other words if the cell that you want to use as the source data for autofill contains a value like, Room 101, autofilling this cell down a column will give new values like Room 102, Room 103, Room 104 ….

That’s all well and good, but what if you wanted to assign a number of people to the same room, Room 101? In previous versions of Excel, immediately after autofilling, Excel displayed the Paste Options button. The surprised that Excel 2016 introduced is that the default does not display Paste Options.

That can prove very frustrating, especially if you autofilling cells across may rows to many columns. Fortunately, the solution is relatively simple: enable the Paste Options button by modifying your Excel Options to Show Paste Options button when content is pasted.

image

Check it out!

Are You a OneNote Fan?

Have a look at this article. It has some valuable tips on note-taking. Although the article is addressed to medical students, anyone who needs to take notes of any kind will find some excellent tips here.

Excel Financial Functions–Calculating IRR for Irregular Cashflows

This week, in the final article of this series, guest blogger Nick Williams discusses calculating the Internal Rate of Return for an irregular cashflow. Nick is an Access tutor based in the U.K.

XIRR – Internal Rate Of Return

XIRR function returns the value for an Internal Rate Of Return for a supplied series of cash flows, set of values. The key difference between IRR and XIRR is that XIRR can deal with cashflows that aren’t evenly spaced. Because it can deal with cashflows that aren’t evenly spaced you need to tell is both the series of cashflows and also the dates of each cashflow.

The syntax of the XIRR function in Excel is XIRR (values, dates, [guess])

values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values.

dates is another required parameter, it is a series of dates. You need to have a date that corresponds to each cash flow for the calculation to run. The first date is the date of the investment/loan, and subsequent dates refer to income values.

guess is an optional parameter, and works as it does for IRR. Again, if this parameter is omitted, it is set to 0.1 (10%) by default

Continuing with our previous example, let’s introduce dates into our cash flow set.

We now have dates for the initial investment, and irregular dates of actual and/or forecasted income over the seven years of the project.

clip_image001clip_image003clip_image005

As we would expect, as the returns come sooner on this project the return on it has gone up, when compared with our previous calculation.

If we entered dates that were exactly one year apart for each of the cashflows this would give an answer of 1.82%, the same answer as using the IRR function.

Again continuing with our previous example, lets we add more expected income and periods. Again as the returns come sooner the return on the project has increased when compared to have all of the cashflows spaced exactly one year apart.

clip_image007

WHAT TO DO IF YOUR CALCULATION RETURNS #num!

Microsoft Excel uses an iterative technique for calculating IRR AND XIRR starting from guess. If XIRR can’t find a result that works after 100 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.

#NUM! error can also appear if:

1. there isn’t at least one positive and at least one negative value in the list of cashflows

2. any of the specified dates precede the first specified date

3. the specified dates and values arrays have different lengths

XIRR can also return a #VALUE! error if it cannot not recognize any of the specified dates as dates (e.g. you have the value of 20 among your dates array)

Excel Financial Functions–Calculating IRR

This week, guest blogger Nick Williams discusses calculating the Internal Rate of Return for a regular cashflow. Nick is an Access tutor based in the U.K.

IRR – Internal Rate Of Return

IRR returns an interest rate. It is the interest rate that would need to be applied to all of the cashflows in the calculation to give an NPV of zero for the project (see above). In short it tells you the return on the money invested in a project and is often used as an investment decision tool. It is designed to work with a set of regularly spaced cashflows only.

IRR is the same as interest rate of savings or loan, however there is it applied to a very straight forward situation.

The syntax of the IRR function in Excel is IRR(values, [guess])

values is a required parameter. It is an array or reference to a set of cells that contain the cash flow values for the project.

guess is an optional parameter. It is a number that you are guessing is close to the result of IRR. If this parameter is omitted, it is set to 0.1 (10%) by default.

Continuing with our example of above we can see the function in use below.

clip_image002clip_image003clip_image005

Intuitively we know that that is right. Above we discovered that the project had a negative NPV if we applied a 3% discount rate. Therefore we knew that the discount rate to get an NPV of zero had to be below 3%.

Again, as above, lets add two more years of income and see what happens to the result. We got a positive NPV using a 3% rate before so we know that the answer will be above 3% before we start.

clip_image007

Note: Microsoft Excel uses an iterative technique for calculating IRR starting from guess. If IRR can’t find a result that works after 20 tries/iterations, the #NUM! error value is returned. If this happens, or if the result is not what you expected to see, try again with setting a different value for guess.

Excel Financial Functions–Calculating NPV

We’d like to welcome back guest blogger Nick Williams. This week and for the next two weeks, Nick will be publishing a series of Excel financial calculation tutorials. Nick is an Access tutor based in the U.K.

One area where Excel is used extensively is in financial forecasting and modeling. It contains lots of financial formulas which can save hours of time.

As ever the trick is to give Excel the right data in the right way … and then let it do the work for you.

In this article we’re going to have a quick look at three of the basic formulas that Excel has in its kit bag IRR, XIRR and NPV.

NPV – Net Present Value

The NPV returns a value amount. You insert all of the forecast cashflows (both investment and return) relating to a project and also your cost of capital. NPV will then tell you if the project will return a ‘profit’. Profit in this context is that it returns more than your cost of capital for the period that your capital is invested. Put the other way round the value of all of the projects future cashflows, today, is greater than nought.

The basis of the calculation is that a pound now is worth more than a pound in future. Your cost of capital (or discount rate) is the difference in value between a pound now and a pound in a year’s time.

To make this clearer a very simple illustration would be:

Imagine you are investing $10 today. You think that it will return $12 in one year’s time. You like to make at least 10% return on your money when it is invested so your cost of capital is 10%.

The net present value of the $12 today is $10.9 (=£11/(1+10%)) so the $12 in one year is worth more than the $10 today, meaning that you should invest as the project will earn you more than the 10% return that you need.

Syntax of the NPV function in Excel is NPV( rate, value1, [value2], [value3], … )

rate is a required parameter and is the discount rate or cost of capital over a single period.

value1, value2,… are values of future income (positive value) and/or payments (negative value). Value 1 is a required parameter and subsequent values are optional. You will need to put a value, even if it is zero, in for each period of the project.

Below is a more complex example. We have a $200,000 investment which returns different amounts each year for seven years.

We put the initial investment in cell B3. Initially we have the forecasted seven years of income in cells B4:B12. The cost of capital or annual discount rate is in cell B2.

After 7 periods our NPV is going to be:

clip_image001clip_image003clip_image005

If we add income for two additional periods, NPV is going to change:

clip_image007

Five Essential Excel Keyboard Shortcuts

Some keyboard shortcuts are designed to enhance your productivity in specific applications. Here are five that have long been my favourites in Excel.

 

<ctrl>;

Insert the current date. The inserted date will remain the same; it does not update when Excel recalculates formulas.

<shift><ctrl>:

Insert the current time. The inserted time will remain the same; it does not update when Excel recalculates formulas.

<ctrl>HOME

Move the selection to the beginning of the worksheet. Cell A1 becomes the active cell. Note: if freeze panes is in effect, the shortcut behaves a little differently, making the top left cell of the lower right quadrant active. If Row 1 contains the header of a structured table, <ctrl>HOME will select cell A2 rather than A1.

<ctrl>END

Selects the cell at the lower right corner of the portion of the worksheet that has actual contents.

F4

When entering formulas, make a cell reference absolute. Each time you press F4 when you are entering a formula the absolute reference changes from Absolute Cell or Range to Absolute Row/Relative Column to Absolute Column/Relative Row to Relative Cell or Range

Links for the technically inclined:

Announcing the Microsoft Cloud Roadshow

https://microsoftcloudroadshow.com/cities/?Wt.mc_id=dx_MVP400580

This is a free, two day technical training event for IT Professionals and Developers that provides best practices and insight from those who run cloud services across Office 365, Micros oft Azure, and Windows 10.

Developer Interview Series #1 –

The Power of Cross Platform Development with Universal Apps and Xamarin

https://channel9.msdn.com/Blogs/Case-Studies/The-Power-of-Cross-Platform-Development-with-Universal-Apps-and-Xamarin?WT.mc_id=dx_MVP400580

In this interview series, we bring you best practices, anecdotes, and insights from developers who are building creative solutions using Microsoft technologies.

We’re on the road to self-driving business applications

http://blogs.msdn.com/b/stevengu/archive/2015/09/09/we-re-on-the-road-to-self-driving-business-applications.aspx?WT.mc_id=dx_MVP400580

A blog by Steve “Guggs” Guggenheimer on self-driving ERP (Enterprise Resource Planning software)

Memory Compression in Windows 10 RTM

https://channel9.msdn.com/Blogs/Seth-Juarez/Memory-Compression-in-Windows-10-RTM?WT.mc_id=dx_MVP400580

The OS is doing some clever optimizations that allow your processes to trim some of the memory but not necessarily page it out to disk.

Managing hidden apps, beta apps and visibility of in-app purchases in Dev Center

http://blogs.windows.com/buildingapps/2015/09/10/managing-hidden-apps-beta-apps-and-visibility-of-in-app-purchases-in-dev-center/?WT.mc_id=dx_MVP400580

The unified Dev Center introduced several new options to manage the visibility of apps and in-app purchase.

Excel Function Key Shortcuts

I resisted learning and using keyboard shortcuts for more years than I care to admit. In recent years, however, I have come to appreciate just how valuable keyboard shortcuts can be in Excel and almost any other application you can name. The Blog ExcelTip has just published a detailed article about Function Key Shortcuts that is definitely worth a read if your are interested in expanding your shortcut key horizon.

Office 2016 Public Preview

Yesterday Microsoft announced Office 2016 availability for public preview. Details and a link to the Office 2016 Preview site are in this blog article.

Over the last 12 months, we’ve transformed Office from a suite of desktop applications to a complete, cross-platform, cross-device solution for getting work done. We’ve expanded the Office footprint to iPad and Android tablets. We’ve upgraded Office experiences on the Mac, the iPhone and on the web. We’ve even added new apps to the Office family with Sway and Office Lens. All designed to keep your work moving, everywhere. But that doesn’t mean we’ve forgotten where we came from. While you’ve seen us focus on tuning Office for different platforms over the last year, make no mistake, Office on Windows desktop is central to our strategy.

Office Blogs

This version introduces some exciting new Office features:

  • Anywhere Document authoring
  • Real-time collaboration
  • Smart Applications
  • Faster and easier data analysis

All in all this looks like an exciting advance in Office technology.

Protecting Custom Templates

 

Between now and May 31st, check out these offers from Microsoft:

Azure 30-day Trial

MSDN Subscription

CANITPRO At The Movies (English)

CANITPRO At The Movies (French)

 

 

A crash can impact your computing life in ways that are mere inconveniences to major disasters. In this article I am going to discuss ways of limiting inconvenient data loss.

First a definition: an inconvenient data loss (as opposed to a catastrophic loss) is the loss of data which can be rebuilt relatively easily, requiring only a moderate amount of time and inconvenience to accomplish the re-build.

Catastrophic loss, on the other hand, refers to the loss of critical data that would be very difficult, perhaps even impossible, to rebuild. Some examples of critical data are business or personal financial records, family photos where a print doesn’t exist, original artistic creations, any non-trivial original work that you have created on the computer.

 

Data loss is a question of when, not
if it will happen; it will happen.

Catastrophic Data Loss

The only way to prevent catastrophic data loss is to regularly and frequently back the data up to a second location. How often should you back up? The more important the data, the more frequently you should back it up. A web search will turn up links to many backup applications that will assist you in backing up. Once you have decided on an application, you then need to decide what (individual files and folders) to backup, where to place the backup, and when the backup should happen.

  • Choose what to backup carefully
  • Choose a location that is not on the same
    disk or media as the actual data
  • Choose a backup frequency that will
    minimize the amount of critical data
    that will be lost when your computer crashes

Inconvenient Data Loss

The specific inconvenient data loss that prompted this article was a recent computer failure. In short, Windows crashed, making my computer unusable until I re-installed Windows. At the time, Office 2010 applications were my main tools. Roughly 75% of everything I do using my computer involves one or more Office applications (Word, Access, Excel, Publisher, PowerPoint.) I thought I had a reliable backup strategy in places with backup software copying my documents, spreadsheets, etc. to a backup folder that was in turned synced to a cloud location. That way, even if my computer experienced a total failure, I would still have all my files, with little or no loss.

Over the years, custom templates have become an essential part of my electronic tool box. For example I have Word templates that help me quickly create several different documents that are an essential part of my training practice. These include class lists, class evaluations, training quotations, and course outline formats. In short, whenever I recognized that I was creating documents that repeated standard information, I created a template that would include the information common to each of these types of documents.

Of course it takes time to create good templates but the invested time is quickly repaid because having a template eliminated re-inventing the wheel to create routine documents.

imageAnd so it seems, templates were the Achilles’ heel of my  backup strategy. I had overlooked that fact that custom templates and page parts in Office 2010 and earlier, were not stored in a location that is readily accessible to backup software. In other words, backing up document does not back up templates.

When windows went down for the count, the crash took out of circulation the folders where my custom templates were stored. Recovery, while not difficult, has been time consuming. Because I didn’t lose my data, I have been able to open files that I had originally created from each template and delete any of the content that was not part of the generic template. The difficult part has been remembering exactly what templates I had been using.

Avoiding the Problem in Office 2010 and Prior

Obviously this is an experience I would like to avoid in the future so I have modified my backup strategy. Ironically, the solution I came up with resembles the Office 2013 approach to custom template storage, something I became aware of only after I had worked out my new strategy.

First, I created a folder, MyTemplates,  in my Documents folder. Each time I create a new template, I save it to the the default templates folder and then save a new copy to MyTemplates. My backup strategy already included the Documents folder so MyTemplates is automatically backed up with every scheduled backup.

This approach isn’t ideal because it does require manual intervention whenever I create or modify a template but this minor inconvenience is well worth the bit of time it takes because it minimizes the risk of having to recreate templates in the event of a catastrophe.

How Office 2013 Handles Custom Templates

Office 2010 and prior Office versions buried custom templates in subfolders managed by Windows. The exact folder location depended on the particular Office version. In Office 2013, custom templates can become more accessible to the user. The default is a folder, Custom Office Template in the user’s Documents folders.

Given the experience I outlined above, I strongly recommend going with the default and then ensuring that the Custom Office Templates folder is included in the list of locations that you regularly backup.

 

Related articles:

The article Finding Template From Previous Office Versions suggests pointing your custom templates link to the templates folder that Office 2010 used. This approach is not a solution to the lost templates issue discussed above.

This article: Office 2013 Custom Templates Location the Custom Office Templates folder in Office 2013.