Getting Started with Excel Formulas & Functions – Part 1

Excel is great for working with numbers and math.   In this lesson, you will improve your office skills by learning how to use Excel by opening up an existing workbook and using cell references in formulas.  Excel can automatically update results when values change or when you copy formulas.  You will also use the AutoSum feature to add totals to columns and rows and the AutoFill feature to copy formulas to other cells.

Excel is an electronic spreadsheet program that allows you to efficiently work with data that not only includes numbers but also databases.  An Excel workbook is a Microsoft Office file with a .xls extension.  Each workbook can accommodate way more data than you will ever need with the latest specifications being 1,048,576 rows by 16,384 columns.

Format the Spreadsheet

Open up the ex2-cruise-revenue file.

Before setting up the formulas, let’s do some basic formatting on the spreadsheet.

1. Click the Revenue tab to make it active
2. Select cells A1:G1 then in the Home tab > Alignment Group, click the Merge and Center button
   

3. Recommended text

   Now lets use the AutoFill feature to copy the Quarterly Month series to the other cells.

   Click cell B2 and point to the fill handle to display the + pointer then hold down the mouse and drag right through to cell E2 to AutoFill the labels.

   Release the handle and display the AutoFill options by clicking the down arrow.  Under fill options, make sure Fill Series is selected.  Your spreadsheet should look like the following:
   

4. Select the range F2:G2
5. On the Home tab > Alignment Group, click the Wrap Text button and then the Center button
   
6. Point the mouse pointer to the right edge of the heading area of column A to display as the mouse pointer as shown below.  With the pointer displayed, double-click to AutoFit the column.  This will change the column width to accommodate the longest entry.  Adjust any other columns as needed.
   
7. Select the column headings of the range B:E and adjust the column width until you see the ScreenTip reach 14:00 (103 pixels)
   
8. Click cell A1 and then in the Home tab > Styles group choose Heading 1.
   
9. Select the range A2:G2 and then in the Home tab > Styles group choose Heading 2.
   

What are formulas and Functions?

A formula is a math problem that performs calculations on data in Excel. You can add, subtract, multiply, divide and create complex calculations.  A function is a predefined formula written to simplify complex procedures. You can use functions to determine the sum, average, highest and lowest values in a column or row of data.

What are Cell References?

Cell references identify individual cells or cell ranges in columns and rows. Cell references tell Excel where to look for values to use in a formula.  Excel uses a cell reference style such as A1, which refers to columns with letters and to rows with numbers. The numbers and letters are called row and column headings.  This lesson shows how Excel can automatically update the results of formulas that use cell references, and how cell references work when you copy formulas.

The most common basic tasks you can do in Excel are Addition (+) Subtraction(-), Multiplication(*) and Division (/).   This can be accomplished by using these basic operators: +, -, *, / in your formulas.  Formulas always begin with an equal sign so for example, if you want to add the Quarterly Cruise Expenditures for Alaska as shown below you would type =B3+C3+D3+E3 and then press the ENTER key on your keyboard.

You could also use the built-in SUM function and achieve the same result.  The SUM function will take the cell references or ranges you specify and total them.  In our example below, the formula is =SUM(B3:E3) which means it’s totaling the cell ranges B3 through E3.

Total Columns and Use Autosum and AutoFill

A much easier way to add up your columns is to use the AutoSum feature in Excel.  As the AutoSum feature uses the SUM function with cell references in its calculation, the results can be automatically recalculated whenever those cells are copied or changed.

1. Click in cell F3 and on the Home tab > Editing Group click the AutoSum button.  Tap the ENTER key on your keyboard to accept the calculation.
   
2. Click in cell F3 and point the fill handle to display the + pointer then hold down the mouse and drag down through to cell F10 to AutoFill the totals.  Under fill options, make sure Copy Cells is selected.  Notice how Excel updated the cell references as it copied each row.  Your spreadsheet should look like the following:
   
3. Click in cell B11 and on the Home tab > Editing Group click the AutoSum button.  Click cell B3 and point the fill handle to display the + pointer then hold down the mouse and drag right through cell F10 to AutoFill the totals.  Under fill options, make sure Copy Cells is selected.  Your speadsheet should look like the following:
   

From the File tab > Save menu, save your spreadsheet for a future lesson on more of the basics, Absolute Values and the Average, Min, Max and Date Functions in Excel.