The correct answer is Three. Show
Mistake Points
Important Points
Key Points
Important Points
India’s #1 Learning Platform Start Complete Exam Preparation
Video Lessons & PDF Notes Trusted by 3,10,08,178+ Students From a birds eye view, Microsoft Excel, is a software program that is a part of the Microsoft Office suite, used to create spreadsheets. However, excel with it’s spreadsheets has done wonders in the world of business analytics. And in this tutorial we will learn the A to Z of Microsoft excel, with a heavy emphasis on creating and managing workbooks and worksheets. The Microsoft (MS) Excel workbook is a file within the MS Excel application, where one can enter and store data. A workbook contains multiple worksheets. Each worksheet is a combination of a number of cells that hold information pertaining to a particular subject and can be modified as per the requirements. A workbook defines the data that is contained within the worksheet. However, the manipulation of data happens only through worksheets (not workbooks). In Excel 2013, each workbook has a separate window. It becomes easier to work on workbooks or two monitors at the same time as the name of the workbook is displayed in the title bar. Usually, we can create a new workbook when we start a new project. There are several ways to create a workbook in Excel 2013
Creating a Blank Excel WorkbookExcel 2013 allows users to create a new workbook from a blank document. There is also an option to create a new workbook based on the existing workbook. By default, a new workbook contains three worksheets. However, we can change the number of worksheets in a workbook as per the requirements. Create a Workbook from an Excel TemplateA template is a pre-designed worksheet which could be modified to suit users' needs. The Excel template contains predefined formulas and custom formatting. This saves a lot of time and effort while working on a new project. To create a workbook from a template, we need to select an appropriate template, as per the requirements. Besides Microsoft, there are many individual users as well as third-party providers to create customized templates. Open an Existing Excel WorkbookAn existing workbook is one that has been previously saved and stored in the computer or on the web. One can open existing workbooks from local computer drives, Skydrive, online storage and other online storage places. Skydrive is a Microsoft product, and anyone can sign in or register on Skydrive for storing files online. Business ScenariosJohn has been assigned the task of creating an inventory data sheet for his company’s assets. To do this, he has to work extensively on Microsoft Excel 2013. He needs to enter and edit data in workbooks, starting with creating a workbook. Let us take a look at the overview of the steps for opening an Excel workbook.
Let us first understand what Native and Non-Native files are.
Some popular native file format in Excel 2013:
Some popular non-native file format used in Excel 2013 are
Connecting or Importing External FilesIn Excel 2013, the main advantage of connecting to external data is a periodic analysis of this data without repeatedly copying it. Repeated copying is time-consuming and an error-prone process. By default connections to external data may be disabled on the computer. If we want to use this feature, we need to first enable the external data connections from trust center settings. There are two ways of importing data:
In Excel 2013 we can connect or import data from the following sources:
External data can be imported in a number of ways.
Business ScenariosJohn has been assigned the task of taking an inventory of his company’s data assets. He will have to use data from non-Excel files, as well. He wants to import text files into Excel 2013. He also wants to explore the Get External Data option in Excel. Let us have an overview of the steps for importing files into Excel workbook.
An Excel Worksheet contains different rows and columns. The intersection of a row and column is a cell. Various options can be performed using a worksheet.
In Excel 2013, different worksheet tabs can be differentiated by the use of different tab colors. If sheet tabs have been color-coded, the sheet tab name will be underlined in the user-specified color when selected. If the sheet tab is displayed with a background color, then the sheet has not been selected. Hide and Unhide Excel WorksheetSometimes, we may want to hide certain worksheets for security and later unhide when required. For instance, while creating a dashboard for the top management to review, we can easily hide the rule data worksheet. When worksheets are hidden, there is no effect on formulas. All Worksheets in a workbook can be hidden, but at least one worksheet needs to be visible. Business ScenariosTo create and collate employee data, John has to work with multiple worksheets at a time. To be able to manage multiple worksheets, John wants to use the worksheet Tab Color and Hide/Unhide options. Let us have an overview of the steps for using tab color and hide/Unhide in Excel workbook.
You can search for data and replace old data with new data in Excel 2013. This feature is very useful to search and replace data in multiple records instead of moving from one cell to another to make changes. This function also saves a lot of time and effort.
GoTo and named box features in Excel can be used to quickly move to different cells in a worksheet. This feature is useful when we're working on a large set of data. The GoTo and Named Box functionality can be used to select named cells and a specific data range in a worksheet. The GoTo function allows us to select all comments, Constants, Formulas, Visible cells, conditional format, and blank cells in a worksheet.
Business ScenariosThe marketing department has now been renamed the Online Marketing department. John has been assigned the task of changing this in the employee data sheet. He wants to complete this task using the Find and Replace tool in Excel 2013. While managing the employee data records, John has to navigate through large worksheets in Excel. He wants to explore easier ways of navigating a worksheet, such as GoTo and Name Box. Let us have an overview of the steps used to Find and Replace in Excel workbook.
For worksheets with a lot of data, select the column reference first in the GoTo pop-up window, and then jump to the row using the Name box. Hyperlinks enable quick access to other files, documents, and Excel workbooks via links. The hyperlinks that we add to the Excel Worksheets can be of the following types:
Business ScenariosJohn is preparing an invoice template for the purchasing department. He needs to provide a link to a particular web page within the template for reference. This can be done by inserting hyperlinks. Let us look at the steps used to insert hyperlinks in Excel workbook.
A hyperlink can also be created to an existing document or a place in the current document. By default, in Excel 2013 every workbook uses an office theme. A workbook theme has a unique sense of colors, fonts, and effects. These themes are shared across MS office programs so that all the official documents can have a uniform look. You can browse for themes, customize them based on the requirements, or even save the current theme and apply it to other workbooks. This feature allows us to change color and style with the selection of a single theme. Also, in case any changes are done in the cells, styles, and color, they will be applied automatically throughout the workbook. A worksheet sometimes contains a large amount of data or even multiple charts. If we want to print worksheet or workbooks, we first need to fine tune the page setup options.
Margins: This option allows us to change or modify margins preferences based on our requirements. Some of the options it allows are:
In a worksheet, we can insert and delete columns or rows. Columns are labeled from A to XFD, whereas the rows are labeled from 1 to 1048576. Below are the shortcut keys to insert and delete columns or rows:
In Excel 2013, by default, each row, height and column width is set to the same measurement. We can change the row height and column width in several ways, such as text wrap and cell merge. Sometimes we need to manually change the row, height and column with the displaying cell contents clearly, or use autofit the content. The row height value can be changed between 0 to 249 and column width value can be changed between 0 to 255. Sometimes, we may want to compare certain rows or columns without changing the structure of the worksheet, or by removing a row or column temporarily instead of deleting them permanently. Microsoft Excel has a feature that allows us to temporarily hide a row or column from view.
Business ScenariosAfter looking at the employee data table, John's manager has asked him to change the theme of the worksheet. He has also asked John to delete the SSN column and insert a new column to add the employees work timing details. Also, John has to hide the earnings data when the table is displayed to others without deleting the column. Let us look at the steps used to perform the above tasks in the Excel workbook.
Microsoft Excel 2013 allows us to customize a worksheet by adding headers and footers. We can add pictures, page numbers, copyright information, date, time elements in headers and footers of a worksheet. Generally, this information is inserted for printing purposes. Headers and footers are not displayed on the worksheet in the normal view and displayed only in page layout view and on printed pages.
John is preparing an invoice for the purchasing department. He needs to add the time, page number and company name in the header and footer of every sheet in the invoice. Let us look at the steps used to perform the above tasks in Excel workbook.
Data validation is an Excel feature that allows us to restrict the data entered in a cell. We can prevent invalid user entry's through data validation. This feature allows us to enter invalid data but warns us when we try to type it in the cell and provides custom messages to define what type of data the user can enter into the cell. This function also provides instructions guiding users to enter correct entries. Data Validation is used mainly for creating common templates or workbooks to work with multiple users for storing accurate and consistent data. By using data validation, we can prevent invalid user entries through set rules. Below of elevation rules with equal to, between, minimum and maximum values.
Warning Messages for Data ValidationData Validation will show the default input and alert messages to users. An input message to guide users on the type of data that should be entered in the cells. This message appears near the cell. There are three types of error alert messages displayed to the users when they enter invalid data:
Business Scenarios:John is collecting the details of new employees in his company. He wants to restrict the data that would be entered in a cell so that the employees enter the correct details. John performs this using the data validation option. Let us look at the steps used to perform the above tasks in Excel workbook.
In Excel 2013, the Developer tab is not enabled by default, but we need to set it up to use the features given below: Write Macros in the visual basic editor to automate tasks. Run macros that were previously recorded or written. Use XML commands to work with XML data. Insert and use form and Active X controls. Create applications to use with Microsoft office programs. When we open a workbook, we can change the macro security settings to control which macro to run and under what circumstance. There are several macro security settings options:
Macros are a set of directions or instructions for Excel to automate a task to be performed in a particular worksheet with a simple click of a button. A micro recorder registers all the steps required to complete the transaction that we want the macro to perform. These steps can include typing text or numbers, clicking cells or commands on the ribbon or on menus, formatting, selecting cells, rows or columns and dragging the mouse to select cells on the worksheet.
Business ScenariosJohn has been assigned the task of highlighting the earnings of the employees in the accounts department in an Excel workbook. Along with the employee details, he wants to explore the use of macro details to do this. Let us look at the steps used to perform the above tasks in Excel workbook.
Backward compatibility means checking the compatibility with earlier models or earlier versions of the same product. A new version of the program is said to be backward compatible if it uses files and data created by an older version of the same program. Backward compatibility is important, as it enables easy exchange and accessibility of data irrespective of the Excel version in use. In general, manufacturers are trying to keep all their products backward compatible. However, at times we need to sacrifice the backward compatibility feature in any product to take advantage of new technology. In Excel 2013, we can check the backward compatibility for earlier versions in three ways.
Excel applications have only backward compatibility which means the latest versions of Excel features cannot be used in the earlier versions of Excel. In Excel 2013, by default, workbook views are said to be normal, and sometimes we need to change based on the requirements. Excel application contains four types of workbook views.
If the workbook contains huge data and does not display all the content in the window, we can use the zoom feature. We can zoom in and zoom out with a camera to increase the size of an object in the camera's viewer. Zoom option is found at the bottom right corner, next to the workbook view icons.
Freeze PanesSometimes, if our workbook contains a lot of content and is difficult to compare sections, in Excel 2013, we have an option called freeze panes that works in three ways as listed below.
To unfreeze the rows or columns, click the freeze panes command and then select unfreeze panes from the drop-down menu. Sometimes, we may need to compare different sections of the same workbook without creating a new window. In such cases, we can use the split window functionality. This command allows us to divide the worksheet content into four parts with scroll bars and increase or decrease the window size. Business ScenariosJohn is working with a lot of data in Excel. He needs to scroll down and view rows of data, but when he reaches the bottom of the screen, the column is named in the top row disappear. Also, he has not been able to view the entire data sheet from left to right. To view all part of the data sheets, he wants to use freeze panes and split window features in Excel 2013. Let us look at the steps used to perform the above tasks in Excel workbook.
In Excel, by default, we can see formula result in cells and sometimes we may need to see which cells contain formulas. By using the show formulas feature, we can see the formulas in all the cells instead of the formula result. This feature allows us to quickly read through all formulas to check for errors. Business ScenariosThe HR team has sent an earning summary to John. He has been asked to check the formula used and verify the calculations. Let us look at the steps used to perform the above tasks in Excel workbook.
Add Values to Excel Workbook PropertiesIn Excel 2013, by default, a workbook author is the name of the person who created the workbook. It is usually one name, however, at times, a workbook may have several authors and requires adding other author's names to the workbook. We can add additional author information, such as title, tanks and comments, status, category subject, hyperlink base company name and manager of the author. Business ScenariosThe HR team has sent an employee report to John. He has been asked to add a title and tag to the workbook so that it would be easier to organize and retrieve the workbook. Let us look at the steps used to perform the above tasks in the Excel workbook.
Save Workbooks in Alternate File FormatsIn Excel 2013, by default, workbooks will be saved with XLXS file extension, and we can save a workbook in alternate file formats as listed below.
Set the Print Area for an Excel WorkbookSometimes we need to print a worksheet, which contains huge amounts of data. In this case, Excel allows us to set up a print area. This option allows us to set a print area based on our requirements and clear the print area if it is not required. We can print workbooks in three ways:
Business ScenariosAfter looking at the employee data table, John's manager has asked him to print the employee_code, Last_Name, first_name, SSN, and Region columns. John needs to set the prince area to print these. Let us look at the steps used to perform the above tasks in Excel workbook.
Save Workbooks to Remote LocationIn Excel 2013, we have several options for sharing workbooks online. We can save the file on the cloud, publish a link, share the file through several social media platforms, or send it by email. We can save Excel files on the cloud and share it using Windows SkyDrive. SkyDrive is an online storage space hosted by Microsoft. The advantage of using the cloud space is that we can access the files from anywhere and from any device. However, we can use Skydrive only if we have a Microsoft account. Here’s a quick summary of the Excel 13 workbook and worksheet tutorial:
Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today. Creating and managing workbooks and worksheets is an essential part of excel 2013. And now that you have learned it, you might wish to master excel, or take another leap forward, and step into business analytics. With Simplilearn Business Analyst Master’s Program, you will not just be able to master excel, but other BI tools like, Tableau, Power BI, Jira and various other tools too! Enroll now, and start your journey to becoming a successful business intelligence professional now |