9.1 Microsoft Excel Basics

Common Uses of Spreadsheets

Excel is a spreadsheet software application type that has rows and columns in a grid pattern. A cell is the intersection of a row and a column, and it looks like a rectangle. You can enter data into the cells to design tables and graphs and perform data analysis. A workbook is a file that contains at least one worksheet, which is a tab within a workbook where you can input data and design tables and graphs that use the data (Figure 9.2). The terms worksheet and spreadsheet are interchangeable, but Excel uses the worksheet terminology.

A workbook may contain several worksheets, similar to taking a hard copy of a set of data and adding it to another hard copy in a file folder. One benefit of using a workbook is that the worksheets within it can communicate with each other. Having multiple worksheets in one workbook gives you flexibility in organizing and analyzing your data. Data analysis grows in complexity as a business grows. Even a simple accounting system for a small business designed in Excel will require many sheets, and each worksheet can feed or receive data from other worksheets. One workbook can contain all of these worksheets together, as in a manila file folder.

Figure 9.2 An Excel workbook contains individual worksheets made up of cells. (Used with permission from Microsoft)

The cells are named based on their location. For example, the upper-left cell is in column A and row 1, so Excel designates its address relative to the other cells in the worksheet, or its cell reference, as A1. You can use these cell references to perform operations that use either formulas or functions. A formula is a mathematical operation that may use constants or cell references, or a combination of constants and cell references. All formulas must begin with “=.” For example, you can calculate 4 + 4 by typing “=4+4” in a cell, or you can add the contents of cell A1 and 4 by typing “=A1+4” or typing “=” and then clicking on cell A1 and then completing the formula.

A function is a mathematical or analytical operation that uses words instead of mathematical operators. All functions must also begin with “=” in Excel. For example, to calculate 4 + 4, you can type “=sum(4,4).” Excel can do the calculations for you just as you would using a calculator. Excel’s formulas and functions can take the place of a calculator.

One use for spreadsheets is to create a monthly food budget. You can keep track of how much you spend on groceries by category and on eating out at restaurants. You could list it by the type of product (i.e., milk) and by restaurant. Over time, you can use these numbers to determine your average cost for groceries and for eating out, and you will then be able to predict what you will spend on groceries. This tracking can serve as a control on your budget and will help you save money in the long run. You can use a similar process for all your personal finance and family expenses to enable you to create a budget that works for your situation by tracking expenses.

In business, the same principles apply. If you can track business sales, you will be able to allocate the money more effectively. In other words, by having a better accounting system and dispatching of orders, a manager will be able to see where money is being generated. If the manager realizes that a certain product is generating a higher profit, then the manager can allocate resources to increase the sales of that product. For example, the company could increase the unit production and hire more sales agents to sell that product, which in turn will increase distribution venues, such as corporate retail clients that sell the products in their stores. Not only that, the company may also increase the marketing and advertisement expenses of these products, to gain new clients. All of these actions stemmed from the tracking of sales and analyzing what products sell the most units. In all these layers of actions, there will be many worksheets tracking data, such as an order-dispatching spreadsheet or the unit cost manufacturing sheets.

Basic Features

The most salient Excel feature is the arrangement of cells in columns and rows. The appearance of Excel is similar to graph paper. You can easily customize the width or height to fit words or numbers in the cells. To navigate through the cells in the spreadsheet, you can click on the cell and then use the Enter or Return key to move down to the next row, use the Tab key to move to the next column, or use the arrows on your keyboard to move around the worksheet.

Spreadsheets are designed for data analysis. Each cell has its own integrated calculators that automatically compute the result when you type in a formula or function. Another major benefit to Excel is that you can quickly and easily copy an equation into other cells, so that you do not have to retype it. Excel has many other features that can save you time over using a calculator.

To demonstrate how efficient Excel can be in organizing and analyzing data, let’s look at an order-dispatching spreadsheet (Figure 9.3). Each row represents one order, and each column represents a different characteristic of the order, identified by the labels in the top row (called the headers), with the data separated into different cells.

Figure 9.3 Separating data by columns makes it easier to analyze and perform calculations with the data. (Used with permission from Microsoft)

Separating the data by columns serves multiple purposes. First, it allows a user to organize the information by any one or more of the columns. For example, you could organize the data by date, as shown in Figure 9.3 or by destination. You can also keep different types of data, such as item description (column D) and price per unit (column G), separated. That way, if the price per unit changed, it could be updated quickly and easily. Further, if they were combined in a single column, you would be unable to use formulas to do calculations, because Excel cannot perform calculations when there are both letters and numbers in a cell. Excel recognizes numerals for calculations but cannot perform calculations with words or letters, just as a calculator cannot calculate letters. Another benefit of separating data into categories by column is that each column can later become a graph and help the user visually analyze the information.

The data in column E and column G in Figure 9.3 are not linked to other columns; in other words, they do not contain formulas or functions but are raw quantities entered into the spreadsheet. Column H, however, is a quantity based on the data in columns E and G. The FOB $ is the number of products (column E) multiplied by the price per unit (column G). The formula is displayed at the top of the Excel worksheet in the Formula Bar, which is next to the “f_x ” symbol. You can see in the Formula Bar in Figure 9.4 that the formula is “=E2*G2,” yet the cell displays the result of the multiplication. The “*” is the mathematical symbol Excel uses for multiplication. This simple multiplication formula shows how Excel calculates numbers.

Figure 9.4 Clicking on a cell reveals the formula above the headers in the Formula Bar, while the result of the calculation shows in the cell. (Used with permission from Microsoft)

In order to apply this calculation to the rest of the data, you can repeat the formula in other cells in the same column. In Figure 9.5a, all data has been removed from column H, except for the first cell, which contains our formula “=E2*G2.” The easiest way to repeat the formula is to use AutoFill. Place the cursor on the lower-right corner of the cell, until the cursor changes to a black cross. Then, click and drag the cross to the end of your column of data, which copies the formula into each cell. Figure 9.5b shows the result of dragging the formula from H2 to H18. AutoFill is also a useful feature for repeating text. The text could be a static value, such as when you enter multiple orders that contain the same item, or a time value, such as months of the year or days of the week. You can simply type in “January” and then use AutoFill to drag across or down to fill in the rest of the months. This method works for dates, days of the week, other text such as Quarter 1 and Quarter 2, and nearly any pattern of numbers (i.e., odd numbers or counting by 5). When using AutoFill for a pattern of numbers or text you want to repeat, you need to select enough of the pattern so that Excel can recognize the repetition. For example, if you want to generate a list of numbers counting up by 5, you will need to select at least the cells that contain 5 and 10 so that Excel understands you want to increase the value in each row in the AutoFill by 5.

Figure 9.5 The AutoFill feature is useful to get the results without retyping or copying and pasting the formula. (a) Column H is empty, except for the first cell, which contains the formula, as seen in the Formula Bar. Clicking and dragging the formula to the end of the column automatically applies the same formula to subsequent rows. (b) By dragging the formula down the column using the AutoFill feature (small black cross sign in the lower-right corner of a cell), the values are calculated for each row. (Used with permission from Microsoft)

The Ribbon

As you recall from Essentials of Software Applications for Business, Excel’s interface includes a ribbon of commands, grouped into categories that are similar across Microsoft Office programs. The Home tab for Excel (Figure 9.6a) has commands such as conditional formatting and inserting/deleting columns or cells. The Insert tab (Figure 9.6b) includes inserting a chart or equation, which is not included on the Insert tab of other programs such as Microsoft Word. The Formulas (Figure 9.6c) and Data (Figure 9.6d) tabs are specific to Excel. These tabs include commands for analyzing data in various ways.

Figure 9.6 The Excel interface uses a ribbon to organize commands into tabs, which include (a) the Home tab and (b) the Insert tab, which are similar to other Office programs. The Excel ribbon also includes tabs specific to Excel, including (c) the Formulas tab, and (d) the Data tab. (Used with permission from Microsoft)

Creating Worksheets and Navigating Existing Files

Now that you understand how to navigate a worksheet, let’s see how to apply this skill. Suppose you need to build sales figures of different retail store locations. Each location will have its own worksheet, and then you’ll add one more sheet to total the sales figures and make a comparison using a table. Figure 9.7a gives the data for location X. You will need to use the copy-and-paste method to use this table to make the table for location Y. In the copy-and-paste method, copy text or objects by typing Ctrl+C (or right-click to Copy or use the Clipboard command group on the Home tab), and then typing Ctrl+V to paste the text or object in the new area. To use this procedure in this workbook, follow these steps:

1. Highlight the table, as shown in Figure 9.7a.
2. Press Ctrl+C to copy the table.
3. Create a new sheet by clicking on the circled plus sign at the bottom of the worksheet.
4. Click on cell A1.
5. Press Ctrl+V to paste the table into your new sheet.

This process copies the table exactly, so you will need to change the data so that it reflects the sales figures of location Y using the sales figures received from the finance department.

Data that you provide or that is provided to you is often raw data, and designing a worksheet or even a workbook helps to organize it. Replace the data from location X with the data from location Y and change the location, as shown in Figure 9.7b. Using the copy-and-paste procedure saves time when dealing with the same or similar scenarios because it eliminates the need to create a new table from scratch. Note, however, that this process works with cells that contain numbers and not formulas; copying and pasting tables with formulas requires additional work after the initial copy and paste to retain the correct formulas and cell references.

Figure 9.7 The copy-and-paste process makes it easy to create multiple tables that are similar to each other. Start with a formatted data table for location X. (a) Highlight (select) the table and copy it. Notice the dotted line around the selection to show that it has been copied. (b) Paste the table in a new worksheet and update it with the data for location Y. (Used with permission from Microsoft)

Now, replicate the steps for location Z: copy and paste the table, and then retype the real data. Next, focus on creating the final sheet that displays the totals. In the workbook, this would be “Sheet 4.” Because the summary table needs to be a similar structure to the other tables, you can copy and paste the same table here, but you will need to modify Column D so that it adds up the units sold in each location. However, the summary table will need to use the data from Sheets 1, 2, and 3.

To calculate the totals, you need an addition formula. Follow the same process used previously to insert a formula. First, in cell D2, type “=,” which tells Excel that a formula or a function is being entered. Next, click on the tab for Sheet 1 and then on cell D2 in that sheet (Figure 9.8a). Then, type the operator (in this case +), and then repeat the process for the appropriate cells in Sheets 2 and 3. When finished, press Enter, and Excel will display the total in the cell. Notice in the syntax that Excel has inserted “Sheet1!” before the cell designation in the first part of the formula. This tells Excel that it needs to use the contents of cell D2 in Sheet 1 for that portion of the calculation. The next element is “Sheet2!D2,” which is cell D2 in Sheet 2.

Figure 9.8 Excel allows formulas to reference data appearing on any worksheet in the workbook. To create a total of the number of units sold at each location, begin the equation with an equals sign. (a) Then, click on the relevant cell in the first worksheet. Insert the mathematical operator and click on another cell you want in the formula. The formula shows the references to other worksheets. AutoFill can complete the table with formulas that reference the relevant cells. (b) The completed table uses data from other worksheets. (Used with permission from Microsoft)

Now that the formula is in place for cell D2, use the AutoFill feature to apply the formula to subsequent rows. Highlight cell D2, click on the cross, and drag it to the bottom of the table. If you then click on cell D3, for example, you will see that the formula has been repeated but references cells D3 in the other sheets. Figure 9.8b shows the completed table.

This summary table can be very useful. In addition to providing a straightforward list of the totals, you could create graphs using this data and have a visual representation of sales trends. This is a simple scenario assumption; in the real-world sales totals, you would have much more than just five TV models. Imagine adding all the unit and dollar sale figures of three Best Buy locations in a given city; there would be hundreds of different products. You will learn increased complexity that will help in analyzing real-world problems such as this as your knowledge of Excel grows. The sorting and filtering feature is covered in Data Tables and Ranges, where you will learn how to set up a condition and a criterion to hide or show values for analysis.

Printing Worksheets

Excel offers two options for printing: either printing each worksheet separately or printing the whole workbook. To do either, first go to the File tab, and then click on Print (Figure 9.9). The default is to print only the currently opened worksheet, so we’ll do that first. Choose the number of copies you want for this page, then choose the correct printer. There are other options for printing, include changing the size of the paper, changing vertical or horizontal orientation of the paper, adjusting the margins, and regulating the scaling (i.e., zooming in or not while printing). Once you have chosen your settings, click on the Print icon, and Sheet 1 will come out of the printer. Different printers might have different options for you to choose from.

Figure 9.9 The options for printing are on the File tab. Click on Print. To print only a worksheet, leave the default selection of Print Active Sheets and make any necessary changes to the options. To print the whole workbook, select Print Entire Workbook from the first menu in the Settings. On a Mac, the steps are the same, but the appearance will differ slightly. (Used with permission from Microsoft)

It is important to note that, when constructing spreadsheets, what you see on the computer screen is rarely how it looks when you actually print the workbook. When you have large spreadsheets with multiple rows or columns, they may not always fit within the bounds of the printed page. When you print the document, the result could be columns of data split oddly across several pages. Be sure to preview the printed version prior to printing. When you select Print, you will see the print preview on the right of the screen. Double-check that the columns and rows are displayed in the way you would like them to print. If not, you can change the margins of the worksheet or insert a page break as appropriate to print a final, professional workbook. More will be covered about formatting the worksheets and workbook in Formatting and Templates in Excel.

If you want to print all the sheets in the workbook, select Print Entire Workbook from the drop-down menu under Settings (Figure 9.9). Then, click on the Print icon, and this time all the worksheets will be printed (including any blank worksheets that are in the workbook), instead of individual worksheets.