MS Office Excel

Introduction

Microsoft Excel is a powerful electronic spreadsheet program you can use to automate accounting work, organize data, and perform a wide variety of tasks.  Excel is designed to perform calculations, analyze information, and visualize data in a spreadsheet. Also this application includes database and charting features.

1. Launch Excel

To launch Excel for the first time:

1. Click on the Start
2. Click on All Programs.
3. Select Microsoft Office from the menu options, and then click on Microsoft Excel 2013.

1. Window Features

The purpose of the window features is to enable the user to perform routine tasks related to the Microsoft applications.  All the Office applications share a common appearance and similar features. The window features provide a quick means to execute commands. Here are some pertinent Excel features:

1. Spreadsheet Terms

1. Mouse Pointer Styles

The Excel mouse pointer takes on many different appearances as you move around the spreadsheet.  The following table summarizes the most common mouse pointer appearances:

1. Spreadsheet Navigation

The following table provides various methods to navigation around a spreadsheet. 

1. Practice moving around the spreadsheet.
2. Practice selecting cells and cell ranges.

1. Basic Steps for Creating a Spreadsheet

When creating a spreadsheet it is recommended to do the following steps:

1. Made a draft of your spreadsheet idea on paper.
2. Enter the data from your draft onto the actual spreadsheet.
3. Format your data after entering onto the spreadsheet.
4. Calculate data by using mathematical formulas.
5. Save the document.
6. Preview and Print the spreadsheet.

1. Enter and Format Data

1. Create Spreadsheet

1. Illustration of spreadsheet to be completed in exercise below:

2. Open Excel Practice File.xlsx , and then click on the Budget sheet                 

(The instructor will indicate the location for this file.)

1. Select cell A1, and then type Budget for Guest Speakers.
2. Select cell A3, type Item, and then press the Tab
3. Select cell B3, type Fall, and then press the Tab
4. Select cell C3, type Spring, and then press the Tab
5. Select cell D3, type Summer, and then press the Tab
6. Select cell E3, type Annual, and then press the Tab

1. Adjust Column Width

Initially all columns have the same width in a spreadsheet. Often you will need to make columns wider or narrower.  For example, a long text entry in one cell will be cut off/truncated when the cell to its right contains any information. Likewise, numbers will appear as pound symbols ### when larger than cell width.  There are several ways to modify column width.

1. Increase the width of column A via the dragging method so that all text entries are visible.
2. Decrease the width of column C via the dragging method until pound symbols ### appear.

3. Increase the width of column C to return to its original size.
4. Type Text and Numbers

Use the plus sign mouse pointer to select a cell then begin typing in that cell to enter data.  If there is existing text/data in a cell, the new text will replace the existing text.  Press the Enter or Tab key after typing text in a cell.

1. Type the following text and numbers in rows 10 and 11:

1. Undo and Redo

Use the Undo      button to undo (reverse) previous actions in reverse sequence. Choose this option immediately after performing an unwanted action.  Note that Undo is not available for all commands.  The Redo       button will restore the process that was just undone.

1. Click on the Undo The last item that you typed is removed from the spreadsheet.
2. Click on the Redo           The text that you removed with Undo should be replaced.

1. Insert and Delete Rows and Columns

Insert rows and columns to add information between existing rows or columns of information.

1. Select any cell in column C.
2. On the Home ribbon in the Cell group, click on the Insert drop-down arrow, and then select Insert Sheet Columns. A new column will appear to the left of your selected column.
3. Click the Undo
4. Select any cell in row 6.
5. On the Home ribbon in the Cell group, click on the Insert drop-down arrow, and then select Insert Sheet Rows. A new roll will appear above your selected cell row.
6. Select cell A6, and then type Photocopy Services.
7. Press Tab and complete the additional columns as follows:

1. Text and Number Alignment

Microsoft Excel aligns data in a cell in three ways; left, center, and right. Also, a range of cells can be merged into one cell; this is good for text titles. The default text alignment is left, and the default number alignment is right.  Alignment can be changed by using the alignment  icons located on the Home ribbon in the Paragraph group.

Select a range before changing alignment to more than one cell at a time.

1. Select cell A3, and then click on the Center alignment button, located on the Home
2. Select the range B3:E3, and the click on the Center alignment button, located on the Home
3. Select the range A1:E1, and then click on the Merge & Center button, located on the Home

1. Format Fonts

Character formats include changing the font, point size, and style of text or numbers. The fastest way to change fonts is to use the associated buttons on the Home ribbon:

1. Select cell A1, and Increase the point size for the title, by clicking on the dropdown arrow on the Font size
2. Select cell range A3:E3, and then click on the Bold button to bold text.

1. Format Numbers

Excel provides many different types of numeric formats including currency, percent, comma, scientific, etc. On the Home ribbon the numeric formats are located in the Number group. Select the drop-down arrow next to General to view all format types. Select a range of cell/s before choosing format.  In fact, this range can include cell/s that does not yet contain data.

1. Select the cell range B4:E12.
2. Click on the Comma button, located on the Home
3. With that same range selected, click on the Currency button, located on the Home 
4. To display only dollars and no cents, click on the Decrease Decimal button, located on the Home

Note: To remove a number format from cells, select the General format option from the Number group.

1. Cut, Copy, and Paste Text

Avoid retyping in Excel by moving or copying text and formulas.  The following list includes commands and definitions involved in cut, copy, and paste.

Suppose you want to show an identical budget for an additional year.  In this exercise, you will copy data in cell range A3:E13, then paste it to sheet2.  

1. Select cell range A3:E13.
2. Click on the Copy button, located on the Home
3. Click on the Copy sheet  
4. Select cell A3, and then click on the Paste button, located on the Home
5. Click on the Undo button to clear data from spreadsheet. This sheet will be used again for another exercise.

Note: When you copy a range, a moving border with appear around the selected area. Once you paste the data to remove the moving border, double click in any cell outside of the selected range. 

1. Print a Spreadsheet

Click on the File tab, and select the Print option.  Preview your spreadsheet on the right-hand side of the File screen. If you are satisfied with the preview, click the Print button, otherwise click on the Home tab to return to the document and edited document. (Page Setup options are covered in the Additional Features section on page 47.) 

1. Exit Excel

When you are finished using Excel, use click on the File tab, and select the Exit option or click on the Close     button in the upper right-hand corner of the Excel window.  If your file has recently been saved, Excel will exit promptly.  However, if the file needs to be saved before quitting, Excel will prompt you to save.  

III.     Basic Formulas

Microsoft Excel is an electronic spreadsheet that automates manual calculations involved in accounting and bookkeeping. After you have typed the basic text and number entries in a spreadsheet cell, Excel can perform the math calculations for you. You will learn how to create formulas and functions to perform calculations in a spreadsheet. 

Example formulas are:  =D15+D18+D21 :  =B4-B12  :  =A10/B15   :  =(B16+C16)*1.07 Do not use any spaces in formulas.  Also, when creating formulas you may choose to either type the cell address or use the mouse to select the cell address.

1. Create Formula

You can create any type of math calculation on your own using the following mathematical operators:

The numeric keypad on the right side of the keyboard provides most of these operators. Excel follows the mathematical order of hierarchy where operators are processed in the order: negation, exponentiation, multiplication/division, and then addition/subtraction.  Use parentheses to clarify the order of calculation in a formula.

1. Basic steps for creating a formula:

1. Click in the empty cell which will contain the formula.
2. Type an equal sign (=).
3. Type the cell address or click the cell that contains the first number.
4. Type the math operator (+ – / * ^).
5. Type the cell address or click the cell that contains the second number.
6. Continue in this manner until the formula is complete.
7. Use parenthesis for clarification.
8. Press the Enter

The following image depicts various formulas in an Excel spreadsheet which will be created in a following exercise:

1. AutoSum

Adding is the most common math operation performed in Excel.  The Home ribbon

Basic Steps for using AutoSum:

1. Move to the empty cell that will contain the formula.
2. Click on the AutoSum
3. Proofread the formula that Excel provides, make any necessary changes.
4. Press the Enter key or click the check mark on the formula bar. 

Click back on the Budget sheet  tab.

1. Select cell B12, click on the AutoSum   button, and then press the Enter
2. Repeat the AutoSum process for cells C12, D12, E12.

Note: You can copy formulas that refer to empty cells.  After you type numbers in the empty cells, the formulas will be updated.

3. Click in cell B4 and change the amount $20 to $50, and then press Enter

Note: Formula results are updated automatically in Excel.  As you change any values that are referred to in a formula, the formula will reflect these changes.

4. Complete the AutoSum process for column E. Click in cell E4.

5. Click on the AutoSum button to add the Research expenses for the three semesters.
6. Press the Enter
7. Select the cell range B5:E5, click the AutoSum button, and then press Enter
8. Auto Fill this formula to the cell range É            E6:E11.
9. Copy this formula to the cell range E6:E11 by using the Auto Fill method illustrated above. Place the mouse pointer on the small solid square on lower right corner of cell E5, when the mouse pointer changes to a plus sign (Fill handle), then hold down on the right mouse button and drag the mouse down the designed cells (E6:E11) to copy the formula. The Auto Fill feature is explained in more detail in the Additional Features section on page 44.

Note: If Excel has to make a choice regarding adding values to the left (horizontally) of the formula cell or above (vertically) the formula cell, it will choose vertically.  This can occasionally present a problem.  Therefore, you may decide to select a range including the values to be added and the empty cell that will contain the formula then click the AutoSum button.  

1. Borders and Shading

Use borders to separate different areas of the spreadsheet.  Borders can be applied to one cell or a range of cells.  Use the Borders  button, on the Home ribbon to apply border styles. Also, the Fill Color  button will add or remove color/shading for a cell or range.

When you apply borders to data on your spreadsheet, you may want to print the data without gridlines (applying and removing gridlines is covered in the Preference section).

1. Use a border to emphasize the Total row for the Budget for Guest Speakers spreadsheet.
2. Select the cell range A12:E12.
3. Click on the Border drop-down arrow, located on the Home ribbon, and then choose the Thick Box Border  

3. Click on any single cell to deselect the range to see the border.
4. Select the cell range A12:E12
5. Click on the Fill Color drop-down arrow, located on the Home ribbon to add color/shading to this range.

1. Manual Formula

You can make manual entries for mathematical formula by typing the numbers, cell location, and mathematical function in the spreadsheet cell.

11. Click in Cell B14 and create a formula that will calculate 40% of the Fall publicity amount (e.g. =.4*B6).

12. Copy this formula to the cell the range C14:E14 by using the Auto Fill illustrated above.

Place the mouse pointer on the small solid square on lower right corner of cell B14, when the mouse pointer changes to a plus sign (Fill handle), then hold down on the right mouse button and drag the mouse across the designed cells (C14:E14) to copy the formula.