Create Excel Templates

How to Select Standard Excel Template?

First, we must open an Excel workbook and Go to File >- New >-

We may see a similar screen to the below screenshot.

It allows us to select from some predefined options divided by various categories. Then, you can choose the one relevant to your requirement and double click on it to use that template. For example, suppose we select the “Academic calendar” (any year) and double-click on it.

We may see a screenshot like the one below. We can see here that we can change the year and start using it for a different year than the one in which it was built, giving us the flexibility to use it whenever we want.

Here, we will need to make another change: “select the week start day” for a month. The screenshot is below.

However, if we want to see a preview first and then decide if we wish to select that template or not, do a single click.

If satisfied with it, we must click on the “Create” button to proceed with it. Otherwise, we need to close it by clicking on the “X” symbol in the dialog box and proceeding again. We can also move forward and backward to select the required one among the standard templates by clicking on the two symbols on the side of the dialog box (marked above in black).

Like this, in any template that we choose, there will be some things that we need to tweak to reuse for our purposes.

How to Create a Custom Excel Template?

It will be like the way copies are done from the original piece. We take the utmost care in implementing the changes in the original work, making sure all the formatting, headers, placeholders, etc., are perfectly done. Please devote a sufficient amount of time here, as it will replicate anything and everything (example, style, formatting, graphics, etc.) related to this workbook in its entirety.

What changes can be saved when we create a custom template?

It can save anything we can do in a workbook in a template. Some of these include:

  • Data Validation in ExcelData Validation In ExcelThe data validation in excel helps control the kind of input entered by a user in the worksheet.read more (selecting values from a list).Having a macro button.We can format some cells as we like (e.g., cells highlighted in yellow).Protect a range of cells from making any changes to it.Hide certain rows and columns, charts, images, excel hyperlinks, etc.Merge certain cells in excelMerge Certain Cells In ExcelMerging a cell in excel refers to combining two or more adjacent cells either vertically, horizontally or both ways. Merging excel cells is specifically required when a heading or title has to be centered over an area of a worksheet.read more or wrap excel textWrap Excel TextWrap text in Excel belongs to the “Formatting” class of excel function that does not make any changes to the value of the cell but just change the way a sentence is displayed in the cell. This means that a sentence that is formatted as warp text is always the same as that sentence that is not formatted as a wrap text.read more in some.Change the cell type to a specific format (e.g., date format in a cell)Freeze Excel cells so that header columns are always visible.

In the below screenshot, we have shown some of these examples in an Excel template we have created.

  • Cell B3 will only take inputs from a pre-specified list. (Go to cell B3, then go to the “Data” tab. Select “Data Validation”).

In the drop-down, we must select the first one,  “Data Validation.”

  • Then make the following changes as shown in the below screenshot.

As a result, a drop-down list is created.

  • Cells A3 to A6 will be highlighted in yellow. (We need to  select Cell “A3:A6”, go to  “Home Tab,” then click on  “Fill Color”)

  • The image will be there at the place shown (For this, we must go to the cell where we want to insert the picture, then go to the “Insert” tab, click on “Pictures,” and then upload the image where it is stored on your computer).

A picture is uploaded to that cell.

  • A button is present, which will run the required macro on clicking. (We must go to the cell where we want to place the button, then go to the “Insert” tab, and go to “Shapes.”)

  • Then, we must choose the “Shapes”; Right-click on it to select “Edit Text” if we want to write anything in it.

Then, we need to write, “Click me to run the Macro.”

  • Once done, right-click again and choose “Assign Macro.”

As a result, it will open a dialog box wherein we can assign the relevant macro we wrote in VBA.

  • Rows from 8 to 12 are hidden. (For this, we must, select rowsSelect RowsTo select a row, press shift+space on your keyboard.read more 8 to 12).

Then, we need to go to the “Home” tab, then go to “Format,’” then to “Visibility,” then to “Hide & Unhide,” and then “Hide Rows.”

Similarly, in the above screenshot, apart from the ones mentioned above, we can see:

  • Cell F2 will always be in currency format. (For this, we must go to cell J2, do “Ctrl+1,” then change the type to “Currency”)

  • Cells C15: D16 are merged into one. (For this, we need to select cell C15: D16 and then click on “Merge & Center in Excel“)

  • A worksheet is a freeze at cellFreeze At CellFreezing cells in excel is when we move up or down in the sheet, we freeze desired cells, not to be moved. To freeze cells in excel, select the cells to freeze. Then, in the View tab of the windows section, click on freeze panes.read more C19 so that despite scrolling down, contents on row 19 will always be visible. We can use this when we always want to see the headers in our worksheet. (For this, we must go to cell N20, then go to the “View” tab and click on “Freeze Panes.”

The output is shown below.

Now, assuming we have done everything that needs to be done in our workbook, we will show you the process that needs to be followed to save this created template and utilize it the next time.

How to use the Created Excel Template?

Once we have created our templates and are made the changes to the workbook or worksheet, we must click “Save As Type,” and we can save it in the format “Excel Template” (.xltx)’ if there is no macro. (Using Office 2016). However, if a macro is present, we must use the format “Excel Macro-Enabled Template” (.xltm).

The screenshot is shown below:

We saved it as ‘xltm’ since there was a macro present.

  • Now that we have saved the created Excel template, we can utilize it like the way we showed when selecting a standard template. Now, we need to open an Excel workbook.Then, go to File >- New >-

We may see a similar screen like the below screenshot.

We can see a “PERSONAL” category adjacent to the “FEATURED” one. All our templates will be present here.

We will go to the “Personal” one and double click the template “Personal” template (the name that we gave to your template). On clicking it, we may see a workbook with all the initial settings already present, which we do not need to do again.

You may see the screenshot below for reference.

Things to Remember

  • Using an Excel template is about creating a perfect first copy and duplicating it many times. So, invest an awful lot of time in designing the first copy.Depending on whether a macro is present or not in our template, save it accordingly. Save it as the “.xltm” file if the macro is present. Otherwise, you can save it in the “xlt” excel extensionExcel ExtensionExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.read more.The created Excel templates will be present in a different section called “PERSONAL,” apart from the already present ones in the “FEATURED” section.

This article is a guide to Create Templates in Excel. We discuss creating a custom Excel template, practical examples, and a downloadable template here. You may learn more about Excel from the following articles: –

  • Calendar Template in ExcelExcel Inventory TemplateCreating a Dashboard in ExcelCombination Charts in Excel