Excel in Thirty Minutes: Comprehensive Tutorial for Business Foundations

Introduction and Scope of the Tutorial

• Instructor: Salih Kaseli.

• Title: Excel in Thirty Minutes.

• Objective: This tutorial is a concise guide designed for absolute beginners to understand how Microsoft Excel works and how to utilize it effectively in business settings.

• Utility: The skills presented are intended to be tools in a technology toolkit for job applications or general professional competence.

• Software Version: The demonstration is based on Microsoft Excel 2016; however, the functionality remains consistent with previous versions of the software.

Workbook Structure and Interface Navigation

• Files and Tabs:   - Excel files are called workbooks or spreadsheets, similar to documents in Microsoft Word.   - Unlike Word, Excel is specifically designed to perform calculations using formulas.   - The interface contains tabs at the bottom of the screen. Initially, a workbook typically starts with one tab.

• Managing Tabs:   - Adding Tabs: New tabs are added by clicking the plus sign ($+$) at the bottom.   - Renaming: Right-click a tab and select "Rename" to change its label.   - Deleting: Tabs can be deleted by right-clicking and selecting "Delete" (with caution).

• Grid System:   - The spreadsheet consists of vertical columns and horizontal rows.   - Cells: The intersection of a column and a row is called a cell.   - Cell References: Cells are referenced by their column letter and row number (e.g., $B3$ is the intersection of Column B and Row 3).   - Formulas typically use these references rather than the literal values inside the cells.

Data Types and Formatting

• In-Cell Content: Cells can hold text, numbers, or formulas.

• Number Formats: Numbers can be formatted via a dropdown menu. Options include:   - General   - Currency   - Accounting   - Date   - Time   - Percentage

• Automatic Updates: A fundamental power of Excel is that changing a value in a referenced cell automatically updates the result of any formula linked to that cell.

• Column Adjustment:   - Manual: Users can drag the right edge of a column header to the right to widen it.   - Auto-fit: Double-clicking the edge of the column header will automatically adjust the width to fit the widest point of the data in that column.

• Merging Cells:   - To create titles, select a range of cells and use the "Merge and Center" feature. This combines the selected cells into one large cell and centers the text.

• Cell Styles and Themes: Users can apply pre-set "Cell Styles" or themes to format text and background properties quickly.

Basic Calculations and Formulas

• Formula Syntax: Every formula in Excel must begin with an equal sign ($=$). This sign signals to the computer that it must perform a calculation rather than treat the entry as text.

• The SUM Function:   - AutoSum Tool: Located at the top right of the ribbon. It automatically detects adjacent numerical ranges for addition.   - Manual Entry: To total a range manually, use the syntax: $= \text{SUM}(\text{Range})$.   - Ranges: A range is expressed using a colon. For example, $C5:E5$ includes every cell from $C5$ through $E5$.   - Example: $= \text{SUM}(C5:E5)$.

• AutoFill Feature:   - Located at the bottom-right corner of a selected cell (indicated by a small plus sign cursor).   - Allows users to drag a formula vertically (top to bottom) or horizontally (left to right) to repeat the logic for adjacent rows or columns.

• Other Common Statistical Functions:   - Average: $= \text{AVERAGE}(\text{Range})$. Returns the arithmetic mean.   - Minimum: $= \text{MIN}(\text{Range})$. Returns the lowest value.   - Maximum: $= \text{MAX}(\text{Range})$. Returns the highest value.

• Function Insert Tool:   - Under the "Formulas" tab, users can click "Insert Function" to search for specific calculations.   - This tool provides descriptions, syntax rules, and links to Microsoft help documentation for further explanation.

Practical Arithmetic for Business (Payroll Example)

• Scenario: Calculating gross pay, deductions, and net pay for employees (e.g., Hubert).

• Addition (Total Deductions):   - Example: Adding Deduction 1 and Deduction 2 using $= \text{SUM}(D4:E4)$.

• Subtraction (Net Pay):   - Formula: $\text{Net Pay} = \text{Gross Pay} - \text{Total Deductions}$.   - Excel equivalent: $= C4 - F4$.

• Multiplication (Annual Net Pay):   - To calculate yearly salary from monthly net pay: $= G4 \times 12$.

• Division (Weekly Pay):   - To calculate weekly pay from annual pay: $= \frac{H4}{52}$.   - In Excel syntax: $= H4 / 52$.

Data Management: Sorting and Filtering

• Sorting:   - Select a column and use the "Sort and Filter" tool.   - Options include Oldest to Newest (for dates) or A through Z (alphabetical).

• Filtering:   - Activating "Filter" places dropdown arrows on the headers.   - Criteria: Users can filter by names (e.g., only sales from "Frank"), regions (e.g., "Eastern"), or numerical thresholds (greater than, smaller than, top 10).   - Clearing Filters: Filters can be removed by unchecking the selection or selecting "Clear Filter" from the menu.

Data Visualization with Charts

• Quick Analysis Tool: In Office 2013 and 2016, selecting data often triggers a small icon in the bottom-right for quick chart creation (line charts, cluster charts, etc.).

• Recommended Charts: Under the "Insert" tab, "Recommended Charts" suggests the best visual representation based on selected data.

• Customization:   - Contextual tabs appear when a chart is selected to change layouts and colors.   - Multiple Data Sets: To graph multiple variables, select all corresponding labels and data columns before inserting the chart.

Inter-Worksheet Linking and Formatting

• Linking Data:   - To post a value from one worksheet to another, hit $=$ in the target cell, navigate to the source worksheet, click the desired cell (e.g., Hubert\'s pay), and hit Enter.   - This creates a dynamic link; updates in the employee list will reflect in the annual report sheet automatically.

• Error Indicators: The appearance of pound signs (####) in a cell indicates the column is too narrow to display the numerical value.

• Format as Table: Users can highlight data and select "Format as Table" to apply pre-designed headers and zebra-striping.

• Conditional Formatting: Uses data bars or color scales to format cells based on their value relative to others.

Printing and Output

• Page Layout:   - Margins: Customizable under "Page Layout" -> "Margins" -> "Custom Margins."   - Headers/Footers: Can be added to appear on every printed page.

• Print Area: To avoid printing entire worksheets, select a specific region and use "Print Area" -> "Set Print Area."

• Exporting: Spreadsheets can be shared via email, cloud, or exported as a PDF through the "Export" menu.

Integration with Microsoft Word

• Dynamic Linking to Word Reports:   - Copy data from Excel ($Ctrl+C$).   - In Word, use "Paste Special" and select "Paste Link" as an "Excel Object."   - Result: When the Word document is opened, it will ask to update links. Selecting "Yes" pulls any recent changes made in the Excel file into the Word report.

• Mail Merge:   - Set-up: Use Excel as the data file with field names in the first row.   - Process: In Word, go to "Mailings" -> "Start Mail Merge" -> "Step-by-Step Mail Merge Wizard."   - Fields: Insert merge fields (e.g., "Dear «First_Name»").   - Completion: Letters for all recipients (e.g., 38 customers) are generated automatically.

• Email Merge: Similar to mail merge but sends messages directly through Microsoft Outlook using the "To" field from the Excel file.