1.1Getting Started with Excel
Understand and be confident using basic functions in Excel.
Know how to apply sorting and filtering techniques to real data sets.
Excel is one of the most important business applications of all time. It enables managers to perform sophisticated and powerful analyses and to connect with corporate databases containing hundreds of thousands of records within moments. At the same time, Excel is intuitive and straightforward to use. It would be hard to find any business, small or large, whose managers did not rely on Excel in their daily work.
In this book we start by explaining the basics of Excel but will quickly move on to demonstrate how to use some of Excel’s most advanced features. At the outset it is important to mention two key points. First, there is no substitute for experience. Each lesson in this book contains a rich set of practice problems and assessments that require you to apply what you are learning in a live Excel application. Don’t skip over these problems. The only way to learn how to use Excel is to apply the skills you are being taught.
The second important point is that you should find ways to experiment with Excel outside of the examples and practice problems provided in this text. When you have a decision to make that may require 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 and be able to use it to help you solve many types of business and personal problems. With that out of the way, we will first discuss the Excel 2013 working environment.
Working in Excel
When you open Excel, you typically see 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. If it looks overwhelming right now, don’t worry! We will discuss Excel’s menu options 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-sign icon (+) next to the word “Sheet 1.” When you have more than one worksheet in your workbook, you can move between worksheets by clicking on a particular worksheet tab.
You will notice that each of the worksheets is a two-dimensional table made up of rows and columns. The rows are labeled with sequential numbers, and the columns are labeled with letters. Each specific cell within the table has a reference point, or address, that describes the cell’s location in terms of the row and column. For example, the upper left-hand cell in a worksheet is called cell A1. The cell in column F and row 10 is called cell F10. You can select a cell simply by clicking on it.
Excel has 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 Excel 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.
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 corner of each menu group. 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. View the menu tabs in Figure 1.2 from left o right. The File menu is used to manipulate workbooks. The Home menu includes items used in formatting the appearance of data in cells. Items in the Insert menu 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 menu 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 area as you begin to use more of the features included in those groups. More details about each menu will be provided when we start to use the items in each area.
Working with Excel Workbook Files Using the File Menu and Getting Help
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.
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.
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).
Open is used to select a different workbook that you want to open in Excel (see Figure 1.3). Here you can open recently used workbooks or select a workbook that has been saved on SkyDrive (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 in a different location, or (3) save a copy of the current workbook as a different file type. These options can be helpful to you 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.
Items in the Share area (see Figure 1.4) are used make this workbook available to 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 menu items allow you to customize the appearance and functionality of Excel. Finally, the Account menu items allow you to manage users.
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.
Take a few minutes and explore Excel; you will find that it is intuitive to use and easy to learn.