Spreadsheet Basics PDF

SPREADSHEET BASICS GROUP 3 1 Working with the workbook and moving LEARNING around the worksheet CONTENT 2 Manipulating the worksheet 3 Formating the worksheet 4 Entering and Manipulating Data 5 Cel...

SPREADSHEET BASICS GROUP 3 1 Working with the workbook and moving LEARNING around the worksheet CONTENT 2 Manipulating the worksheet 3 Formating the worksheet 4 Entering and Manipulating Data 5 Cell referencing  MICROSOFT EXCEL A software program that uses speadsheets to organize numbers and data with formulas and functions.  01 Working with the Workbook and moving around the Worksheet PARTS, TABS and COMMANDS of Ms Excel  WORKBOOK WORKSHEET - is a spreadsheet - also known as program file that you spreadsheet consists of create in Excel. It is a file cell in which you can containing multiple enter and calculate data. spreadsheet. The cells are organized into columns and rows.  CELLS CELL ADDRESS - is a rectangle in a - is the combination of worksheet that is the the column letter and row intersection of a row and number that identifies a column. cell.  RIBBON TABS - is a command bar that - it contain groups of organizes features into commands that users can tabs at the top of the use to perform common Excel interface tasks.  QUICK ACCESS OFFICE BUTTON TOOLBAR - The Office button was - located in the upper-left introduced in Microsoft corner of Excel, either Office 2007 and was above or below the located in the top-left ribbon. It's always visible, corner of Microsoft Office regardless of which tab is programs. selected in the ribbon.  TITLE BAR SCROLL BARS - The title bar in Microsoft - allow users to scroll by Excel displays the name clicking the scroll arrows of the file that is or dragging the scroll box currently open. and have both a horizontal and vertical scroll bar.  HOME TAB COMMANDS Clipboard: Cut, Copy, Paste. Font: Adjust font, size, bold, italic, underline, color. Alignment: Align text, merge cells, wrap text. Number: Format numbers (currency, percentage, etc.). Editing: AutoSum, Fill, Clear, Sort & Filter. Uses: Formatting and basic editing.  INSERT TAB COMMANDS Tables: Insert tables. Illustrations: Add pictures, shapes, icons. Charts: Create bar, line, pie, and other charts. Text: Insert text boxes, headers, and footers. Symbols: Add symbols and equations. Uses: Add elements to enhance worksheets.  PAGE LAYOUT TAB COMMANDS Themes: Change document themes. Page Setup: Adjust margins, orientation, size. Scale to Fit: Adjust scaling for printing. Sheet Options: Show gridlines or headings. Uses: Control worksheet appearance and print settings.  FORMULAS TAB COMMANDS Function Library: Insert functions like SUM, AVERAGE, VLOOKUP. Defined Names: Create or edit named ranges. Formula Auditing: Trace precedents, show formulas. Calculation: Set calculation options (automatic/manual). Uses: Manage and audit formulas.  DATA TAB COMMANDS Get & Transform Data: Import external data. Sort & Filter: Organize data. Data Tools: Remove duplicates, validate data, consolidate. Forecast: Create data trends. Uses: Data analysis and management.  REVIEW TAB COMMANDS Proofing: Spelling and grammar check. Comments: Add and manage comments. Protect: Protect worksheets or workbooks. Uses: Proofread and secure worksheets.  VIEW TAB COMMANDS Workbook Views: Normal, Page Break, Page Layout. Show: Display rulers, gridlines, formula bar. Zoom: Adjust zoom level. Window: Arrange, split, or freeze panes. Uses: Control how the worksheet is displayed.  HELP TAB COMMANDS Search: Find features or help articles. Support: Access Microsoft support. Uses: Access help and tutorials.  02 Manipulating the Worksheet  COMMON COMMANDS IN EXCEL Cut, Copy, Paste Basic commands to move or duplicate data. Insert Add rows, columns, or cells. Delete Remove rows, columns, or cells. AutoFill Automatically fill cells with data following a pattern or sequence (e.g., dates, numbers, or text). MOVING AND ADUSTING DATA Drag and Drop Select a cell or range, drag the border to a new location, and drop it to move the data. Insert and Delete Rows/Columns Right-click on row numbers or column letters to insert or delete them. SORTING AND FILTERING DATA Sort Organize data in ascending or descending order based on a selected column (found under the Data tab). Filter Hide data temporarily based on specific criteria (found under the Data tab).  FIND AND REPLACE Ctrl + F Open the Find dialog to search for specific data. Ctrl + H Open the Replace dialog to find and replace data throughout the worksheet.  03 Formatting the Worksheet  FORMATTING CELLS Align text to the left, right, Change font or center. You can also style, size, bold, italics, TEXT AND wrap text and merge cells. underline. FILL COLOR BOARDERS FONT ALLIGNMENT Modify the color of the Apply borders to text and the background cells or ranges to of the cell. separate data visually.  NUMBER FORMATTING Under the Number Group on the Home Tab, you can format numbers, dates, percentages, and currencies. Format Cells Dialog Box: Right-click a cell, select Format Cells to open advanced formatting options like date, time, text, or custom formatting.  CONDITIONAL FORMATTING Apply conditional formatting to cells that meet certain conditions (e.g., highlight values above a certain number or apply color scales). Found under the Home Tab > Conditional Formatting.  CELL STYLES Use predefined styles to quickly format headings, titles, or data and model in your worksheet.  04 Entering and Manipulating Data  ENTERING DATA TEXT Simply type ( Marie Dale ) NUMBERS Numeric Data ( 5000 ) DATE/TIME Standard Formats ( 11/16/2024 ) MANIPULATING DATA USING FUNCTIONS Excel offers built-in functions for common calculations like SUM(), AVERAGE(), HYPERLINK (),COUNT(), MAX (), SIN () and IF().  USING FILL HANDLE The Fill Handle - a small square at the bottom- right corner of a selected cell, allows you to drag and fill data across cells in a sequence or pattern (e.g., copying a series of dates or numbers). USING FLASH FILL Flash Fill - is an automatic data-entry tool that fills data based on a pattern you establish. For example, if you have a column with full names ( "Marie Dale” ) and you start typing just the first name ("Marie"), Excel will automatically suggest filling the rest of the column with the first names, and you can press Enter to accept the suggestion. You can access Flash Fill by going to the Data Tab > Flash Fill or pressing Ctrl + E.  05 Cell Referencing - determines how Excel identifies and uses cell values in formulas TYPES OF CELL REFERENCES ❑ Relative Reference - A reference that changes based on where the formula is copied or moved. Example: If you have the formula =A1 + B1 in cell C1 and copy it to cell C2, it automatically adjusts to =A2 + B2. Use case: Ideal when you need formulas to adapt based on their location (e.g., summing rows or columns).  TYPES OF CELL REFERENCES ❑ Absolute Reference - A reference that does not change no matter where the formula is copied or moved. Example: $A$1 always refers to cell A1. How it works: Adding $ locks both the row and column. $A$1: Locked row and column $A1: Locked column only A$1: Locked row only Use case: Perfect for fixed values like tax rates or constants used in multiple calculations. TYPES OF CELL REFERENCES ❑ Mixed Reference - A combination of relative and absolute references. Either the column or the row is fixed while the other is relative. Examples: $A1: Column A is fixed, but the row changes when copied. A$1: Row 1 is fixed, but the column changes when copied. Use case: Useful for referencing a specific row or column in dynamic formulas.