How to Use Templates and Outlines in Excel 2013

Creating an Outline Automatically

Outlining groups sections of the sheet together so you can hide them.

Below we have a worksheet that contains sales amounts for different employees in different months. It also contains quarterly totals, then a grand total.

To automatically create an outline in Excel 2013, move your cursor anywhere in the data. You can see our cursor below.

Click on the Data tab.

Go to Groups, then choose Auto Outline.

As long as you have subtotals in your worksheet, it will become outlined, as shown below:

As we said at the beginning of the section, outlining groups sections of a sheet together so you can hide them if you want.

In our outline, you can see 1, 2, and 3 in the row headings.

If we click 1, all the columns but the Total column is hidden.

If we click 2, we see our quarterly totals along with our Total column.

If we click 3, we see nothing is hidden.

You can also use the plus and minus signs in the outline to expand or collapse data.

If we click 2 again, we see this:

We can click on the plus sign above column E, which represents the Q1 total to see all the data for Q1.

If you want to hide that data again, click on the minus sign in the outline above column E.

Manually Creating an Outline

In the last section, we learned how to let Excel do all the work in creating an outline. Now let's learn how to create one manually. This will be important to know if you don't have sums (or totals as in the last section) in your worksheet, which are required to create outlines automatically.

To start with, select the data you want to include in the outline, as we've done below.

Next, click on the Data tab. Go to Group, then select Group from the dropdown menu.

This groups columns of data together.

You will then see the outline symbols above the group. You can now expand and collapse to show and hide the data in the outline.

Hiding or Removing an Outline

As you work with your data, you may find the need to hide an outline – or to remove it completely.

To hide an outline, go to the File tab. Click Options, then click on the Advanced tab.

Go to the Display Options For This Worksheet section, then select the worksheet which contains the outline you want to hide.

Uncheck the box that says Show Outline Symbols If An Outline is Applied.

To remove an outline, go to the worksheet that contains the outline.

Next, go to the Data tab and go to the Outline group. Go to Ungroup, then select Clear Outline.

Editing an Outline

By using the Group and Ungroup commands under the Data tab, you can add or remove columns and rows to or from your outline.

Let's say you add a new row to your worksheet that already contains an outline.

In the snapshot below, we've added a new column. We've named it Carryovers.

If this new column isn't grouped with the rest of the outline, click the Data tab, then click Group and select Group.

Make sure you click on all the outline views (1, 2, and 3) to make sure that the new column is hidden when it's supposed to be. For example, if it's not hidden in 2 and it's supposed to be, you will have to go to Group under the Data tab, then select Group.

To remove a row or column and exclude it from the outline, go to the Data tab, select Ungroup, then click Ungroup.

Working with Custom Views

Custom views allow you to save display settings. For example, you can save column widths, row heights, cell selections, window settings, filter settings, and hidden rows and columns. You can also save print settings. These include headers, footers, margins, sheet, and page settings. When you save these settings in a custom view, you can easily and quickly apply them to a worksheet.

You can create different custom views for each worksheet; however, you can only apply a custom view to the worksheet that was active when the custom view was created.

Creating a Custom View

To create a custom view, first change the settings that you want to save in a custom view. We are going select a non-contiguous range of cells, as shown below.

Next, click on the View tab. Go to the Worksheet Views group, then click on Custom Views.

You will see the Custom Views dialogue box, as shown below.

Click the Add button.

Enter a name for the view in the Name box.

Now go to Include In View and select the settings that you want to include.

Click OK.

To apply a custom view, click the View tab again, then click Custom Views. Select the name of your view in the Custom Views dialogue box.

Click the Show button.

When you click the show button, the worksheet that was active when you created the view will be displayed.

Editing and Deleting Custom Views

To edit a custom view, display the custom view you want to edit by going to View>Custom Views, selecting your custom view, then clicking the Show button. Make any changes to the view that you want to make.

Go to View>Custom Views again. Click the Add button.

Re-enter the name of the custom view that you just edited.

You will see this warning:

Click the Yes button.

The custom view has been edited. It will be appear with your other custom views.

To delete a custom view, go to the View tab, click Custom Views, then select the name of the custom view you want to delete. Click the Delete button.

Using an Outline to Create a Custom View

Outlines can be used to help you create custom views. You can use an outline to show or hide rows and columns, as we learned earlier in this article. You can then create a custom view that has rows or columns hidden by using the outline.

Let's show you what we mean.

Here's our worksheet.

We are going to click the number 2 in our outline to hide all the data except for the columns that contain sums.

Now, we can go to View>Custom Views and save this as a custom view.

We'll name the custom view Totals.

Whenever we show the custom view "Totals," we will see our worksheet as it's displayed below.

Getting Quick Access to Custom Views

You can also add custom views to the Quick Access Toolbar so that you can access them quicker and easier than going through the steps of clicking on the View tab.

To do this, go to the Quick Access Toolbar and select

In the Choose Commands From dropdown menu, choose View Menu, as circled in red below.

Click on Custom Views to select it.

Next, click the Add button.

Click OK.

You will then see a Custom Views icon displayed in the Quick Access Toolbar. We have highlighted it below.

Click the downward arrow beside the icon.

Clicking this icon will bring up the Custom Views dialogue box where you can show, add, edit, or delete custom views.

Using Excel Templates

Excel 2013 offers you access to a wide variety of templates that you can use to create worksheets and workbooks. In Excel, a pre-designed template comes as a workbook. It may contain formatting, design, and layout. It may also include tables, column headers, row headers, pre-designed tables, as well as pre-designed lists. There are different types of templates for different types of workbooks and worksheets. For example, you can choose a Budget or Inventory template.

To access Excel's templates, click the File tab, then select New.

You can type in the type of template you want into the search box, or you can search by category by clicking on a thumbnail.

You should already be familiar with templates, as well as how to open an Excel 2013 pre-designed template. However, take the time to explore the multitude of pre-designed templates available to you.

Creating a Template

In addition to using a pre-designed template, Excel gives you the ability to create your own too.

Templates can be helpful to create if you want to create a universal look for all of your workbooks and worksheets. For example, if you do invoicing for your company using Excel, creating an invoicing template with your company's logo, name, and address can save a lot of time over adding those things each time you create an invoice. In addition, you can also add the rows and columns that you want to appear in your template.

In summary, you can save the following things in a template:

Text including page titles, row labels, and column labels.

Data, such as constant values that includes tax rates, profit margins, and contact information.

Graphics, such a logo (as we mentioned above).

Formulas that you will use on future sheets, such as sums and averages.

To create your own template, all you have to do is design your workbook. It may contain several worksheets or just one. Use the Insert tab to add graphics. Add row and column labels, data, any formulas (such as with a Total box for an invoice).

You can also customize the appearance by going to the Page Layout tab and choosing a theme and adding a background image.

Take time to format the text and data as well.

In the example below, we have started to create a template.

We started by adding a fill color to a selected range of cells by going to the Home tab and clicking the Fill Color button: .

Next, we clicked the Insert tab and inserted an image. We double clicked on the image to remove the background.

Next, we added row and column headers.

We already added our formulas earlier in this article.

Under the Page Layout tab, we selected a theme, specified our margins, and also chose to have the gridlines printed.

We also set our print area by selecting the area, then going to Print Area>Set Print Area.

If your spreadsheet is wide, you will also want to set the scale so the entire worksheet prints, as well as the orientation.

When creating your own template, you should go ahead and apply all the settings that you want applied to every workbook and/or worksheet that uses this template.

Take the time to create your template.

When you're finished, go to File>Save As.

Choose the location where you want to store the template.

Then, enter a name for the file in the File Name text box.

Choose Excel Template (*.xlts) from the Save as Type dropdown menu.