IBM Excel Data Fundamentals 1

Basic Spreadsheet Terminology

• Spreadsheet software like Excel organizes data using workbooks, which are stored as .XLSX files.
• A workbook contains one or more worksheets, each represented by a tab.
• Default names for worksheet tabs are Sheet1, Sheet2, etc., which can be renamed for clarity (e.g., January Sales).
• The active worksheet is highlighted, and can be reordered by dragging or via the Move or Copy option.
• Worksheets consist of cells, arranged in columns (labeled A, B, C) and rows (labeled 1, 2, 3).
• Each cell is identified by its reference, such as M20, combining its column letter and row number.
• Cell ranges are denoted with a colon, e.g., D9:D19, and can specify multiple rows and columns.
• A 3D reference allows you to refer to cells in another worksheet.

Navigating a Spreadsheet

• The Excel ribbon includes tabs (e.g., Home, Insert, Formulas) with groups for organized access to features.
• Hiding the ribbon can create more workspace, toggled by double-clicking a tab or using CTRL+F1.
• Use arrow keys for one-cell movement, Page Up/Page Down for faster navigation, and scroll bars for large datasets.
• CTRL shortcuts simplify navigation: CTRL+Home to go to A1, CTRL+End to last data cell, CTRL+Down to the end of a column.
• Selecting data can involve clicking a cell, using SHIFT for adjoining cells, or CTRL for non-contiguous selections.
• The entire worksheet can be selected with a click in the top left corner or using CTRL+A for only populated cells.

Viewing and Editing Data

• The Zoom Slider allows users to adjust view for detailed analysis.
• Options include predefined zoom levels, custom zoom, and zooming into a selection.
• The Split button provides simultaneous views of different data areas.
• Freeze Panes keeps certain rows/columns visible during scrolling; select a cell to the right and below to freeze.
• Use CTRL+F6 to switch between multiple open workbooks.
• Data entry can be performed quickly by using Tab to move across cells and resizing columns remains essential for visibility.
• Editing cells can be done directly, using F2 for in-line editing, double-clicking, or editing in the formula bar.

Copying, Filling, and Formatting Data

• Data can be moved by dragging selected cells, indicated by a Move pointer, or copied by holding CTRL while dragging.
• Use CTRL+C and CTRL+V shortcuts for copy and paste functions.
• Paste options like Keep Source Column Widths ensure formatting remains consistent between worksheets.
• AutoFill automates data entry for sequential patterns (like months or days of the week).
• To define a filling pattern, always select cells that establish the sequence before using the fill handle (e.g., entering Mon and Wed to fill the weekdays).
• For numerical patterns, inputting a base value allows for progression; AutoFill increments based on identified patterns (e.g., filling in increments of 5).

Shortcuts Overview

• Close Workbook: CTRL+W, Open: CTRL+O, Save: CTRL+S
• Copy: CTRL+C, Cut: CTRL+X, Paste: CTRL+V, Undo: CTRL+Z
• Move around: Up/Down/Left/Right Arrow keys, Page Down/Up for faster scroll
• Edge of data: CTRL+Arrow keys (e.g., CTRL+Down)
• Navigate sheets: CTRL+Page Up/Page Down
• Editing and timestamps: F2 for editing, CTRL+Shift+: for time, CTRL+; for date.### Formatting Data in Excel
• Two types of formatting:
  • Cell formatting involves applying fill colors, bold borders, and bold text.
  • Data formatting specifies how to display data, such as text, numbers, or currencies.
• Use CTRL+SHIFT+Right Arrow to select multiple headings in cells A3 to P3.
• Access styles in the Home tab and apply a color style to selected cells.
• Ensure to make important headings and categories bold for better visibility.
• Additional formatting can include italicizing specific data (e.g., Model column).
• Adjust font sizes and styles to enhance readability.
• Use mouse or shortcut keys (CTRL+SHIFT+Right Arrow then Down Arrow) to select all data and apply borders.
• Sales figures formatting can be refined to show only two decimal places using the Decrease Decimal button.
• Incorrect dates appearing instead of model names can be amended by changing the cell format to Text and re-entering model numbers.
• Currency format can be applied by selecting columns and choosing the appropriate currency format from the dropdown.

Basics of Formulas

• Formulas begin with an equal sign, followed by functions indicating calculations, such as SUM or arithmetic operators (+ for addition, - for subtraction).
• Operators categorize calculations: arithmetic, comparison, text concatenation, and reference.
• A basic formula example: =SUM(B5*20) for performing operations on cell values.
• Formulas can reference a range by using colons for a continuous range (e.g., E2:E4).
• Formulas can be auto-filled across cells using the fill handle, adapting to relative cell positions automatically.
• To compute tax or subtotals, create additional rows like Subtotals and Tax at 20%, then use relative references.
• AutoSum feature automates common calculations like sum, average, and others, activated by the shortcut Alt + =.

Introduction to Functions

• Common statistical functions include Average, Min, Max, Count, and Median.
• AutoSum assists in calculating various statistics but requires modification of ranges for accuracy.
• The median function identifies the middle value in a range, relevant for odd/even sets of data.
• Excel includes over 400 functions, organized in categories like Financial, Logical, Text, Date/Time, Lookup & Reference, and more for specific calculations.
• Functions can be found through the Formulas tab or by using the "Insert Function" dialog for searching by name or category.

Referencing Data in Formulas

• Relative references adjust based on the formula's position, while absolute references remain constant across copies (invoked with $).
• Mixed references use a combination of relative and absolute references for either rows or columns.
• Understanding these reference types is crucial for accurate formula replication and operation.
• Formula errors are common; types include #NAME? for unrecognized text and adjusting column width if multiple hash symbols appear.

Handling Formula Errors

• Errors reveal issues in formula syntax or cell reference selection.
• Fixing syntax errors (e.g., using * instead of x) can be identified by green triangles and corrections suggested in the error dropdown.
• Error-checking options allow users to adjust settings for specific error checks according to personal preferences.

Excel Features and Data Management

• Excel offers multiple features for modifying views, making data entry and editing user-friendly.
• Data can be moved or copied easily within a worksheet or across different worksheets.
• The AutoFill feature allows for automatic data entry according to patterns or sequences.

Cell and Data Formatting

• Both cells and individual data entries in Excel can be formatted for better presentation and readability.

Formulas in Excel

• Formulas consist of various components and can perform calculations using direct numbers or cell references.
• The Fill Handle tool is available for quickly copying formulas to adjacent cells, enhancing efficiency.
• Excel includes various function categories, allowing users to search for specific functions either by name or category.

Cell References in Formulas

• Formulas can reference cells through relative, absolute, or mixed references, enabling flexible data handling.
• To make a cell reference absolute (fixed), include a dollar symbol ($) in the formula.

Error Handling in Excel

• In case of formula errors, Excel's built-in error-checking features can assist in identifying and resolving issues.

Explore the fundamental terminology and elements of spreadsheet software, focusing on Excel. This quiz will guide you through essential concepts that are critical for utilizing spreadsheets effectively as a Data Analyst. Gain insights into both desktop and online versions of Excel to enhance your data management skills.