1.1 Getting Started with Excel
-
Understand and be confident using basic functions in Excel.
-
Know how to apply sorting and filtering techniques to real data sets.
Introduction
Excel is one of the most important business applications of all time. It provides managers with the capability to perform sophisticated and powerful analyses, including connecting with corporate databases with hundreds of thousands of records, within moments. At the same time, it is intuitive and straightforward to use. It would be hard to find any business, small or large, whose managers do not rely on Excel in their daily work.
In this book we start with the basics, but will quickly demonstrate how to use some of the most advance features of Excel. At the outset it is important to get two important things out of the way. First, as a beginner, there is no substitute for experience. Each lesson in this book contains a rich set of practice problems and assessments that require that you apply what you are learning in the live Excel application. Don't skip over the problems. The only way to learn a skill such as Excel is to apply it.
The second important point is that you should also find ways to experiment with Excel. When you have a decision to make that may require some of you to analyze something numerically, put the numbers in Excel. When you are done with a homework assignment or set of practice problems, play with the numbers in the assignment. Eventually, you will feel comfortable with Excel as a tool to help you solve many types of business and personal problems. With that out of the way, we will first discuss the Excel working environment.
Working in Excel
When you open Excel, you are typically presented with a blank workbook. A workbook is an Excel file that stores all the information, previous calculations, and analyses that you may have already completed. A workbook must contain at least one worksheet. A worksheet contains the actual data and calculations. A worksheet is organized into a collection of cells arranged in the form of a table. Cells store individual pieces of data or calculations. Figure 1.1 shows a blank Excel workbook. You will notice a fairly sophisticated menu system at the top of the workbook—but don’t worry! We will discuss Excel’s menus in the next few sections. Near the bottom left of Figure 1.1, you can see the text "Sheet1" on a tab. This designates that there is one worksheet in the workbook. You can add a worksheet by clicking on the "plus" icon next to the word "Sheet 1." When you have more than one worksheet in your workbook, you can move to another worksheet in the workbook by clicking on that particular worksheet tab.
You will also notice that each of the worksheets is divided into a number of cells. You can select a cell by clicking on that cell. You will also notice that each cell has an address that can be described in terms of the row and column that corresponds to that particular cell; we refer to cells by their addresses. The upper left hand cell in a worksheet is cell "A1." The cell in column F and row 10 is called cell "F10."
Excel has literally dozens of menu items that you can access. The menu area is divided into major groups according to the function that each group performs. The major menu areas can be accessed by clicking on the various menu tabs. Figure 1.2 shows the various menu tabs and the specific menu items available under the "Home" tab. It is worth noting that it is possible that the menu displayed on Figure 1.2 will not exactly match the menu you will see when you open Excel on your computer, but it should be pretty close. Each of the major menu areas is further organized into specific groups that are separated with vertical lines. As you can see, the "Home" menu area is divided into menu groups (such as "Clipboard," "Font," and "Alignment"). There are other groups in the "Home" area that are not shown in Figure 1.2.
::: Error in source xml condition "'Win2013' in OSVersion" (name 'OSVersion' is not defined) :::Hidden within each of the menu groups are many other menu items that are not shown. In most cases, Excel displays the most commonly used menu features for convenient access. You can access the menu items that are not shown by clicking on the
::: Error in source xml condition "'Win2013' in OSVersion" (name 'OSVersion' is not defined) :::
icon in the bottom right area of each menu grouping. Don’t let the number of features and menu items overwhelm you; while each item can perform a helpful task, there are many items that you may never use.
::: Error in source xml condition "'Win2016' in OSVersion" (name 'OSVersion' is not defined) :::Hidden within each of the menu groups are many other menu items that are not shown. In most cases, Excel displays the most commonly used menu features for convenient access. You can access the menu items that are not shown by clicking on the
::: Error in source xml condition "'Win2016' in OSVersion" (name 'OSVersion' is not defined) :::
icon in the bottom right area of each menu grouping. Don’t let the number of features and menu items overwhelm you; while each item can perform a helpful task, there are many items that you may never use.
::: Error in source xml condition "'Win2019' in OSVersion" (name 'OSVersion' is not defined) :::Hidden within each of the menu groups are many other menu items that are not shown. In most cases, Excel displays the most commonly used menu features for convenient access. You can access the menu items that are not shown by clicking on the
icon in the bottom right area of each menu grouping. Don’t let the number of features and menu items overwhelm you; while each item can perform a helpful task, there are many items that you may never use.
::: Error in source xml condition "'Mac' in OSVersion" (name 'OSVersion' is not defined) :::Hidden within each of the menu groups are many other menu items that are not shown. In most cases, Excel displays the most commonly used menu features for convenient access. You can access the menu items that are not shown by right clicking to display the shortcut menu and selecting Format Cells.
::: Error in source xml condition "'Mac' in OSVersion" (name 'OSVersion' is not defined) :::
Don’t let the number of features and menu items overwhelm you; while each item can perform a helpful task, there are many items that you may never use.
The features in each menu tab are grouped together because they perform similar functions. (From left to right in Figure 1.2) The "File" menu is used to manipulate workbooks. The "Home" area includes items used in formatting the appearance of data in cells. Items in the "Insert" area include features that allow you to place specialized elements into your workbooks, such as charts, pictures, and shapes. The "Page Layout" menu items are used to format the appearance of worksheets. The "Formulas" menu items aid in performing calculations using Excel’s built-in functions. Items in the "Data" area are used to aid in importing and working with large amounts of data. The "Review" menu items help in sharing an Excel workbook with colleagues. The "View" menu items are used to change how a worksheet is displayed on the screen. The Excel menu area is also customizable; other menu tabs, such as the "Developer" or "Add-Ins" tabs, can be added to the Excel menu as you begin to use more of the features included in these groups. More details about each menu area will be provided when we start to use the items in each area.
Working with Excel Workbook Files using the "File" Menu
The "File" menu tab contains the menu items necessary for working with workbooks. You will notice that the "File" menu is different from the other tabs in Excel. First, it is a different color than the other tabs. In addition, clicking on the "File" menu tab causes the entire current worksheet to be replaced with the "File" menu items (see Figure 1.3), while clicking on any other tab reveals a new set of icons in the menu area that can be used with the open worksheet.
::: Error in source xml condition "'Win2016' in OSVersion" (name 'OSVersion' is not defined) :::The "File" menu tab contains the menu items necessary for working with workbooks. You will notice that the "File" menu is different from the other tabs in Excel. Clicking on the "File" menu tab causes the entire current worksheet to be replaced with the "File" menu items (see Figure 1.3), while clicking on any other tab reveals a new set of icons in the menu area that can be used with the open worksheet.
::: Error in source xml condition "'Win2019' in OSVersion" (name 'OSVersion' is not defined) :::The "File" menu tab contains the menu items necessary for working with workbooks. You will notice that the "File" menu is different from the other tabs in Excel. Clicking on the "File" menu tab causes the entire current worksheet to be replaced with the "File" menu items (see Figure 1.3), while clicking on any other tab reveals a new set of icons in the menu area that can be used with the open worksheet.
::: Error in source xml condition "'Mac' in OSVersion" (name 'OSVersion' is not defined) :::The "Mac Menu Bar" is displayed at the top of the window. This bar contains the Excel "File" menu which contains the menu items necessary for working with workbooks. Clicking on the "File" menu option displays a new set of options that can be used with the workbook, while clicking on the tabs in the Excel ribbon bar reveals a new set of icons in the menu area that can be used with the open worksheet.
::: Error in source xml condition "'Win2013' in OSVersion" (name 'OSVersion' is not defined) :::The left column of the "File" menu contains major categories of tasks that can be performed on workbooks. The "Info" portion of the "File" menu displays information about the current workbook and the users who have contributed to the file.
::: Error in source xml condition "'Win2016' in OSVersion" (name 'OSVersion' is not defined) :::The left column of the "File" menu contains major categories of tasks that can be performed on workbooks. The "Info" portion of the "File" menu displays information about the current workbook and the users who have contributed to the file.
::: Error in source xml condition "'Win2019' in OSVersion" (name 'OSVersion' is not defined) :::The left column of the "File" menu contains major categories of tasks that can be performed on workbooks. The "Info" portion of the "File" menu displays information about the current workbook and the users who have contributed to the file.
::: Error in source xml condition "'Win2013' in OSVersion" (name 'OSVersion' is not defined) :::The "New" portion of the "File" menu contains features related to creating a new workbook. You will notice that you can create a blank workbook or workbooks that are based on templates. Templates can be particularly useful because they often contain data, formatting and calculations that relate to common spreadsheet tasks (such as managing a budget or expense report).
::: Error in source xml condition "'Win2016' in OSVersion" (name 'OSVersion' is not defined) :::The "New" portion of the "File" menu contains features related to creating a new workbook. You will notice that you can create a blank workbook or workbooks that are based on templates. Templates can be particularly useful because they often contain data, formatting and calculations that relate to common spreadsheet tasks (such as managing a budget or expense report).
::: Error in source xml condition "'Win2019' in OSVersion" (name 'OSVersion' is not defined) :::The "New" portion of the "File" menu contains features related to creating a new workbook. You will notice that you can create a blank workbook or workbooks that are based on templates. Templates can be particularly useful because they often contain data, formatting and calculations that relate to common spreadsheet tasks (such as managing a budget or expense report).
::: Error in source xml condition "'Mac' in OSVersion" (name 'OSVersion' is not defined) :::The "New" option on the "File" menu allows you to create a blank workbook. The "New from Template" option allows you to create a workbook based on templates. Templates can be particularly useful because they often contain data, formatting and calculations that relate to common spreadsheet tasks (such as managing a budget or expense report).
"Open" is used to select a different workbook that you want to be opened in Excel (Figure 1.3). Here you can open recently used workbooks or select a workbook that has been saved on OneDrive (a cloud service provided by Microsoft) or locally on your computer.
The "Save" and "Save As" menu items allow you to (1) save the current workbook, (2) save a copy of the current workbook with a new name or location, or (3) save a copy of the current workbook as a different file type. These options can be helpful as you gain experience with some of the more advanced features in Excel.
The "Print" menu area contains items related to printing a worksheet. These items include selecting the correct printer, manipulating the various printer functions, and sending a worksheet to the printer. The "Print" area also displays a preview of how a particular worksheet will look once printed.
::: Error in source xml condition "'Win2013' in OSVersion" (name 'OSVersion' is not defined) :::Items in the "Share" area (Figure 1.4) are used make this workbook available for colleagues. The “Export” area allows you to convert your work to another file format (such as PDF) for those colleagues who prefer to review the work in a different application.
::: Error in source xml condition "'Win2014' in OSVersion" (name 'OSVersion' is not defined) :::Items in the "Share" area (Figure 1.4) are used make this workbook available for colleagues. The “Export” area allows you to convert your work to another file format (such as PDF) for those colleagues who prefer to review the work in a different application.
::: Error in source xml condition "'Win2019' in OSVersion" (name 'OSVersion' is not defined) :::Items in the "Share" area (Figure 1.4) are used make this workbook available for colleagues. The “Export” area allows you to convert your work to another file format (such as PDF) for those colleagues who prefer to review the work in a different application.
The “Close” menu closes the open workbook. The “Options” set of menu items allows you to customize the appearance and functionality of Excel. Finally, the “Account” menu items allow you to manage users.
::: Error in source xml condition "'Win2016' in OSVersion" (name 'OSVersion' is not defined) :::The “Close” menu closes the open workbook. The “Options” set of menu items allows you to customize the appearance and functionality of Excel. Finally, the “Account” menu items allow you to manage users.
::: Error in source xml condition "'Win2019' in OSVersion" (name 'OSVersion' is not defined) :::The “Close” menu closes the open workbook. The “Options” set of menu items allows you to customize the appearance and functionality of Excel. Finally, the “Account” menu items allow you to manage users.
::: Error in source xml condition "'Mac' in OSVersion" (name 'OSVersion' is not defined) :::The “Close” menu closes the open workbook. The “Preferences” set of menu items found on the "Excel" menu allows you to customize the appearance and functionality of Excel. Finally, the “Restrict Permissions” menu item allow you to manage users.
Getting help
::: Error in source xml condition "'Win2013' in OSVersion" (name 'OSVersion' is not defined) :::While all of the options and features available in Excel can be useful, it is difficult to keep track of them all. It is common to need help along the way. In addition to the large amount of Excel content that is available on the internet, there is a large help database available within Excel itself. To access this help, click on the “?” icon in the top right corner of any Excel window. The Excel Help area (see Figure 1.5) provides access to Microsoft’s extensive online help materials, information about how to get started with Excel, and information about the version of Excel you are using. As you gain more experience with Excel, you will find that using the help features can save you a lot of time and frustration.
::: Error in source xml condition "'Win2016' in OSVersion" (name 'OSVersion' is not defined) :::Finally, with all of the options and features available in Excel, it is nearly impossible to keep track of them all. It is not uncommon to need help along the way. In addition to the large amount on Excel content that is available on the Internet, there is a large help database available within Excel. To access this help, click on the lightbulb icon (Tell me what you want to do) in line with the menu options. The "Excel Help" area (Figure 1.5) provides access to Microsoft’s extensive online help materials, information about how to get started with Excel, as well as information about the version of Excel you are using. As you gain more experience with Excel, you will find that using the help features can save you a lot of time and frustration.
::: Error in source xml condition "'Win2019' in OSVersion" (name 'OSVersion' is not defined) :::Finally, with all of the options and features available in Excel, it is nearly impossible to keep track of them all. It is not uncommon to need help along the way. In addition to the large amount on Excel content that is available on the Internet, there is a large help database available within Excel. To access this help, click on the lightbulb icon (Tell me what you want to do) in the top right corner. The "Excel Help" area (Figure 1.5) provides access to Microsoft’s extensive online help materials, information about how to get started with Excel, as well as information about the version of Excel you are using. As you gain more experience with Excel, you will find that using the help features can save you a lot of time and frustration.
::: Error in source xml condition "'Mac' in OSVersion" (name 'OSVersion' is not defined) :::While all of the options and features available in Excel can be useful, it is difficult to keep track of them all. It is common to need help along the way. In addition to the large amount of Excel content that is available on the internet, there is a large help database available within Excel itself. To access this help, click on Help in the Mac Menu Bar at the top of the window and select Excel Help. The Excel Help area (see Figure 1.5) provides access to Microsoft’s extensive online help materials, information about how to get started with Excel, and information about the version of Excel you are using. As you gain more experience with Excel, you will find that using the help features can save you a lot of time and frustration.
Take a few minutes and explore Excel; you will find that it is intuitive to use and easy to learn.