Computer Basics: Microsoft Excel

Microsoft Excel is a powerful spreadsheet application that is part of the Microsoft Office Professional Plus 2019 suite. It allows you to organize, analyze, and visualize data using a range of tools and features. This tutorial will cover the basics of using Excel, from creating a new workbook to performing advanced data analysis.

Contents

1. Getting started overview of the interface
2. Launching Excel
3. Creating and Saving a New Workbook
4. Basic Excel Operations
5. Formatting Cells
6. Working with Formulas and Functions
7. Data Visualization with Charts
8. Data Sorting and Filtering
9. Protecting and Sharing Workbooks
10. Print and Export Options
11. Keyboard Shortcuts for Efficiency
12. Advanced Excel Tools

Getting Started with Excel

1. Launching Excel

• Open Excel:
  1. Click on the Start menu or press the Windows key.
  2. Type “Excel” and click on Excel 2019 from the search results to open the application.

2. Understanding the Excel Interface

• Workbook and Worksheet:
  • An Excel workbook is a file that contains one or more sheets (worksheets). By default, a new workbook has one blank sheet named “Sheet1.”
• Ribbon:
  • The Ribbon is located at the top of the Excel window and contains tabs (Home, Insert, Page Layout, etc.) with groups of related tools and commands.
• Formula Bar:
  • The Formula Bar is located below the Ribbon. It shows the contents or formula in the selected cell.
• Name Box:
  • The Name Box displays the address of the active cell (e.g., A1) and allows you to name cells or ranges for easy reference.

3. Creating and Saving a New Workbook

• Create a New Workbook:
  1. Click on the File tab in the Ribbon.
  2. Select New and click on Blank Workbook.
• Save Your Workbook:
  1. Click on the File tab, then select Save As.
  2. Choose a location on your computer or cloud storage (such as OneDrive).
  3. Enter a file name and click Save.

4. Basic Excel Operations

• Enter Data:
  • Click on a cell and type the data you want to enter. Press Enter to move to the next cell below or Tab to move to the next cell on the right.
• Select Cells:
  • Click and drag the mouse to select a range of cells or click a single cell. Use Ctrl to select multiple non-adjacent cells.
• Copy and Paste Data:
  • Select the cells you want to copy, press Ctrl + C (Windows) or Cmd + C (Mac), select the destination cell, and press Ctrl + V (Windows) or Cmd + V (Mac).
• Undo and Redo:
  • Use Ctrl + Z (Windows) or Cmd + Z (Mac) to undo an action and Ctrl + Y (Windows) or Cmd + Y (Mac) to redo it.

5. Formatting Cells

• Change Font and Size:
  • Go to the Home tab, and in the Font group, choose your desired font and size.
• Apply Bold, Italic, or Underline:
  • Use the B, I, or U buttons in the Home tab under the Font group.
• Adjust Cell Alignment:
  • In the Home tab under the Alignment group, use the alignment buttons to align text to the left, center, or right.
• Format Numbers:
  • Select the cells you want to format. In the Home tab, click on the dropdown in the Number group to choose number formats (e.g., General, Currency, Percentage).

6. Working with Formulas and Functions

• Entering Formulas:
  • Click on a cell where you want the result to appear, type =, and then enter your formula (e.g., =A1+B1). Press Enter to calculate.
• Using Common Functions:
  • Excel provides various built-in functions to perform calculations:
    • SUM: Adds numbers (=SUM(A1:A5)).
    • AVERAGE: Calculates the average (=AVERAGE(A1:A5)).
    • IF: Checks a condition and returns one value if true and another if false (=IF(A1>10, “Yes”, “No”)).
    • VLOOKUP: Searches for a value in the leftmost column and returns a value in the same row from a specified column (=VLOOKUP(A1, B1:C10, 2, FALSE)).
• AutoSum Feature:
  • Select the cell below the numbers you want to sum and click on the AutoSum button in the Home tab under the Editing group.

7. Data Visualization with Charts

• Create a Chart:
  1. Select the data range you want to visualize.
  2. Go to the Insert tab and choose a chart type (e.g., Column, Line, Pie).
  3. Click on the desired chart type, and Excel will insert the chart into your worksheet.
• Customize Charts:
  • Click on the chart to activate the Chart Tools (Design and Format tabs) on the Ribbon, where you can customize the chart title, axis labels, data series, and more.

8. Data Sorting and Filtering

• Sort Data:
  1. Select the range of data you want to sort.
  2. Go to the Data tab and click on Sort.
  3. Choose the column by which you want to sort and the order (ascending or descending).
• Filter Data:
  1. Select the range of data you want to filter.
  2. Go to the Data tab and click on Filter.
  3. Use the drop-down arrows in the column headers to filter the data by criteria.

9. Using PivotTables

• Create a PivotTable:
  1. Select the data range you want to analyze.
  2. Go to the Insert tab and click on PivotTable.
  3. Choose where you want to place the PivotTable (new worksheet or existing worksheet) and click OK.
  4. Drag and drop fields from the PivotTable field list to the Rows, Columns, Values, or Filters areas to create your analysis.

10. Protecting and Sharing Workbooks

• Protect a Workbook or Worksheet:
  1. Go to the Review tab.
  2. Click on Protect Sheet or Protect Workbook.
  3. Set a password to prevent unauthorized editing.
• Share a Workbook:
  1. Click on the File tab, then select Share.
  2. Choose “Share with People” to send via email or save to a shared location like OneDrive.

11. Print and Export Options

• Print a Worksheet:
  1. Click on the File tab and select Print.
  2. Choose your printer, settings, and click on Print.
• Export as PDF:
  1. Click on the File tab.
  2. Select Save As and choose PDF from the “Save as type” dropdown menu.

12. Keyboard Shortcuts for Efficiency

• Quick Navigation and Editing:
  • Ctrl + S (Save), Ctrl + C (Copy), Ctrl + V (Paste), Ctrl + Z (Undo)
  • Ctrl + F (Find), Ctrl + H (Replace)
  • Ctrl + Arrow keys (Navigate to the edge of the data)

13. Advanced Excel Tools

• Data Analysis Toolpak:
  • Go to the File tab > Options > Add-ins. Select Excel Add-ins from the dropdown and check Analysis Toolpak. Use it for advanced data analysis like regression, ANOVA, etc.
• Macros:
  • Record and automate repetitive tasks by going to the View tab and selecting Macros > Record Macro.

Microsoft Excel 2019 is a versatile tool for managing data, performing calculations, and visualizing information. With these foundational skills, you can start creating and analyzing data efficiently. As you become more comfortable, explore advanced features like PivotTables, Macros, and the Data Analysis Toolpak to unlock Excel’s full potential.

Class Outline