Introduction to Microsoft Excel 2013

Introduction to Microsoft Excel 2013

INTRODUCTION TO
MICROSOFT EXCEL 2013

INTRODUCTION TO MICROSOFT EXCEL 2013

  1. Introduction to Microsoft Excel 2010
  2. Start Excel
  3. Working with Excel
  4. Change Excel’s View
  5. Work with an Existing Workbook
  1. Working with Microsoft Excel 2013
  2. Create Workbooks
  3. Save Workbooks
  4. Enter and Edit Basic Data in a Worksheet
  5. Use Data Types to Populate a Worksheet
  6. Cut, Copy, and Paste Data
  1. Using the File Tab
  2. Printing Worksheets
  3. Create New Workbooks using Templates
  1. Using Basic Formulas
  2. Understand and Display Formulas
  3. Build Basic Formulas
  1. Using Functions
  2. Exploring Functions
  3. Summarize Data with Functions
  4. Use a Financial Function
  5. Uncover Formula Errors

SECTION I: INTRODUCTION TO MICROSOFT EXCEL 2013

LESSON ITEM #1: Starting Excel

Action:

  1. Click the Start Screen.
  2. Find Microsoft Office folder.
  3. Click Excel 2013.

LESSON ITEM #2: Working with Excel

Navigating the Ribbon

Action:

  1. Click the Home tab to activate it.
  2. Notice the commands and groups located on the Home Ribbon.
  3. Click the Insert tab.
  4. Notice the commands and groups located on the Insert Ribbon.
  5. Click through the remaining tabs.
  6. Notice the different commands and groups located on each Ribbon.
  7. Click back to the Home tab.
  8. Notice the Undo, Redo, and Save buttons are all located at the top left corner of Microsoft Word.

LESSON ITEM #3: Changing Excel’s View

Change Excel’s View

Action:

  1. Click the View tab.
  2. In the Workbook Views group, click Page Layout.
  3. Notice you can now see the margins, where page breaks occur, and where you can add a header and/or footer.
  4. In the Workbook Views group, click Normal to return to the default view.

Note: Notice all the other views that are available. Those view options are also available at the bottom-left hand side of the workbook.

LESSON ITEM #4: Working with an Existing Workbook

Open an Existing Workbook

Action:

  1. In Excel, click the File tab.
  2. Click Open.
  3. Click Computer and then click Browse.
  4. Find Understanding Excel Practice.
  5. Click Open or double-click on practice.

Navigate a Worksheet

Action:

  1. Press Ctrl + End and move to the end of the document.
  2. Press Ctrl + Home and move to the beginning of the document.
  3. Click in the Name Box, type A3 and press Enter.
  4. Press Ctrl + Down Arrow to go to the last row of data.
  5. Press Ctrl + Right Arrow to go to the last column in the range of data.
  6. Press Ctrl + Home.
  7. Press Scroll Lock at the top left hand side of the keyboard.
  8. Press the Right Arrow key. This moves the active column one column to the right, and the whole worksheet moves.
  9. Press Scroll Lock again to turn it off.
  • SECTION I: COMPLETE!

SECTION II: Working with Microsoft Excel 2013

LESSON ITEM #5: Creating Workbooks

Create a Workbook

Open Excel

  1. Go to the File tab.
  2. Click the New command.
  3. Double-click on Blank Workbook.
  4. In cell A1, type Workforce Centers.
  5. Press Enter.
  6. In cell A2, type 2021 N Amidon.
  7. In cell A3, type Wichita, KS 67203
  8. In cell B1, type Phone Calls and press Enter.

Note: Opening a new Workbook is helpful when you need a quick work area to complete another task while in the middle of a workbook.

Switching Between Open Workbooks

Action:

  1. At the bottom of the screen, you should see the Excel icon.
  2. Hover the mouse over the icon.
  3. Notice two windows appear.
  4. Click Understanding Excel Practice to get back to the first workbook.

LESSON ITEM #6: Saving Workbooks

Name and Save a Workbook

Action:

  1. Click the File tab and click Save As.
  2. Double-click Computer to open the Save As dialog box and click Browse.
  3. Save the workbook paying attention to the file type.

LESSON ITEM #7: Entering and Editing Basic Data in a Worksheet

Entering Basic Data

Action:

  1. Open a new blank workbook.
  2. Click cell A1, type Workforce Alliance, and Press Enter.
  3. In cell A2 type Employee List and press Tab.
  4. Type Extension in B2 and press Tab.
  5. Type Department and press Tab.
  6. Type PTO Days and press Tab.
  7. Type Next Performance Review and press Tab.
  8. Type Write Ups and press Tab.
  9. Type ID Number and press Enter.
  10. Click cell A3 type: Michael Crave and press Tab.
  11. Type 103 and press Tab.
  12. Enter the following information for the remaining rows:
  13. Business153022568
  14. Click cell A3 and notice that the complete entry for Michael Crave appears in the formula bar.
  15. Click cell A4 and enter the following information on that row:
  16. Orlando Meyers 102Career Center51502569
  17. Press Enter.
  18. In row 5 type the following information:
  19. Amanda Owens 104Business1010612570
  20. Press Enter
  21. In row 6 type the following information:
  22. Nick Koons 110Administrative 3020102571

Note: If you type the wrong data, you can click the cell and retype the entry.

Change the Column Width

Action:

  1. Move the mouse pointer between the columns A and B, the pointer should change to a double-headed arrow.
  2. Double-click.
  3. Highlight the entire column C.
  4. On the Home tab, in the Cells group, click the Format drop-down.
  5. Choose Auto-fit column width.
  6. Widen the remaining columns.

Edit a Cell’s Contents

Action:

  1. Click in cell A1, type Workforce, and press Enter.
  2. Notice, what was previously in A1 disappears.
  3. Click cell A1. Notice that the formula bar displays Workforce.
  4. Click after Workforce in the formula bar, add a space, and type Alliance.
  5. Press Tab.
  6. Click cell A1 and in the formula bar, double-click on Alliance to select it. Type Centers.
  7. Press Enter.
  8. Click cell A2 and notice the formula bar displays Employee List.
  9. Press Enter.
  10. Select row 6 to select the entire row and hit the Delete key on the keyboard.

LESSON ITEM #8: Using Data Types to Populate a Worksheet

Enter Labels and Use AutoComplete

Action:

  1. Open a new blank workbook.
  2. In cell A1 type Workforce Centers, and press Enter.
  3. Type Monthly Traffic and press Enter.
  4. Click cell A4 and type Workforce Professional and press Tab.
  5. In cell B4 type Date and press Tab.
  6. In cell C4, type January Costs and press Enter.
  7. In cell A5, type Randy Bloom, and press Enter.
  8. In cell A6, type Daniel Overland, and press Enter.
  9. In cell A7, type Kathy Aims and press Enter.
  10. Type Nancy Drew and press Enter.
  11. Click cell A9 and type the letter ‘R’.
  12. Notice the AutoComplete displays the entry for Randy Bloom.
  13. Type the letter ‘Y’.
  14. Notice the AutoComplete disappears.
  15. Finish typing the entry for Rylie Calms and press Enter.
  16. Type the letter ‘R’.
  17. Notice no AutoComplete appears.
  18. Type the letter ‘A’ and notice that the AutoComplete appears for Randy Bloom.
  19. Press Esc key to undo the entry.
  20. Increase the column widths to show column A.

Enter Numeric Values

Action:

  1. Click cell C5, type $3,235, and press Enter.
  2. Click cell C6, type 1,356, and press Enter.
  3. Type 2536 and press Enter.
  4. Type 5632 and press Enter.
  5. Type 1462 and press Enter.

Enter Dates

Action:

  1. Click cell B5, type 1/3/2016, and press Enter.
  2. Click cell B6, type 1/20/2016, and press Enter.
  3. Type 1/21 and press Enter.
  4. In cell B8, type 1/27/16 and press Enter.
  5. Type January 29, 2016 and press Enter.

LESSON ITEM #9: Cutting, Copying, and Pasting Data

Copy, Cut and Paste Data

Action:

  1. Navigate to Understanding Excel Practice.
  2. Select A2:C36 and in the Clipboard group on the Home tab, click the Cut button.
  3. Select row 105 and right-click. Choose Insert cut cells on the shortcut menu.
  4. Select A2:C18 and in the Clipboard group on the Home tab, click the Copy button.
  5. Press the Esc key to exit the Copy command.
  6. Select A2 and click the Copy button.
  7. Select A3 and click the Clipboard Paste button.
  8. Hit the undo button.
  • SECTION II: COMPLETE!

SECTION III: The File Tab

LESSON ITEM #10: Printing Worksheets

Print and Preview a Document

Action:

  1. Go to the File Tab.
  2. Select the Print Command.
  3. To Print your Worksheet, click the Print button.

Note: Notice all the available options that can be customized before printing.

LESSON ITEM #11: Accessing and Using Excel Templates

Select a Template from the New Tab

Action:

  1. Click the File Tab and select New.
  2. In the Online Templates search box, search for Project Tracker.
  3. Choose Project Tracker and click the Create button.
  4. After reviewing the template, click the File Tab and close without saving.
  5. Click the File Tab and select New.
  6. Search for Any year calendar template and click the Create button.
  7. Click on the year and use the up or down arrow to change to the current year if necessary.
  • SECTION III: COMPLETE!

SECTION IV: Using Basic Formulas

LESSON ITEM #12: Understanding and Displaying Formulas

Display Formulas

Action:

  1. Open a New Blank document.
  2. Click cell A1.
  3. Type: =7+8*3/2-4
  4. Press Enter. You just entered a formula!
  5. Click cell A1. Notice the formula is found in the Formula Bar.
  6. Double click in cell A1. The formula is now found in both the active cell and the formula bar.
  7. Press Enter.

Note: Formulas should be typed without spaces, but if you type spaces, Excel eliminates them when you press Enter. All formulas begin with the equal sign (=).

LESSON ITEM #13: Building Basic Formulas

Create a Formula that performs Addition

Action:

  1. Open Basic Formulas Practice.
  2. In cell B3 type your name.
  3. Starting in cell B6, enter the following information for row 6:
  4. 253408358388
  5. Press Enter.
  6. Starting in cell B7, enter the following information for the rest of the rows:
  7. 308450408250
  8. 428308338532
  9. 358354333156
  10. 365563564253
  11. 455405450358
  12. 366269489108
  13. 333222535225
  14. 532125452366
  15. 256665541235
  16. 455655455254
  17. 351245453532
  18. In cell F6, type the equal (=) sign, type 253+408+358+388, and press Enter.
  • SECTION IV: COMPLETE!

SECTION V: Using Functions

LESSON ITEM #14: Exploring Functions

Explore Functions

Action:

  1. Open a New Blank Document.
  2. Click the Formulas tab.
  3. Excel arranges function by category in the Function Library group.
  4. Click the Financial button arrow to display a drop-down list of functions.
  5. You can also find a function using the Insert Function dialog box.
  6. On the Formulas tab or in the formula bar, click the Insert Function button.
  7. In the Insert Function dialog box, type a description of what you want to do.
  8. Type Date and click Go.
  9. With Date selected in the Select a function list, click OK. The Function Arguments dialog box opens.
  10. Enter the current year, the number of the current month, and the number of the current day.
  11. Click OK.

Use the TODAY function

Action:

  1. In cell A5, type =TODAY() and press Enter.
  2. The current date now displays.

Note: Even functions that require no arguments must still have parentheses following the name as in =TODAY().

LESSON ITEM #15: Summarizing Data with Functions

Use the SUM Function

Action:

  1. Open Understanding Excel Practice.
  2. In cell C36, type =SUM(C2:C35) and press Enter.
  3. Click in cell C43. Click the Formulas tab and then click the top part of the AutoSum button.
  4. Notice the SUM function appears with arguments filled in.
  5. Press Enter.
  6. Use the SUM function to total the remaining categories.

Use the AVERAGE Function

Action:

  1. In cell I16, type Average and press Enter.
  2. In cell I17, type =AVERAGE(C2:C35) and press Enter.
  3. The result is your average amount of Product Sales for Tools.
  4. In cell I17, type =AVERAGE(C37:C42) and press Enter.
  5. The result is your average amount of Product Sales for Soils/Sands.

LESSON ITEM #16: Using a Financial Function

  • PMT Function: requires a series of inputs regarding interest rate, loan amount (principal), and loan duration, and then calculates the resulting loan payment.

Use the PMT Function

Action:

  1. Open a New Blank document.
  2. In cell A1, type Electronics and press Enter.
  3. In cell A2, type Interest and press Enter.
  4. In cell A3, type Years and press Enter.
  5. In cell A4, type Loan Amt and press Enter.
  6. In cell A5, type Payment and press Enter.
  7. In cell B2, type 7.5% and press Enter.
  8. This is the interest rate on the loan.
  9. In cell B3, type 2 and press Enter.
  10. This is the number of years in which the loan will be repaid.
  11. In cell B4, type 2500 and press Enter.
  12. This is the loan amount, which will cover the total cost of the equipment.
  13. In cell B5, type = -PMT(B2/12 , B3 * 12, B4) and press Enter.
  14. This is your calculated monthly payment.

LESSON ITEM #17: Uncovering Formula Errors

Review an Error Message

Action:

  1. Open practice.
  2. Click in cell G9.
  3. A small exclamation point appears next to the cell.
  4. Click the drop down to read the error.
  5. Edit the formula in the formula bar to remove the (-) and press Enter.
  6. Click in cell G10. Click the small, yellow warning icon to the left of the cell. A pop-up menu appears.
  7. In this menu, select Help on this error.
  8. Excel Help opens to a page on information regarding formula errors. Browse the help topics to see if any of the potential solutions apply to your situation.
  9. In the formula bar, type SUM before the equation and press Enter.
  10. Don’t forget to add parentheses () surrounding your equation.
  • SECTION V: COMPLETE!

CONGRATULATIONS – YOU’VE FINISHED!!!

Page 1 of 12

“Equal Opportunity Employer/Program - Auxiliary aids and services are available upon request to individuals with disabilities.”

The hearing impaired may contact the Workforce Center by calling the Kansas Relay Center at 1-800-766-3777

Top View