Create and Manage Workbooks and Worksheets: Excel 2013

This is the ‘Create and Manage Workbooks and Worksheets’ tutorial of the Excel 2013 MOS Foundation course offered by Simplilearn. We will learn various methods associated with using Excel workbooks and Excel worksheets in this tutorial.

Objectives

By the end of this lesson, we will be able to:

  • Create a new Excel workbook
  • Use different Excel worksheet operations
  • Add values to workbook properties
  • Save Excel workbooks in alternate file formats and to remote locations
  • Print Excel workbooks by setting the print area

Excel 2013 Workbooks

The Microsoft (MS) Excel workbook is a file within the MS Excel application, where one can enter and stored 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 on 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

  • Create a workbook with a blank document
  • Create a workbook from a template
  • Open an existing workbook

Creating a Blank Excel Workbook

Excel 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 Template

A 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 Workbook

An 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 Scenarios

John 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.

  • To create a new worksheet, open Microsoft Excel and click the File tab.
  • Click New and then click the Blank Workbook option.
  • To create a workbook from a template, under New, click the Search for Online Templates search bar and enter the type of template required
  • Click the Search icon.
  • Select any of the available Templates and click Create.
  • To open an existing workbook, click File, then click Open.
  • Click Computer and then click Browse.
  • In the open pop-up window, navigate to the Excel file you want to open, select it and click open.

Native and Non-Native Files

Let us first understand was Native and Non-Native files are.

  • The type of file format that each software program can create or accept is called as native files.
  • A software program that allows us to work and save files in a different format is called non-native files.

Some popular native file format in Excel 2013:

  • Excel Workbook(XLSX): It is the default XML based file format from Excel 2007 - 2013 versions.
  • Excel Workbook (XLS): It is the default file format from Excel 1997- 2003 version.
  • Excel Binary Workbook(XLSB): It is the binary file format for Excel 2007 to 2013
  • Excel Workbook Code(XLSM): It is the XML based and macro-enabled file format for Excel 2007 to 2013.
  • Excel Workbook Code(XLM): It is the macro-enabled file format for earlier versions of Excel. There are Excel workbook templates XLTS, XML data(XML) and Excel Addin(XLAM).

Some popular non-native file format used in Excel 2013 are

  • text(.txt): It allows the user to save a workbook as a tab-delimited text file. The User can save this file as Macintosh and MS-DOS operating system compatible.
  • Comma Separated Values (csv):  Allows a user to save a workbook as a comma delimited text file. The User can save this while as Macintosh and MS-DOS operating system compatible.

Eager to know more about Excel Workbook and Excel Worksheets? Click to view course intro!

Connecting or Importing external files

In Excel 2013, the main advantage of connecting to external data is a periodic analysis of this data without repeatedly copying it. Repeated copying is a 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:

  • Delimited: This option is used when the text contains the comma, tab, semicolon and other symbols.
  • Fixed Width: This option is used when all the rows have similar text length.

In Excel 2013 we can connect or import data from the following sources:

  • From Access: This option allows for easy access to data from MS Access databases that store huge information. Once the link to the access database has been registered in MS Excel then any information changes in the access database will result in automatic updating of the Excel file.
  • From the Web:  This option allows for access to data from websites such as share markets and live currency converters. It is a time-consuming task to copy the data from the website to MS Excel. Excel allows for easy import of external data from the website.
  • From Text Files: This option allows for access to text format files as supported in many operating systems. There are some other popular data sources in Excel to connect or import data for analysis including SQL server, analysis services, windows zero marketplace and Microsoft query.

External data can be imported in a number of ways.

  • Table: This is a general table format where the data will be imported in rows and columns.
  • Pivot Table Report: A pivot table reports is a summary of raw data in a table format. If we select this option, the data will be imported in a pivot table form.
  • Pivot Chart: A pivot chart represents data series, categories, and chart access the same way as a standard chart. It also allows us to filter controls right on the chart so we can quickly analyze a subset of the data.
  • Power View Report is a new feature introduced in Excel 2013. Power view report is an interactive data exploration, visualization and presentation experience that encourage intuitive, ad hoc reporting.
  • Only Create Connection: Creates a connection between the data source and the Excel file.

Business Scenarios

John 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.

  • To open a non-native file in Excel, click the open item under the file tab.
  • Click computer and then browse.
  • In the open pop-up window, navigate to the required folder, then select all files in the files of type Combo box.
  • Select the required .txt or .csv file and click open.
  • In the text import wizard, select the appropriate options, click next, then click finish.
  • To import external files click the data tab.
  • Select the from text option.
  • In the import text from the pop-up window, select the required text file and click Import.
  • Complete the steps in the Text import wizard and click finish.
  • In the import data dialogue box, choose the destination for the imported text and click OK.
  • Use the refresh all item under the Data tab to import any changes made to a text file.

Excel Worksheet Operations

A Worksheet contains different rows and columns. The intersection of a row and column is a cell. Various options can be performed using a worksheet.

  • Insert: This option allows we to insert a new worksheet to an existing workbook.
  • Delete: This option allows us to delete selected worksheets from a current workbook.
  • Rename: This option permits us to rename worksheet.
  • Move or Copy: This option allows we to move or copy a worksheet from one workbook to another workbook. You can also change the order of worksheet, using this functionality.
  • View Code: This option allows us to view VBA macro code in the selected worksheet.
  • Protect Sheet: This option allows us to lock or password protects the worksheet.
  • Tab Color: This option allows us to color the worksheet tab.
  • Hide: This option allows us to hide selected worksheet in the current workbook.
  • Unhide: This option allows us to unhide worksheets in the current workbook.
  • Select All Sheets: This option allows us to delete, move or copy workbooks to another worksheet.

Change Worksheet Tab Color

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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/changing-tab-colour.JPG

Hide and Unhide Excel Worksheet

Sometimes, 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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/hide-unhide-columns-rows.JPG

All Worksheets in a workbook can be hidden, but at least one worksheet needs to be visible.

Business Scenarios

To 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.

  • Right-click the worksheet tab to open the context menu.
  • To change the tab color, select the Tab color menu item and select color.
  • To hide a worksheet, right click on the worksheet tab and select the hidden menu.
  • To unhide a worksheet, right click on the worksheet tab and select the unhide menu.
  • To view the worksheet again, click to select the hidden worksheet and click OK.

Search and Replace Data

Excel 2013 allowed us to search for data and replace old data with new data. 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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/find-replace-data.JPG

GoTo and Named Box

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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/goto-name-box.JPG

Business Scenarios

The Marketing department has now been renamed as 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.

  • To find and replace a particular entry in an Excel worksheet, click the find and select menu in the editing group on the home tab.
  • Select the Replace item.
  • In the Find and replace the pop-up window, type the value to be found in the find what field.
  • Select the required .txt or .csv file and click open.
  • Type the value that will replace the current value in the Replace with field and click OK.
  • Close the find and replace the pop-up window.
  • To navigate to a particular row and column, select the GoTo option in the Find and select menu.
  • In the reference field, type the column and row to jump to and click OK.

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

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:

  • Existing file or Web page: This option allows us to hyperlink a web page or an existing file. We can also link pictures, videos, audio and other file formats.
  • Place in this document: This option allows us to place a hyperlink in the document. Once clicked on the cell, it jumps to the hyperlinked cell or worksheet.
  • Create a New Document: This option allows us to create new documents when clicked on the hyperlinked cell.
  • Email Address: This option allows us access to the specific email address so that we can send out an email by clicking hyperlink cell.

Business Scenarios

John 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 this by inserting hyperlinks.

Let us look at the steps used to insert hyperlinks in Excel workbook.

  • Select the desired cell to insert a hyperlink.
  • Right click on the selected cell.
  • Click hyperlink from the drop-down menu.
  • Type the URL in the address bar of the insert a hyperlink pop-up window.
  • Click OK.
  • Click the hyperlink to open the webpage.

A Hyperlink can also be created to an existing document or a place in the current document.

https://www.simplilearn.com/ice9/free_resources_article_thumb/video-preview-banner-microsoft-excel-2013-foundation.jpg

Modifying Workbook Theme

By default, in Excel 2013 every workbook uses an office theme. A workbook theme is 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 saved the current theme and apply it to other workbooks.

https://www.simplilearn.com/ice9/free_resources_article_thumb/workbook-themes-change.JPG

This feature allows we to change color and style with the selection of a single theme. Also, in case any changes have done in the cells, styles, and color, they will be applied automatically throughout the workbook.

Modifying Page Setup

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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/modify-page-setup.JPG

Margins: This option allows us to change or modify margins preferences based on our requirements. Some of the options it allows are:

  • Default settings or Normal
  • Wide and Narrow

Orientation: This option allows us to change or modify the orientation of the workbook layout to portrait or landscape view.

Size: This option allows us to change the paper size for printing. It also allows us to select different types of paper sizes.

Print Area: This option allows us to set a print area or clear prints area.

Breaks: This option allows us to set page breaks for workbooks.

Backgrounds: This option allows us to set the background of our workbook to a picture from local disc or from the web.

Print Titles: This option allows us to print only titles available in the Workbook.

Insert and Delete Columns and Rows

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:

  • Shift + SpaceBar: Allows us to select the entire row.
  • Control +  Spacebar: Allows us to select the entire column.
  • Control + or (-): Allows us to select rows or columns that need to be deleted in a workbook.
  • Control + shift + +: Allows us to insert columns or rows.
  • Clear Content Option: Allows us to clear the cell contents.

Modify Row Height and Column Width

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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/column-row-height-width.JPG

Hide and Unhide Columns and Rows

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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/hide-unhide-columns-rows.JPG

Business Scenarios

After 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 Excel workbook.

  • To change the theme, click the page layout tab and select the required theme under the themes drop-down.
  • To add a column, identify the column where a new column needs to be inserted.
  • Right-click on the selected column, select from the right-click Context menu.
  • To delete a column, select the column to be deleted, right-click and click to select Delete.
  • To insert a row, identify a row where a new row needs to be inserted.
  • Right-click on the selected row, select insert from the Right-click context menu.
  • To delete a row, select the row to be deleted, right-click and click to delete.
  • To hide a column, select the required column, right-click and click to hide.
  • To unhide the column, select the columns on either side of the hidden column, right-click and click to select Unhide.
  • To hide a row, select the required row, right-click and click to select.
  • To unhide the row, select the row above and the row below the hidden row, right-click and click to select Unhide.
  • To change the row height, select the row, click format and click to select row height.
  • In the row height pop-up window, type in the required size and click OK.
  • To change the column width, select the column, click format and click to select column width.
  • In the column width pop-up window, type in the required size and click OK.

Insert Header and Footers

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 purpose.

https://www.simplilearn.com/ice9/free_resources_article_thumb/insert-headers-footers.JPG

Headers and footers are not displayed on the worksheet in the normal view and displayed only in page layout view and on printed pages.

Customize Headers and Footers

  • Different First Page: This option allows us to differentiate the first page of the worksheet with a different header and footer
  • Different Odd and Even Pages: This option allows us to differentiate the header and footers for odd pages and even pages.
  • Scale with Documents: This option allows us to scale the header and footer to fit the document.
  • Align with Page Margins: This option allows us to align all the pages of the document with margins for printing.

Business Scenarios

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.

  • Click header and footer under the insert tab.
  • Click current date from the design tab.
  • Click the GoTo footer icon.
  • Click the page number icon from the design tab.
  • Click the Number of pages icon from Design Tab.
  • Click the GoTo header icon.
  • Click the first grid and type the desired text. Then press Enter so that text is displayed.

Data Validation

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 we 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 template 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.

  • Whole number: This option allows users to enter only integers. As per software programming. Whole numbers are called integers.
  • Decimal: This option allows users to enter only decimal values.
  • List: This option allows users to display a list of items as a dropdown in cells.
  • Date: This option allows users to restrict date entries.
  • Time: This option allows users to restrict time entries.
  • Text Length: This option allows users to enter text based on the validation rule.
  • Custom: This option allows users to customize options by using formulas or functions to create a validation rule.

Warning Messages for Data Validation

Data 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:

  • Stop: This message prevents users from entering invalid data into a cell with two options. Retry to edit the invalid entry or cancel to remove the invalid entry.
  • Warning: This message warns, or alerts users when an invalid entry is made with three options.
    • Yes - to accept the invalid entry,
    • No - To edit the invalid entry
    • Cancel - To remove the invalid entry
  • Information: This message informs users when an invalid entry is made with two options.
    • OK - To accept the invalid value
    • Cancel - To remove the invalid entry

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.

  • Select a cell and click data validation from the data tab
  • Select text length from the combo box.
  • Select Equal to, from data combo box and type the limit in the length box and click OK.
  • To customize the input message, select a cell and click data validation under the data tab.
  • Click the input message tab from data validation pop-up window and type the messages.
  • To customize error alert messages click Error Alert tab and enter the message.
  • Select warning from style combo box.
  • Click OK.

Enable Developer Tab

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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/enable-developer-tab.JPG

Macron Security Options

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:

Disable all macros without notification: If this is set as the default setting, then all macros in the document as well as the security alert, are disabled.

Disable all macros with notification: If this is set as a default setting, then all macros in the document are disabled, and the security alert will be notified.

Disable all macros except digitally signed macros: If this is set as a default setting, then all the macros in the document except the digitally signed macros as well as the security alert, get disabled without notification. This function is similar to disabling all macros with notification option.

Enable all macros: Not recommended as potentially dangerous code can run. If this is the default setting, all macros in the document will run without any alerts.

Trust Access to the VBA project object model: If this is the default setting, it provides a security code that will automate an office program and programmatically change the Microsoft visual basic for applications, VBA environment, and the object model.

Record a macros

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.

  • Macro Name: Key in a proper macro name and follow the below rules for setting the name for macros.
    • Rule 1: Do not use two words for the macro name.
    • Rule 2: Do not use application of built-in keywords for macro name.
    • Rule 3: Macro name should not start with special characters, symbols and numerical.
  • Assign Shortcut Keys: You can assign shortcut keys, the macro, as per our requirements, but it is not mandatory.
  • Store Macro: By default, Macros will be stored in the workbook where we are recording or writing code. If we want to store macros in a new workbook, we need to change this option, and if we want to run macros in all workbooks, select personal macro workbook.
  • Description: we can provide a description for each micro to help other users understand the macro, but it is not mandatory.

Business Scenarios

John 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.

  • Click options item in the file tab.
  • Click the customize ribbon.
  • Select the Developer checkbox to add the Developer tab to the ribbon and click OK.
  • Click macro security on the Developer tab.
  • Select the enable all macros(not recommended; potentially dangerous code can run) radio button and click OK.
  • Click Record macro item on the Developer tab. Right-click on the selected row, select insert from right-click context menu.
  • Enter the required details in the record macro pop-up window.
  • Perform the tasks to be recorded in the macro.
  • Click stop recording item.
  • Click macros in the view tab.
  • Select view macros.
  • Select this workbook for macros in.
  • Select the macro name
  • Click Run.

Backward Compatibility

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 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.

  • Inspect Document: This option allows checking for hidden properties of the workbook or personal information if any.
  • Check Accessibility: This option allows checking for the accessibility of the workbook content to people with disabilities.
  • Check Compatibility: This option allows checking for compatibility of the workbook features to work with earlier versions of Excel.

Excel applications have only backward compatibility which means the latest versions of Excel features cannot be used in the earlier versions of Excel.

Workbook Views

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.

  • Normal: Displays the ruler and allows for data to be entered into cells for insertion of charts and pictures into the worksheet
  • Page Break View: Displays the workbook with page breaks and page numbers to adjust work with content to print.
  • Page Layout: Displays the workbook as pages with rulers, displays headers, and footers. It is primarily used for printing purpose.
  • Custom View: Allows we to change workbooks using custom zoom options. Once this option is set and we opened the workbook, it will automatically zoom the world book according to the given specifications.

Zoom for Excel Workbooks

If the workbook contains huge data and does not display all the content in the window, we can use the zoom feature. We can use 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.

Zoom Out: Click on this option to decrease workbook, zoom Size, and the minimum zoom level is 10%.

Zoom In: Click on this option to increase workbook zoom size and max zoom level is 400%.

Get detailed information about Excel Workbook and Excel Worksheets here. Click here for course preview!

Freeze Panes

Sometimes, if our workbook contains a lot of content and is difficult to compare sections, in Excel 2013, we have an option called freeze pains that works in three ways as listed below.

  • Freeze Panes: This option allows for the rows and columns to be visible to rest of the worksheet, based on the current range selection, even while strolling up and down the worksheet.
  • Freeze Top Row: This option allows for top row visibility and is preferred when the top road contains any headers.
  • Freeze First Column: This option allows for first column visibility and is preferred when the first column contains any headers.

To Unfreeze the rows or columns, click the freeze panes command and then select unfreeze panes from the drop-down menu.

Split Window

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 Scenarios

John 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.

  • To freeze a row or column, click the view tab.
  • Click the freeze panes items in the windows group.
  • Click the freeze top row or freeze first column item.
  • To unfreeze a row or column, click to select unfreeze panes from the freeze panes drop-down menu.
  • To divide the window into different panes that each scroll separately, click split on the view tab.
  • To remove the dissection, click split again.

Show Formulas

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 Scenarios

The 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.

  • Select the cell whose formula has to be viewed.
  • On the Formulas tab, select Show Formulas and verify them.

Add Values to Excel Workbook Properties

In 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 Scenarios

The 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 Excel workbook.

  • Click the file tab.
  • Type appropriate entries in the title and tags text boxes.
  • Click show all properties to view the entire workbook properties.

Save Workbooks in Alternate File Formats

In 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.

PDF: Portable Document Format.

XPS: XML paper specification: Allows us to easily print a workbook. For instance, if we don't have access to the printer and someone else does, but they don't have the Excel application installed in their system, this file format comes to our rescue.

Text(txt): Saves a workbook as a tab-delimited text file. Text files of the most widely compatible format for data. They can't be opened and viewed on any computer. They're usually called .txt files.

Comma Separated Values(CSV): Saves a workbook as a comma delimited text file. Comma-separated value files are a kind of text files which do not store formatting information as contained in the original worksheet. They're usually called .CSV files.

Set the print area for an Excel Workbook

Sometimes we need to print a worksheet, which contains huge amounts of data. In this case, Excel allows us to set 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:

  • Prince Active Sheets: This option allows us to prince only active worksheet content.
  • Print Entire Workbook: This option allows us to print entire workbook content.
  • Print Selection: This option allows us to print only the selected area of a worksheet. Prince area of a worksheet can be sent using the page break view mode.

Business Scenarios

After 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.

  • Select the columns/area to be printed.
  • Click the page layout tab and click the print area item.
  • From the drop-down menu, select the print area menu item.
  • Click OK to continue.
  • Click the file tab and click to select print.
  • Click the print icon.
  • To clear the print area, click the black arrow to go back.
  • Click print area item again.
  • Click the clear print area menu item.

Save Workbooks to Remote Location

In Excel 2013, we have several options for sharing workbooks online. We can save that the file on the cloud, published 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.

https://www.simplilearn.com/ice9/free_resources_article_thumb/skydrive-by-microsoft.JPG

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.

Key Takeaways

Let us summarize what we've learned till now.

  • MS Excel Workbook is a file within the MS Excel application where one can enter and store data. A workbook can be created from an existing workbook or from a blank document.
  • 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.
  • Excel application contains four types of workbook views – normal, page break view, page layout and a custom view.
  • Excel worksheets can be saved to .pdf, .txt, and .csv formats. This feature ensures compatibility with other operating systems.
  • SkyDrive is an MS product that is used to store files online so that it can be accessed from anywhere.

Conclusion

This concludes Excel Workbooks and Excel Worksheets Tutorial.

  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

Request more information

For individuals
For business
Name*
Email*
Phone Number*
Your Message (Optional)
We are looking into your query.
Our consultants will get in touch with you soon.

A Simplilearn representative will get back to you in one business day.

First Name*
Last Name*
Email*
Phone Number*
Company*
Job Title*