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 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.
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.
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. 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 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.
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.
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.
Take a few minutes and explore Excel; you will find that it is intuitive to use and easy to learn.