Chapter 6

Getting Started with Excel Chapter-6: Understanding Excel Files and Templates Structure 1.0 Learning Objectives 1.1 Extensions of Excel 1.2 Changing Excel file format and Extensions 1.3 Identifying file type of saved files 1.4 Introduction to Excel templates 1.5 Create templates 1.6 Summary 1.7 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain Extensions of MS Excel  Explain how to change Excel file format and Extensions in MS Excel  Explain Excel templates  Create Excel templates 1.1 Extensions of Excel Excel is one the tool which has a variety of extension in which we can save the file. A commonly used extension in Excel is .xlsx, which is used for storing a simple type of data. XLS is another type of default extension which was used until MS Office 2007. For storing a VBA code, we have XLSM. This is purely made for macros. Another extension, CSV (Comma Separated Values), delimits the data, which is separated by commas. XLSB extension is used for compression, saving, opening etc. For example, the filename “XYZ.doc” has an extension of “.doc” a file extension associated with the document file. Page 38 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel Excel file extensions can be of various types. Here we will cover the most common file type:  XLS – Excel file extension This extension is the most common and default type in the spreadsheet generated by Microsoft office. Prior to Excel 2007, the file extension was XLS. This extension refers to a file which contains all type of information including data, formats, graphics etc. The operating system recognizes the file type with the help of an extension and operates this file using the Excel application. The XLS is the default file type for Excel version 2.0 to Excel 2003.  XLSX – Excel file extension This extension is used by the spreadsheet files generated with the Excel version 2007 onwards. For an Excel file, the current default file extension is XLSX. XSLX is an XML based file format. With the use of this technology, the file with XSLX format is very less in weight or size, and as compared to the XLS file format, this leads directly to saving space. It requires less time to download or upload excel documents. Only one disadvantage of this XSLX extension is that this version is incompatible to run the files prior to Excel 2007. Page 39 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  XLSM – Excel file extension This extension file type is generated by the spreadsheet with the excel version 2007 onwards, including Excel macros. With the help of an extension, it’s easy to understand that the file contains a macro. Because of security reasons and for protecting a file with computer viruses, malicious macros, infecting computers, etc., this version comes into existence. This file extension is very reliable in terms of macros and for security reasons.  XLSB – Excel file extension If excel files contain a large amount of data or information, this file extension type fully supports in the compression process, saving, opening, etc. An excel file that contains a large amount of data takes a lot of time in the opening in processing that file. Sometimes while opening, it gets hanged and frequently crashes. 1.2 Changing Excel File Format & Extension: For changing the file extension, follow the below steps: - Open the workbook for which you want to change the format. - Go to the FILE tab. Page 40 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel - It will open a left pane window. There are a lot of options available in this pane. Refer to the below screenshot. - Click on Save As option as shown below. - It will open a dialog box, as shown below. Page 41 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel - Now you need to select where you want to save the file in the system. Refer to the below screenshot. - I have selected the Desktop for saving this file as a destination. - Under the File name field, give the file name for the workbook. Page 42 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel - Under Save as type field, we need to choose the file format. - Click on Save as type filed; it will open a list of formats as shown in the below screenshot. - Choose the file format and click on the Save button for saving the file. - It will save the file with the extension. 1.3 Identifying file type of saved files For this, follow below steps:  Select the file for which you want to know the file format.  Right-click on that file.  It will open a drop-down list of options.  Click on the Properties option from the list. Refer to the below screenshot. Page 43 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  It will open a Properties window.  Under the Type of file option, you can see the file type or extension of the file. Refer to the below screenshot.  Things to Remember about Excel File Formats: - Any file format doesn’t support all the features of excel like formulas etc.; then it will display an alert message box. - Then you can change the file format again. Page 44 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel 1.4 Introduction to Excel templates  Templates A template is a pre-defined workbook (with one or more worksheets) that can be used to help you create your final workbook. Using templates that contain pre-formatted worksheets can save you a lot of time. Every time you select (File > New) a template is used to create the blank workbook.  Important Facts About Templates: - An Excel template has the file extension (\".xltx\") - Excel templates with VBA code create workbooks which have the same VBA code. This is different to Word - A template can contain a single worksheet - A template can contain multiple worksheets - You can use templates in insert worksheet(s) into existing workbooks  Advantages of using a template? - Templates can save you a lot of time so it is worth taking a few minutes to look at the ones that are built-in. - A template can help you create workbooks that are consistent and can help you to customise your workbooks for specific tasks. - If you save a workbook as a template, you can create a new workbook containing the same formatting and styles by opening the template. - They provide a way of ensuring consistency among your reports.  What can a template include? Page 45 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel  When you save a workbook as a template all the information is saved within the file.  Formatting - These worksheets normally contain formatting and formulas, so they are \"ready to use\" immediately.  User Defined Functions, Formulas, Macros, Custom Toolbars.  When you use the (File > New) dialog box to open a template you are actually opening a copy of the original file.  Before you open template, you can see a preview to get some idea of the appearance of the workbook.  How to Create a Template?  If you have a particular workbook that you want to use as a skeleton or template for other workbooks you can save it as a template.  You can save yourself a lot of time by saving your favourite workbooks as templates.  Open one of your workbooks. Remove all the numbers but leave the formatting and formulas intact.  The next time you want to use this workbook you just need to fill in the numbers.  Select (File > Save As) and select \"Template (\".xlt\") in the save as type drop-down box.  Templates will automatically be saved in the default Templates folder.  The folder will automatically change to your default templates folder when you select this file format.  Select this folder or a subfolder. You can easily create a new subfolder within this folder. Page 46 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel 1.6 Summary  A commonly used extension in Excel is .xlsx, which is used for storing a simple type of data.  .xls is another type of default extension which was used until MS Office 2007.  For storing a VBA code, we have XLSM. This is purely made for macros.  A template is a pre-defined workbook that can be used to help you create your final workbook.  An Excel template has the file extension (\".xltx\") 1.7 References https://www.educba.com/extensions-of-excel/ https://docs.oracle.com/middleware/12211/bip/BIPRD/GUID- D52EA315-8A37-4A7C-942D-A884F4C34CD4.htm#BIPRD2745 https://bettersolutions.com/excel/workbooks/templates.htm https://www.oreilly.com/library/view/excel-2019- bible/9781119514787/c06.xhtml https://www.excel-easy.com/basics/templates.html Page 47 of 59 All Rights Reserved. Vol. TLE001/03-2022