Understand Spreadsheet Software

Microsoft Excel is the electronic spreadsheet program within the Microsoft Office suite. An  electronic spreadsheet  is an app you use to perform numeric calculations and to analyze and present numeric data. One advantage of a spreadsheet program over pencil and paper is that your calculations are updated automatically, so you can change entries without having to manually recalculate.  Table 1-1  shows some of the common business tasks people accomplish using Excel. In Excel, the electronic spreadsheet you work in is called a  worksheet , and it is contained in a file called a  workbook , which has the file extension .xlsx.  At R2G, you use Excel extensively to track finances and manage corporate data.

Table 1-1

Business Tasks you can Accomplish Using Excel

Details

When you use Excel, you have the ability to:

· Quick Tip

You can also use the  Quick Analysis tool  to easily create charts and other elements that help you visualize how data is distributed.

Enter data quickly and accurately

With Excel, you can enter information faster and more accurately than with pencil and paper.  Figure 1-1  shows a payroll worksheet created using pencil and paper.  Figure 1-2  shows the same worksheet created using Excel. Equations were added to calculate the hours and pay. You can use Excel to recreate this information for each week by copying the worksheet’s structure and the information that doesn’t change from week to week, then entering unique data and formulas for each week.

Figure 1-1Traditional Paper Worksheet

Figure 1-2Excel Worksheet

· Recalculate data easily

Fixing typing errors or updating data is easy in Excel. In the payroll example, if you receive updated hours for an employee, you just enter the new hours and Excel recalculates the pay.

· Quick Tip

Power users can perform more complex analysis using  Business Intelligence tools  such as Power Query and new forecasting functions.

Perform what-if analysis

The ability to change data and quickly view the recalculated results gives you the power to make informed business decisions. For instance, if you’re considering raising the hourly rate for an entry-level tour guide from $12.50 to $15.00, you can enter the new value in the worksheet and immediately see the impact on the overall payroll as well as on the individual employee. Any time you use a worksheet to ask the question “What if?” you are performing  what-if analysis . Excel also includes a Scenario Manager where you can name and save different what-if versions of your worksheet.

· Change the appearance of information

Excel provides powerful features, such as the Quick Analysis tool, for making information visually appealing and easier to understand. Format text and numbers in different fonts, colors, and styles to make it stand out.

· Create charts

Excel makes it easy to create charts based on worksheet information. Charts are updated automatically in Excel whenever data changes. The worksheet in  Figure 1-2  includes a 3-D pie chart.

· Share information

It’s easy for everyone at R2G to collaborate in Excel using the company intranet, the Internet, or a network storage device. For example, you can complete the weekly payroll that your boss, Yolanda Lee, started creating. You can also take advantage of collaboration tools such as shared workbooks so that multiple people can edit a workbook simultaneously.

· Quick Tip

The  flash fill  feature makes it easy to fill a range of text based on examples that are already in your worksheet. Simply type [Ctrl][E] if Excel correctly matches the information you want, and it will be entered in a cell for you.

Build on previous work

Instead of creating a new worksheet for every project, it’s easy to modify an existing Excel worksheet. When you are ready to create next week’s payroll, you can open the file for last week’s payroll, save it with a new filename, and modify the information as necessary. You can also use predesigned, formatted files called  templates  to create new worksheets quickly. Excel comes with many templates that you can customize.

Understand Formulas

Excel is a truly powerful program because users at every level of mathematical expertise can make calculations with accuracy. To do so, you use formulas. A  formula  is an equation in a worksheet. You use formulas to make calculations as simple as adding a column of numbers, or as complex as creating profitand- loss projections for a global corporation. To tap into the power of Excel, you should understand how formulas work.  Managers at R2G use the Project Leader Payroll Calculator workbook to keep track of employee hours prior to submitting them to the Payroll Department. You’ll be using this workbook regularly, so you need to understand the formulas it contains and how Excel calculates the results.

Steps

1. 1

Click cell E5

The active cell contains a formula, which appears on the formula bar. All Excel formulas begin with the equal sign ( = ). If you want a cell to show the result of adding 4 plus 2, the formula in the cell would look like this: =4+2. If you want a cell to show the result of multiplying two values in your worksheet, such as the values in cells B5 and D5, the formula would look like this: =B5*D5, as shown in  Figure 1-5 . While you’re entering a formula in a cell, the cell references and arithmetic operators appear on the formula bar. See  Table 1-2  for a list of commonly used arithmetic operators. When you’re finished entering the formula, you can either click the Enter button on the formula bar or press [Enter].

Figure 1-5Viewing a Formula

Table 1-2

Excel Arithmetic Operators

2. 2

Click cell F5

This cell contains an example of a more complex formula, which calculates overtime pay. At R2G, overtime pay is calculated at twice the regular hourly rate times the number of overtime hours. The formula used to calculate overtime pay for the employee in row 5 is:

O/T Hrs times (2 times Hrly Rate)

In the worksheet cell, you would enter: =C5*(2*D5), as shown in  Figure 1-6 . The use of parentheses creates groups within the formula and indicates which calculations to complete first—an important consideration in complex formulas. In this formula, first the hourly rate is multiplied by 2, because that calculation is within the parentheses. Next, that value is multiplied by the number of overtime hours. Because overtime is calculated at twice the hourly rate, managers are aware that they need to closely watch this expense.

Figure 1-6Formula with Multiple Operators

Details

In creating calculations in Excel, it is important to:

· Know where the formulas should be

An Excel formula is created in the cell where the formula’s results should appear. This means that the formula calculating Gross Pay for the employee in row 5 will be entered in cell G5.

· Know exactly what cells and arithmetic operations are needed

Don’t guess; make sure you know exactly what cells are involved before creating a formula.

· Create formulas with care

Make sure you know exactly what you want a formula to accomplish before it is created. An inaccurate formula may have far-reaching effects if the formula or its results are referenced by other formulas, as shown in the payroll example in  Figure 1-6 .

· Use cell references rather than values

The beauty of Excel is that whenever you change a value in a cell, any formula containing a reference to that cell is automatically updated. For this reason, it’s important that you use cell references in formulas, rather than actual values, whenever possible.

· Determine what calculations will be needed

Sometimes it’s difficult to predict what data will be needed within a worksheet, but you should try to anticipate what statistical information may be required. For example, if there are columns of numbers, chances are good that both column and row totals should be present.

Identify Excel 2016 Window Components

Using  Figure 1-3  as a guide, identify the following items:

Figure 1-3Open Workbook

· The  Name box  displays the active cell address. “A1” appears in the Name box.

· The  formula bar  allows you to enter or edit data in the worksheet.

· The  worksheet window  contains a grid of columns and rows. Columns are labeled alphabetically and rows are labeled numerically. The worksheet window can contain a total of 1,048,576 rows and 16,384 columns. The intersection of a column and a row is called a  cell . Cells can contain text, numbers, formulas, or a combination of all three. Every cell has its own unique location or  cell address , which is identified by the coordinates of the intersecting column and row. The column and row indicators are shaded to make identifying the cell address easy.

· The  cell pointer  is a dark rectangle that outlines the cell you are working in. This cell is called the  active cell . In  Figure 1-3 , the cell pointer outlines cell A1, so A1 is the active cell. The column and row headings for the active cell are highlighted, making it easier to locate.

· Sheet tabs  below the worksheet grid let you switch from sheet to sheet in a workbook. By default, a workbook file contains one worksheet—but you can have as many sheets as your computer’s memory allows, in a workbook. The New sheet button to the right of Sheet 1 allows you to add worksheets to a workbook.  Sheet tab scrolling buttons  let you navigate to additional sheet tabs when available.

· You can use the  scroll bars  to move around in a worksheet that is too large to fit on the screen at once.

· The  status bar  is located at the bottom of the Excel window. It provides a brief description of the active command or task in progress.  The mode indicator  in the lower-left corner of the status bar provides additional information about certain tasks.

5. 5

Click cell A4

Cell A4 becomes the active cell. To activate a different cell, you can click the cell or press the arrow keys on your keyboard to move to it.

Quick Tip

The button that displays in the bottom-right corner of a range is the Quick Analysis tool.

6. 6

Click cell B5, press and hold the mouse button, drag  to cell B14, then release the mouse button

You selected a group of cells and they are highlighted, as shown in  Figure 1-4 . A selection of two or more cells such as B5:B14 is called a  range ; you select a range when you want to perform an action on a group of cells at once, such as moving them or formatting them. When you select a range, the status bar displays the average, count (or number of items selected), and sum of the selected cells as a quick reference.

Figure 1-4Selected Range

Using OneDrive and Office Online

If you have a Microsoft account, you can save your Excel files and photos in OneDrive, a cloud-based service from Microsoft. When you save files in OneDrive, you can access them on other devices—such as a tablet or smartphone. OneDrive is available as an app on smartphones and tablets, making access simple. You can open files to view them on any device, and you can even make edits to them using Office Online, which includes simplified versions of the apps found in the Office 2016 suite. Because Office Online is web-based, the apps take up no computer disk space and you can use them on any Internet-connected device.

Enter Labels and Values and Use the AutoSum Button

To enter content in a cell, you can type in the formula bar or directly in the cell itself. When entering content in a worksheet, you should start by entering all the labels first.  Labels  are entries that contain text and numerical information not used in calculations, such as “2019 Sales” or “Travel Expenses”. Labels help you identify data in worksheet rows and columns, making your worksheet easier to understand.  Values  are numbers, formulas, and functions that can be used in calculations. To enter a calculation, you type an equal sign (=) plus the formula for the calculation; some examples of an Excel calculation are “=2+2” and “=C5+C6”. Functions are built-in formulas; you learn more about them in the next module.  You want to enter some information in the Project Leader Payroll Calculator workbook and use a very simple function to total a range of cells.

Steps

1. 1

Click cell A15, then click in the formula bar

Notice that the  mode indicator  on the status bar now reads “Edit,” indicating you are in Edit mode. You are in Edit mode any time you are entering or changing the contents of a cell.

Quick Tip

If you change your mind and want to cancel an entry in the formula bar, click the Cancel button  on the formula bar.

2. 2

Type Totals, then click the Enter button  on the formula bar

Clicking the Enter button accepts the entry. The new text is left-aligned in the cell. Labels are left-aligned by default, and values are right-aligned by default. Excel recognizes an entry as a value if it is a number or it begins with one of these symbols: +, −, =, @, #, or $. When a cell contains both text and numbers, Excel recognizes it as a label.

3. 3

Click cell B15

You want this cell to total the hours worked by all the trip advisors. You might think you need to create a formula that looks like this: =B5+B6+B7+B8+B9+B10+B11+B12+B13+B14. However, there’s an easier way to achieve this result.

Quick Tip

The AutoSum button is also referred to as the Sum button because clicking it inserts the SUM function.

4. 4

Click the AutoSum button  in the Editing group on the Home tab on the Ribbon

The SUM function is inserted in the cell, and a suggested range appears in parentheses, as shown in  Figure 1-7 . A  function  is a built-in formula; it includes the  arguments  (the information necessary to calculate an answer) as well as cell references and other unique information. Clicking the AutoSum button sums the adjacent range (that is, the cells next to the active cell) above or to the left, although you can adjust the range if necessary by selecting a different range before accepting the cell entry. Using the SUM function is quicker than entering a formula, and using the range B5:B14 is more efficient than entering individual cell references.

Figure 1-7Creating a Formula Using the AutoSum Button

Quick Tip

You can create formulas in a cell even before you enter the values to be calculated.

5. 5

Click  on the formula bar

Excel calculates the total contained in cells B5:B14 and displays the result, 378, in cell B15. The cell actually contains the formula =SUM(B5:B14), and the result is displayed.

6. 6

Click cell C13, type 6, then press [Enter]

The number 6 replaces the cell’s contents, the cell pointer moves to cell C14, and the value in cell F13 changes.

Quick Tip

You can also press [Tab] to complete a cell entry and move the cell pointer to the right.

7. 7

Click cell C18, type Average Gross Pay, then press [Enter]

The new label is entered in cell C18. The contents appear to spill into the empty cells to the right.

8. 8

Click cell B15, position the pointer on the lower-right corner of the cell (the fill handle) so that the pointer changes to , drag  to cell G15, then release the mouse button

Dragging the fill handle across a range of cells copies the contents of the first cell into the other cells in the range. In the range B15:G15, each filled cell now contains a function that sums the range of cells above, as shown in  Figure 1-8 .

Figure 1-8Results of Copied SUM Functions

9. 9

Save your work